Wayne-In-Manchester

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

--
Ray J

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

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" wrote:

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

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" wrote:

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" wrote:

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

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

Raymond wrote:
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.

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" wrote:

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" wrote:

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" wrote:

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

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

Raymond wrote:
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


"John Spencer" wrote:

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

Raymond wrote:
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.

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" wrote:

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

Raymond wrote:
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.