Having no luck with .FindFirst

[GregoryWest]

I have a VBA routine that is called from a button on a form. This code opens a table containing a bunch of audit records, then processes each records. Part of the processing is to go to the master parts table and update two fields. Because of the way the audit table is created I am guaranteed that the select will find a record so I have no error checking there.

My problem is how is the best way to do this?

I tried this inside the loop:
strSql_out = "select * from public_parts where partnumber = " & Chr(39) & Mid(Str(rs_in!PartID), 2, 15) & Chr(39) & ";"
Set rs_out = DBEngine(0)(0).OpenRecordset(strSql_out)
rs_out.Edit
rs_out!countdue = Now() + Str(rs_out!cycledays)
rs_out.Update
rs_out.Close

And I get a 3197 error saying someone else if modifying the record. This is for sure not happening as I am the only one in this database.

Then I tried the set rs_out DBEngine(0)(0).OpenRecordset(strSql_out)
with out the where clause in the string before the loop and doing a:
rs_out.FindFirst (partnumber = 123456789) and I get a syntax error.

What is the best way to do this? It should not be this frustrating.

Thanks in advance!

 

[Cronk]

Partnumber would appear to be a Text field, given the chr(39) in the code to generate single quotes.

This is then why
rs_out.FindFirst (partnumber = 123456789)
would give an error.

Use rs_out.FindFirst (partnumber = '123456789')

But as to why your recordset cannot be updated, I'd insert a line with
debug.print strSql_out
and paste the output from the Immediate window into a new query and see if you can update it from the query.

That might reveal something.

 

[arnelgp]

if master part table is linked to audit record table, it will complain since you have it open in your form.
if this is the case, use the recordset of your form using RecordsetClone.