Lookup wizard/drop down list problem (1 Viewer)

[snoopy12]

i can't get the lookup wizard/drop down list to work properly. i have two fields that need a drop down list. the first works - it shows two columns, name and account; and populates the field with the account. the second doesn't work - it shows two columns, name and item number; and populates the field with the name. what am i overlooking that is making it use the name instead of the account?

 

[Kiwiman]

Howzit

I take it you have the combo box in an input form as opposed to a table? Check the bound column property to make sure that the coorect column is bound. Also check to see if it is a just a visual thing you see by checking the column widths of the combobox. You may find that the correct column is bound but the width is st to 0 so you only see the second column.

 

[snoopy12]

I'm setting all the requirements in the table before creating the form. Am i doing this backwards? The drop down is set to show two columns - name and account. I don't want to remember the accounts for each name and want the account to populate the field. I've attached the database for anyone to review. The table in question is sales records and the field in question is Case GTIN. I would prefer the drop down to pull the information from the Case GTIN table. I did the same thing with Manufacturer ID and that one works fine.
Yes, i've tried the bound columns and widths. i even started over with a new table. It was working and then of course i modified something and can't get it back to the old settings.
Thanks for the help.

 

[Kiwiman]

Howzit

Personally, I never have lookups in tables (and a lot of users of this forum are of the same mind) as it makes it a lot harder to troubleshoot potential problems - such as these. I only put lookups in forms that will show exactly the same data as you want to see in the table. But iat the end of the day it is your choice as the developer

The lookup for the manufacturer id doesn't actually work, as

• your manufacturer id in the "manufacturer Id #'s" table is a text field
• the corresponding field in the "sales record" table is a number field
• so when you make a selection it stores the numeric equivalent of your manufacturer id in the this field - not possibly what you expect
• you can see that by selecting a manugfacturers id you already have in this table and it will not open the combobox at the correct record, but at the top of the list
• you can only then join back to the manufacturers table by rebuiding the id by adding zero's in a query

The solution (on the basis that you want to keep the lookup in your table) is to change the manufacturers id in the sales record table to text and make it the second column in your combobox row source (if you keep it 3rd as it stands the manfacturers name will be visible in the table, although it will not be the value stored.

My solution would be to remove all lookups in the tables and only use in the form and ensure that the corresponding values are of the same data type

In regards to your table \ field names etc, I would strongly recommend that you remove any spaces \ special characters from these fields as this will again make potential query \ vba coding more problematic

 

[snoopy12]

Thank you for clarifying. I thought you had to have everything set in the tables before building the forms. As long as i understand - i can remove the drop down from the two fields in question from the tables, but keep my settings for text/numbers/etc. Then i can create the form and add the drop downs there? Did i follow correctly? If I do it that way, will the drop down allow me to see two columns and then populate the field with the account instead of name? or can i show just the name column in the drop down and 'tell' it to populate with the account?
I've always been an access user; this is my first time developing.

 

[Kiwiman]

Howzit

Yes you can retain whatever datatype that you want for your fields. In your form you specify which column in your lookup is bound to the field in your table and which field(s) are visible (by using the column width property) - you have pretty much done this already in your tableas you have done already in the table).

Typically I would have two columns in your scenario in the lookup, with the first column (the manuafacturer id) as the bound column with the column widths being set at 0 and 5(eg) respectively - so the user only see the manufacturers name

 

[snoopy12]

Thanks, you've been most helpful. Now i don't feel so discouraged that i couldn't figure out what was wrong.