Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2013
    Posts
    22

    Question Unanswered: 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,Transaction Date,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

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Working in Jet SQL, any field names that contain non-alphanumeric characters must be enclosed in brackets. In your example, you need to do this to the following:
    • Plant Number
    • Transaction Date
    • Hours Worked
    • Fuel Cons Fuel/Hours
    • Hour Meter Replaced

    So that you end up with:
    • [Plant Number]
    • [Transaction Date]
    • [Hours Worked]
    • [Fuel Cons Fuel/Hours]
    • [Hour Meter Replaced]

    You might also want to look at renaming your text boxes to something meaningful, so that it's easier to write code. I think that you got in a knot over which inputs need delimiting with quotation marks.

    In this situation, it's usually easier to declare a string variable and populate this with the SQL statement that you want to execute. Then you can view the contents through debugging to find out exactly where the problem is.

    Consider this:
    Code:
    Add_Click()
    
    Dim strSQL As String
    
    strSQL = "INSERT INTO PlantTransactionQuery (TransactionID, [Plant Number], Categories, Description, Location, [Transaction Date], Opening_Hours, Closing_Hours, [Hours Worked], Fuel, [Fuel Cons Fuel/Hours], [Hour Meter Replaced], Comments) "
    strSQL = strSQL & "VALUES (" & txtTranID & ", '" & txtPlantNo & "', '" & txtCategories & "', '" & txtDescription & "', '" & txtLocation & "', #" & txtTransDate & "#, " & txtOpenHrs  & ", " & CloseHrs & ", " & txtHrsWorked & ", '" & txtFuel & "', " & txtFuelConsFuelHr & ", " & txtHrMtrRep & ", '" & txtComments & "');"
    CurrentDb.Execute strSQL
    PlantTransactionsubform.Form.Requery
    End Sub
    I've had to guess at some of the data types, but that should do for a starting point.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Apr 2013
    Posts
    22

    Wink

    Thank you so much it work but im just trying to update my edit fields if I click the edit button it changes the Add caption to update so where ill have two adds where ill add my new fields and where ill update the existing fields everything is working im just trying to solve solution.
    I get this error Run-time error '3134'.
    Syntax error in UPDATE statement.
    Thanks for helping me guy I appreciated aloth.

    Private Sub cmdAdd_Click()

    Dim strSQL As String

    If Me.txtTranID.Tag & "" = "" Then

    strSQL = "INSERT INTO PlantTransactionQuery (TransactionID, [PlantTransaction_Plant Number], Categories, Description, Location, [TransactionDate], Opening_Hours, Closing_Hours, [Hours Worked], Fuel, [Fuel Cons Fuel/Hours], [Hour Meter Replaced], Comments) "
    strSQL = strSQL & "VALUES (" & txtTranID & ", '" & txtPlantNo & "', '" & txtCategories & "', '" & txtDescription & "', '" & txtLocation & "', #" & txtTransDate & "#, " & txtOpenHrs & ", " & CloseHrs & ", " & txtHrsWorked & ", '" & txtFuel & "', " & txtFuelConsFuelHr & ", " & txtHrMtrRep & ", '" & txtComments & "');"
    CurrentDb.Execute strSQL
    Else
    strSQL = "UPDATE PlantTransactionQuery " & _
    "SET TransactionID=" & Me.txtTranID & _
    ",PlantTransaction_Plant Number='" & Me.txtPlantNo & "'" & _
    ",Categories='" & Me.txtCategories & "'" & _
    ",Description='" & Me.txtDescription & "'" & _
    ",Location='" & Me.txtLocation & "'" & _
    ",TransactionDate='" & Me.txtTransDate & "'" & _
    ",Opening_Hours='" & Me.txtOpenHrs & "'" & _
    ",Closing_Hours='" & Me.txtDescription & "'" & _
    ",Hours Worked='" & Me.txtDescription & "'" & _
    ",Fuel='" & Me.txtFuel & "'" & _
    ",Fuel Cons Fuel/Hours='" & Me.txtFuelConsFuelHr & "'" & _
    ",Hour Meter Replaced='" & Me.txtHrMtrRep & "'" & _
    ",Comments='" & Me.txtComments & "'" & _
    " WHERE TransactionID=" & Me.txtTranID.Tag
    CurrentDb.Execute strSQL
    End If
    End Sub
    Last edited by grampo; 05-10-13 at 08:42.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I'm sorry, but I can't understand what you've written in the first sentence. If you can, it might be easier to upload a zipped copy of the database.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Apr 2013
    Posts
    22
    When I test the add button with your code it worked but now im trying to update existing fields awso on this add button so ill have a if else statement if I add the new record it will add to the subform and all those existing records if i click on one of the subform records change a few fields and click add button it must update

    Ive changed the code:
    Im getting a Run time-error '3134' Syntax error in UPDATE statement
    my code below I cant find the problem just need your help to help me with this one please!!!

    Private Sub cmdAdd_Click()

    Dim strSQL As String

    If Me.txtTranID.Tag & "" = "" Then

    strSQL = "INSERT INTO PlantTransactionQuery (TransactionID, [PlantTransaction_Plant Number], Categories, Description, Location, [TransactionDate], Opening_Hours, Closing_Hours, [Hours Worked], Fuel, [Fuel Cons Fuel/Hours], [Hour Meter Replaced], Comments) "
    strSQL = strSQL & "VALUES (" & txtTranID & ", '" & txtPlantNo & "', '" & txtCategories & "', '" & txtDescription & "', '" & txtLocation & "', #" & txtTransDate & "#, " & txtOpenHrs & ", " & CloseHrs & ", " & txtHrsWorked & ", '" & txtFuel & "', " & txtFuelConsFuelHr & ", " & txtHrMtrRep & ", '" & txtComments & "');"
    CurrentDb.Execute strSQL
    Me.PlantTransactionQuery.Form.Requery
    Else
    strSQL = "UPDATE PlantTransactionQuery " & _
    "SET TransactionID=" & Me.txtTranID & _
    ",PlantTransaction_Plant Number='" & Me.txtPlantNo & "'" & _
    ",Categories='" & Me.txtCategories & "'" & _
    ",Description='" & Me.txtDescription & "'" & _
    ",Location='" & Me.txtLocation & "'" & _
    ",TransactionDate='" & Me.txtTransDate & "'" & _
    ",Opening_Hours='" & Me.txtOpenHrs & "'" & _
    ",Closing_Hours='" & Me.CloseHrs & "'" & _
    ",Hours Worked='" & Me.txtHrsWorked & "'" & _
    ",Fuel='" & Me.txtFuel & "'" & _
    ",Fuel Cons Fuel/Hours='" & Me.txtFuelConsFuelHr & "'" & _
    ",Hour Meter Replaced='" & Me.txtHrMtrRep & "'" & _
    ",Comments='" & Me.txtComments & "'" & _
    " WHERE TransactionID=" & Me.txtTranID.Tag
    CurrentDb.Execute strSQL
    Me.PlantTransactionQuery.Form.Requery
    End If
    End Sub
    Last edited by grampo; 05-10-13 at 09:03.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I refer you to the first line of my first reply to this thread:
    Working in Jet SQL, any field names that contain non-alphanumeric characters must be enclosed in brackets.
    This applies whether you are inserting records or updating them.

    It's also worth dumping the contents of strSQl to the Debug window (type "?strSQL" in the window during Break mode and hit Enter), to have a look at the command that you have issued to the database.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Apr 2013
    Posts
    22
    It worked thank you so much you a star im just having one error in the delete statement to complete this assignment will you help me?

  8. #8
    Join Date
    Apr 2013
    Posts
    22
    When i select a record in the subform then click Delete button if give me an error!!!
    run time error '3131' Syntax error in FROM clause
    What have I done wrong in my code?
    Code below:

    Private Sub cmdDelete_Click()
    Dim strSQL
    If Not (Me.PlantTransactionQuery.Form.Recordset.EOF And Me.PlantTransactionQuery.Form.Recordset.BOF) Then
    If MsgBox("Are you sure you want to delete", vbYesNo) = vbYes Then
    strSQL = "Delete * FROM PlantTransactionQuery" & _
    "WHERE TransactionID=" & Me![TransactionID] & Me.PlantTransactionQuery.Form.Recordset.Fields("Tr ansactionID")
    CurrentDb.Execute strSQL
    Me.PlantTransactionQuery.Form.Requery
    End If
    End If
    End Sub
    Last edited by grampo; 05-10-13 at 09:47.

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Have a look at the contents of strSQL. The answer will come to you.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •