Hi I have a access DB with 4 Tables. 

Table tblproduct

ProductID - pk
ProductInfo

Table tblBookingStock

BookingStockId - pk
ProductID (Related to Tbl product productID)
BookindID (Related to Tbl booking bookingID)
StoreID (Related to Tbl store storeID)
Qty

Table tblBooking

BookingID ? pk
Date
Notes
ConsNo

Table tblStore

StoreID
Store
Info


I have created a from with text box?s with the relevant look up etc,
which the user enters all the information for the booking. And then
selects a update button which I want the information in the text box?s
to be inserted into the bookingStock table.

 (form)

On the form the user selects the information from the cmbo boxes and
then the selects the update button with the following code behind it.


'Set the booking ID
Dim BookingID As Integer
BookingID = Me.Unq_Booking_ID

'Set the product
Dim ProductID As Integer
ProductID = Me.CmbProductID

'set the store
Dim Store As Integer
Store = Me.CmboStore

'Inserts the updated details into the booking stock table, the table
that tracks transactions


sqlString = "insert into TblbookingStock (bookingid, productid,
Storeid) Values ( " & BookingID & "," & ProductID & "," & Store & ")"

DoCmd.RunSQL (sqlString)


But I get the following error msg. 


Cant append all the records in the append query.

Set 0 fields(s) to null due to a type conversion failure, and it
didn?t add 1 record(s) to the table due to key violations, 0 record(s)
due to lock violations(s) due to validation rule violations.

So my question is how do I correct this error I need to insert from
text box?s to the table bookingstock?

---

Request for Question Clarification by hammer-ga on 06 Apr 2005 05:41 PDT

chrismaxwell,

Let's check the validity of the Insert statement you are assembling.
After the line:
sqlString = "insert into TblbookingStock (bookingid, productid,
Storeid) Values ( " & BookingID & "," & ProductID & "," & Store & ")"

... Place the following line of code:
MsgBox sqlString

...and comment out:
DoCmd.RunSQL (sqlString)

...so the insert does not actually try to run.

This will cause the sql statement you are running to appear in a
message box. Look at it carefully and see if it contains the correct
values. The type conversion message makes me suspicious. Your combo
boxes may not be providing the values you think they are. Also, you
are using Integers for your variable types. Normally, a foriegn key in
a table is of type Long.

Is it possible for you to upload your mdb to someplace where we could
download it and take a look at it?

- Hammer


BTW, if you know how, you can also use 
Debug.Print sqlString

---

Clarification of Question by chrismaxwell-ga on 07 Apr 2005 04:57 PDT

Hi,

I ran msgbox (sql string) and it returned the correct values in the
msgbox. if i remove the relationships is runs no problem. I have tried
changing the names (as recommended by the last comment) and this made
no difference. Reading into it there is no reason it cant work because
i put the numbers in manually it also works?

Unfortunately I don?t have anywhere I can upload it too

---

Request for Question Clarification by hammer-ga on 07 Apr 2005 05:29 PDT

<<Reading into it there is no reason it cant work because i put the
numbers in manually it also works?>>

That's why it sounds to me like a subtle syntax error of some kind.

Can you please post *exactly* what appears in the message box when you
show sqlString?

- Hammer

---

Clarification of Question by chrismaxwell-ga on 07 Apr 2005 05:45 PDT

HI,
---------------------
Microsoft Access

Insert into Tbl_booking_Stock (unq_booking_id, unq_product_id,
unq_store_id) Values ( 1002,804,3)


OK
--------------------
The Names are slightly different from moy original posting to make it
simpler to read. They are correct i have double checked thanks

---

Request for Question Clarification by hammer-ga on 07 Apr 2005 06:30 PDT

chrismaxwell,

I built a database based on your structure. Your INSERT works fine
unless I try to insert a record that actually does cause a key
violation.

Have you checked to make sure that you:
1. Have a Booking record with id 1002
2. Have a Product record with id 804
3. Have a Store record with id 3 

If all your values are indeed valid, let me know. I'll upload the
working sample I built and we can look for where my mdb differs from
yours.

- Hammer

---

Clarification of Question by chrismaxwell-ga on 07 Apr 2005 08:06 PDT

Thats was it. spot on, The Booking record was created but it couldnt
be inserted because it wasn't saved yet. Simple, Thanks for your help

---

Clarification of Question by chrismaxwell-ga on 07 Apr 2005 08:11 PDT

How do I accept as a answer to close the question?

ChrisMaxwell,

Based on our troubleshooting above, the key clue to your problem is in
teh error message you received when trying to insert the record.
Specifically:
"didn?t add 1 record(s) to the table due to key violations"

This message means that Access cannot insert the record without
violating the referential intergrity rules. This was confirmed when
removing the relationships also removed the problem.

Whenever you see this message, the first thing to do it try adding a
record by hand that uses the exact same values you are using in the
insert statement. You also want to confirm that the necessary records
exist on the other side of the relationship.

I'm glad I was able to help you track down your problem.

Good luck with your Access project!

- Hammer 

Search strategy
----------------
None. Troubleshooting/debugging based on symptoms reported.

I was really stuck on this problem, it really helped having another
point of view and to be pointed in the right direction. Thanks, very
impressed.

Check the ProductID, BookingID and StoreID fields in tblBookingStock.

You may have set the Indexed property to "Yes (No Duplicates)"  If so,
set them to "Yes (Duplicates OK)"

Also, make sure that the data type for the BookingStockId field in
tblBookingStock is AutoNumber.

If these were already correct, try temporarily removing the
relationships from tblBookingStock to other tables one at a time until
your code runs.  Once your code runs, then you were inserting an
invalid value into the field for the relationship you just removed.

For example, if you remove this relationship:

tblBookingStock.ProductID -> tblProduct.ProductID

and then your code begins to work, then your code was inserting an
invalid value into the ProductID field of tblBookingStock.  Once you
get your code inserting correct values, put the relationships back.

Hi, Thanks for your response. They are set to duplicates ok, and the
id is set to autonumber. if i remove all the relastionships it works
but not with any of them in place. But if i go into the table and
manualy put the same data in as the statement, it allows the data to
be entered no problem.

Any Ideas?

Thanks

One more thing.  Try changing your variable names in your code like this:

Dim iBookingID, iProductID, iStore As Integer
iBookingID = Me.Unq_Booking_ID
iProductID = Me.CmbProductID
iStore = Me.CmboStore

sqlString = "insert into TblbookingStock (bookingid, productid,
Storeid) Values ( " & iBookingID & "," & iProductID & "," & iStore & ")"

It is possible that you are getting a conflict between the form's
fields and the variable names, since they are the same.  I've had
weird problems in this sort of situation that were resolved by
avoiding identical variable and field names.