Error from txtbox looking up listbox

[mrrayj60]

I have a form and subform, they are linked by the field License Tag Number, a text field in my table, vistorlog.
I enter a tag number to display of all the entries that equal this tag number and I get an error on the line noted **
Can anyone suggest a fix? Runtime error 438
Object doesnt support this property or method

Option Compare Database

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
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

 

[mrrayj60]

This was a reply to a post early in the week...
*********
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 with that tag in the listbox? (my answer,correct)

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

***

 

[mrrayj60]

Then he also said to use this line instead as my data is a character field with numbers and letters..

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

 

[mrrayj60]

thanks for your reply, but what is the questions, I've posted the code and it fails I'm hopiing 'SOMEONE' can help fix it for me but asking the same questions twice doesnt help a newbie like me...I can only assume in the world of access that frmAny means something

 

[boblarson]

Tony:

The code the OP posted sets frmAny to this:

Set frmAny = Me.NameOfSubFormControl.Form

Which means if there is no NameOfSubFormControl control on the main form then it will fail.

 

[mrrayj60]

I changed 'NameOfSubFormControl' to both the form name 'test2' or the name of the listbox, 'list27', neither helped...

 

[boblarson]

I changed 'NameOfSubFormControl' to both the form name 'test2' or the name of the listbox, 'list27', neither helped...

It would need to be the name of the control on the main form that houses the subform on the main form (if you have a subform on your main form).

 

[mrrayj60]

It would need to be the name of the control on the main form that houses the subform on the main form (if you have a subform on your main form).

Maybe I'm not sure what the control is. Is it the text box that I type into that has the after event? I do have a form and sub form, they are linked by the field License Tag Number, a text field, the table is Visitorlog.

The main form only has one item, the text box.
The Sub form has a listbox in license plate order.

The need is to type into the text box and the list box below will display all records with the same tag number, preferable highlighting the first found record. Thanks again to both of you for your patience and input.

 

[boblarson]

Maybe I'm not sure what the control is.

 

[mrrayj60]

It would need to be the name of the control on the main form that houses the subform on the main form (if you have a subform on your main form).

Bob,
So I added a textbox that has a control source as the License Tag Number to each of the forms (test1, subform is Test2) I named them under the other tab as txttag and made them visible, no.
The code fails as I attempt to use it.

Private Sub txtSearchReg_AfterUpdate()
Dim rs As DAO.Recordset
Dim frmAny As Form
Set frmAny = TEST2.txttag.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
End Sub

 

[boblarson]

I think we're at a point where you need to post your database. Otherwise we're just going to be going 'round and 'round on this.

 

[mrrayj60]

I think we're at a point where you need to post your database. Otherwise we're just going to be going 'round and 'round on this.

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=28763&stc=1&d=1252342085

Not sure if I uploaded it right or not...

 

[boblarson]

This may not be exactly what you are looking for, but I modified things a fair amount as it didn't make sense some of what you were doing. You shouldn't have the same recordsource for the main form as the subform. Also, now, if you look at the form that opens (Test1) you will see that you can type into the box and it will narrow things down to the records that match.

 

[mrrayj60]

1st, Thank you for your time. Using this does get me the records and I see its using a filter. That moved away from using a listbox, that was a recommendation. I will using the doubleclick next to add this info to a violations table. Thanks alot as I can adapt this into the project.