Why Is my Lookup Value Being Copied to Other Records in My Form?

[gravesk]

am using Access 2003, and am not very familiar with Access or its terminology. I am not at all familiar with VB.

I created a form from a query, and everything is generally working fine.

One of the fields on the form is based on a lookup table for location. I have assigned a default value of 1 (which translates to NOT CHOSEN), and that's how everything shows up when I go into the form. I assigned the default both in the originating table and in the form itself.

However, when I am in the form and change the first record to a different value (say, 2 which translates to QUEENS), then all of the other records now show up as QUEENS when I page through them.

How do I stop the chosen lookup value from being propogated through the rest of my database?

Many thanks,
Kristin Graves

[TheSmileyCoder]

Sounds to me like the field is not bound to a field in your table/query. If you look at the properties of your combobox what is the:
Controlsource
RowSource

[gravesk]

control source = location (one of the fields in the query that is my source
for the form)
row/source type = table/query
row/source = locations (name of lookup table)

[TheSmileyCoder]

What is the SQL statement of your query? If you right click in the grey area of query design window, you can select SQL view. Just copy paste that SQL statement here.

[gravesk]

What is the SQL statement of your query? If you right click in the grey area of query design window, you can select SQL view. Just copy paste that SQL statement here.

This is the SQL statement of the query on which my form is based:

SELECT [Gibbons Oct 2010 Master - Account Level].account, Left([Gibbons Oct 2010 Master - Account Level]!Account,14) AS Acct_txt, [Gibbons Oct 2010 Master - Account Level]!Account*10+[chk_digit] AS Account15, [Gibbons Oct 2010 Master - Account Level].Division, [Gibbons Oct 2010 Master - Account Level].CUST_NAME AS Name, [Gibbons Oct 2010 Master - Account Level].SADD AS Address, [Gibbons Oct 2010 Master - Account Level].TOWN AS City, [Gibbons Oct 2010 Master - Account Level].ZIP, [Gibbons Oct 2010 Master - Account Level].E_METERS AS [No of Meters], [Gibbons Oct 2010 Master - Account Level].[kvar metering required by] AS [Reqd By], [Gibbons Oct 2010 Master - Account Level].folder_no AS [Folder #], [Gibbons Oct 2010 Master - Account Level].case_no AS [Case #], [Gibbons Oct 2010 Master - Account Level].wa_no AS [WA #], [Gibbons Oct 2010 Master - Account Level].location, [Gibbons Oct 2010 Master - Account Level].survey_info AS [Survey Info], [Gibbons Oct 2010 Master - Account Level].high_tension AS [High Tension?], [Gibbons Oct 2010 Master - Account Level].mhp AS [MHP?], [Gibbons Oct 2010 Master - Account Level].reactive AS [Reactive?], [Gibbons Oct 2010 Master - Account Level].remarks, [Gibbons Oct 2010 Master - Account Level].aextra1 AS Phase
FROM [Gibbons Oct 2010 Master - Account Level];

[TheSmileyCoder]

I can't really say what the problem is from your description, neither do I have a good idea what to ask next. Is it possible for you to attach the database here?

In your main table, what type of field is location? and what fields are in the table called location?

Do you have any relations set up between these 2 tables?

On a general note, try to be consistent with your naming of fields and tables, it will make it much easier to maintain in the future. Prefix your tables with tbl_ (example tbl_Location) and your fields with ID_Location if its the unique ID of the location, tx_Location if its the textstring of the location.

[gravesk]

Copy of database

OK, here is a small copy of my database. My users have already been adding data to the real thing, so I'd appreciate being guided as to what repairs to make.

Thanks in advance for your help.

Kristin

[NeoPa]

Kirstin,

This is simply an optical illusion. What you see doesn't reflect the value of the ListBox at all. It merely reflects the Scroll Position of it as you're using the up and down controls to scroll. What you see is black writing on black background. If the item were selected (try clicking on one) it becomes white on black and behaves quite differently.

This is ultimately down to sizing a ListBox control as you would a TextBox. Your assumptions about it reflect what you expect.

[gravesk]

I think I understand what you're saying, but what steps do I need to take to fix this? I want to see "NOT CHOSEN" for any record without a selection, and then see the selection when it's been made.

You mention "sizing a ListBox as you would a TextBox" - but I assume that "sizing" isn't referring to the size of the box?

thanks

[TheSmileyCoder]

I think you should:
1) Change your listbox to a combobox.
2) Set the Rowsource to: "SELECT locations.key, locations.locat ions FROM locations;"
3) Set Column Count =2
4) Set column Widths: 0cm;3cm (or whatever that is in inches :P)

This will make the combobox show the locations, but the main table will store the key of the location. This is the proper way of doing it, so that if you at some point want to edit NOT CHOSEN to Not Chosen, you would only need to do it in the locations table, and not in every single record in the master table.

[NeoPa]

I'm afraid you've got more wrong than that then :(

Your ListBox and field setups all reference the [Locations] table, but only as a single column. The related number is not included in either the field setup or the form ListBox control (Hence your default value of 1 would never be possible - The available values are all strings).

To select a value using the ListBox, whatever size you make it (and I am referring to the size of the ListBox control on your form here), you should scroll the values so the one you want is visible, but then either click on it or hit the spacebar to select it. Scrolling it with the scrollbar simply makes it visible for selection.

[gravesk]

NeoPa and TheSmileyOne -

Thanks for your help with this! I was able to get the thing to work by using a combo box instead of a list box, and fixing the data stored in the source table [Locations]. Also, I appreciate the advice about naming the queries, tables, etc with a prefix of tbl, qry, or whatever to help distinguish them. My Access knowledge is advancing ever so slowly....

Kristin

[NeoPa]

Smiley (Post #10) has given some practical advice on a good way forward Kristin. If you haven't already taken these points on board (I suspect from your comments you may have mind) then I strongly recommend you do now. They are a very common and quite basic way of handling such lookup type values. I suspect you will be using this concept many times in your database work in future.

By the way, you're welcome for the help. It's good to see it's being put to good use :)