Can't Bookmark a form from a vba subroutine!

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

The following code in the form's vba module works fine
when triggered from a listbox After update event. Its
objective is to synchronize the form to the item in a
listbox that a user clicked on.
******************************************************
Dim rs As Object
Set rs = Me.Recordset.Clone

Me.LstB_MainFormListBox1 = _
LstB_MainFormListBox1.Column(0, 1)

rs.FindFirst "[ClientTypesID] = " & _
Str(Nz(Me![LstB_MainFormListBox1], 0))

Set rs = Nothing
******************************************************

However since I have about 30 forms in my application
where I use this code fragment about 3 to 5 times per
form, I find it is alot of redundant coding. So I would
like to make a subroutine where I could hard code it once
and call this routine from my forms.
I created the subroutine to do what the above code does.
However the line:

"If Not rs.EOF Then f_Form.Bookmark = rs.Bookmark"

does not bookmark the form! I believe that the Findfirst
works but now I have to reflect what Findfirst found
(Synchronize) to the form so the form displays the right
data. The form stays put and nothing happens at the form
level. Maybe bookmarking only works on the form's vba
modules and not in a subroutine like I have it.
I had dropped BOOKMARKING a while back
and now I would like to render my code a little more
efficient and find I am running into the same problem!
Can anyone help!

Robert.

******************************************************
Public Sub SetFormTo(f_FormAny1 As Form, _
ctl_LstB_MainFormListBox1 As Control, _
s_NameOfColumnIDInTable As String)

Dim rs As Object
Set rs = f_FormAny1.Recordset.Clone

rs.FindFirst s_NameOfColumnIDInTable = _
Str(Nz(ctl_LstB_MainFormListBox1, 0))

If Not rs.EOF Then f_FormAny1.Bookmark = rs.Bookmark

Set rs = Nothing

******************************************************
 
Paste this function into a standard moule. Call it like this:
Call Move2Record(Me, "SomeField = 999")

The function saves the record, and attempts to locate a match. If no match
is found and the form is filtered, it turns off the form's filter and tries
again. The return value is True if successful. That lets you notify the user
if you wish, e.g.:
If Not Move2Record(Me, "SomeField = 999") Then
MsgBox "Nowhere to be found."
End If

Because the function accepts a WHERE clause, your code can pass a string
that has the delimiters for Text or Date types, or that includes the IN
operator with values from a multi-select listbox, or whatever takes your
fancy.


Public Function Move2Record(frm As Form, strWhere As String) As Boolean
On Error GoTo Err_Move2Record
'Purpose: Move a bound form to the record matching the Where string.
'Return: True if successful.
'Arguments: frm = reference to the form.
' strWhere = the WHERE clause to match.

Dim rs As DAO.Recordset

If frm.Dirty Then 'Save before move.
frm.Dirty = False
End If

Set rs = frm.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
If frm.FilterOn Then 'Not found: try again without the filter.
Set rs = Nothing
frm.FilterOn = False
Set rs = frm.RecordsetClone
rs.FindFirst strWhere
End If
End If

If Not rs.NoMatch Then
'Display the record.
frm.Bookmark = rs.Bookmark
If Not frm.Visible Then
frm.Visible = True
End If
frm.SetFocus
Move2Record = True
End If

Exit_Move2Record:
Set rs = Nothing
Exit Function

Err_Move2Record:
If Err.Number = 2449 Then 'Invalid expression: can't set focus to form
(perhaps subform?)
Resume Next
Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Move2Record
End If
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Robert said:
The following code in the form's vba module works fine
when triggered from a listbox After update event. Its
objective is to synchronize the form to the item in a
listbox that a user clicked on.
******************************************************
Dim rs As Object
Set rs = Me.Recordset.Clone

Me.LstB_MainFormListBox1 = _
LstB_MainFormListBox1.Column(0, 1)

rs.FindFirst "[ClientTypesID] = " & _
Str(Nz(Me![LstB_MainFormListBox1], 0))

Set rs = Nothing
******************************************************

However since I have about 30 forms in my application
where I use this code fragment about 3 to 5 times per
form, I find it is alot of redundant coding. So I would
like to make a subroutine where I could hard code it once
and call this routine from my forms.
I created the subroutine to do what the above code does.
However the line:

"If Not rs.EOF Then f_Form.Bookmark = rs.Bookmark"

does not bookmark the form! I believe that the Findfirst
works but now I have to reflect what Findfirst found
(Synchronize) to the form so the form displays the right
data. The form stays put and nothing happens at the form
level. Maybe bookmarking only works on the form's vba
modules and not in a subroutine like I have it.
I had dropped BOOKMARKING a while back
and now I would like to render my code a little more
efficient and find I am running into the same problem!
Can anyone help!

Robert.

******************************************************
Public Sub SetFormTo(f_FormAny1 As Form, _
ctl_LstB_MainFormListBox1 As Control, _
s_NameOfColumnIDInTable As String)

Dim rs As Object
Set rs = f_FormAny1.Recordset.Clone

rs.FindFirst s_NameOfColumnIDInTable = _
Str(Nz(ctl_LstB_MainFormListBox1, 0))

If Not rs.EOF Then f_FormAny1.Bookmark = rs.Bookmark

Set rs = Nothing

******************************************************
Click to expand...
 
Back
Top