Auto-increment Control on Form and Increase Quantity

[dlab85]

This code is driving me crazy . I get a compile error: Argument not optional, which highlights the Me.Room_Number in the values portion of the SQL. I deleted that field from both the InsertInto and Values portion of the SQL, and retried. I then received the same error, with Me.Expiration_Date being highlighted. Guidance? The coding below is located on a button I currently have labeled "Add", located on the subform, next to the txtIDQuantity, which is what should trigger the looping. Before I went Gung-ho, I added the lot number to the Insert Into and Values portion of the SQL, and it looped and worked as desired, so I'm sure I am messing something up syntax wise.

Private Sub btnAddChemicals_Click()Dim strChemicalID As String
'search for aborted ChemicalID and use it, else if none then NewChemicalID
strChemicalID = Nz(DLookup("ChemicalID", "tblChemicalID"), "")
DoCmd.SetWarnings False
strChemicalID = NewChemicalID()
If strChemicalID <> "" Then
 DoCmd.RunCommand acCmdSaveRecord
 For i = 1 To Me.txtIDQuantity - 1
    CurrentDb.Execute "INSERT INTO tblChemicalID(chemicalID, [Catalog Number], [Lot Number], [Received Date], [Expiration Date], [Room Number], [Section], [Comments], [OptionsID]) VALUES ('" & NewChemicalID() & "','" & Me.Catalog_Number & "', '" & Me.Lot_Number & "', '" & Me.Received_Date & "', '" & Me.Expiration_Date & "', '" & Me.Room_Number & "', '" & Me.Section & "', '" & Me.Comments & "', '" & Me.OptionsID & "')"
    Next
End If
DoCmd.SetWarnings True
End Sub

I will give the NotInList event a shot, thanks!

[dlab85]

Anybody out there that could take a shot at my compile error i am receiving in the code above?! It would be much appreciated.

[June7]

Values for date/time field type must use # delimiter instead of apostrophe. If any field is number type, do not use any delimiters.

Otherwise, don't see anything else wrong with syntax.

[dlab85]

Thank you so very much June7, this code has been a huge time saver, even though I have stumbled along the way . I do have one more question regarding: on the frmlogin2, there are the comboboxes for section and kit insert that are based on queries for the tables tblSection and tblOptions, showing column 2 to display the text that corresponds to the Autonumber SectionID and OptionID respectively, while the column 1 is the bound column that saves the selected value into tblChemicalID. I had a syntax issue when trying to determine how to loop that data through as well; I tried having the control as [SectionID] in the INSERT portion of the SQL, and then " & Me.SectionID & ", treating it as a number (since that is the bound column), but the syntax errored out, I assume because the actually value being seen is not a number but text, so should it be '" & Me.SectionID & "'? Maybe I just need to retry it or had it mistyped.

[June7]

The value of combobox is the bound column, regardless of which column value is displayed. If the field to be saved in is truly a number type then the query syntax for number should work.

Post your exact code for analysis.

[dlab85]

June7,
After reexamining my code, i had some of the fields out of order, so once I hashed that out and had the appropriate delimiters it worked. I am going to mark this thread as solved. I want to THANK YOU so greatly for helping me work through this coding, you have been a lifesaver!