Cascade combo box help!

  • Thread starter Thread starter Hannah
  • Start date Start date
H

Hannah

Hello,
I'm a self-taught access newbie so please accept my apologies if the answer
to this is already out there and I just haven't recognised it!

I've got a frmCountry and a sfrmHarbours, each has a combo box on.
cboCountry (main form) lets you choose a country and cboHarbour (sub form)
lets you choose a harbour from that country. frmCountry also has all the
fields for country (e.g. contact person, address) and sfrm Harbour also has
all the fields about the harbour (number of vessels, comments, type). To get
the forms to open blank, I've used this code (although I realise this isn't
ideal and somebody elsewhere suggested using a filter but I've no idea how to
do this):

DoCmd.RunCommand acCmdRecordsGoToNew

Which is great because my form opens blank BUT as soon as I make a selection
in cboCountry, cboHarbour remains blank but a record still shows up in all
the Harbour fields. What can I do to get the cboHarbour AND Harbour fields to
remain blank until I make a selection in cboHarbour? Thanks already for your
help - the advice and support I've received from this group has been amazing!
Feel free to suggest ways to improve my design too - I've just sort of
muddled through so there may well be a much better way to be doing this!
 
Hannah said:
I've got a frmCountry and a sfrmHarbours, each has a combo box on.
cboCountry (main form) lets you choose a country and cboHarbour (sub form)
lets you choose a harbour from that country. frmCountry also has all the
fields for country (e.g. contact person, address) and sfrm Harbour also has
all the fields about the harbour (number of vessels, comments, type). To get
the forms to open blank, I've used this code (although I realise this isn't
ideal and somebody elsewhere suggested using a filter but I've no idea how to
do this):

DoCmd.RunCommand acCmdRecordsGoToNew

Which is great because my form opens blank BUT as soon as I make a selection
in cboCountry, cboHarbour remains blank but a record still shows up in all
the Harbour fields. What can I do to get the cboHarbour AND Harbour fields to
remain blank until I make a selection in cboHarbour?
Click to expand...


Change the harbour form's record source to a query with a
criteria for the harbourID field:
=Forms!countryform.harboursubform.Form.harbourcombobox
 
Hi Marshall,
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. Thanks again for all your help! Hannah
 
Hannah said:
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.
Click to expand...


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.

You were supposed to change the **subform** RecordSource and
the thing I posted was only an example because you did not
provide the names of your main form, subform *control* (on
the main form) or combo box. You are supposed to replace
the dummy names I used with the real names you have for
those objects.
 
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? Thanks again for all your help.
 
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.
 
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 said:
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]

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?
Click to expand...
.
Click to expand...
 
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 said:
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]

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?
Click to expand...
.
Click to expand...
 
Hannah said:
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
Click to expand...


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.
 
Back
Top