Every 382 Records

  • Thread starter Thread starter Gee...
  • Start date Start date
G

Gee...

I have a simple line to put a date into a table using a form. The tables
have an average of 300,000 records in them and to put in a pre-determined
date I just run:

Private Sub ID_GotFocus()
Date = "08/04/2008"
DoCmd.GoToRecord , , acNext
End Sub

When it's gone through the whole thing and put in the dates, I delete it
because it's only used once...the tables are data for that month so never
update.

My problem is that every 382nd record, it stops and says: :Run-time error
'2105': You can't go th the specified record."

I just hit E to end and scroll one and it continues to run through the
records and insert the date. But then it will go through another 382
records, stop and give me the message again and I have to hit E and scroll.
It's kind of a pain...why does it keep stopping like that and how can I make
it not do that anymore?
 
Just out of curiousity, why wouldn't a simple update query running off the
form work? You could update the date field all in one fell swoop and be done,
rather than going line by line. Unless there's a need to go line by line, I
don't see why that wouldn't be the simplest solution.
 
That would be much easier, I just couldn't quite figure out how to do
it...can you tell me?
Thanks!
 
Basically, you would need to either call an Access query using the
"DoCmd.OpenQuery" command, or you could write an SQL query in code that would
be able to have variables built in so you could change the date at any time
without having to change the form's code.

Try this first and see if it works. Replace your current code with this:

Private Sub ID_GotFocus()
Dim tmpDate as Date ' Declare the variable
tmpDate = #08/04/2008# ' Set the variable (using #'s surrounding the date to
let Access know to use the value as a date - don't use quotation marks for
dates, Access will see the data inside the quotes as text)
DoCmd.RunSQL "UPDATE [your table name here] SET [the name of the date field]
= " & tmpDate & "" ' The SQL query to update the date to the value contained
in tmpDate
End Sub

Using this code, with a slight modification, will allow you to enter the
date to fill in all the fields with. Change the "tmpDate" line to this:

tmpDate = Inputbox ("Date?","Enter a date")

to pop up an InputBox to type the date into. The syntax is InputBox
("prompt","title"), where prompt is the text you want to display on the main
part of the InputBox, and title is what you want displayed in the title bar
of the InputBox.
 
Back
Top