Google Answers Logo
View Question
 
Q: Access 2000 ( Answered 5 out of 5 stars,   5 Comments )
Question  
Subject: Access 2000
Category: Computers > Programming
Asked by: chrismaxwell-ga
List Price: $60.00
Posted: 05 Apr 2005 09:56 PDT
Expires: 05 May 2005 09:56 PDT
Question ID: 505274
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?
Answer  
Subject: Re: Access 2000
Answered By: hammer-ga on 07 Apr 2005 08:36 PDT
Rated:5 out of 5 stars
 
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.
chrismaxwell-ga rated this answer:5 out of 5 stars and gave an additional tip of: $10.00
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.

Comments  
Subject: Re: Access 2000
From: willcodeforfood-ga on 05 Apr 2005 11:30 PDT
 
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)"
Subject: Re: Access 2000
From: willcodeforfood-ga on 05 Apr 2005 11:32 PDT
 
Also, make sure that the data type for the BookingStockId field in
tblBookingStock is AutoNumber.
Subject: Re: Access 2000
From: willcodeforfood-ga on 05 Apr 2005 11:39 PDT
 
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.
Subject: Re: Access 2000
From: chrismaxwell-ga on 06 Apr 2005 05:07 PDT
 
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
Subject: Re: Access 2000
From: willcodeforfood-ga on 06 Apr 2005 07:22 PDT
 
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.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy