How to stop records from being entered into a 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.


Reply





Pat Hartman(MVP)
Dec 13, 12:30 pm show options

Newsgroups: microsoft.public.access.forms
From: "Pat Hartman\(MVP\)" <[email protected]> - Find
messages by this author
Date: Tue, 13 Dec 2005 15:30:03 -0500
Local: Tues, Dec 13 2005 12:30 pm
Subject: Re: Want to stop records from being created in table
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

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.







- Hide quoted text -
- Show quoted text -

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.

Reply





Scott
Dec 13, 4:02 pm show options

Newsgroups: microsoft.public.access.forms
From: "Scott" <[email protected]> - Find messages by this author
Date: 13 Dec 2005 16:02:23 -0800
Local: Tues, Dec 13 2005 4:02 pm
Subject: Re: Want to stop records from being created in table
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

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.....