CODEWITHSUNDEEP
ms-accessms-access-2007vba
user1983152
user1983152

Reputation: 77

How to update a mainform into a subform

After I edit information and change the information and click update, it gives me a error. I tried the parenthesis brackets no luck.

Too few parameters Expected 1. Run time error '3061'

Private Sub cmdUpdate_Click()
Dim strSql As String
strSql = "UPDATE PlantTransaction " & _
  "SET TransactionID=" & Me.txtTranID & _
  ",[Plant Number]='" & Me.txtPlantNo & "'" & _
  ",TransactionDate=#" & Me.txtTransDate & "#" & _
  ",Opening_Hours='" & Me.txtOpeningHRS & "'" & _
  ",Closing_Hours='" & Me.CloseHrs & "'" & _
  ",Fuel='" & Me.txtFuel & "'" & _
  ",[Fuel Cons Fuel/Hours]='" & Me.txtFuelConsFuelHr & "'" & _
  ",[Hour Meter Replaced]='" & Me.txtHrMtrRep & "'" & _
  ",Comments='" & Me.txtComments & "'" & _
  ",[Take on Hour]='" & Me.txtTOH & "'" & _
  " WHERE TransactionID=" & Me.PlantTransactionQuery.Form.Recordset.Fields("Tr ansactionID")
Debug.Print strSql ' <- prints to Immediate window
CurrentDb.Execute strSql, dbFailOnError
cmdClear_Click
Me.PlantTransactionQuery.Form.Requery
End Sub

Upvotes: 1

Views: 94

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123409

Any time that you "glue together" a long SQL statement with lots of user input you face the challenges of

  1. correctly delimiting strings and dates,

  2. escaping delimiters within such fields (usually quotes inside a text field), and

  3. getting all of the required commas in the right places

You can avoid those annoyances by using a Recordset to perform the update:

Dim rst As DAO.RecordSet
Set rst = CurrentDb.OpenRecordset("PlantTransaction", dbOpenDynaset)
rst.FindFirst "TransactionID=" & Me.PlantTransactionQuery.Form.Recordset.Fields("Tr ansactionID")
If Not rst.NoMatch Then
    rst.Edit
    rst!TransactionID = Me.txtTranID
    rst![Plant Number] = Me.txtPlantNo
    rst!TransactionDate = Me.txtTransDate
    rst!Opening_Hours = Me.txtOpeningHRS
    rst!Closing_Hours = Me.CloseHrs
    rst!Fuel = Me.txtFuel
    rst![Fuel Cons Fuel/Hours] = Me.txtFuelConsFuelHr
    rst![Hour Meter Replaced] = Me.txtHrMtrRep
    rst!Comments = Me.txtComments
    rst![Take on Hour] = Me.txtTOH
    rst.Update
End If
rst.Close
Set rst = Nothing

Upvotes: 1

HansUp
HansUp

Reputation: 97101

You were smart to include this line in your code:

Debug.Print strSql ' <- prints to Immediate window

Now when you get the missing parameter message, go to the Immediate window (you can use Ctrl+g to go there) and copy the SQL statement.

Then create a new Access query in the query designer, switch to SQL View, and paste in the text you copied. When you attempt to run that query, Access will present a parameter input box which includes the name of whatever it thinks is the parameter.

Compare that parameter name with the field names in your data source. Often this situation occurs because the query includes a misspelled field name. Another possibility with an UPDATE is that one of the values you're trying to update is unquoted text. Regardless of the cause, the parameter name from that input box should help you track it down. Show us the actual text from that UPDATE statement if you need further help.

Upvotes: 1

Related Questions