If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Wayne-In-Manchester
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 |
#3
|
|||
|
|||
Wayne-In-Manchester
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 |
#4
|
|||
|
|||
Wayne-In-Manchester
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 |
#5
|
|||
|
|||
Wayne-In-Manchester
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. |
#6
|
|||
|
|||
Wayne-In-Manchester
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 |
#7
|
|||
|
|||
Wayne-In-Manchester
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 |
#8
|
|||
|
|||
Wayne-In-Manchester
-- 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. |
#9
|
|||
|
|||
Wayne-In-Manchester
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. |
Thread Tools | |
Display Modes | |
|
|