INSERT or UPDATE Query to Add Value to Subform Record

[RunTime91]

Greetings...



I am trying to write a query for access vba that will trigger from either an AfterUpdate event or a CommandButton Click event.

The end goal is to simply add the value "Yes" to the field of the specific record in the subform being worked on by the enduser.

The AfterUpdate event would be on an already bound textbox and of course the Click event would be on a CmdButton

The Update Query I created updated 'all' the records in the table instead of just the one being worked on.

Below is my attempt at an INSERT query

CurrentDb.Execute "INSERT INTO tblOOTDetail_New ([Researched])" & _
"VALUES('Yes');"

While the above query does not error - it also does not place the value of 'Yes' into the record being worked on

I have tried about every iteration of the above code and get an error (mostly syntax errors) on every iteration except the above...

Help??

Thank You...

[kd2017]

To update an existing record you'll need to use the UPDATE command. https://www.w3schools.com/sql/sql_update.asp

You'll want to be careful to specify the record(s) you want to update in the WHERE clause. Or you could end up changing the entire table with just the one command.

You can refer to the subform's current record with something like Me.Subfrom_Name_Here.Form!ID_Field_Here

Something like this:

Option Compare Database
Option Explicit


Private Sub UpdateYes()
On Error GoTo ErrHandler
    Dim qry As String
    
    If Not IsNull(Me.SUBFORM_NAME_HERE.Form!ID_FIELD_NAME_HERE) Then
        qry = "UPDATE tblOOTDetail_New SET Researched = 'Yes' WHERE ID_FIELD_NAME_HERE=" & Me.SUBFORM_NAME_HERE.Form!ID_FIELD_NAME_HERE
        
        CurrentDb.Execute qry, dbFailOnError
    Else
        'There is no selected record in the subform
        
    End If
    
ExitHandler:
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description, vbExclamation, "UpdateYes() Error " & Err.Number
    Resume ExitHandler
End Sub



Private Sub Command0_Click()
    UpdateYes
End Sub

Private Sub Form_AfterUpdate()
    UpdateYes
End Sub

[pbaldy]

Is there a reason you don't just change the value of a control on the subform? If you're modifying the same record, you're going to run into a write conflict with this method.

[kd2017]

Paul makes a great point. Sometimes I'm much too quick to do it the hard way.

[pbaldy]

I do the same thing, get focused on fixing the problem presented and forget to step back and look at the big picture.

[RunTime91]

kd - Awesome! Thank you....

I've abandoned the subform for a continuous form and I'm trying to add some additional values to the table which will come from form controls and I can't escape the dreaded syntax error

I have tried every iteration I can possibly think of and still, I get a syntax error...

The below code is showing just one control ( there are actually 8 others) as I have narrowed the SQL statement to its least common denominator in my attempt to isolate the error - no luck

If Not IsNull(Me.RecrdID) Then
        StrQry = "UPDATE tblOOTDetail_New SET Program = '" & Me.cboProgram & "', Researched = 'Yes', " & _
        "WHERE tblOOTDetail_New.RecrdID = '" & Me.RecrdID & "';"
CurrentDb.Execute StrQry, dbFailOnError

I seem to recall reading somewhere that you have to surround numbers, text & dates differently when writing a SQL statement.

As Always - any help is greatly appreciated

RT91

Edit: Hey Paul... I didn't see your responses prior to submitting mine (had the site open but didn't refresh)

Can you expand on your idea?

Thanks guys...

[RunTime91]

Update:

I have abandoned my attempt at trying to run a SQL statement in VBA - I was told you cannot do so on a linked local table - not sure why, but I'm done arguing with the Syntax Error...

My code now consists of simply calling a query I built in the query builder - it looks ridiculously verbose...but it works....the VBA SQL code is below

UPDATE TblOOTDetail_New SET TblOOTDetail_New.DateFirstKeyed = [Forms].[FrmOOTTracking_New].[TxtFirstKeyDte].[Value], TblOOTDetail_New.OOTReasonCode = [Forms].[FrmOOTTracking_New].[CboOOT].[Value], TblOOTDetail_New.DipReason = [Forms].[FrmOOTTracking_New].[CboDIP].[Value], TblOOTDetail_New.ResearcherComments = [Forms].[FrmOOTTracking_New].[TxtComments].[Value], TblOOTDetail_New.ResearcherIntls = [Forms].[FrmOOTTracking_New].[TxtRschrini].[Value], TblOOTDetail_New.AssigndToAssociate = [Forms].[FrmOOTTracking_New].[cboAttToAssoc].[Value], TblOOTDetail_New.AssigndToDepartment = [Forms].[FrmOOTTracking_New].[CboAttToDept].[Value], TblOOTDetail_New.ManagerComments = [Forms].[FrmOOTTracking_New].[txtMgrComments].[Value], TblOOTDetail_New.Program = [Forms].[FrmOOTTracking_New].[CboProgram].[Value], TblOOTDetail_New.Researched = 'STR'
WHERE (((TblOOTDetail_New.RecrdID)=[Forms]![FrmOOTTracking_New]![TxtRcrdID]));

Problem is... and now I understand what Paul was saying about the Write Error - because now that is the error I'm dealing with.

What I am trying to do is simply fill in the rest of a record that is partially uploaded -

Thus the user will open the UI and it is populated with records that need to be researched and the data is added through an Update Query - seems simple to me

The form is a Continuous with Cycle set to All Records and the controls bound - The portion of the record that is uploaded populates textboxes and the researched data is filled in with txt & combo boxes & an

update query

Ideally, when one record is has been completed I want it to 'go away' using a filter that will just leave the remaining records needing to be worked...

I hope this helps you guys help me

Thanks...

[pbaldy]

The problem is you're editing the record via the form, and then concurrently trying to edit it via your code. That gets you a write conflict error. You could actually get around that by saving the record before executing the SQL, but I would expect you to have a control (combo, textbox, etc) bound to the desired field on the form, and simply set that control's value via your code.:

Me.TextboxName = Me.cboProgram

By the way, as long as the table is editable from the front end, you should be able to execute SQL against it. not sure what you mean by "linked local table". A table is either linked or local, not both. This FAQ may help with the delimiters required by different data types:

http://www.baldyweb.com/BuildSQL.htm

[kd2017]

kd - Awesome! Thank you....

Happy to help in any way I can! even if it's how not to do something...

...I get a syntax error...

If Not IsNull(Me.RecrdID) Then
        StrQry = "UPDATE tblOOTDetail_New SET Program = '" & Me.cboProgram & "', Researched = 'Yes', " & _
        "WHERE tblOOTDetail_New.RecrdID = '" & Me.RecrdID & "';"
CurrentDb.Execute StrQry, dbFailOnError

Looks to me like you had a comma after Researched = 'Yes' that you didn't need.

I seem to recall reading somewhere that you have to surround numbers, text & dates differently when writing a SQL statement.

Yes. Numbers don't need to be surrounding with anything, dates need to be surrounded with hash tags like this #12/25/2018#, and strings need to be surrounded my single or double quotes.
If I'm building a query string in vba I find it helpful to print the string to the debug window so I can easily identify where I missed a quotation mark or threw in an extra comma, etc. And I like to actually start off building the query in Access's query designer and just copy and paste the generated code into vba. Allen Browne has a useful tool for this: http://www.allenbrowne.com/ser-71.html

Update:

I have abandoned my attempt at trying to run a SQL statement in VBA - I was told you cannot do so on a linked local table - not sure why, but I'm done arguing with the Syntax Error...

...

Where did you hear that? I don't believe that's true. But in the end Paul's approach is the one you want anyway. Good luck!

[RunTime91]

Kd & Paul ~ Thank You Both! I'm up and running!

Guys like yourselves are the reason guys like me come here when we are getting mercilessly beat-up by Access..

Thanks Again Guys!