CODEWITHSUNDEEP
ms-accessms-access-2007vba
user1983152
user1983152

Reputation: 77

How to add a new record from unbound fields of two tables in to a subform

Hey there Good Day can anyone help me please? I got a mainform and a subform and Ive created a query to join the two tables and ive tried to use the query to insert fields which is unbound, my subform fields which im getting from my join query if I insert into my fields it says and click the Add button I get a error it says: Run-time error '3134': Syntax error in insert into statement.

Code I used below:

Private Sub Add_Click() 
CurrentDb.Execute "INSERT INTO PlantTransactionQuery(TransactionID,Plant Number,Categories,Description,Location,TransactionDate,Opening_Hours,Closing_Hours,Hours Worked,Fuel,Fuel Cons Fuel/Hours,Hour Meter Replaced,Comments)" & _
"VALUES(" & Me.txt13 & ",'" & Me.txt1 & "','" & Me.txt2 & "','" & Me.txt3 & "','" & Me.txt4 & "','" & Me.txt5 & "','" & Me.txt6 & "','" & Me.txt7 & "','" & Me.txt8 & "','" & Me.txt9 & "','" & Me.txt10 & "'," & Me.txt11 & "," & Me.txt12 & ")"
PlantTransactionsubform.Form.Requery 
End Sub

I dont know if u can insert into a query and i dont know which name I must put into brackets for reserved name. Any help will be much appreciated of how to add two tables in a subform in a button onclick on the same page or even maybe i made a mistake in my code for me everything looks good. Thanks in advance

Upvotes: 1

Views: 3858

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123664

You need to put square brackets [] around any table or field name that

Try this instead:

CurrentDb.Execute "INSERT INTO PlantTransactionQuery (TransactionID,[Plant Number],Categories,Description,Location,TransactionDate,Opening_Hours,Closing_Hours,[Hours Worked],Fuel,[Fuel Cons Fuel/Hours],[Hour Meter Replaced],Comments) " & _
"VALUES (" & Me.txt13 & ",'" & Me.txt1 & "','" & Me.txt2 & "','" & Me.txt3 & "','" & Me.txt4 & "','" & Me.txt5 & "','" & Me.txt6 & "','" & Me.txt7 & "','" & Me.txt8 & "','" & Me.txt9 & "','" & Me.txt10 & "'," & Me.txt11 & "," & Me.txt12 & ")"

Edit

You can make SQL statements easier to verify visually if you break them up line-by-line, something like this:

CurrentDb.Execute _
        "INSERT INTO [PlantTransactionQuery] (" & _
                "[TransactionID], " & _
                "[Plant Number], " & _
                "[Categories], " & _
                "[Description], " & _
                "[Location], " & _
                "[TransactionDate], " & _
                "[Opening_Hours], " & _
                "[Closing_Hours], " & _
                "[Hours Worked], " & _
                "[Fuel], " & _
                "[Fuel Cons Fuel/Hours], " & _
                "[Hour Meter Replaced], " & _
                "[Comments] " & _
            ") VALUES (" & _
                Me.txt13 & ", " & _
                "'" & Me.txt1 & "', " & _
                "'" & Me.txt2 & "', " & _
                "'" & Me.txt3 & "', " & _
                "'" & Me.txt4 & "', " & _
                "#" & Me.txt5 & "#, " & _
                "'" & Me.txt6 & "', " & _
                "'" & Me.txt7 & "', " & _
                "'" & Me.txt8 & "', " & _
                "'" & Me.txt9 & "', " & _
                "'" & Me.txt10 & "', " & _
                Me.txt11 & ", " & _
                Me.txt12 & " " & _
            ")"

The 3061 error message "Too few parameters..." can happen if a field (column) name is mis-spelled, or a field name that happens to be a reserved word is not enclosed in square brackets. I've edited the query above so all table and column names are bracketed, just to be safe.

Also, check that you are quoting strings and are not quoting numbers. A mistake there could also result in the aforementioned error.

As for [TransactionDate], you probably should un-bind the text box for consistency. Notice that the value for that field is enclosed in hash marks (#) instead of single quotes. You can add a "date" input mask to the text box, or you can use something like... 

Format(CDate(Me.txt5), "yyyy-mm-dd")

...to format the date for you.

Finally, be aware that building SQL strings like that leaves you open to SQL injection problems, so you might also consider replacing that .Execute with something like

Dim rst as DAO.Recordset
Set rst = CurrentDB.OpenRecordset("[PlantTransactionQuery]", dbOpenTable)
rst.AddNew
rst![TransactionID] = CLng(Me.txt13)
rst![Plant Number] = Me.txt1
rst![Categories] = Me.txt2
...
rst![Comments] = Me.txt12
rst.Update

Upvotes: 0

Related Questions