Problems adding data to Table (2003)

Topic

New Reply

OK, this is a total newbie q, but I can’t seem to solve it on my own. Though the solution must be obvious.

Situation

I have a table named tblActivities. Fields in tblActivities include lngzLocationID#, intPriorityID# and intStatusID#. Each of these fields is joined to a lookup table with two fields, a numeric index number and a text descriptor. E.g., tblLocationLkUP has field idsLocationID# and field chrLocationName. tblPriorityLkUp has field intPriorityID# and filed chrPriorityDescriptor. idsLocationID# can take on any long integer value >= 0. Each value is uniquely associated with a text description of the location to which that value refers. Same for intPriorityID# except that it can only take on the values 0, 1 or 2, corresponding to chrPriorityDescriptor values of “Low”, “Normal” and “High”, respectively. Similarly with tblStatusLkUp (0, 1, 2; “Pending”, “Completed”, “Abandoned”).

Each of lngzLocationID#, intPriorityID# and intStatusID# is linked to the corresponding field in the appropriate look up table. Referential Integrity is required. The joins are one-to-many (tblActivities one, look up tables many) with all occurrences in tblActivity selected but only corresponding entries in the look up tables selected.

Problem

Every time I try to enter data into tblActivities in datasheet mode, I get error messages. (This appears not to be the case if I accept the default entries I have set up for the number fields, but that’s an aside.) Specifically, if I enter a number, I get a message telling me “The text you entered is not on the list.” This seems to be referring to a drop down list that appears when I click on the field. The dropdown list is populated with the _text_ entries I have made in the look up tables (although the links between tblActivities and the look up tables are via the Id# fields).

If I select an entry in the dropdown list I get the error message “The value you entered isn’t valid for this field.”

This is all a tad confusing. I’m sure what I’m doing wrong is trivially easy to correct, but I haven’t hit on how just yet.

HELP!

Reply | Quote

Replies

Normally, you should do all data entry in a form, not in a table, but the problem would probably be the same.

Open your table in design view.
Click in one of the problem fields.
Activate the Lookup tab in the Field Properties pane (the lower half of the table design window).
It should look similar to the screenshot below. In particular, the Row Source should either be the name of the lookup table, or the SQL for a query to select the ID and description fields from the lookup table, Bound Column should be 1, Column Count should be 2 and the Column Widths property should be set to 0″ or to 0″;1″.

Reply | Quote

Thanks for the, as usual, swift response.

The Lookup tab for tblStatusLkUp shows :

Row Source Type: Value List
Row Source: “Abandoned”; “Completed”; “Pending”
Bound Column: 1
Column Count: 2
Column Widths: no value

I changed as you suggested and all works well.

A couple of questions:

1. How did I get Lookup combo boxes? I certainly didn’t ask for them.
2. Why did they reference values not fields?
3. Is it good practice to use them? I recall seeing on another site that one shouldn’t use some aspect of Access’s automatic lookup tables.

And, for the future:

4. How do you get those great screenshots? They’re certainly worth a thousand words.

As always,

Many thanks

Reply | Quote

1. Are you sure you didn’t create the fields in tblActivities using the Lookup Wizard?
2. Since you had a list of text values as row source for the dropdown list, Access had no way of connecting those text values to the corresponding numbers in the lookup table.
3. In general, it is not necessary to use lookups in the tables themselves, but they are extremely useful in forms. Using a combo box with the lookup table as row source is much more user-friendly than having the user type in the numeric code, and less prone to errors.
4. You can put a screenshot on the Windows clipboard by pressing the Print Screen key (entire screen) or Alt+Print Screen (foreground window only). You can then paste the screenshot into a graphics application such as Paint, and crop to the part you want to show (screenshots attached to a post should be at most 640 by 480 pixels). Save as a .png file, for this kind of screenshot that results in high quality and small file size.
There are also dedicated screen grabber applications, many of them free. Search the Software Finds and Wants forum for “screen grab” or “screen capture”.

Reply | Quote

1. I don’t think so. But I can’t be absolutely sure since I’ve been developing this over quite a while.
2. Okay.
3. I’ll probably get rid of the combo box in the table. I understand their value in forms.
4. Thanks!

Reply | Quote