Want to stop records from being created in table

[Scott]

Hello-

I think I am missing something simple. I have a table (tblItems) that
is part of a query (qryInquiries) containing several fields from this
table and others. My form (frmInquiries) is based on the query. On the
form, there is a drop down for Item Number that the user can pick the
item number and 3 other text boxes will fill in The fields in the form
that are based on the Items table are as follows:

cboItemNum= the drop down that gets the item number from the Items
table
txtItemDesc= text box that fills in the item description
txtManuCode= text box that fills the manufacturer code
txtManuName= the full name of the manufacturer

When users enter a new inquiry in the form, the record is added to the
query. I will base my reports on the query and everything looks like it
will work ok. The problem I am getting is that for every new record
added via the form, a new record is being added to the Items table. The
new record duplicates the Items table record that previously existed,
yet it leaves the Item Number field as null.
Since the combo box on the form is based on the item number, when you
open the drop down, you will see blanks before you get to actual item
codes. The more records added to the form, the more blanks appear in
the combo box. I am lost at this point.

Any ideas would be greatly appreciated.

 

[Pat Hartman\(MVP\)]

I'm going to guess that your query is selecting the join field from the
1-side table rather than the join field from the many-side table. Change
the query. Modify the many-side table definition to make the default value
for the foreign key field to null. Do this by removing the 0 that Access
assumes you want. You also need to add the relationship to the relationship
window and enforce referential integrity. You will have to clean up the
orphan records before Access will allow you to enforce RI.

 

[Scott]

Pat-

Thnaks for the info. I am still having trouble, I think I looked at
this so much over the weekend that nothing makes sense to me at this
point. I do not have a relationship in the relationships window between
tblItems and tblInquiries. They are linked in the query though. Here
are the fields of my tables, where would I link them in Relationships
and do I need other fields in either table?

tblInquiries tblItems

InquiryID ItemID
EnterDate ItemNum
OrderDate ItemDesc
AccountNumber ManuCode
InvoiceNumber ManuName
Comment

The query the form is based on, qryInquiries, contains both of these
tables and has a link between the InquiryID field and the ItemID field.
It looks like a one-to-one relationship. Hope this helps.....

 

[Pat Hartman\(MVP\)]

You haven't given me enough information to determine the correct
relationship between the two tables and I can't work it out from the table
columns, but I think you need to add ItemID to tblInquiries.

The relationship is showing up as 1-1 because you have joined the two tables
on their primary keys. In a 1-many relationship, the primary key of the
1-side table(Items) is placed in the many-side table(Inquiries) where it is
referred to as a "foreign key".