Synchronized boxes on the fritz - Can someone please help??!!

[Lnwolf]

Hello, I am having an issue with my synchronized combo boxes and don't know how to fix it.

I have created two boxes and have made the synchronization work (the user clicks on the first box and makes a choice and the second only shows items which are related to the first box's choice).

My problem is that the user can not choose from any of the choices in the second combo... whatever is clicked on the very first item is the only thing that will show up. I have 48 choices in the first box (Tool Types) and 540 choices in my second box (Tool Variations - based off of the first) and would like to not have to show the user the entire list that is why I went with synchronized boxes.

The table names are as below:

tblToolTypeLookup:
ToolTypeID: Primary Key
ToolType: Text

tblToolVariationsLookup:
ToolVariationID: PrimaryKey
ToolVariation: Text
ToolTypeID: Int Foreign Key Reference ToolTypeID in TblToolTypeLookup

combo box props are as below:
Combo#1 is:

Name: Tool_Type
Control Source: Tool_Type
Column Count 2
Column Widths: 0";1"
Row Source: tblToolTypeLookup
Row Source Type: Table/Query
Bound column 1
After Update: Embedded Macro - Requery -> Tool Variation as Argument

Combo#2 is:

Name: Tool_Variation
Control Source: Tool_Variation
Column Count: 2
Column Widths: 0";2"
Row Source Type: Table/Query
Bound column: 1

Below you will find my SQL.

SELECT tblToolTypeLookup.ToolTypeID,
       tblToolVariationsLookup.[Tool Variation]
FROM   tblToolTypeLookup INNER JOIN
       tblToolVariationsLookup
  ON   tblToolTypeLookup.ToolTypeID = tblToolVariationsLookup.ToolTypeID
WHERE (((tblToolVariationsLookup.[Tool Type])=[forms]![KRDL00057 MIL FORM].[Tool_Type]));

the boxes work... per se... when the user chooses lets say "Wrenches" from the first box the second box when clicked only provides the types of wrenches...just as a synchronized box should do.

My problem is if the user chooses any item (it doesn't matter how far down on the second box) it doesn't "lock" on that item and place it in the record. It will not let the user choose anything but the first item in the list of the second combo box.

I have an embedded macro in the first combo box in the "After Update" that is Tool_Variation. requery so that when the user makes a choice combobox2 will update its listing according to the choice in combobox1.

Please help with any ideas you might have to share.

Wolf

[NeoPa]

You may like to check out Example Filtering on a Form. If that doesn't help you then post back and let us know.

Welcome to Bytes!

PS. Congratulations for putting the time in to explain the question as well as you did. Lots of good info to help us understand.

[Lnwolf]

I entered the following per the example in the insight that was given to me:

Private Sub Tool_Type_AfterUpdate()
 
  With Me![Tool_Variation]
    If IsNull(Me!Tool_Type) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [Tool Variation] " & _
                   "FROM tblToolVariationsLookup " & _
                   "WHERE [Tool Type]=" & Me!Tool_Type
    End If
    Call .Requery
  End With
 
 
  End Sub

Now my second combo box doesn't have anything in it (see attached).

[NeoPa]

I would say 2 things :

1. You have spelled the items as [Tool Variation] & [Tool Type] whereas your indications earlier were that the controls were named with underscores (_) and the fields had no break at all.
2. You have not included the quotes (') around the string value in line #9. See Quotes (') and Double-Quotes (") - Where and When to use them for the full explanation.

[ADezii]

@NeoPa
Hello NeoPa, the Tool_Type Combo referred to in Line #9 is Bound to the Primary Key, and the defined Relationships are:

[tblToolTypeLookup].[ToolTypeID]{1} ==> [tblToolVariationsLookup].[ToolTypeID]{MANY}. 

The Tool_Variation Combo Box has 2 Columns, the 1st ToolTypeID (Bound, Column Width =0), and the 2nd ToolVariation (Column Width = 2"):
Shouldn't the Syntax be:

Private Sub Tool_Type_AfterUpdate()
  With Me![Tool_Variation]
    If IsNull(Me!Tool_Type) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [ToolTypeID], [ToolVariation] " & _
                   "FROM tblToolVariationsLookup " & _
                   "WHERE [ToolTypeID]=" & Me![Tool_Type]
    End If
    Call .Requery
  End With
End Sub

or am I barking up the wrong tree?

[NeoPa]

@ADezii
No. I think you've found another issue with the code.

Certainly the first step would be to ensure that the names of the various items used are spelt correctly. The next step would be to confirm that the RecordSource for the control actually specified the right items within it.

When both of these issues have been looked at and corrected I'm sure the OP will have found that the suggested solution works well for them.

[Lnwolf]

I guess you are both right... I should go back and rename them all the same so there is no confusion.. I have Tool Variation and Tool Type in the tables, but I have Tool_Variation and Tool_Type as the combo names... I will try your suggestions and see what happens... Thanks alot for your help. I have posted to other sites and never got so much useful (if any) feedback.

[Lnwolf]

OK... I guess I really am a newbie.... I exported all the pertinent info to another database so it wouldn't be so huge... and am attaching it..this way you can see all that is involved.... please give any help you can. I appreciate it... truly...

[NeoPa]

I'm happy to give help where I can, but I draw the line at going through somebody's database for them. If you can explain your current problem then I'm happy to look at it with you, but I don't find that doing the work for you works very well. You learn little and continue to depend on external help.

Having said that, it seems you have a better than average ability to layout and explain your problem. Why not continue to work within the forum layout as before. I'm sure we can be of more help in that case.

[Lnwolf]

OK..thanks. I appreciate it... I will continue to post as needed..