Newbie Access Question

[Guest]

Hi all,

I created one table named 'Fields' expressly for the purpose of
housing commonly used values for drop-downs in my main table called
'Main'.


One of the fields in 'Fields' is a list of last names.
i.e. that way the dropdown in Main contains last names i.e. Smith,
Jones, Taylor.


The problem I'm having is this:


When I select from the dropdown in one record in Main, it changes the
entries for all the records in Main.


In form view in Main, I selected Smith from the dropdown in record #1.


When I create a new record #2, 'Smith' is prepopulated in the field,
though I can change it to 'Jones.' When I save my data and go back to
look at record #1, Jones has taken Smith's place.


The General Tab for this field reads:
Field Size: 50
Required: Yes
Allow Zero Length: No
Indexed: Yes (Duplicates OK)
Unicode Compression: Yes
(all other fields blank)


The Lookup Tab for this field reads:
Display Control: List Box
Row Source/Type: Table/Query
Row Source: SELECT [Fields].[Last] FROM Fields;
Bound Column: 1
Column Count: 1
Column Heads: No
(all other fields blank)


Someone please tell me what I'm doing wrong


Thanks,
Pat

 

[Guest]

Hi Pat,

Someone please tell me what I'm doing wrong

The first thing you are doing wrong is using reserved words (Fields and Last
are both considered reserved words). You should never use a reserved word for
anything that you assign a name to in Access. Please see this comprehensive
listing of reserved words:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html


The next potential gotcha is that you have used a Lookup field in your
table. See this article on MVPS:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm

I would change the display control to a Text Box, to eliminate the Lookup
field.

It sounds to me like your combo box (dropdown) on your form is an unbound
control (ie. it has no field specified for it's Control Source property).
Open the form in design view. Click on View > Properties if the Properties
dialog is not already displayed. Select the Data tab. Now, click on the combo
box in question. You should see the name of the combo box in the title bar of
the Properties dialog. What do you see for the first property listed, which
is Control Source?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi all,

I created one table named 'Fields' expressly for the purpose of
housing commonly used values for drop-downs in my main table called
'Main'.

One of the fields in 'Fields' is a list of last names.
i.e. that way the dropdown in Main contains last names i.e. Smith,
Jones, Taylor.

The problem I'm having is this:

When I select from the dropdown in one record in Main, it changes the
entries for all the records in Main.

In form view in Main, I selected Smith from the dropdown in record #1.

When I create a new record #2, 'Smith' is prepopulated in the field,
though I can change it to 'Jones.' When I save my data and go back to
look at record #1, Jones has taken Smith's place.

The General Tab for this field reads:
Field Size: 50
Required: Yes
Allow Zero Length: No
Indexed: Yes (Duplicates OK)
Unicode Compression: Yes
(all other fields blank)

The Lookup Tab for this field reads:
Display Control: List Box
Row Source/Type: Table/Query
Row Source: SELECT [Fields].[Last] FROM Fields;
Bound Column: 1
Column Count: 1
Column Heads: No
(all other fields blank)

Someone please tell me what I'm doing wrong

Thanks,
Pat

 

[pat]

It sounds to me like your combo box (dropdown) on your form is an unbound
control (ie. it has no field specified for it's Control Source property).
Open the form in design view. Click on View > Properties if the Properties
dialog is not already displayed. Select the Data tab. Now, click on the combo
box in question. You should see the name of the combo box in the title bar of
the Properties dialog. What do you see for the first property listed, which
is Control Source?

Tom,

I think this led me to a solution. I changed all my listboxes to
comboboxes and it seems to have fixed my problem.

The fields were renamed in my post because the actual field names I
chose were silly and I wanted the example to make sense.
(I did take your suggestion about not using Lookup under advisement,
too

Thanks,
Pat