Forms with Lookup problem

I am new to Access Development I am trying to build a 'simple' form that will
allow me to add records to my log table , display the category of a record by
displaying the Category name for the category ID in the Log table. I also
need to change the category ID in the Log table by having the user select the
category name for a drop down list. I tried this with a form based on a
query that displays the Category name in a combo box but I get errors when I
try to select a different row in the combo box. The error tells me that I am
creating a duplicate value. I think that it is trying to add a new row in my
category table.


Here is what I have setup

Table Category
CategoryID Autonumber Primary Key
Categoryname text(50)


Table Log
ID Autonumber Primary Key
CategoryID Fk to Category table
LogDate Date
Comments

Query vw_log

SELECT Category.CategoryName, Category.CategoryID AS Cat_ID, tbl_Log.WLID,
tbl_Log.CategoryID AS WL_CatID, tbl_Log.LogDate,tbl_WorkLog.comments
FROM Category INNER JOIN tbl_Log ON Category.CategoryID = tbl_Log.CategoryID

I'm guessing that since your query includes CategoryID from both tables you
probably have the combo bound to the CategoryID from the Category table. You
really don't need to join to the Category table in your Recordsource query.
Change the query to a simple select query from the log table. Then make sure
to check the ControlSource of the CategoryID to verify that it refers to the
CategoryID from the Log table. The beauty of a combo box is that it allows
you to display the information from the related table (Category) without
joining to it in the Recordsource.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


ahuntertate wrote:
I am new to Access Development I am trying to build a 'simple' form
that will allow me to add records to my log table , display the
category of a record by displaying the Category name for the category
ID in the Log table. I also need to change the category ID in the
Log table by having the user select the category name for a drop down
list. I tried this with a form based on a query that displays the
Category name in a combo box but I get errors when I try to select a
different row in the combo box. The error tells me that I am
creating a duplicate value. I think that it is trying to add a new
row in my category table.


Here is what I have setup

Table Category
CategoryID Autonumber Primary Key
Categoryname text(50)


Table Log
ID Autonumber Primary Key
CategoryID Fk to Category table
LogDate Date
Comments

Query vw_log

SELECT Category.CategoryName, Category.CategoryID AS Cat_ID,
tbl_Log.WLID, tbl_Log.CategoryID AS WL_CatID,
tbl_Log.LogDate,tbl_WorkLog.comments
FROM Category INNER JOIN tbl_Log ON Category.CategoryID =
tbl_Log.CategoryID