Cascade combo box help!
in [Forms]
|
Prev: Form filter problem
Next: Format Memo field
From: Marshall Barton on 19 Nov 2009 13:32 Does "go wrong" still mean you are being prompted for something like: Forms!formname.subformcontrolname.Form.comboname If so and you have double checked the spelling of everything, then I suspect that you have used the name of the form object displayed in the subform control instead of the name of the subform control on the main form. The subform control on the main form is often named the same as the form object it displays, but they can be different. -- Marsh MVP [MS Access] Hannah wrote: >Thanks Marshall. I did change the subform record source and I changed it >using the names of my own controls in place of your dummies so really not >sure what's making it go wrong? Any ideas? > >"Marshall Barton" wrote: >> The fact that you were prompted to enter somethng means that >> you misspelled whatever the prompt box was asking for. >> >> You may be confused about what you were supposed to do. A >> combo box does not have a record source, they do have a >> RowSource. Either way, you should not mess with the combo >> box. > >> Hannah wrote: >> >Thanks for such a speedy reply! I tried doing what you said but I got a >> >message saying "Enter Parameter Value" and then the bit of code you gave me. >> >I've checked I entered it correctly and in the right place but beyond that >> >I'm a bit stumped about what's causing the problem. Could it be clashing with >> >other code? I previously had the cboHarbour record source as the Harbour >> >table.
From: Hannah on 20 Nov 2009 04:46 You were right Marshall! That was the problem - thought I'd checked it but clearly not! Thanks again for being so patient, I'm still horribly bad at explaining when things don't work for me! So saying, I've hit a bit of a new issue: when I choose something from my cboHarbour, nothing shows up. I think this might be to do with the code I have on the Row Source property of the combo box which is: SELECT DISTINCTROW [Harbours].[HID], [Harbours].[HarbourName] FROM Harbours WHERE ((([Harbours].[CID]) Like [forms]![frmTrial]![combo28])); (Harbours is the actual table). If this is the problem, how do I match it up with the query my form's now based on? Again, apologies for really bad explanation! Thanks for your help so much - I feel like I'm finally getting there. "Marshall Barton" wrote: > Does "go wrong" still mean you are being prompted for > something like: > > Forms!formname.subformcontrolname.Form.comboname > > If so and you have double checked the spelling of > everything, then I suspect that you have used the name of > the form object displayed in the subform control instead of > the name of the subform control on the main form. The > subform control on the main form is often named the same as > the form object it displays, but they can be different. > -- > Marsh > MVP [MS Access] > > > Hannah wrote: > >Thanks Marshall. I did change the subform record source and I changed it > >using the names of my own controls in place of your dummies so really not > >sure what's making it go wrong? Any ideas? > > > >"Marshall Barton" wrote: > >> The fact that you were prompted to enter somethng means that > >> you misspelled whatever the prompt box was asking for. > >> > >> You may be confused about what you were supposed to do. A > >> combo box does not have a record source, they do have a > >> RowSource. Either way, you should not mess with the combo > >> box. > > > >> Hannah wrote: > >> >Thanks for such a speedy reply! I tried doing what you said but I got a > >> >message saying "Enter Parameter Value" and then the bit of code you gave me. > >> >I've checked I entered it correctly and in the right place but beyond that > >> >I'm a bit stumped about what's causing the problem. Could it be clashing with > >> >other code? I previously had the cboHarbour record source as the Harbour > >> >table. > . >
From: Hannah on 20 Nov 2009 05:06 I take back my first attempt at trying to work out why no records show! What happens when I choose something in cboHarbour is that I get the the message "Run-time error '3021': no current record" I then get taken to my code for sfrmHarbours which is: Private Sub cboHarbour_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[HID] = " & Str(Me![cboHarbour]) Me.Bookmark = rs.Bookmark End Sub The section of the code highlighted is the bookmark property. How can I get around this?! Thank you for all your help again! "Marshall Barton" wrote: > Does "go wrong" still mean you are being prompted for > something like: > > Forms!formname.subformcontrolname.Form.comboname > > If so and you have double checked the spelling of > everything, then I suspect that you have used the name of > the form object displayed in the subform control instead of > the name of the subform control on the main form. The > subform control on the main form is often named the same as > the form object it displays, but they can be different. > -- > Marsh > MVP [MS Access] > > > Hannah wrote: > >Thanks Marshall. I did change the subform record source and I changed it > >using the names of my own controls in place of your dummies so really not > >sure what's making it go wrong? Any ideas? > > > >"Marshall Barton" wrote: > >> The fact that you were prompted to enter somethng means that > >> you misspelled whatever the prompt box was asking for. > >> > >> You may be confused about what you were supposed to do. A > >> combo box does not have a record source, they do have a > >> RowSource. Either way, you should not mess with the combo > >> box. > > > >> Hannah wrote: > >> >Thanks for such a speedy reply! I tried doing what you said but I got a > >> >message saying "Enter Parameter Value" and then the bit of code you gave me. > >> >I've checked I entered it correctly and in the right place but beyond that > >> >I'm a bit stumped about what's causing the problem. Could it be clashing with > >> >other code? I previously had the cboHarbour record source as the Harbour > >> >table. > . >
From: Marshall Barton on 20 Nov 2009 09:31
Hannah wrote: >I take back my first attempt at trying to work out why no records show! What >happens when I choose something in cboHarbour is that I get the the message >"Run-time error '3021': no current record" I then get taken to my code for >sfrmHarbours which is: > >Private Sub cboHarbour_AfterUpdate() > ' Find the record that matches the control. > Dim rs As Object > > Set rs = Me.Recordset.Clone > rs.FindFirst "[HID] = " & Str(Me![cboHarbour]) > Me.Bookmark = rs.Bookmark > >End Sub No current record means that the recordset is empty, something tried to move the current record off the end of the recordset or, in your case, the find operation did not find a match. Your code below has some minor issue and a major problem. The big one is that you need to check if the FindFirst found a record or not. If it did not find a match, you can not set the form's bookmark as if it did find a match. I would code that more like this air code: Private Sub cboHarbour_AfterUpdate() ' Find the record that matches the control. With Me.RecordsetClone If .RecordCount > 0 Then .FindFirst "HID = " & Me!cboHarbour If Not .NoMatch Then Me.Bookmark = .Bookmark Else MsgBox "No matching record" End If Else MsgBox "There are no records to seach" End If End With End Sub Note that HID must be a number type field in the table, If it were a Text field, it would be: .FindFirst "HID = """ & Me!cboHarbour & """" If you get the no matching record message, check to make sure the combo box's BoundColumn corresponds to the HID field in its RowSource. -- Marsh MVP [MS Access] |