Run time error 2105 (Unusual Situation)

[oliverte]

Strange problem here.

When I hold down my shift key and bypass the startup sequence this
works fine.

When I open the database normally, hiding the database window, I
receive a run time error 2105, "you can't go to the specified record"
after double clicking this form control. The check box for the "PDStub"
control is checked on the record I double click, and then the 2105
error is displayed.

Here is the code from the control:

'I double click the [PDPeriodEnding] field in a record, and based on
the [site] field in the record that I double click, the procedure loops
through the remaining records (which are always sorted by [site]), and
checks every check box [PDStub] with records that have the same site.

Private Sub PDPeriodEnding_DblClick(Cancel As Integer)

Dim strCurrentSite As String
Dim strMsgBoxResult

strCurrentSite = Site 'refers to the site name in each record (sorted
by this)
strMsgBoxResult = MsgBox("Are you sure you want to mark all Per Diem
stubs received for: " & strCurrentSite & "?", vbYesNo, "Confirm All
Stubs Received")

Select Case strMsgBoxResult
Case Is = 6

Do Until Site <> strCurrentSite
PDStub = -1
DoCmd.GoToRecord , , acNext
Loop

Case Is = 7
MsgBox "Action Cancelled"
End Select
End Sub

Any ideas?

 

[Wayne Morgan]

For starters, you're looping until Site <> strCurrentSite. There is no check
for the end of the recordset. If you do hit the end of the recordset (i.e.
you're at a new record entry point) then Site will be Null and Site <>
strCurrentSite will return Null, not False. If the loop was Loop While Site
= strCurrentSite, then when Null was returned, it would simply be treated as
Not True and the loop would end.

Next, there is a quicker and easier way to do this without having the form
move through the records. The way to do it would be with an Update Query.

Private Sub PDPeriodEnding_DblClick(Cancel As Integer)

Dim strCurrentSite As String
Dim strMsgBoxResult
Dim strSQL As String

strCurrentSite = Site 'refers to the site name in each record (sorted
by this)
strMsgBoxResult = MsgBox("Are you sure you want to mark all Per Diem
stubs received for: " & strCurrentSite & "?", vbYesNo, "Confirm All
Stubs Received")

Select Case strMsgBoxResult
Case Is = 6

'Do Until Site <> strCurrentSite
'PDStub = -1
'DoCmd.GoToRecord , , acNext
'Loop
strSQL = "UPDATE MyTable SET MyTable.PDStub = True WHERE Site = '" &
strCurrentSite & "';"
CurrentDb.Execute strSQL, dbFailOnError

Case Is = 7
MsgBox "Action Cancelled"
End Select
End Sub

This will update all records in the table MyTable where Site =
strCurrentSite to True (i.e. -1) in the field PDStub. If there are (or could
be in the future) apostrophes in the Site name, then the quotes will need to
be modified slightly.

--
Wayne Morgan
MS Access MVP

Strange problem here.

When I hold down my shift key and bypass the startup sequence this
works fine.

When I open the database normally, hiding the database window, I
receive a run time error 2105, "you can't go to the specified record"
after double clicking this form control. The check box for the "PDStub"
control is checked on the record I double click, and then the 2105
error is displayed.

Here is the code from the control:

'I double click the [PDPeriodEnding] field in a record, and based on
the [site] field in the record that I double click, the procedure loops
through the remaining records (which are always sorted by [site]), and
checks every check box [PDStub] with records that have the same site.

Private Sub PDPeriodEnding_DblClick(Cancel As Integer)

Dim strCurrentSite As String
Dim strMsgBoxResult

strCurrentSite = Site 'refers to the site name in each record (sorted
by this)
strMsgBoxResult = MsgBox("Are you sure you want to mark all Per Diem
stubs received for: " & strCurrentSite & "?", vbYesNo, "Confirm All
Stubs Received")

Select Case strMsgBoxResult
Case Is = 6

Do Until Site <> strCurrentSite
PDStub = -1
DoCmd.GoToRecord , , acNext
Loop

Case Is = 7
MsgBox "Action Cancelled"
End Select
End Sub

Any ideas?

 

[TomInQ8]

Worked like a champ!

Working from the query design is very easy for me, but using it in
modules gets complicated.

Thanks for the help!

 

[Wayne Morgan]

If it works well for you in query design, you can still use it in modules.
You can design the query in query design mode then use one of two options.
1) Change to SQL view after you've designed the query and copy/paste the SQL
into the module. 2) Save the query and call the saved query. That would
change the Execute statement to:

CurrentDb.QueryDefs("MyQuery").Execute dbFailOnError

If you are going to use CurrentDb for a lot of items, it would be better to
assign it to an object variable first, but for just a single line, this
works fine.

Dim db As DAO.Database, qdf As DAO.Querydef
Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery")
qdf.Execute dbFailOnError
Set qdf = Nothing
Set db = Nothing