By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,173 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

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

P: 13
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
Apr 20 '10 #1
Share this Question
Share on Google+
12 Replies

Expert Mod 100+
P: 2,321
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:
Apr 20 '10 #2

P: 13
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)
Apr 20 '10 #3

Expert Mod 100+
P: 2,321
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.
Apr 20 '10 #4

P: 13
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];
Apr 20 '10 #5

Expert Mod 100+
P: 2,321
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.
Apr 20 '10 #6

P: 13
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.

Attached Files
File Type: zip Meter Prep (148.7 KB, 34 views)
Apr 21 '10 #7

Expert Mod 15k+
P: 31,494

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.
Apr 21 '10 #8

P: 13
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?

Apr 21 '10 #9

Expert Mod 100+
P: 2,321
I think you should:
1) Change your listbox to a combobox.
2) Set the Rowsource to: "SELECT locations.key, locations.locations 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.
Apr 21 '10 #10

Expert Mod 15k+
P: 31,494
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.
Apr 21 '10 #11

P: 13
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....

Apr 22 '10 #12

Expert Mod 15k+
P: 31,494
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 :)
Apr 23 '10 #13

Post your reply

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