wrong record displayed

[SandyR]

Sorry - I posted part of this message by accident, so here it is in its
entirety:

I have a form which will be instantiated multiple times. The first time it
will be opened from the switchboard, and I want it positioned to the last
record in the file. Later it will be opened from another form which will
pass it the record number to display. I haven't gotten that far yet - I am
still trying to get the first open to work correctly.

The switchboard runs this routine:


********** start of code **********
'---------------------------------------------------------------------------------------
' Procedure : Openform
' DateTime : 1/18/2007 12:04
' Author : rosensan
' Purpose : This procedure opens a new instance of the complaint form,
and makes the
' switchboard invisible until the form is closed
'---------------------------------------------------------------------------------------
'
Public Function Openform(recordno As Long)
Dim cfrm As Form

On Error GoTo Openform_Error
' Create a new instance of the form
Set cfrm = New Form_Form1
' use the parameter to set the sort order
cfrm.Visible = True
cfrm.Caption = cfrm.Hwnd & " work order"
' append it to our collection
clnMainForm.Add Item:=cfrm, Key:=CStr(cfrm.Hwnd)
Set cfrm = Nothing
Openform_exit:
On Error GoTo 0
Exit Function

Openform_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Openform of VBA Document Form_Switchboard"
Resume Openform_exit
End Function
****** end of code *****************

When the statement creating the new instance is run, the follwoing code runs
in the newly opened form - it is called from the FORM_OPEN procedure:

******** start of code ********************
Public Sub gotothisrecord(recordno As Long)

On Error GoTo gotothisrecord_Error
Set RS = Me.Recordset.Clone
If recordno > 0 Then
'find the record that matches the control
RS.FINDFIRST "[] = " & Str(recordno)
Else
RS.MoveLast
End If
Me.Bookmark = RS.Bookmark
MsgBox " GOTO " & recordno & " BOOKMARK " & Str([RECORD NUMBER])


gotothisrecord_exit:
On Error GoTo 0
Exit Sub

gotothisrecord_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
gotothisrecord of VBA Document Form_Switchboard"
Resume gotothisrecord_exit
End Sub
******** end of code **********************

I know that this works correctly because of the diagnostic MSGBOX that I put
in. In spite of this, the form opens showing the first record in the file.
There doesn't seem to be anything happening after this to reset the bookmark.
Can anyone help?

 

[Marshall Barton]

I have a form which will be instantiated multiple times. The first time it
will be opened from the switchboard, and I want it positioned to the last
record in the file. Later it will be opened from another form which will
pass it the record number to display. I haven't gotten that far yet - I am
still trying to get the first open to work correctly.

The switchboard runs this routine:


********** start of code **********
'---------------------------------------------------------------------------------------
' Procedure : Openform
' DateTime : 1/18/2007 12:04
' Author : rosensan
' Purpose : This procedure opens a new instance of the complaint form,
and makes the
' switchboard invisible until the form is closed
'---------------------------------------------------------------------------------------
'
Public Function Openform(recordno As Long)
Dim cfrm As Form

On Error GoTo Openform_Error
' Create a new instance of the form
Set cfrm = New Form_Form1
' use the parameter to set the sort order
cfrm.Visible = True
cfrm.Caption = cfrm.Hwnd & " work order"
' append it to our collection
clnMainForm.Add Item:=cfrm, Key:=CStr(cfrm.Hwnd)
Set cfrm = Nothing
Openform_exit:
On Error GoTo 0
Exit Function

Openform_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Openform of VBA Document Form_Switchboard"
Resume Openform_exit
End Function
****** end of code *****************

When the statement creating the new instance is run, the follwoing code runs
in the newly opened form - it is called from the FORM_OPEN procedure:

******** start of code ********************
Public Sub gotothisrecord(recordno As Long)

On Error GoTo gotothisrecord_Error
Set RS = Me.Recordset.Clone
If recordno > 0 Then
'find the record that matches the control
RS.FINDFIRST "[] = " & Str(recordno)
Else
RS.MoveLast
End If
Me.Bookmark = RS.Bookmark
MsgBox " GOTO " & recordno & " BOOKMARK " & Str([RECORD NUMBER])


gotothisrecord_exit:
On Error GoTo 0
Exit Sub

gotothisrecord_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
gotothisrecord of VBA Document Form_Switchboard"
Resume gotothisrecord_exit
End Sub
******** end of code **********************

I know that this works correctly because of the diagnostic MSGBOX that I put
in. In spite of this, the form opens showing the first record in the file.
There doesn't seem to be anything happening after this to reset the bookmark.

These things do get tricky, but shouldn't that code be in
the form's Load event?

What is the FindFirst doing without a field name in the [ ]?

 

[SandyR]

Moving the code to the Form Load procedure did the trick. Thank you so much!

Could you tell me why the form belongs in the load rather than the open
procedure?

I have a form which will be instantiated multiple times. The first time it
will be opened from the switchboard, and I want it positioned to the last
record in the file. Later it will be opened from another form which will
pass it the record number to display. I haven't gotten that far yet - I am
still trying to get the first open to work correctly.

The switchboard runs this routine:


********** start of code **********
'---------------------------------------------------------------------------------------
' Procedure : Openform
' DateTime : 1/18/2007 12:04
' Author : rosensan
' Purpose : This procedure opens a new instance of the complaint form,
and makes the
' switchboard invisible until the form is closed
'---------------------------------------------------------------------------------------
'
Public Function Openform(recordno As Long)
Dim cfrm As Form

On Error GoTo Openform_Error
' Create a new instance of the form
Set cfrm = New Form_Form1
' use the parameter to set the sort order
cfrm.Visible = True
cfrm.Caption = cfrm.Hwnd & " work order"
' append it to our collection
clnMainForm.Add Item:=cfrm, Key:=CStr(cfrm.Hwnd)
Set cfrm = Nothing
Openform_exit:
On Error GoTo 0
Exit Function

Openform_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Openform of VBA Document Form_Switchboard"
Resume Openform_exit
End Function
****** end of code *****************

When the statement creating the new instance is run, the follwoing code runs
in the newly opened form - it is called from the FORM_OPEN procedure:

******** start of code ********************
Public Sub gotothisrecord(recordno As Long)

On Error GoTo gotothisrecord_Error
Set RS = Me.Recordset.Clone
If recordno > 0 Then
'find the record that matches the control
RS.FINDFIRST "[] = " & Str(recordno)
Else
RS.MoveLast
End If
Me.Bookmark = RS.Bookmark
MsgBox " GOTO " & recordno & " BOOKMARK " & Str([RECORD NUMBER])


gotothisrecord_exit:
On Error GoTo 0
Exit Sub

gotothisrecord_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
gotothisrecord of VBA Document Form_Switchboard"
Resume gotothisrecord_exit
End Sub
******** end of code **********************

I know that this works correctly because of the diagnostic MSGBOX that I put
in. In spite of this, the form opens showing the first record in the file.
There doesn't seem to be anything happening after this to reset the bookmark.

These things do get tricky, but shouldn't that code be in
the form's Load event?

What is the FindFirst doing without a field name in the [ ]?

 

[Marshall Barton]

The Open event initializes the form and control properties
(e.g. RecordSource). The Load event is when the data is
loaded. It makes sense that you can not perform data
related operations when the data source might not yet be
specified.

As far as I am concerned, any attempt to manipulate or use
data in the open event should produce an error instead of
just not doing it.
--
Marsh
MVP [MS Access]

Moving the code to the Form Load procedure did the trick. Thank you so much!

Could you tell me why the form belongs in the load rather than the open
procedure?

I have a form which will be instantiated multiple times. The first time it
will be opened from the switchboard, and I want it positioned to the last
record in the file. Later it will be opened from another form which will
pass it the record number to display. I haven't gotten that far yet - I am
still trying to get the first open to work correctly.

The switchboard runs this routine:


********** start of code **********
'---------------------------------------------------------------------------------------
' Procedure : Openform
' DateTime : 1/18/2007 12:04
' Author : rosensan
' Purpose : This procedure opens a new instance of the complaint form,
and makes the
' switchboard invisible until the form is closed
'---------------------------------------------------------------------------------------
'
Public Function Openform(recordno As Long)
Dim cfrm As Form

On Error GoTo Openform_Error
' Create a new instance of the form
Set cfrm = New Form_Form1
' use the parameter to set the sort order
cfrm.Visible = True
cfrm.Caption = cfrm.Hwnd & " work order"
' append it to our collection
clnMainForm.Add Item:=cfrm, Key:=CStr(cfrm.Hwnd)
Set cfrm = Nothing
Openform_exit:
On Error GoTo 0
Exit Function

Openform_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Openform of VBA Document Form_Switchboard"
Resume Openform_exit
End Function
****** end of code *****************

When the statement creating the new instance is run, the follwoing code runs
in the newly opened form - it is called from the FORM_OPEN procedure:

******** start of code ********************
Public Sub gotothisrecord(recordno As Long)

On Error GoTo gotothisrecord_Error
Set RS = Me.Recordset.Clone
If recordno > 0 Then
'find the record that matches the control
RS.FINDFIRST "[] = " & Str(recordno)
Else
RS.MoveLast
End If
Me.Bookmark = RS.Bookmark
MsgBox " GOTO " & recordno & " BOOKMARK " & Str([RECORD NUMBER])


gotothisrecord_exit:
On Error GoTo 0
Exit Sub

gotothisrecord_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
gotothisrecord of VBA Document Form_Switchboard"
Resume gotothisrecord_exit
End Sub
******** end of code **********************

I know that this works correctly because of the diagnostic MSGBOX that I put
in. In spite of this, the form opens showing the first record in the file.
There doesn't seem to be anything happening after this to reset the bookmark.

These things do get tricky, but shouldn't that code be in
the form's Load event?

What is the FindFirst doing without a field name in the [ ]?

 

[SandyR]

Thank you again - you have been an enormous help. I have been stuck on this
for days.

The Open event initializes the form and control properties
(e.g. RecordSource). The Load event is when the data is
loaded. It makes sense that you can not perform data
related operations when the data source might not yet be
specified.

As far as I am concerned, any attempt to manipulate or use
data in the open event should produce an error instead of
just not doing it.
--
Marsh
MVP [MS Access]

Moving the code to the Form Load procedure did the trick. Thank you so much!

Could you tell me why the form belongs in the load rather than the open
procedure?

SandyR wrote:
I have a form which will be instantiated multiple times. The first time it
will be opened from the switchboard, and I want it positioned to the last
record in the file. Later it will be opened from another form which will
pass it the record number to display. I haven't gotten that far yet - I am
still trying to get the first open to work correctly.

The switchboard runs this routine:


********** start of code **********
'---------------------------------------------------------------------------------------
' Procedure : Openform
' DateTime : 1/18/2007 12:04
' Author : rosensan
' Purpose : This procedure opens a new instance of the complaint form,
and makes the
' switchboard invisible until the form is closed
'---------------------------------------------------------------------------------------
'
Public Function Openform(recordno As Long)
Dim cfrm As Form

On Error GoTo Openform_Error
' Create a new instance of the form
Set cfrm = New Form_Form1
' use the parameter to set the sort order
cfrm.Visible = True
cfrm.Caption = cfrm.Hwnd & " work order"
' append it to our collection
clnMainForm.Add Item:=cfrm, Key:=CStr(cfrm.Hwnd)
Set cfrm = Nothing
Openform_exit:
On Error GoTo 0
Exit Function

Openform_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Openform of VBA Document Form_Switchboard"
Resume Openform_exit
End Function
****** end of code *****************

When the statement creating the new instance is run, the follwoing code runs
in the newly opened form - it is called from the FORM_OPEN procedure:

******** start of code ********************
Public Sub gotothisrecord(recordno As Long)

On Error GoTo gotothisrecord_Error
Set RS = Me.Recordset.Clone
If recordno > 0 Then
'find the record that matches the control
RS.FINDFIRST "[] = " & Str(recordno)
Else
RS.MoveLast
End If
Me.Bookmark = RS.Bookmark
MsgBox " GOTO " & recordno & " BOOKMARK " & Str([RECORD NUMBER])


gotothisrecord_exit:
On Error GoTo 0
Exit Sub

gotothisrecord_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
gotothisrecord of VBA Document Form_Switchboard"
Resume gotothisrecord_exit
End Sub
******** end of code **********************

I know that this works correctly because of the diagnostic MSGBOX that I put
in. In spite of this, the form opens showing the first record in the file.
There doesn't seem to be anything happening after this to reset the bookmark.


These things do get tricky, but shouldn't that code be in
the form's Load event?

What is the FindFirst doing without a field name in the [ ]?