Record Sets to update a table

[Adzi]

Hi,

I'm still working on a single project (and have been for the past year?) which is starting to annoy me greatly.

Basically;
I have a recordset setup to update the data in a table after an append query copies the record accross to an archive, but setting the recordset to update the right record isnt working.

I keep getting a run-time 3070;

The Jet Database doesnt recognise 'CMP' as a valid field name or expression.

My Code;

Set EditBooking = CurrentDb.OpenRecordset("tblCampingBookings", dbOpenDynaset)
EditBooking.FindFirst "[Booking Reference]=" & BookingRefB & ""

BookingRefB is a string consisting of "CMP-BCAxxxx" where the x's represent digits in sequential order.

Can anyone help me with this?

Cheers,
-Adzi

 

[Rabbie]

Try something like this

Set EditBooking = CurrentDb.OpenRecordset("tblCampingBookings", dbOpenDynaset)
EditBooking.FindFirst "[Booking Reference]= '" & BookingRefB & "'"

 

[DCrake]

Simple Software Solutions

Don't know if this works any faster but my normal approach is the following

Set EditBooking = CurrentDb.OpenRecordset("Select * From tblCampingBookings Where [Booking Reference]='" & BookingRefB & "'")

Your approach brings the whole of the database and then processes the filter, whereas the above only returns the one record. If you have a large table then it could impact on performance.

I assume you have NoMatch validation later on in the code?

CodeMaster: