"NZ" function doesn't work with "Me" (HELP)

  • 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 a
subroutine as below but Access generates the following
error message:
"App1 can't find the form 'f_Form' refered to in a macro
expression or in VBA code".
I am not referencing the form right but I have tried many
ways. I don't know if its even possible to do it this
way. Can someone please help.
Robert.

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

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

rs.FindFirst "'[s_NameOfColumnIDInTable]' = " & _
Str(Nz(Forms!f_Form![ctl_LstB_MainFormListBox1], 0))

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

Set rs = Nothing

End Sub
******************************************************
 
Hi Robert,

On this line:

rs.FindFirst "'[s_NameOfColumnIDInTable]' = " & _
Str(Nz(Forms!f_Form![ctl_LstB_MainFormListBox1], 0))

Change
Forms!f_Form![ctl_LstB_MainFormListBox1]
To
ctl_LstB_MainFormListBox1

You already have a reference to the control so you don't need to go through
the forms collection or through the form reference.
 
Your help is so much appreciated and thankyou very much
Sandra!
That line of the code worked thanks to you, however the
next 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'm so discouraged, I always had problems with
this "BOOKMARK" thats why I had droped it a while back
and now I am running into the same problem!
Can you help!

Robert.

-----Original Message-----
Hi Robert,

On this line:

rs.FindFirst "'[s_NameOfColumnIDInTable]' = " & _
Str(Nz(Forms!f_Form![ctl_LstB_MainFormListBox1], 0))

Change
Forms!f_Form![ctl_LstB_MainFormListBox1]
To
ctl_LstB_MainFormListBox1

You already have a reference to the control so you don't need to go through
the forms collection or through the form reference.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
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 a
subroutine as below but Access generates the following
error message:
"App1 can't find the form 'f_Form' refered to in a macro
expression or in VBA code".
I am not referencing the form right but I have tried many
ways. I don't know if its even possible to do it this
way. Can someone please help.
Robert.

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

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

rs.FindFirst "'[s_NameOfColumnIDInTable]' = " & _
Str(Nz(Forms!f_Form![ctl_LstB_MainFormListBox1], 0))

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

Set rs = Nothing

End Sub
******************************************************
Click to expand...

.
Click to expand...
 
Hi Robert,

I wonder if it is because you declared rs as object instead of recordset -
or it may be because you are using Recordset.clone instead of
Recordsetclone. Regardless, you might try revising the code as follows by
eliminating the rs varible.

with f_Form.RecordsetClone
.FindFirst "'[s_NameOfColumnIDInTable]' = " & _
Str(Nz(ctl_LstB_MainFormListBox1, 0))
If Not .nomatch Then
f_Form.Bookmark = .Bookmark
endif
end with

The position of the recordset is indeterminate if there is no matching
record after the findfirst - the NoMatch property will be true when this
conditon occurs. The With..End With construct allows you to use the
recordsetclone without declaring a variable.

Let me know if this solves the problem for you.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
Your help is so much appreciated and thankyou very much
Sandra!
That line of the code worked thanks to you, however the
next 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'm so discouraged, I always had problems with
this "BOOKMARK" thats why I had droped it a while back
and now I am running into the same problem!
Can you help!

Robert.

-----Original Message-----
Hi Robert,

On this line:

rs.FindFirst "'[s_NameOfColumnIDInTable]' = " & _
Str(Nz(Forms!f_Form![ctl_LstB_MainFormListBox1], 0))

Change
Forms!f_Form![ctl_LstB_MainFormListBox1]
To
ctl_LstB_MainFormListBox1

You already have a reference to the control so you don't need to go
through the forms collection or through the form reference.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
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 a
subroutine as below but Access generates the following
error message:
"App1 can't find the form 'f_Form' refered to in a macro
expression or in VBA code".
I am not referencing the form right but I have tried many
ways. I don't know if its even possible to do it this
way. Can someone please help.
Robert.

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

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

rs.FindFirst "'[s_NameOfColumnIDInTable]' = " & _
Str(Nz(Forms!f_Form![ctl_LstB_MainFormListBox1], 0))

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

Set rs = Nothing

End Sub
******************************************************
Click to expand...

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