469,616 Members | 1,806 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,616 developers. It's quick & easy.

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

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:
Expand|Select|Wrap|Line Numbers
  1. tblToolTypeLookup:
  2. ToolTypeID: Primary Key
  3. ToolType: Text
Expand|Select|Wrap|Line Numbers
  1. tblToolVariationsLookup:
  2. ToolVariationID: PrimaryKey
  3. ToolVariation: Text
  4. ToolTypeID: Int Foreign Key Reference ToolTypeID in TblToolTypeLookup
combo box props are as below:
Combo#1 is:
Expand|Select|Wrap|Line Numbers
  1. Name: Tool_Type
  2. Control Source: Tool_Type
  3. Column Count 2
  4. Column Widths: 0";1"
  5. Row Source: tblToolTypeLookup
  6. Row Source Type: Table/Query
  7. Bound column 1
  8. After Update: Embedded Macro - Requery -> Tool Variation as Argument
Combo#2 is:
Expand|Select|Wrap|Line Numbers
  1. Name: Tool_Variation
  2. Control Source: Tool_Variation
  3. Column Count: 2
  4. Column Widths: 0";2"
  5. Row Source Type: Table/Query
  6. Bound column: 1
Below you will find my SQL.
Expand|Select|Wrap|Line Numbers
  1. SELECT tblToolTypeLookup.ToolTypeID,
  2.        tblToolVariationsLookup.[Tool Variation]
  3. FROM   tblToolTypeLookup INNER JOIN
  4.        tblToolVariationsLookup
  5.   ON   tblToolTypeLookup.ToolTypeID = tblToolVariationsLookup.ToolTypeID
  6. 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.

Attached Images
File Type: jpg Problem with dropdown box2.jpg (19.7 KB, 167 views)
Sep 28 '09 #1
12 1832
32,200 Expert Mod 16PB
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.
Sep 28 '09 #2
I entered the following per the example in the insight that was given to me:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Tool_Type_AfterUpdate()
  3.   With Me![Tool_Variation]
  4.     If IsNull(Me!Tool_Type) Then
  5.       .RowSource = ""
  6.     Else
  7.       .RowSource = "SELECT [Tool Variation] " & _
  8.                    "FROM tblToolVariationsLookup " & _
  9.                    "WHERE [Tool Type]=" & Me!Tool_Type
  10.     End If
  11.     Call .Requery
  12.   End With
  15.   End Sub
Now my second combo box doesn't have anything in it (see attached).
Attached Images
File Type: jpg Problem with empty dropdown box.jpg (18.3 KB, 168 views)
Sep 28 '09 #3
32,200 Expert Mod 16PB
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.
Sep 28 '09 #4
8,800 Expert 8TB
Hello NeoPa, the Tool_Type Combo referred to in Line #9 is Bound to the Primary Key, and the defined Relationships are:
Expand|Select|Wrap|Line Numbers
  1. [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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Tool_Type_AfterUpdate()
  2.   With Me![Tool_Variation]
  3.     If IsNull(Me!Tool_Type) Then
  4.       .RowSource = ""
  5.     Else
  6.       .RowSource = "SELECT [ToolTypeID], [ToolVariation] " & _
  7.                    "FROM tblToolVariationsLookup " & _
  8.                    "WHERE [ToolTypeID]=" & Me![Tool_Type]
  9.     End If
  10.     Call .Requery
  11.   End With
  12. End Sub
or am I barking up the wrong tree?
Sep 28 '09 #5
32,200 Expert Mod 16PB
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.
Sep 29 '09 #6
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.
Sep 29 '09 #7
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...
Sep 29 '09 #8
32,200 Expert Mod 16PB
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.
Sep 29 '09 #9
OK..thanks. I appreciate it... I will continue to post as needed..
Sep 29 '09 #10
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.
Sep 29 '09 #11
32,200 Expert Mod 16PB
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.
Sep 29 '09 #12
OK...thanks for your input.
Sep 29 '09 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Frank | last post: by
5 posts views Thread by Max Ischenko | last post: by
4 posts views Thread by Rich Sienkiewicz | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.