Keeps Saying DeBug

[Gee...]

I have a simple code to insert a date in a 324,000 record table.

Private Sub ID_GotFocus()
Date = "07/04/2008"
If Model <> "" Then
DoCmd.GoToRecord , , acNext
End If

End Sub

For some reason, it zips through a bunch of records, inserting the date,
then says "Run-time error '2105': You can't go to the specified record".

I click End, then go on and it works for a while, zipping through records,
inserting the date, then stops again and gives me that same error.

Anyone have any idea how I can make this work better?
Thanks in advance for any help.
Gee.

 

[Graham Mandeno]

Hi Gee

Usually you will get this error if you are trying to move to another record
when the current record cannot be saved for some reason.

There are a few questions and problems about your approach.

1. I'm not sure why you are using a GotFocus event to update a whole bunch
of records. This event is usually used to work on the current record only
or, more often, to change the properties of controls on your form.

2. Date is a predefined function in VBA that returns the current date. It
is not a good idea to have a field or control named "Date". It will cause
confusion as to which "Date" you are referring to. I suggest renaming your
field "PurchaseDate" or whatever is appropriate.

3. Is Model a field in your underlying table? If so, it is very unusual for
a text field to contain a zero-length string ("") unless you have set the
Required and AllowZeroLength properties on that field. A blank field is far
more likely to contain Null.

4. The standard method to update a large number of records is to use an
update query.

Taking a stab in the dark, I am guessing that for every record in your table
where Model is blank, you want to set some date field to 4-July-2008. The
following SQL statement will do this for you:

Update YourTable set YourDateField=#2008-07-04# where Model is null;

 

[Jeff Boyce]

In addition to Graham's comments, your code tries to use "07/04/2008" as a
date. Yes, yes, I know, it LOOKS like a date, but when you tell Access
you're giving it something between quote marks, you are telling Access you
are giving it a string value. Date/time values are NOT string values!

Try delimiting the date (date/time) value with the "#" (pound sign) -- that
way Access knows its a real date.

Regards

Jeff Boyce
Microsoft Office/Access MVP