text box lookup to list box

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

Raymond

newbi user, trying to enter text in a text box (on a form) to find a record
in the database and display the records in a list box below on the same form.
Can anyone help. Thanks
 
Hi Raymond

You can do this quite simply with a little code on your form's text box
afterUpdate event. But, (as you say you are new to access) can I suggest
that you design a form that displays all the records that you may want to to
search and then use the wizard to create a Combo Box and set the options on
the wizard to
Find a record on my form.

If this is not suitable for your uses then post back as most people will be
able to give you some help with the code.

Hope this helps
 
Hi Wayne, Thanks for your reply. History or use info
I have a table of vistors by license plate, where when etc...
Desire is to type a tag number into a text box and the list below will list
all the times that car came into the park. The user can then click the
specific time/record and generate a letter(my next step, sub form). I used
the combo box you suggested but knowing the users I dont think that will
work.

I have an unbound text box to type in the plate number and wanted to use a
search command button to click, desired results would show in the list box
below.

Thougts? Thanks a bunch from Florida
Cheers
 
Ok thats not too difficult.

You need 2 forms
frm1 - format singl form
This will be based on a table or query that holds the details of the cars

frm2 - format continous form
This will be based on the times that all cars entered the car park

Link the 2 forms on the reg number.

I assume that you have a table with
IDfield
RegNumber
plus other details of the car (this is what frm1 will be based on)

Add and inbound to frm1
Use something like this on the AfterUpdate of the text box (I assume the
text box is called txtSearchReg)

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


Now this will do what you want - but ;-)
If you added a combo box insead of using a text box - rember that you can
just type the reg number into a combo just as simply as a text box you can
set the expend to yes - which users like plus you could add something like
this to the combo
The same code as above to find the record
Plus
This will inform the users if they type in the wrong reg number or (if the
number is correct but the reg is not in the database ) give the option to add
a new reg
I would suggest you use a combo - but up to you


Private Sub txtSeachReg_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim msg As String
msg = "'The reg you typed in - " & NewData & "' is not on file." & vbCr & vbCr
msg = msg & "Do you want to add a new car reg to the file?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "OK then try again."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("ADDTableNameHere", dbOpenDynaset)
rs.AddNew
rs![ADDRegField] = NewData
rs.Update
Response = acDataErrAdded
End If
End sub

Plus there is lots of other useful stuff you can do
Of course you can also do these with a text box - but more with combos

Hope this helps a little

Top Tip - use a combo to search for a record not a text box ?
 
ooops

should be

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

change XXXX to the name of the field in the table that hlds the reg number

sorry - should read the stuff I wright before pressing the enter button

ooops



--
Wayne
Manchester, England.



Wayne-I-M said:
Ok thats not too difficult.

You need 2 forms
frm1 - format singl form
This will be based on a table or query that holds the details of the cars

frm2 - format continous form
This will be based on the times that all cars entered the car park

Link the 2 forms on the reg number.

I assume that you have a table with
IDfield
RegNumber
plus other details of the car (this is what frm1 will be based on)

Add and inbound to frm1
Use something like this on the AfterUpdate of the text box (I assume the
text box is called txtSearchReg)

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


Now this will do what you want - but ;-)
If you added a combo box insead of using a text box - rember that you can
just type the reg number into a combo just as simply as a text box you can
set the expend to yes - which users like plus you could add something like
this to the combo
The same code as above to find the record
Plus
This will inform the users if they type in the wrong reg number or (if the
number is correct but the reg is not in the database ) give the option to add
a new reg
I would suggest you use a combo - but up to you


Private Sub txtSeachReg_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim msg As String
msg = "'The reg you typed in - " & NewData & "' is not on file." & vbCr & vbCr
msg = msg & "Do you want to add a new car reg to the file?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "OK then try again."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("ADDTableNameHere", dbOpenDynaset)
rs.AddNew
rs![ADDRegField] = NewData
rs.Update
Response = acDataErrAdded
End If
End sub

Plus there is lots of other useful stuff you can do
Of course you can also do these with a text box - but more with combos

Hope this helps a little

Top Tip - use a combo to search for a record not a text box ?



--
Wayne
Manchester, England.



Raymond said:
Hi Wayne, Thanks for your reply. History or use info
I have a table of vistors by license plate, where when etc...
Desire is to type a tag number into a text box and the list below will list
all the times that car came into the park. The user can then click the
specific time/record and generate a letter(my next step, sub form). I used
the combo box you suggested but knowing the users I dont think that will
work.

I have an unbound text box to type in the plate number and wanted to use a
search command button to click, desired results would show in the list box
below.

Thougts? Thanks a bunch from Florida
Cheers
Click to expand...
Click to expand...
 
Thanks for helping Wayne
I set up the two forms and added this to the text boxes after update event
but it does not display the searched record in the list box/form 2 below
My field name is License Tag Number in the table and I renamed my text box
to SearchReg for ease.

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

They are bound.

--
Ray J


Wayne-I-M said:
Ok thats not too difficult.

You need 2 forms
frm1 - format singl form
This will be based on a table or query that holds the details of the cars

frm2 - format continous form
This will be based on the times that all cars entered the car park

Link the 2 forms on the reg number.

I assume that you have a table with
IDfield
RegNumber
plus other details of the car (this is what frm1 will be based on)

Add and inbound to frm1
Use something like this on the AfterUpdate of the text box (I assume the
text box is called txtSearchReg)

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


Now this will do what you want - but ;-)
If you added a combo box insead of using a text box - rember that you can
just type the reg number into a combo just as simply as a text box you can
set the expend to yes - which users like plus you could add something like
this to the combo
The same code as above to find the record
Plus
This will inform the users if they type in the wrong reg number or (if the
number is correct but the reg is not in the database ) give the option to add
a new reg
I would suggest you use a combo - but up to you


Private Sub txtSeachReg_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim msg As String
msg = "'The reg you typed in - " & NewData & "' is not on file." & vbCr & vbCr
msg = msg & "Do you want to add a new car reg to the file?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "OK then try again."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("ADDTableNameHere", dbOpenDynaset)
rs.AddNew
rs![ADDRegField] = NewData
rs.Update
Response = acDataErrAdded
End If
End sub

Plus there is lots of other useful stuff you can do
Of course you can also do these with a text box - but more with combos

Hope this helps a little

Top Tip - use a combo to search for a record not a text box ?



--
Wayne
Manchester, England.



Raymond said:
Hi Wayne, Thanks for your reply. History or use info
I have a table of vistors by license plate, where when etc...
Desire is to type a tag number into a text box and the list below will list
all the times that car came into the park. The user can then click the
specific time/record and generate a letter(my next step, sub form). I used
the combo box you suggested but knowing the users I dont think that will
work.

I have an unbound text box to type in the plate number and wanted to use a
search command button to click, desired results would show in the list box
below.

Thougts? Thanks a bunch from Florida
Cheers
Click to expand...
Click to expand...
 
oh, I did use License_tag_number instead of ID but left the brackets

Private Sub txtSearchReg_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[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

--
Ray J


Raymond said:
Thanks for helping Wayne
I set up the two forms and added this to the text boxes after update event
but it does not display the searched record in the list box/form 2 below
My field name is License Tag Number in the table and I renamed my text box
to SearchReg for ease.

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

They are bound.

--
Ray J


Wayne-I-M said:
Ok thats not too difficult.

You need 2 forms
frm1 - format singl form
This will be based on a table or query that holds the details of the cars

frm2 - format continous form
This will be based on the times that all cars entered the car park

Link the 2 forms on the reg number.

I assume that you have a table with
IDfield
RegNumber
plus other details of the car (this is what frm1 will be based on)

Add and inbound to frm1
Use something like this on the AfterUpdate of the text box (I assume the
text box is called txtSearchReg)

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


Now this will do what you want - but ;-)
If you added a combo box insead of using a text box - rember that you can
just type the reg number into a combo just as simply as a text box you can
set the expend to yes - which users like plus you could add something like
this to the combo
The same code as above to find the record
Plus
This will inform the users if they type in the wrong reg number or (if the
number is correct but the reg is not in the database ) give the option to add
a new reg
I would suggest you use a combo - but up to you


Private Sub txtSeachReg_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim msg As String
msg = "'The reg you typed in - " & NewData & "' is not on file." & vbCr & vbCr
msg = msg & "Do you want to add a new car reg to the file?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "OK then try again."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("ADDTableNameHere", dbOpenDynaset)
rs.AddNew
rs![ADDRegField] = NewData
rs.Update
Response = acDataErrAdded
End If
End sub

Plus there is lots of other useful stuff you can do
Of course you can also do these with a text box - but more with combos

Hope this helps a little

Top Tip - use a combo to search for a record not a text box ?



--
Wayne
Manchester, England.



Raymond said:
Hi Wayne, Thanks for your reply. History or use info
I have a table of vistors by license plate, where when etc...
Desire is to type a tag number into a text box and the list below will list
all the times that car came into the park. The user can then click the
specific time/record and generate a letter(my next step, sub form). I used
the combo box you suggested but knowing the users I dont think that will
work.

I have an unbound text box to type in the plate number and wanted to use a
search command button to click, desired results would show in the list box
below.

Thougts? Thanks a bunch from Florida
Cheers


--
Ray J


:

Hi Raymond

You can do this quite simply with a little code on your form's text box
afterUpdate event. But, (as you say you are new to access) can I suggest
that you design a form that displays all the records that you may want to to
search and then use the wizard to create a Combo Box and set the options on
the wizard to
Find a record on my form.

If this is not suitable for your uses then post back as most people will be
able to give you some help with the code.

Hope this helps

--
Wayne
Manchester, England.



:

newbi user, trying to enter text in a text box (on a form) to find a record
in the database and display the records in a list box below on the same form.
Can anyone help. Thanks
Click to expand...
Click to expand...
Click to expand...
 
Glad you got it working.

--
Wayne
Manchester, England.



Raymond said:
oh, I did use License_tag_number instead of ID but left the brackets

Private Sub txtSearchReg_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[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

--
Ray J


Raymond said:
Thanks for helping Wayne
I set up the two forms and added this to the text boxes after update event
but it does not display the searched record in the list box/form 2 below
My field name is License Tag Number in the table and I renamed my text box
to SearchReg for ease.

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

They are bound.

--
Ray J


Wayne-I-M said:
Ok thats not too difficult.

You need 2 forms
frm1 - format singl form
This will be based on a table or query that holds the details of the cars

frm2 - format continous form
This will be based on the times that all cars entered the car park

Link the 2 forms on the reg number.

I assume that you have a table with
IDfield
RegNumber
plus other details of the car (this is what frm1 will be based on)

Add and inbound to frm1
Use something like this on the AfterUpdate of the text box (I assume the
text box is called txtSearchReg)

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


Now this will do what you want - but ;-)
If you added a combo box insead of using a text box - rember that you can
just type the reg number into a combo just as simply as a text box you can
set the expend to yes - which users like plus you could add something like
this to the combo
The same code as above to find the record
Plus
This will inform the users if they type in the wrong reg number or (if the
number is correct but the reg is not in the database ) give the option to add
a new reg
I would suggest you use a combo - but up to you


Private Sub txtSeachReg_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim msg As String
msg = "'The reg you typed in - " & NewData & "' is not on file." & vbCr & vbCr
msg = msg & "Do you want to add a new car reg to the file?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "OK then try again."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("ADDTableNameHere", dbOpenDynaset)
rs.AddNew
rs![ADDRegField] = NewData
rs.Update
Response = acDataErrAdded
End If
End sub

Plus there is lots of other useful stuff you can do
Of course you can also do these with a text box - but more with combos

Hope this helps a little

Top Tip - use a combo to search for a record not a text box ?



--
Wayne
Manchester, England.



:

Hi Wayne, Thanks for your reply. History or use info
I have a table of vistors by license plate, where when etc...
Desire is to type a tag number into a text box and the list below will list
all the times that car came into the park. The user can then click the
specific time/record and generate a letter(my next step, sub form). I used
the combo box you suggested but knowing the users I dont think that will
work.

I have an unbound text box to type in the plate number and wanted to use a
search command button to click, desired results would show in the list box
below.

Thougts? Thanks a bunch from Florida
Cheers


--
Ray J


:

Hi Raymond

You can do this quite simply with a little code on your form's text box
afterUpdate event. But, (as you say you are new to access) can I suggest
that you design a form that displays all the records that you may want to to
search and then use the wizard to create a Combo Box and set the options on
the wizard to
Find a record on my form.

If this is not suitable for your uses then post back as most people will be
able to give you some help with the code.

Hope this helps

--
Wayne
Manchester, England.



:

newbi user, trying to enter text in a text box (on a form) to find a record
in the database and display the records in a list box below on the same form.
Can anyone help. Thanks
Click to expand...
Click to expand...
Click to expand...
 
Hi Wayne,
Sorry, that did not work, the 2nd form does not find the desired record.
I don't get any errors. My After code reads as follows..

Private Sub txtSearchReg_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[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

my forms are test1 & test2
Link child & master License Tag Number
data is a table.
Test1 is a text box search
and test2 is a listbox

Hope you see the error I really appreciate your help.


--
Ray J


Wayne-I-M said:
Glad you got it working.

--
Wayne
Manchester, England.



Raymond said:
oh, I did use License_tag_number instead of ID but left the brackets

Private Sub txtSearchReg_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[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

--
Ray J


Raymond said:
Thanks for helping Wayne
I set up the two forms and added this to the text boxes after update event
but it does not display the searched record in the list box/form 2 below
My field name is License Tag Number in the table and I renamed my text box
to SearchReg for ease.

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

They are bound.

--
Ray J


:

Ok thats not too difficult.

You need 2 forms
frm1 - format singl form
This will be based on a table or query that holds the details of the cars

frm2 - format continous form
This will be based on the times that all cars entered the car park

Link the 2 forms on the reg number.

I assume that you have a table with
IDfield
RegNumber
plus other details of the car (this is what frm1 will be based on)

Add and inbound to frm1
Use something like this on the AfterUpdate of the text box (I assume the
text box is called txtSearchReg)

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


Now this will do what you want - but ;-)
If you added a combo box insead of using a text box - rember that you can
just type the reg number into a combo just as simply as a text box you can
set the expend to yes - which users like plus you could add something like
this to the combo
The same code as above to find the record
Plus
This will inform the users if they type in the wrong reg number or (if the
number is correct but the reg is not in the database ) give the option to add
a new reg
I would suggest you use a combo - but up to you


Private Sub txtSeachReg_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim msg As String
msg = "'The reg you typed in - " & NewData & "' is not on file." & vbCr & vbCr
msg = msg & "Do you want to add a new car reg to the file?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "OK then try again."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("ADDTableNameHere", dbOpenDynaset)
rs.AddNew
rs![ADDRegField] = NewData
rs.Update
Response = acDataErrAdded
End If
End sub

Plus there is lots of other useful stuff you can do
Of course you can also do these with a text box - but more with combos

Hope this helps a little

Top Tip - use a combo to search for a record not a text box ?



--
Wayne
Manchester, England.



:

Hi Wayne, Thanks for your reply. History or use info
I have a table of vistors by license plate, where when etc...
Desire is to type a tag number into a text box and the list below will list
all the times that car came into the park. The user can then click the
specific time/record and generate a letter(my next step, sub form). I used
the combo box you suggested but knowing the users I dont think that will
work.

I have an unbound text box to type in the plate number and wanted to use a
search command button to click, desired results would show in the list box
below.

Thougts? Thanks a bunch from Florida
Cheers


--
Ray J


:

Hi Raymond

You can do this quite simply with a little code on your form's text box
afterUpdate event. But, (as you say you are new to access) can I suggest
that you design a form that displays all the records that you may want to to
search and then use the wizard to create a Combo Box and set the options on
the wizard to
Find a record on my form.

If this is not suitable for your uses then post back as most people will be
able to give you some help with the code.

Hope this helps

--
Wayne
Manchester, England.



:

newbi user, trying to enter text in a text box (on a form) to find a record
in the database and display the records in a list box below on the same form.
Can anyone help. Thanks
Click to expand...
Click to expand...
Click to expand...
 
Wayne, I have managed to get this form working right yet, are you still
available for further guidance?

I tried using a combo box to get the record and added all the fields from
the table on the form, I have a DoCmd.Requery in the afterevent but the
fields are not from the record I selected from the combo box. This is a new
form from the one with two forms so i'm experimenting on which one I can get
to work.
Thanks again.

--
Ray J
(e-mail address removed)



Wayne-I-M said:
Glad you got it working.

--
Wayne
Manchester, England.



Raymond said:
oh, I did use License_tag_number instead of ID but left the brackets

Private Sub txtSearchReg_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[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

--
Ray J


Raymond said:
Thanks for helping Wayne
I set up the two forms and added this to the text boxes after update event
but it does not display the searched record in the list box/form 2 below
My field name is License Tag Number in the table and I renamed my text box
to SearchReg for ease.

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

They are bound.

--
Ray J


:

Ok thats not too difficult.

You need 2 forms
frm1 - format singl form
This will be based on a table or query that holds the details of the cars

frm2 - format continous form
This will be based on the times that all cars entered the car park

Link the 2 forms on the reg number.

I assume that you have a table with
IDfield
RegNumber
plus other details of the car (this is what frm1 will be based on)

Add and inbound to frm1
Use something like this on the AfterUpdate of the text box (I assume the
text box is called txtSearchReg)

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


Now this will do what you want - but ;-)
If you added a combo box insead of using a text box - rember that you can
just type the reg number into a combo just as simply as a text box you can
set the expend to yes - which users like plus you could add something like
this to the combo
The same code as above to find the record
Plus
This will inform the users if they type in the wrong reg number or (if the
number is correct but the reg is not in the database ) give the option to add
a new reg
I would suggest you use a combo - but up to you


Private Sub txtSeachReg_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim msg As String
msg = "'The reg you typed in - " & NewData & "' is not on file." & vbCr & vbCr
msg = msg & "Do you want to add a new car reg to the file?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "OK then try again."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("ADDTableNameHere", dbOpenDynaset)
rs.AddNew
rs![ADDRegField] = NewData
rs.Update
Response = acDataErrAdded
End If
End sub

Plus there is lots of other useful stuff you can do
Of course you can also do these with a text box - but more with combos

Hope this helps a little

Top Tip - use a combo to search for a record not a text box ?



--
Wayne
Manchester, England.



:

Hi Wayne, Thanks for your reply. History or use info
I have a table of vistors by license plate, where when etc...
Desire is to type a tag number into a text box and the list below will list
all the times that car came into the park. The user can then click the
specific time/record and generate a letter(my next step, sub form). I used
the combo box you suggested but knowing the users I dont think that will
work.

I have an unbound text box to type in the plate number and wanted to use a
search command button to click, desired results would show in the list box
below.

Thougts? Thanks a bunch from Florida
Cheers


--
Ray J


:

Hi Raymond

You can do this quite simply with a little code on your form's text box
afterUpdate event. But, (as you say you are new to access) can I suggest
that you design a form that displays all the records that you may want to to
search and then use the wizard to create a Combo Box and set the options on
the wizard to
Find a record on my form.

If this is not suitable for your uses then post back as most people will be
able to give you some help with the code.

Hope this helps

--
Wayne
Manchester, England.



:

newbi user, trying to enter text in a text box (on a form) to find a record
in the database and display the records in a list box below on the same form.
Can anyone help. Thanks
Click to expand...
Click to expand...
Click to expand...
 
I too should read them before posting, It does NOT work right...
I've even tried combo boxes. Here is my intent maybe it will help.
The user will type in a tag number and find maybe 25 listings in date and
time order of the same tag, he will double click the record that relates to
his letter, that will then add this log data into a violation table so he can
get a speeding or parking ticket. This person will not be adding records to
this table just searching for the right one. If he found him parked illegally
on Sept. 1st He needs to verify he entered the gate on Sept 1st. Not from
July 25th.

--
Thanks again, Why are so many access experts in Great Britian? lol
Ray J


Wayne-I-M said:
Glad you got it working.

--
Wayne
Manchester, England.



Raymond said:
oh, I did use License_tag_number instead of ID but left the brackets

Private Sub txtSearchReg_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[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

--
Ray J


Raymond said:
Thanks for helping Wayne
I set up the two forms and added this to the text boxes after update event
but it does not display the searched record in the list box/form 2 below
My field name is License Tag Number in the table and I renamed my text box
to SearchReg for ease.

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

They are bound.

--
Ray J


:

Ok thats not too difficult.

You need 2 forms
frm1 - format singl form
This will be based on a table or query that holds the details of the cars

frm2 - format continous form
This will be based on the times that all cars entered the car park

Link the 2 forms on the reg number.

I assume that you have a table with
IDfield
RegNumber
plus other details of the car (this is what frm1 will be based on)

Add and inbound to frm1
Use something like this on the AfterUpdate of the text box (I assume the
text box is called txtSearchReg)

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


Now this will do what you want - but ;-)
If you added a combo box insead of using a text box - rember that you can
just type the reg number into a combo just as simply as a text box you can
set the expend to yes - which users like plus you could add something like
this to the combo
The same code as above to find the record
Plus
This will inform the users if they type in the wrong reg number or (if the
number is correct but the reg is not in the database ) give the option to add
a new reg
I would suggest you use a combo - but up to you


Private Sub txtSeachReg_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim msg As String
msg = "'The reg you typed in - " & NewData & "' is not on file." & vbCr & vbCr
msg = msg & "Do you want to add a new car reg to the file?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "OK then try again."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("ADDTableNameHere", dbOpenDynaset)
rs.AddNew
rs![ADDRegField] = NewData
rs.Update
Response = acDataErrAdded
End If
End sub

Plus there is lots of other useful stuff you can do
Of course you can also do these with a text box - but more with combos

Hope this helps a little

Top Tip - use a combo to search for a record not a text box ?



--
Wayne
Manchester, England.



:

Hi Wayne, Thanks for your reply. History or use info
I have a table of vistors by license plate, where when etc...
Desire is to type a tag number into a text box and the list below will list
all the times that car came into the park. The user can then click the
specific time/record and generate a letter(my next step, sub form). I used
the combo box you suggested but knowing the users I dont think that will
work.

I have an unbound text box to type in the plate number and wanted to use a
search command button to click, desired results would show in the list box
below.

Thougts? Thanks a bunch from Florida
Cheers


--
Ray J


:

Hi Raymond

You can do this quite simply with a little code on your form's text box
afterUpdate event. But, (as you say you are new to access) can I suggest
that you design a form that displays all the records that you may want to to
search and then use the wizard to create a Combo Box and set the options on
the wizard to
Find a record on my form.

If this is not suitable for your uses then post back as most people will be
able to give you some help with the code.

Hope this helps

--
Wayne
Manchester, England.



:

newbi user, trying to enter text in a text box (on a form) to find a record
in the database and display the records in a list box below on the same form.
Can anyone help. Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top