How do I add new records to one table based on info added to form.

[johngalt69austin]

I am using access 2002 xp.

my database is tracking material movement through a production floor.

When a qty is moved from "inspect" to "hold" I am trying to make one transaction count as two by:

click on "hold" button.

systemnumber
partnumber
userid
date
time all autopopulate from current data
"qty" is the field that is requested.
when the "qty" is entered I want a corresponding transaction to take place in the "inspect" table by multiplying the entered qty by -1 and adding a new record based on that information.

Any assistance would be very helpful.

 

[KenReay]

Hi

at its most basic execute an SQL insert string

strSQL = "INSERT INTO tblInspect ...blah blah"
docmd.runsql strSQL

do you need more help with the blah blah bit?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[johngalt69austin]

Thank you for your reply. And in answer to your question I'm afraid that the answer is "Yes, very much blah, blah, please". Below is where I am currently at in the update form. QtyReturnedFromInspect is the field that I am updating with the manual transaction. "PartSentToInspect" is the table that I wish to add a new record to with the opposite qty (*-1). I already know that this doesn't work but I thought that it might help explain what I am doing and where I am at "programatically speaking".

Any assistance you could give would be greatly appreciated.
Do I put the SQQL on "after update" or "on lost focus" (or somewhere else)

Private Sub QtyReturnedFromInspect_AfterUpdate()
On Error GoTo Err_Command10_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "PartSentToInspect"
stLinkCriteria = "[systemnumber]=" & "'" & Me![systemnumber] & "'"
stLinkCriteria = "[partnumber]=" & "'" & Me![PartNumber] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.FindNext
DoCmd.Close

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click
End Sub

 

[johngalt69austin]

Ok. This ws more of an honest effort...but I'm still missing something. It's now giving me a "syntax error in FROM clause"

SELECT [Forms]!ReturnedFromInspect.systemnumber, [Forms]!ReturnedFromInspect.PartNumber, [Forms]!ReturnedFromInspect.QtyReturnedFromInspect, [Forms]!ReturnedFromInspect.ReturnedFromInspect, [Forms]!ReturnedFromInspect.userid
FROM [Forms]!ReturnedFromInspect
INSERT INTO PartSentToInspect.systemnumber, PartSentToInspect.PartNumber, PartSentToInspect.Qty, PartSentToInspect.SentToQA, PartSentToInspect.userid
WHERE (((PartSentToInspect.Qty) =( ReturnedFromInspect.QtyReturnedFromInspect)*-1));

 

[lespaul]

Your insert into sequence is all screwy!

Typical insert statement:

INSERT INTO TABLENAME (FIELDNAME1, FIELDNAME2) VALUES (FIELD1VALUE, FIELD2VALUE)

If you are inserting all the columns then you can leave the first (...) out (where you list all the field names).

If you want to insert from a select its:

INSERT INTO TABLENAME (FIELDNAME1, FIELDNAME2)
SELECT FIELD1, FIELD2 FROM SOMETABLE WHERE CONDITIONS

and I'm not sure you can select from a form.

Leslie


Leslie