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

[Lnwolf]

OK. So I am going to try and add as much description so as give a solid view of what I am trying to do (without adding my DB). Below is all the information I can think of to add. If more is needed, please feel free to ask.

Description of Job:

I am trying to make this inventory as easy as possible for the users.. The least amount of typing involved and also a continuity to the item names. Each user had a tendency to give tools "thier own name". Believe me when I first pulled in all of the inventories into EXCEL I had 3,640 lines of tools and was able to break those down, with time, into two tables - one with a "generic" tool type (47 lines) and the other with a more descriptive tool variation (540).

-----------------
Below are the defining columns/attributes for my tables involved in this little adventure (info deliniated by lines):

tblLocationInCKTLookup where the user picks from a drop down for a drawer/shelf/area where the tool is located.

Column Name - Data Type - Description
LocationID - AutoNumber - PK
Location - Text
----------

tblSizesLookup where the user picks from a drop down for a size of the tool.

Column Name - Data Type - Description
SizesID - AutoNumber - PK
Sizes - Text
------------

tblToolTypeLookup listing for a "generic" description that will create a drop down for the tool.(i.e. Wrench)

Column Name - Data Type - Description
TooltypeID - AutoNumber - PK
Tool Type - Text
-------------

tblToolVariationLookup listing for a more defining description that will create a drop down for the tool.(i.e. Combination Wrench)

Column Name - Data Type - Description
VariationID - AutoNumber - PK
Tool Variation - Text
TooltypeID - Lookup column - Lookup tied to the tblToolTypeLookup
---------------

tblKRDL00057 where the records are to be stored.

Column Name - Data Type - Description
ID - AutoNumber - PK
Location in CTK - Lookup column - Lookup tied to the tblLocationInCTKLookup
Size - Lookup column - Lookup tied to the tbleSizesLookup
Tool Type - Lookup column - Lookup tied to the tblToolTypeLookup
Tool Variation - Lookup column - Lookup tied to the tblToolVariationLookup
Quantity - User enters the quantity of tools - this is used in the reports to create totals.
TMDE# - User information entry area (if needed)
Consumable - User information entry area (if needed)

---------------------------------------------------------------------
To fill in tbleKRDL00057 I have created form KRDL00057 MIL FORM this is a split form with the bottom being the datasheet. The combo boxes/text boxes have the following attributes:

Combobox: Location in CTK: SELECT [tblLocationInCTKLookup].LocationID, [tblLocationInCTKLookup].LOCATION FROM tblLocationInCTKLookup;

Combobox: Size: SELECT [tblSizesLookup].SizeID, [tblSizesLookup].Sizes FROM tblSizesLookup;

Combobox: Tool Type: SELECT tblToolTypeLookup.ToolTypeID, tblToolTypeLookup.[Tool Type]FROM tblToolTypeLookup;

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

The others are textboxes for user input of information (TMDE, Consumable, Quantity).
-------------------------------------------

All the lookup tables have a one-to-many relationship with the tblKRDL00057.

Within the sQL for the Tool Variation Combo box I was sure to add the brackets around all of the "like" named items so as to allow the program to differentiate between which table/box I am trying to use.

[NeoPa]

I think you've misunderstood my point. I don't want to see the entirety of you project in a post any more than I do in an attached database. That is not how to ask a question. Asking a question is about deciding which information is required and posting that, along with meaningful words explaining what you need help with. This should be of limited scope. For example a request to find and fix problems in a database is of far too broad a scope to be allowed even (let alone find someone prepared to spend the time on). As an administrator I am able to comment on both aspects you understand.

Your ability to include important information in a clearly understandable way is good (clearly - though please remember the CODE tags in future posts). What you need to do now is decide exactly what you want help with and express that as a simple question. We consider ourselves pretty competent in Access and are willing to share our knowledge. We do not consider ourselves as a free resource for finding your problems for you. We can advise there too of course, but that part is your responsibility.

The upshot of this is that, while I do not wish to put you off from posting your questions, I must guide you away from such open-ended questions and help you to understand what we find acceptable on this site. NB. This is intended as guidance and not criticism. As a new poster I'm sure you're merely finding your way and the latter would be of no help. I hope the former proves to be.

Moving on, if you are unable to see where your problem even exists in your project, then may I suggest you look at Debugging in VBA. There are many tried and trusted techniques included which most of us use to help us to identify exactly where our idea of what should be happening differs from the execution of the code itself.

[Lnwolf]

OK...thanks for your input.