Updating a record in a table

[irunergoiam]

I'm trying to update a table (named [Data File] from a form. Since the record already exists, I need to identify which record is to be updated. I am using a WHERE statement (where Me.cmbCandidate.Column(1) is the Req field in the table, Me.cmbCandidate.Column(2) is the Applicant ID in the table and Me.cmbCandidate.Column(8) is the DateTime stamp in the table. See code below...

Dim strSQL As String
Dim strCriteria As String

strSQL = ""
strSQL = strSQL & " INSERT INTO [Data File]"
strSQL = strSQL & " WHERE((([Data File].Req) = Me.cmbCandidate.Column(1)) And ([Data File].ApplicantID) = Me.cmbCandidate.Column(2)) And ([Data File].DateTime) = Me.cmbCandidate.Column(8)))"
strSQL = strSQL & " ([ValidationComplete], [ValidatedDate], ,[ValidatedTime], [ValidatedBy])"
strSQL = strSQL & " Values"
strSQL = strSQL & " ('" & "Validated" & "',#" & unbEnteredDate & "#,#" & unbEnteredTime & "#,'" & unbEnteredBy & "')"
DoCmd.RunSQL strSQL

Am I on the right track? What am I missing? Thanks to whoever might add to my fledgling Access knowledge.

 

[RuralGuy]

This may be a stupid question but you do know that the Column() index starts with zero (0), right?

 

[RuralGuy]

Did you use the query builder to create your UpDate query? The syntax does not look right to me.

 

[irunergoiam]

For me there are no stupid Access questions. That said, I do know the columns start with (0). It's not a matter of data updating with the wrong information, i.e., wrong column inputs. It gives me a 3134 error message and takes me to the VB editor, highlighting the DoCmd.RunSQL statement.

I did not use a query builder for the code. Basically modified an INSERT INTO statement that I used somewhere else and am trying to add the WHERE part of it as I need to restrict the record that is updated by Req, ApplicantID, and TimeDate stamp. This is really easy using an Update query with the Criteria based on the form fields. I'm hoping to get away from relying so heavily on queries and think this is a good next step in my Access progression. Thanks for any guidance you might be able to offer.

 

[mbreu996]

What I usually do, not being fluent in SQL is use the query design interface. Then I click SQL view and copy the code into VBA. You can use dummy parameters in the design interface and then replace them with VBA variables in the VBA editor. The convention I use is to populate my string all at once instead of in chunks like you have - it makes me see the punctuation more clearly, which is a common source of error. By testing my "dummy query" using the design query interface 1st, I can rule out many sources of error. I always take it in little steps - change 1 thing at a time and test.

strSQL = "Line1" & _
"Line2" & _
"Line3"

That way it matches what you see in the SQL view.

Hope this helps, and I apologize for wasting your time if it doesn't.

 

[irunergoiam]

Hmmm... I've not done this before and don't know if the criteria from an Update query (in SQL) "transfers" into VBA as the Update query Criteria restrict based on the form!... instead of unbound fields that have a Column(*) correlate.

Worth a try though...

Much thanks!!

 

[mbreu996]

The SQL code the query design view editor writes for you does work with the VBA. I have done unspeakable things using this approach - the sky really is the limit when you toss in vba variables into SQL queries as I'm so awkwardly am attempting to describe.