Wayne-In-Manchester

  • Thread starter Thread starter Raymond
  • Start date Start date
R

Raymond

Hi again,
I have the form.test2 in form.test1
This is the after event of the textbox.

Private Sub txtSearchReg_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[visitorlog.License_Tag_Number] = " &
Str(Nz(Me![txtSearchReg], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.License_Tag_Number.SetFocus
Me.txtSearchReg = ""
End Sub

The listbox in form2 does not find the record I entered above. The control
source in the list is License Tag Number, row source type table/query, row
source vistitlog table. I dont get any errors after I type in the tag number
but it does not locate the desired record. I'm thinking its in my sources of
list27, but I really dont know.
Any help would be appreciated. Thanks
 
Try changing the Set Rs line to

Set rs = Me.RecordsetClone

Also, why not
Dim rs as DAO.Recordset

I would write the code:

Private Sub txtSearchReg_AfterUpdate()
Dim rs As DAO.Recordset

If Len(Me.txtSearchReg & "") <> 0 Then 'Optional line
Set rs = Me.RecordsetClone
rs.FindFirst "[License_Tag_Number] = " & Me.txtSearchReg

If rs.NoMatch = False then Me.Bookmark = rs.Bookmark

Me.License_Tag_Number.SetFocus
Me.txtSearchReg = Null
Else
'If desired
'Insert alternate code if txtSearchReg is null
End If 'Optional line

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Hi John,
Thanks for input I put your code in but it did not work either.
Tell me about the form2 setup, What should my data tab look like, and the
bound colume, is that bound to the colume I see or colume of tag number in
the table?
form1, When I add an after event that goes into the txtbox it does not want
the txt prefix.

Private Sub SearchReg_AfterUpdate()
End Sub

I can really use some help on this and thank you in advance.

--
Ray J


John Spencer said:
Try changing the Set Rs line to

Set rs = Me.RecordsetClone

Also, why not
Dim rs as DAO.Recordset

I would write the code:

Private Sub txtSearchReg_AfterUpdate()
Dim rs As DAO.Recordset

If Len(Me.txtSearchReg & "") <> 0 Then 'Optional line
Set rs = Me.RecordsetClone
rs.FindFirst "[License_Tag_Number] = " & Me.txtSearchReg

If rs.NoMatch = False then Me.Bookmark = rs.Bookmark

Me.License_Tag_Number.SetFocus
Me.txtSearchReg = Null
Else
'If desired
'Insert alternate code if txtSearchReg is null
End If 'Optional line

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi again,
I have the form.test2 in form.test1
This is the after event of the textbox.

Private Sub txtSearchReg_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[visitorlog.License_Tag_Number] = " &
Str(Nz(Me![txtSearchReg], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.License_Tag_Number.SetFocus
Me.txtSearchReg = ""
End Sub

The listbox in form2 does not find the record I entered above. The control
source in the list is License Tag Number, row source type table/query, row
source vistitlog table. I dont get any errors after I type in the tag number
but it does not locate the desired record. I'm thinking its in my sources of
list27, but I really dont know.
Any help would be appreciated. Thanks
Click to expand...
Click to expand...
 
Hi again

I just tested the code that Joh gave you and it works fine for me.
I think the problem may be that you are searching a sub form for a record on
the main form

Place a text box in the main form (based on a query that holds the reg
numbers)
Ensure that there is a control linked to the reg number on the main form
You can hide the controls if you don't want to see them
Ensure that your main form and subform are linked by the primary field (of
the source of the sub form) - so this will need be be the source of a control
on both forms
This should work ?

--
Wayne
Manchester, England.



Raymond said:
Hi John,
Thanks for input I put your code in but it did not work either.
Tell me about the form2 setup, What should my data tab look like, and the
bound colume, is that bound to the colume I see or colume of tag number in
the table?
form1, When I add an after event that goes into the txtbox it does not want
the txt prefix.

Private Sub SearchReg_AfterUpdate()
End Sub

I can really use some help on this and thank you in advance.

--
Ray J


John Spencer said:
Try changing the Set Rs line to

Set rs = Me.RecordsetClone

Also, why not
Dim rs as DAO.Recordset

I would write the code:

Private Sub txtSearchReg_AfterUpdate()
Dim rs As DAO.Recordset

If Len(Me.txtSearchReg & "") <> 0 Then 'Optional line
Set rs = Me.RecordsetClone
rs.FindFirst "[License_Tag_Number] = " & Me.txtSearchReg

If rs.NoMatch = False then Me.Bookmark = rs.Bookmark

Me.License_Tag_Number.SetFocus
Me.txtSearchReg = Null
Else
'If desired
'Insert alternate code if txtSearchReg is null
End If 'Optional line

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi again,
I have the form.test2 in form.test1
This is the after event of the textbox.

Private Sub txtSearchReg_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[visitorlog.License_Tag_Number] = " &
Str(Nz(Me![txtSearchReg], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.License_Tag_Number.SetFocus
Me.txtSearchReg = ""
End Sub

The listbox in form2 does not find the record I entered above. The control
source in the list is License Tag Number, row source type table/query, row
source vistitlog table. I dont get any errors after I type in the tag number
but it does not locate the desired record. I'm thinking its in my sources of
list27, but I really dont know.
Any help would be appreciated. Thanks
Click to expand...
Click to expand...
Click to expand...
 
I missed that you were doing this with a form and subform.

Form2.List27
Form1.txtSearchReg

You are entering a tag number into txtSearchReg.
Then you want to search the listbox (List27) on form2? and highlight a row in
the listbox?

Or do you want to search the records that are currently in form2? And go to
the record if it exists?

Assuming the latter then the code might look like the following UNTESTED code.

Private Sub txtSearchReg_AfterUpdate()
Dim rs As DAO.Recordset
Dim frmAny as Form

If Len(Me.txtSearchReg & "") <> 0 Then 'Optional line
Set frmAny = Me.NameOfSubFormControl.Form
Set rs = frmAny.RecordsetClone

rs.FindFirst "[License_Tag_Number] = " & Me.txtSearchReg

If rs.NoMatch = False then Me.Bookmark = rs.Bookmark

frmAny.License_Tag_Number.SetFocus
Me.txtSearchReg = Null

Else
'If desired
'Insert alternate code if txtSearchReg is null
End If 'Optional line

End Sub

If you are searching the list box then you need a variation on the code. You
would step through the values in the list box and compare thenm to the value
in txtSearchReg.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Home for lunch and checking the posts,
I get an error in this code, the line is marked >>>


Private Sub txtSearchReg_AfterUpdate()
Dim rs As DAO.Recordset

If Len(Me.txtSearchReg & "") <> 0 Then 'Optional line
Set rs = Me.RecordsetClone
This line fails >>> rs.FindFirst "[License Tag Number] = " & Me.txtSearchReg

If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark

Me![License Tag Number].SetFocus
Me.txtSearchReg = Null
Else
'If desired
'Insert alternate code if txtSearchReg is null
End If 'Optional line

End Sub

--
Ray J


Wayne-I-M said:
Hi again

I just tested the code that Joh gave you and it works fine for me.
I think the problem may be that you are searching a sub form for a record on
the main form

Place a text box in the main form (based on a query that holds the reg
numbers)
Ensure that there is a control linked to the reg number on the main form
You can hide the controls if you don't want to see them
Ensure that your main form and subform are linked by the primary field (of
the source of the sub form) - so this will need be be the source of a control
on both forms
This should work ?

--
Wayne
Manchester, England.



Raymond said:
Hi John,
Thanks for input I put your code in but it did not work either.
Tell me about the form2 setup, What should my data tab look like, and the
bound colume, is that bound to the colume I see or colume of tag number in
the table?
form1, When I add an after event that goes into the txtbox it does not want
the txt prefix.

Private Sub SearchReg_AfterUpdate()
End Sub

I can really use some help on this and thank you in advance.

--
Ray J


John Spencer said:
Try changing the Set Rs line to

Set rs = Me.RecordsetClone

Also, why not
Dim rs as DAO.Recordset

I would write the code:

Private Sub txtSearchReg_AfterUpdate()
Dim rs As DAO.Recordset

If Len(Me.txtSearchReg & "") <> 0 Then 'Optional line
Set rs = Me.RecordsetClone
rs.FindFirst "[License_Tag_Number] = " & Me.txtSearchReg

If rs.NoMatch = False then Me.Bookmark = rs.Bookmark

Me.License_Tag_Number.SetFocus
Me.txtSearchReg = Null
Else
'If desired
'Insert alternate code if txtSearchReg is null
End If 'Optional line

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Raymond wrote:
Hi again,
I have the form.test2 in form.test1
This is the after event of the textbox.

Private Sub txtSearchReg_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[visitorlog.License_Tag_Number] = " &
Str(Nz(Me![txtSearchReg], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.License_Tag_Number.SetFocus
Me.txtSearchReg = ""
End Sub

The listbox in form2 does not find the record I entered above. The control
source in the list is License Tag Number, row source type table/query, row
source vistitlog table. I dont get any errors after I type in the tag number
but it does not locate the desired record. I'm thinking its in my sources of
list27, but I really dont know.
Any help would be appreciated. Thanks
Click to expand...
Click to expand...
Click to expand...
 
I assumed (dumb, dumb, dumb) that License Tag Number was a number field.
Obvious (when I bother to think about it) that it would be a text field. And
that means you need to include quote marks around txtSearchReg in the string.

Three ways to do that would be:

rs.FindFirst "[License Tag Number] = """ & Me.txtSearchReg & """"

OR

rs.FindFirst "[License Tag Number] = "& Chr(34) & Me.txtSearchReg & Chr(34)

OR

rs.FindFirst "[License Tag Number] = '" & Me.txtSearchReg & "'"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
--
Ray J


John Spencer said:
I missed that you were doing this with a form and subform.

Form2.List27
Form1.txtSearchReg

You are entering a tag number into txtSearchReg.
Then you want to search the listbox (List27) on form2? and highlight a row in
the listbox?

Or do you want to search the records that are currently in form2? And go to
the record if it exists?

Assuming the latter then the code might look like the following UNTESTED code.

Private Sub txtSearchReg_AfterUpdate()
Dim rs As DAO.Recordset
Dim frmAny as Form

If Len(Me.txtSearchReg & "") <> 0 Then 'Optional line
Set frmAny = Me.NameOfSubFormControl.Form
Set rs = frmAny.RecordsetClone

rs.FindFirst "[License_Tag_Number] = " & Me.txtSearchReg

If rs.NoMatch = False then Me.Bookmark = rs.Bookmark

frmAny.License_Tag_Number.SetFocus
Me.txtSearchReg = Null

Else
'If desired
'Insert alternate code if txtSearchReg is null
End If 'Optional line

End Sub

If you are searching the list box then you need a variation on the code. You
would step through the values in the list box and compare thenm to the value
in txtSearchReg.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi John,
Thanks for input I put your code in but it did not work either.
Tell me about the form2 setup, What should my data tab look like, and the
bound colume, is that bound to the colume I see or colume of tag number in
the table?
form1, When I add an after event that goes into the txtbox it does not want
the txt prefix.

Private Sub SearchReg_AfterUpdate()
End Sub

I can really use some help on this and thank you in advance.
Click to expand...
Click to expand...
 
My reply failed let me try again.

Private Sub txtSearchReg_AfterUpdate()
Dim rs As DAO.Recordset
Dim frmAny As Form

If Len(Me.txtSearchReg & "") <> 0 Then 'Optional line
Set frmAny = Me.TEST2.Form
Set rs = frmAny.RecordsetClone


rs.FindFirst "[License Tag Number] = " & Chr(34) & Me.txtSearchReg & Chr(34)
'rs.FindFirst "[License_Tag_Number] = " & Me.txtSearchReg
***The above live failed, so I input going with one of the text finds***

If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark

***this line now fails, should there be quotes?
frmAny.[License Tag Number].SetFocus


Me.txtSearchReg = Null

Else
'If desired
'Insert alternate code if txtSearchReg is null
End If 'Optional line

End Sub
my txt box has no data just the after event

My list27 row source is the table visitorlog table in license order bound by
column 4, which is licence tag, visible column, actually column 5 in the
table. no control source

I really appreciate your help.

--
Ray J


John Spencer said:
I missed that you were doing this with a form and subform.

Form2.List27
Form1.txtSearchReg

You are entering a tag number into txtSearchReg.
Then you want to search the listbox (List27) on form2? and highlight a row in
the listbox?

Or do you want to search the records that are currently in form2? And go to
the record if it exists?

Assuming the latter then the code might look like the following UNTESTED code.

Private Sub txtSearchReg_AfterUpdate()
Dim rs As DAO.Recordset
Dim frmAny as Form

If Len(Me.txtSearchReg & "") <> 0 Then 'Optional line
Set frmAny = Me.NameOfSubFormControl.Form
Set rs = frmAny.RecordsetClone

rs.FindFirst "[License_Tag_Number] = " & Me.txtSearchReg

If rs.NoMatch = False then Me.Bookmark = rs.Bookmark

frmAny.License_Tag_Number.SetFocus
Me.txtSearchReg = Null

Else
'If desired
'Insert alternate code if txtSearchReg is null
End If 'Optional line

End Sub

If you are searching the list box then you need a variation on the code. You
would step through the values in the list box and compare thenm to the value
in txtSearchReg.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi John,
Thanks for input I put your code in but it did not work either.
Tell me about the form2 setup, What should my data tab look like, and the
bound colume, is that bound to the colume I see or colume of tag number in
the table?
form1, When I add an after event that goes into the txtbox it does not want
the txt prefix.

Private Sub SearchReg_AfterUpdate()
End Sub

I can really use some help on this and thank you in advance.
Click to expand...
Click to expand...
 
Do you have a control on the subform named License Tag Number? Probably
not. Maybe something close like License_Tag_Number?

And if the line fails, what error message do you get? The error message
often points the problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
I have a record source for form2 called visitorlog, the table.
I just control source for the list to License Tag Number, that did not help.

frmAny.[License Tag Number].SetFocus
this is the line that fails. member
object doesn support this property or method.
Thanks
Ray



John Spencer said:
Do you have a control on the subform named License Tag Number? Probably
not. Maybe something close like License_Tag_Number?

And if the line fails, what error message do you get? The error message
often points the problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

My reply failed let me try again.

Private Sub txtSearchReg_AfterUpdate()
Dim rs As DAO.Recordset
Dim frmAny As Form

If Len(Me.txtSearchReg & "") <> 0 Then 'Optional line
Set frmAny = Me.TEST2.Form
Set rs = frmAny.RecordsetClone


rs.FindFirst "[License Tag Number] = " & Chr(34) & Me.txtSearchReg & Chr(34)
'rs.FindFirst "[License_Tag_Number] = " & Me.txtSearchReg
***The above live failed, so I input going with one of the text finds***

If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark

***this line now fails, should there be quotes?
frmAny.[License Tag Number].SetFocus


Me.txtSearchReg = Null

Else
'If desired
'Insert alternate code if txtSearchReg is null
End If 'Optional line

End Sub
my txt box has no data just the after event

My list27 row source is the table visitorlog table in license order bound by
column 4, which is licence tag, visible column, actually column 5 in the
table. no control source

I really appreciate your help.
Click to expand...
Click to expand...
 
I guess I'm not sure what you mean be control, I added the field itself
license tag number, but I'm thinking thats not what you mean.

Private Sub txtSearchReg_AfterUpdate()
Dim rs As DAO.Recordset
Dim frmAny As Form
'Set frmAny = Me.NameOfSubFormControl.Form
Set frmAny = Me.[License Tag Number].TEST2 <<<fails on anything I put here
Set rs = frmAny.RecordsetClone
rs.FindFirst "[License Tag Number] = '" & Me.txtSearchReg & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
frmAny.License_Tag_Number.SetFocus
Me.txtSearchReg = Null
End Sub

--
Ray J


John Spencer said:
Do you have a control on the subform named License Tag Number? Probably
not. Maybe something close like License_Tag_Number?

And if the line fails, what error message do you get? The error message
often points the problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

My reply failed let me try again.

Private Sub txtSearchReg_AfterUpdate()
Dim rs As DAO.Recordset
Dim frmAny As Form

If Len(Me.txtSearchReg & "") <> 0 Then 'Optional line
Set frmAny = Me.TEST2.Form
Set rs = frmAny.RecordsetClone


rs.FindFirst "[License Tag Number] = " & Chr(34) & Me.txtSearchReg & Chr(34)
'rs.FindFirst "[License_Tag_Number] = " & Me.txtSearchReg
***The above live failed, so I input going with one of the text finds***

If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark

***this line now fails, should there be quotes?
frmAny.[License Tag Number].SetFocus


Me.txtSearchReg = Null

Else
'If desired
'Insert alternate code if txtSearchReg is null
End If 'Optional line

End Sub
my txt box has no data just the after event

My list27 row source is the table visitorlog table in license order bound by
column 4, which is licence tag, visible column, actually column 5 in the
table. no control source

I really appreciate your help.
Click to expand...
Click to expand...
 
In Design view:
Click on the object (a control) on the form that shows License Tag
Number field. Now select properties and in the window that shows the
properties for the control, click on OTHER tab. The Other tab's first
property should be the NAME of the control.

You cannot set frmAny to just any control. You must set it to the form
property of the sub-form control.

Once that is done, you still need to know the name of the control on the
sub-form control's form that you want to set the focus to.

And now that I think about it you may need to set the focus the
subcontrol first.

Me.NameOfSubControl.SetFocus
frmAny.LicenseTabNumber.SetFocus


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top