Update table from Form

[michal79]

Hi there..

I'm going nuts here with that code and can't find the error which obviously is somewhere there. I have a data entry form which I want to use for entering new data to my database or to edit the existing records. I have a subform which lists only records marked as 'proposal'. I have an edit button and a piece of code that retrieves the marked record from the subform to the data entry form. That works well. Then I want to update that record and I keep on getting the error here... Run-time error '3144': Syntax error in UPDATE statement. Here's the code:

Private Sub SaveRecord_Click()
    'when we click on button Add there are two options
    '1. for insert
    '2. for update
 
    If Me.TxDEID.Tag & "" = "" Then
    'this is for insert new
    'add data to table
    CurrentDb.Execute "INSERT INTO LLIS(LessonID, [Lesson Title], Phase, Stage, [Well Name], [Field Name], [Rig Name], Section, Activity, Vendor, Date, [Originator Initials], Department, Status, Description, [Learning Points]) " & _
        "VALUES(" & Me.TxDELTitle & ",'" & Me.CbDEPhase & "','" & _
         Me.CbDEStage & "','" & Me.CbDEWellName & "','" & Me.CbDEFieldName & "','" & Me.CbDERigName & "','" & Me.CbDESection & "','" & Me.CbDEActivity & "','" & Me.CbDEVendor & "','" & Me.TxDEDate & "','" & Me.CbDEOrigIni & "','" & Me.CbDEDept & "','" & Me.CbDEStatus & "','" & Me.TxDEDescr & "','" & Me.TxDELearnings & "')"
 
 Else
    'otherwise (Tag of TxDEID store the id of Lesson to be modified)
       CurrentDb.Execute "UPDATE LLIS " & _
    " SET ID=" & Me.TxDEID & _
    ", [Lesson Title]='" & Me.TxDELTitle & "'" & _
    ", Phase='" & Me.CbDEPhase & "'" & _
    ", Stage='" & Me.CbDEStage & "'" & _
    ", [Well Name]='" & Me.CbDEWellName & "'" & _
    ", [Field Name]='" & Me.CbDEFieldName & "'" & _
    ", [Rig Name]='" & Me.CbDERigName & "'" & _
    ", Section='" & Me.CbDESection & "'" & _
    ", Activity='" & Me.CbDEActivity & "'" & _
    ", Vendor='" & Me.CbDEVendor & "'" & _
    ", Date='" & Me.TxDEDate & "'" & _
    ", [Originator Initials]='" & Me.CbDEOrigIni & "'" & _
    ", Department='" & Me.CbDEDept & "'" & _
    ", Status='" & Me.CbDEStatus & "'" & _
    ", Description='" & Me.TxDEDescr & "'" & _
    ", [Learning Points]='" & Me.TxDELearnings & "'" & _
    " WHERE ID=" & Me.TxDEID.Tag
 
 
 End If
 
    'clear form
    CmdClear_Click
    'refresh data in list form
    LPQsubform.Form.Requery
End Sub

Can anyone tell me what's wrong here? I'm very new to VBA and don't really understand the problem here

Thanks for your help. I attach a screenshot of the form

Regards,
Michal

 

[jzwp22]

First, the word date is a reserved word in Access so it should not be used as a field name; for now you will need to enclose the field name in square brackets

", [Date]='" & Me.TxDEDate & "'" & _

Based on your code above, it implies that the date field has a text data type. Is that correct?

If the date field has a date/time data type then you have to use # signs not single quotes as indicated below

", [Date]=#" & Me.TxDEDate & "#" & _

What I typically do is to put the built query into a variable that way I can use the debug.print statement to view the constructed query in the VBA Immediate window and look for any errors. You can also copy the SQL text from the Immediate window into a new query and test it there. You would just reference that variable in the currentdb.execute command.

 

[michal79]

Thanks jzwp22 !

I've changed the date name to LLDate and updated the syntax in VBA by using # intead of '. My date field was in fact a date/time type filed.
Now I'm getting another error:

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

I have a filed called KeyLL which is a True/False type field and I'm not sure if I'm referencing that field correctly. Otherwise still something must be missing... HELP!

Private Sub SaveRecord_Click()
    'when we click on button Add there are two options
    '1. for insert
    '2. for update
 
    If Me.TxDEID.Tag & "" = "" Then
    'this is for insert new
    'add data to table
    CurrentDb.Execute "INSERT INTO LLIS(LessonID, [Lesson Title], Phase, Stage, KeyLL, [Well Name], [Field Name], [Rig Name], Section, Activity, Vendor, LLDate, [Originator Initials], Department, Status, Description, [Learning Points]) " & _
        "VALUES(" & Me.TxDELTitle & ",'" & Me.CbDEPhase & "','" & _
         Me.CbDEStage & "','" & Me.YNDEKeyLL & "', '" & Me.CbDEWellName & "','" & Me.CbDEFieldName & "','" & Me.CbDERigName & "','" & Me.CbDESection & "','" & Me.CbDEActivity & "','" & Me.CbDEVendor & "',#" & Me.TxDEDate & "#,'" & Me.CbDEOrigIni & "','" & Me.CbDEDept & "','" & Me.CbDEStatus & "','" & Me.TxDEDescr & "','" & Me.TxDELearnings & "')"
 
 Else
    'otherwise (Tag of TxDEID store the id of Lesson to be modified)
       CurrentDb.Execute "UPDATE LLIS " & _
    " SET ID=" & Me.TxDEID & _
    ", [Lesson Title]='" & Me.TxDELTitle & "'" & _
    ", Phase='" & Me.CbDEPhase & "'" & _
    ", Stage='" & Me.CbDEStage & "'" & _
    ", KeyLL='" & Me.YNDEKeyLL & "'" & _
    ", [Well Name]='" & Me.CbDEWellName & "'" & _
    ", [Field Name]='" & Me.CbDEFieldName & "'" & _
    ", [Rig Name]='" & Me.CbDERigName & "'" & _
    ", Section='" & Me.CbDESection & "'" & _
    ", Activity='" & Me.CbDEActivity & "'" & _
    ", Vendor='" & Me.CbDEVendor & "'" & _
    ", LLDate=#" & Me.TxDEDate & "#" & _
    ", [Originator Initials]='" & Me.CbDEOrigIni & "'" & _
    ", Department='" & Me.CbDEDept & "'" & _
    ", Status='" & Me.CbDEStatus & "'" & _
    ", Description='" & Me.TxDEDescr & "'" & _
    ", [Learning Points]='" & Me.TxDELearnings & "'" & _
    " WHERE ID=" & Me.TxDEID.Tag
 
 
 End If
 
    'clear form
    CmdClear_Click
    'refresh data in list form
    LPQsubform.Form.Requery
End Sub

Thanks in advance for any suggestions!
Cheers.

M.

 

[Aleni]

Hi michal
I am not sure but i feel like there should be equal number of fields selected from table and the values you want to insert.

In your code you have 17 fields and only 16 values.
I am sorry , I am not sure with this.

 

[michal79]

Aleni, thanks, you're probably very correct. I have noticed it and now have all the fields in there. Now another problem pops uup...

Run-time error '3113':
Cannot update 'LessonID'; field not updateable.

I think it's becaus my LessonID field is an Autonumber type field.
Any suggestions on how to solve this ..

Do I need to change the LessonID to a Number type... but then how will I make sure that an incremental ID is created automatically each time I create a new record.

Here's the code:

Private Sub SaveRecord_Click()
    'when we click on button Add there are two options
    '1. for insert
    '2. for update
    
    If Me.TxDEID.Tag & "" = "" Then
    'this is for insert new
    'add data to table
    CurrentDb.Execute "INSERT INTO LLIS(LessonID, [Lesson Title], Phase, Stage, KeyLL, [Well Name], [Field Name], [Rig Name], Section, Activity, Approvedby, Vendor, LLDate, [Originator Initials], Department, Status, Description, [Learning Points]) " & _
        "VALUES(" & Me.TxDELTitle & ",'" & Me.CbDEPhase & "','" & _
         Me.CbDEStage & "','" & Me.YNDEKeyLL & "', '" & Me.CbDEApprove & "', '" & Me.CbDEWellName & "','" & Me.CbDEFieldName & "','" & Me.CbDERigName & "','" & Me.CbDESection & "','" & Me.CbDEActivity & "','" & Me.CbDEVendor & "',#" & Me.TxDEDate & "#,'" & Me.CbDEOrigIni & "','" & Me.CbDEDept & "','" & Me.CbDEStatus & "','" & Me.TxDEDescr & "','" & Me.TxDELearnings & "')"
         
 Else
    'otherwise (Tag of TxDEID store the id of Lesson to be modified)
       CurrentDb.Execute "UPDATE LLIS " & _
    " SET LessonID=" & Me.TxDEID & _
    ", [Lesson Title]='" & Me.TxDELTitle & "'" & _
    ", Phase='" & Me.CbDEPhase & "'" & _
    ", Stage='" & Me.CbDEStage & "'" & _
    ", KeyLL='" & Me.YNDEKeyLL & "'" & _
    ", [Well Name]='" & Me.CbDEWellName & "'" & _
    ", [Field Name]='" & Me.CbDEFieldName & "'" & _
    ", [Rig Name]='" & Me.CbDERigName & "'" & _
    ", Section='" & Me.CbDESection & "'" & _
    ", Activity='" & Me.CbDEActivity & "'" & _
    ", Vendor='" & Me.CbDEVendor & "'" & _
    ", LLDate=#" & Me.TxDEDate & "#" & _
    ", [Originator Initials]='" & Me.CbDEOrigIni & "'" & _
    ", Department='" & Me.CbDEDept & "'" & _
    ", Status='" & Me.CbDEStatus & "'" & _
    ", Description='" & Me.TxDEDescr & "'" & _
    ", [Learning Points]='" & Me.TxDELearnings & "'" & _
    ", Approvedby='" & Me.CbDEApprove & "'" & _
    " WHERE LessonID=" & Me.TxDEID.Tag

    
 End If
      
    'clear form
    CmdClear_Click
    'refresh data in list form
    LPQsubform.Form.Requery
End Sub

Thanks again!

 

[Aleni]

The primary key autonumber will get inserted automatically. You do not have to insert in manually. So, do not select it from the table only.
Try to insert other values and leave the field LessonID.
I hope this will help because it worked out with me.

 

[michal79]

Hi,

Thanks, that's what I did... I left LessonsID and went straight to [Lesson Title] after SET.

Now my problem is that my subform from which I initially get the data to edit in my form won't reload ... instead it just goes blank.

After updating the record I clear form (CmdClear_Click) and the requery the subform:

LPQsubform.Form.Requery

... but it doesn't refresh

Any suggestions?

Private Sub CmdClear_Click()
    Me.TxDEID.ControlSource = "LessonID"
    Me.TxDELTitle = ""
    Me.CbDEPhase = ""
    Me.CbDEStage = ""
    Me.YNDEKeyLL = ""
    Me.CbDEApprove = ""
    Me.CbDEWellName = ""
    Me.CbDEFieldName = ""
    Me.CbDERigName = ""
    Me.CbDESection = ""
    Me.CbDEActivity = ""
    Me.CbDEVendor = ""
    Me.TxDEDate = ""
    Me.CbDEOrigIni = ""
    Me.CbDEDept = ""
    Me.CbDEStatus = ""
    Me.TxDEDescr = ""
    Me.TxDELearnings = ""
    
    'focus on ID text box
    Me.TxDEID.SetFocus
    'set button edit to enable
    Me.Editbutton.Enabled = True
    'change caption of button to Add
    Me.SaveRecord.Caption = "Add"
    'clear tag on TXDEID for reset new
    Me.TxDEID.Tag = ""
     
    Me.TxDELTitle.SetFocus
       
End Sub

.. and the SaveRecord code is:

Private Sub SaveRecord_Click()
    'when we click on button Add there are two options
    '1. for insert
    '2. for update
    
    If Me.TxDEID.Tag & "" = "" Then
    'this is for insert new
    'add data to table
    CurrentDb.Execute "INSERT INTO LLIS(LessonID, [Lesson Title], Phase, Stage, KeyLL, [Well Name], [Field Name], [Rig Name], Section, Activity, Approvedby, Vendor, LLDate, [Originator Initials], Department, Status, Description, [Learning Points]) " & _
        "VALUES(" & Me.TxDELTitle & ",'" & Me.CbDEPhase & "','" & _
         Me.CbDEStage & "','" & Me.YNDEKeyLL & "', '" & Me.CbDEApprove & "', '" & Me.CbDEWellName & "','" & Me.CbDEFieldName & "','" & Me.CbDERigName & "','" & Me.CbDESection & "','" & Me.CbDEActivity & "','" & Me.CbDEVendor & "',#" & Me.TxDEDate & "#,'" & Me.CbDEOrigIni & "','" & Me.CbDEDept & "','" & Me.CbDEStatus & "','" & Me.TxDEDescr & "','" & Me.TxDELearnings & "')"
         
 Else
    'otherwise (Tag of TxDEID store the id of Lesson to be modified)
       CurrentDb.Execute "UPDATE LLIS " & _
    " SET [Lesson Title]='" & Me.TxDELTitle & "'" & _
    ", Phase='" & Me.CbDEPhase & "'" & _
    ", Stage='" & Me.CbDEStage & "'" & _
    ", KeyLL='" & Me.YNDEKeyLL & "'" & _
    ", [Well Name]='" & Me.CbDEWellName & "'" & _
    ", [Field Name]='" & Me.CbDEFieldName & "'" & _
    ", [Rig Name]='" & Me.CbDERigName & "'" & _
    ", Section='" & Me.CbDESection & "'" & _
    ", Activity='" & Me.CbDEActivity & "'" & _
    ", Vendor='" & Me.CbDEVendor & "'" & _
    ", LLDate=#" & Me.TxDEDate & "#" & _
    ", [Originator Initials]='" & Me.CbDEOrigIni & "'" & _
    ", Department='" & Me.CbDEDept & "'" & _
    ", Status='" & Me.CbDEStatus & "'" & _
    ", Description='" & Me.TxDEDescr & "'" & _
    ", [Learning Points]='" & Me.TxDELearnings & "'" & _
    ", Approvedby='" & Me.CbDEApprove & "'" & _
    " WHERE LessonID=" & Me.TxDEID.Tag
    
 End If
      
    'clear form
    CmdClear_Click
    'refresh data in list form
    LPQsubform.Form.Requery
    
End Sub

 

[maximark]

I have a couple screens like this. When i save the data on the details screen, i close it and use the code below to refresh the list screen

Private Sub Form_Activate()
Me.Requery
End Sub