Combo Boxes

[round]

Hello please can some one help me with my issue. I have read the Access FAQ's about cascading combo boxes and I have had no success. Hopefully I have explained well enough.

I have two comdo boxes on my main form.
The first you can search for a persons record via surname and the second you can search via their ID number.

Using either will populate the details section on the form.

combo 1 (surname search) uses:

SELECT DropDown.surname FROM DropDown ORDER BY DropDown.surname;

as the row source.

It also has the event procedure on after update:

Private Sub Combo45_AfterUpdate() 
    ' Find the record that matches the control. 
    Dim RS As Object 

    Set RS = Me.Recordset.Clone 
    
    RS.FindFirst "[surnamename] = """ & Me![Combo45] & """" 
    If Not RS.EOF Then Me.Bookmark = RS.Bookmark 
    
    
End Sub

The second combo box is pretty much the same:

SELECT DropDown.user_id FROM DropDown ORDER BY DropDown.user_id;

as row source.

Private Sub Combo50_AfterUpdate() 
' Find the record that matches the control. 
    Dim RS As Object 

    Set RS = Me.Recordset.Clone 
    
    RS.FindFirst "[user_id] = """ & Me![Combo50] & """" 
    If Not RS.EOF Then Me.Bookmark = RS.Bookmark 
    

End Sub

as event procedure for after update.

My problem is that if you change one of them it populates the details section but does not change the other combo box to match or correspond with the rest of the form etc.

Is this possible??

Many Thanks

 

[Guus2005]

I believe that you forgot to bound the combobox to the recordset of the form.
After

If Not RS.EOF Then Me.Bookmark = RS.Bookmark

It might help to refresh the form:

me.refresh

 

[round]

Thanks Guus2005
I have just tried

Private Sub Combo45_AfterUpdate()
    ' Find the record that matches the control.
    Dim RS As Object

    Set RS = Me.Recordset.Clone
   
    RS.FindFirst "[surname] = """ & Me![Combo45] & """"
    If Not RS.EOF Then Me.Bookmark = RS.Bookmark
    
    Forms!frmMain_Details.Combo50.RowSource = "SELECT dropdown.user_id FROM dropdown WHERE " & _
  " dropdown.surname = [Combo45]"
    
    Forms!frmMain_Details.Refresh
    
End Sub

but it still isn't changing

 

[Guus2005]

That's not what i mean. The recordsource and the Control Source are two different things. You will find that can enter only one value in this property. Usually with the aid of a combobox. If the combobox is empty, then your form isn't bound!

 

[round]

How do I bound the combo box to the form?

both combo boxes do say unbound when in design view

 

[ajetrumpet]

My problem is that if you change one of them it populates the details section but does not change the other combo box to match or correspond with the rest of the form etc.

Is this possible??

Of course, but I'm afraid some of this stuff you have mentioned is redundant.

Are you trying to offer your users two different selection menus from which to choose from, that will in turn, display the appropriate record details?? If so, there are more efficient ways to go about doing this. Also, there is an extra space in this that will cause an error...

Forms!frmMain_Details.Combo50.RowSource = "SELECT dropdown.user_id FROM dropdown WHERE [color=red]<---space either needed here[/color]" & _
  " [color=red]<---or here, but not both[/color]dropdown.surname = [Combo45]"

 

[round]

I want the user to have two ways to search for a record, which I currently have and does work. In a fashion...
The problem is, if the user uses combo box 1 (surname search) I want the other combo box 2 (ID) combo box to also change to the ID that corrisponds to the surname chosen in combo box 1.

If the user uses combo box 2 (ID search) I want the other combo box 1 (surname) to change to the surname that corrisponds to the ID chosen in combo box 2.

I guessing I've made that as clear as mud... lol

 

[ajetrumpet]

Mud is such as ugly word...but yeah, maybe you're right about that.

Anyway, you may have confused yourself with all of the code you wrote for the two boxes. You can any of the 4 methods found of the Cascading FAQ for both boxes, but the problem with this is the fact that both boxes are present on the form and subject to change by the users. If you want them to be able to able to search by either combo, maybe you could give them an option button / command button, etc... to choose their method of search first, and then use .Visible to get them the appropriate control to use...??