I have been trying to get my database to add rows into an inventory spreadsheet based on inputs from a form. I have tried multiple different methods (listed below). Any help on formatting one of these so they will work would be appreciated. I don't know if I have syntax problems or am approaching this completely wrong, but I have been primarily using code from videos and answers to questions on here explaining how to solve this issue, so it is annoying that I haven't been able to get them to work.
I have tried setting up a function that I can call with a macro, pulling values from the form
Function Add_Record()
DoCmd.RunSQL "INSERT INTO Inventory (Kit Name, Exp Date, Study) VALUES (' " & [Forms]![Add Kits]![Kit name select] & " ', ' " & [Forms]![Add Kits]![Expiration Date Input] & " ', ' " & [Forms]![Add Kits]![Study Select] & " ');"
End Function
I have tried this with a variety of syntax changes (I am unsure if there needs to be _ in place of spaces or not), but nothing has worked yet.
Next I tried setting up a button in the form itself to contain everything in one place.
Private Sub Add_Record_Click()
CurrentDb.Execute "INSERT INTO Inventory (Kit Name, Exp Date, Study) Values (' " & Me.Kit_name_select & "', ' " & Me.Expiration_Date_Input & "', ' " & Me.Study_Select & "')"
End Sub
I have also tried this method with
strSQL = "INSERT INTO Inventory (Kit Name, Exp Date, Study)
VALUES (
Forms!Add Kits!Kit_name_select.Value,
Forms!Add Kits!Expiration_Date_Input.Value,Forms!Add Kits!Study_Select.Value
)"
DoCmd.RunSQL strSQL
Switching the form!X!X format with me. s, but theoretically that shouldn't be the issue? I am hoping there's either some syntax issue happening, or a big change I need to fix. This is aggravating however, because all of these strategies are supposed to do what I am going for according to what I've read.
Any help is really appreciated! Also explainations are great as I am obviously pretty new to this but would like to learn!
Again, this would hopefully result in adding a row to a table based on the inputs from a form - Study (combo box string), Kit Name (combo box String), and Expiration date (text box Date). Also if there is any way to make it loop based on another input (quantity) that would also be helpful. Thank you!
Execute method uses # delimiter instead of apostrophe for date field. Remove space following apostrophe.
Object names with space or special characters or are reserved word need [ ] delimiters. Advise not to use spaces nor punctuation/special characters in naming convention.
Could use a For Next loop to repeatedly enter records based on quantity.
Private Sub Add_Record_Click()
Dim x As Integer
For x = 1 to Me.Quantity
CurrentDb.Execute "INSERT INTO Inventory ([Kit Name], [Exp Date], Study) " & _
"Values ('" & Me.[Kit name select] & "', #" & Me.[Expiration Date Input] & "#, '" & Me.[Study Select] & "')"
Next
End Sub