Write record to table

[NISMOJim]

I'm working on an inventory database. I have a form based on a table (tblParts) that shows the details of a part (PartID, Description, Qty, Brand, etc.). I also have a few other unbound fields on this form to track the details on when a part is signed out (SODate, User, QtyTaken, etc.). When the "Sign Out" button is clicked, I need to write all of the information from this form as a new record in the Transactions table (tblXAct) using VBA.
I've updated records before, so I think I can figure how to change the quantity in tblParts when something is taken out or returned, but I've never had to write a record to a different table before. Can someone help me with this please?

Thank you

[Robeen]

You could try something like this:

This example inserts a numeric field, a text field, a Date field and a Time field into a Table.

You can use this as a starting point for your insert.

The example gets the values to insert from text boxes on a form.

 
Dim RptDate, RptTime As Date
Dim RptID As Integer
Dim strDescription As String
 
Me.txtID.SetFocus
RptID = Me.txtID
 
Me.txtDescription.SetFocus
strDescription = Me.txtDescription
 
Me.txtReportDate.SetFocus
RptDate = Me.txtReportDate
 
Me.txtReportTime.SetFocus
RptTime = Me.txtReportTime
 
StrSQL = "INSERT INTO YourTableNameHere (Rpt_ID, Description, StartDate, StartTime) "
StrSQL = StrSQL & "VALUES (" & RptID & ", " & "'" & strDescription & "'" & ", " & "#" & RptDate & "#" & ", " & "#" & RptTime & "#" & "); "
MsgBox StrSQL
DoCmd.RunSQL StrSQL

Hope this helps!

[John_G]

Another option is to instead of doCmd.RunSQL, use

currentdb.execute strSQL, dbFailOnError

which allows you to trap an error if there a problem with the SQL statement.

John

[NISMOJim]

Thank you both for your help. When the message box at the end of the code pops up everything looks fine, but when I click ok I get a "Syntax Error in INSERT INTO statement" message. I've tried the code both ways you have suggested, and I've looked through the code and don't see anything wrong. I know it's long, but here it is;

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
Dim XactType, Cat, Carrier, InvNo, PartNo, Desc, Requestor, Location, Staff, Comments, IssueNo, UpperDN, LowerDN, UserXact, StaffXact, CmntXact As String
Dim PartsID, Vendor, Usage, QtyXact As Integer
Dim DateRecd, TimeRecd, DispDate, DateXact As Date
Dim HazMat As Boolean

XactType = Me.txtXactType
PartsID = Me.txtPartsID
Cat = Me.txtCat
DateRecd = Me.txtDateRecd
TimeRecd = Me.txtTimeRecd
Carrier = Me.txtCarrier
InvNo = Me.txtInvNo
PartNo = Me.txtPartNo
Desc = Me.txtDesc
Vendor = Me.txtVendorID
Requestor = Me.txtRequestor
Usage = Me.txtUsageID
Location = Me.txtLocation
DispDate = Me.txtDispDate
Comments = Me.txtComments
HazMat = Me.chkHazMat
Staff = Me.txtStaff
IssueNo = Me.txtIssueNo
UpperDN = Me.txtUpperDN
LowerDN = Me.txtLowerDN
DateXact = Me.txtDateXact
UserXact = Me.txtUserXact
QtyXact = Me.txtQtyXact
CmntXact = Me.txtCmntXact
StaffXact = Me.cboStaffXact

strSQL = "INSERT INTO tblXact (XactType, PartsID, Cat, DateRecd, TimeRecd, Carrier, InvNo, PartNo, Desc, Vendor, Requestor, Usage, Location, DispDate, Comments, HazMat, Staff, IssueNo, UpperDN, LowerDN, DateXact, UserXact, QtyXact, CmntXact, StaffXact)"
strSQL = strSQL & "VALUES (" & "'" & XactType & "'" & ", " & "'" & PartsID & "'" & ", " & "'" & Cat & "'" & ", " & "#" & DateRecd & "#" & ", " & "#" & TimeRecd & "#" & ", " & "'" & Carrier & "'" & ", " & "'" & InvNo & "'" & ", " & "'" & PartNo & "'" & ", " & "'" & Desc & "'" & ", " & "'" & Vendor & "'" & ", " & "'" & Requestor & "'" & ", " & Usage & ", " & "'" & Location & "'" & ", " & "#" & DispDate & "#" & ", " & "'" & Comments & "'" & ", " & HazMat & ", " & "'" & Staff & "'" & ", " & "'" & IssueNo & "'" & ", " & "'" & UpperDN & "'" & ", " & "'" & LowerDN & "'" & ", " & "#" & DateXact & "#" & ", " & "'" & UserXact & "'" & ", " & QtyXact & ", " & "'" & CmntXact & "'" & ", " & "'" & StaffXact & "'" & "); "
MsgBox strSQL
'DoCmd.RunSQL strSQL
CurrentDb.Execute strSQL, dbFailOnError
' DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub


One other thing I noticed. If one of the fields has no information entered (very common) I get an "invalid use of null" message.
Lastly, when this is working I have one other thing to do. I'll need to subtract the quantity taken from the original invoice table. Can I do this same type of code on this "OnClose" button, only instead of INSERT INTO the table, I do an UPDATE table?

Thank you for your help.

[Robeen]

Your two
strSQL = "
statements . . . .

Is each one on ONE line of code?
Or do they wrap onto a second line in your code window?
If so - you would need line continuation characters.

Are your Table Names in the first "strSQL = " and the field names in the 'VALUES(' in the same order?

Otherwise . . . I had a quick look through your SQl and I didn't spot anything wrong.

Try doing an insert with one variable of each data type [one string, one Date, one integer and one Boolean] and see if the Insert works.

If it doesn't, eliminate one variable each time to try & figure out which variable is causing the syntax error.

If it DOES work - keep adding variables to both sides of your Insert till you get it all working.

If you have the option of being able to post your DB here - I might be able to try running your SQL here to try & figure it out.

However, my experience has been that an error message like the one you are getting usually means that there IS a SQL error in the statement.
I have never inserted a Boolean into a table using this method - so I don't know if the punctuation you are using for that is correct - I THINK it should be - but you can try removing it from both sides of the Insert statement.

Process of elimination . . . is how I'd approach it.

Let us know how it goes.

[NISMOJim]

Sorry for such a late reply, but I was on vacation, then swamped when I got back.
To answer your questions, yes, both lines of strSQL are on one line. I also checked the order of these fields in relation to the table. The last 2 fields were swapped, but the error came before that. Here's what I found so far...
I stepped through each field like you suggested. All worked fine until I got to the "Desc" field. I deleted it from both lines, then stepped through the rest of the fields successfully. Everything wrote to the table as expected. I tried putting the "Desc" fields back in and got the error again. I checked the spelling throughout the code and in the table, and checked that it was a text field in each place with nothing wrong. Where do I go from here? Also, how do I keep from getting an error if a field is empty?
As always, thanks for your help.

[ssanfu]

Sorry for such a late reply, but I was on vacation, then swamped when I got back.
To answer your questions, yes, both lines of strSQL are on one line. I also checked the order of these fields in relation to the table. The last 2 fields were swapped, but the error came before that. Here's what I found so far...
I stepped through each field like you suggested. All worked fine until I got to the "Desc" field. I deleted it from both lines, then stepped through the rest of the fields successfully. Everything wrote to the table as expected. I tried putting the "Desc" fields back in and got the error again. I checked the spelling throughout the code and in the table, and checked that it was a text field in each place with nothing wrong. Where do I go from here? Also, how do I keep from getting an error if a field is empty?
As always, thanks for your help.

I find long lines make it very hard to trouble shoot. This is how I create my INSERT strings:

  strSQL = "INSERT INTO tblXact("
  strSQL = strSQL & " XactType, PartsID, Cat, DateRecd, TimeRecd,"
  strSQL = strSQL & " Carrier, InvNo, PartNo, Desc, Vendor, Requestor,"
  strSQL = strSQL & " Usage, Location, DispDate, Comments, HazMat,"
  strSQL = strSQL & " Staff, IssueNo, UpperDN, LowerDN, DateXact,"
  strSQL = strSQL & " UserXact, QtyXact, CmntXact, StaffXact"
  strSQL = strSQL & ")"

  strSQL = strSQL & " VALUES("
  strSQL = strSQL & "'" & XactType & "', '" & PartsID & "', '" & Cat & "', #" & DateRecd & "#, #" & TimeRecd & "#, '"
  strSQL = strSQL & Carrier & "', '" & InvNo & "', '" & PartNo & "', '" & Nz(Desc, "") & "', '" & Vendor & "', '" & Requestor & "', "
  strSQL = strSQL & NZ(Usage, 0) & ", '" & Location & "', #" & DispDate & "#, '" & Comments & "', " & HazMat & ", '"
  strSQL = strSQL & Staff & "', '" & IssueNo & "', '" & UpperDN & "', '" & LowerDN & "', #" & DateXact & "#, '"
  strSQL = strSQL & UserXact & "', " & QtyXact & ", '" & CmntXact & "', '" & StaffXact & "'"
  strSQL = strSQL & ");"

With 4 or 5 variables per line, it is easy to comment out lines to find out which one has a problem.

If a control is (or can be) NULL, use the NZ() function to convert the NULL to a zero length string (in RED above) or, if it is a number field, to change it a zero (in BLUE above)

[NISMOJim]

Thanks for the quick reply. I tried to make a second line out of the code using the underscore at the end, but it kept giving me an error. I'll use your way from here on out. Also, thanks for the null field solution. I'll plug that into the lines of SQL. The main problem continues. The "Desc" field is not null, but I'm still getting the "Syntax Error in INSERT INTO statement" when I keep it in the code. Any ideas why this might be happening?
Thank you.

[ssanfu]

Thanks for the quick reply. I tried to make a second line out of the code using the underscore at the end, but it kept giving me an error. I'll use your way from here on out. Also, thanks for the null field solution. I'll plug that into the lines of SQL. The main problem continues. The "Desc" field is not null, but I'm still getting the "Syntax Error in INSERT INTO statement" when I keep it in the code. Any ideas why this might be happening?
Thank you.

After the last " strSQL = strSQL &" line add:

Debug.Print strSQL

Set a break point on this line. Execute the code. When the code breaks (stops) hit the "F8" key once. Then open the Immediate window. Copy the line into a text editor (Notepad). Look at the line where the "DESC" data should be. If you can't determine why there is still a problem, post the line you copied from the immediate window so we can see what is returned.

[SteveH2508]

Desc is a reserved word in SQL so that might be causing a problem.

[ssanfu]

Desc is a reserved word in SQL so that might be causing a problem.

Arrrgh, I knew that. I just dealt with DESC as a field name. Must have been too late in the day. At least, that's my story & I'm going to stick to it.

Good catch Steve