Search Function On Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to build a search function on my form.

My table is called Driver Schedule. Sometimes on any given day a driver can
work 2 to 3 different routes to make up his daily schedule but never the same
route on any given
Day.

DriverID SchWorkDate Route. RouteCategory DailyHours
7091 2/3/2005 003-001-S PM 6.51
7965 2/3/2005 099-035-N PM 6.08
8287 2/3/2005 003-001-S AM 4.17
8287 2/3/2005 003-002-N AM 2.83
8287 2/3/2005 003-003-S AM 4.51

I would like to enter the Driver Id and hit the search button and get the
first record equal to the driver ID entered and display it on the form. Then
if this is not the record you need hit the search button again and search
again for the next record until you find the record you require editing or
reaching end of the table.

I found this sample on the Internet that works when the search is based on
one field.

Private Sub Command4_Click()
Dim DID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

DID = Me.cmbDriverID.Value
'stLinkCriteria = "[DriverID]=" & "'" & DID & "'"
stLinkCriteria = "[DriverID]=" & "" & DID & ""
'Check Drivers Schedule table for duplicate Driver Number
If DCount("DriverID", "DriverSchedule", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Driver Number " _
& DID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
'Go to record of original Driver Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub


Is there anyway this sub can be modified to do the search like I want to do
????

Any help is appreciated
 
Have you tried using a main/subform combination? On the main form have
a combo box that you can select the DriverID. Have this linked to the
subform and display the dates, routes, etc... on the subform. Once you
select the driver all data is displayed on the subform.
 
Thanks for your response. The main/subform using a combo box was a good idea.

However the person that I am doing the database for wants a search function
on this Driver Schedule form. This form is the data entry for the application
and the user will
add,delete, edit records daily.

So if a data entry person makes a mistake I need to be able to search and
find the applicable record and make the necessary fix.

I would like to enter the Driver Id and hit the search button and get the
first record equal to the driver ID entered and display it on the form. Then
if this is not the record you hit the search button again and search again
for the next record or if not found display applicable
Message.


DriverID SchWorkDate Route. RouteCategory DailyHours
7091 2/3/2005 003-001-S PM 6.51
7965 2/3/2005 099-035-N PM 6.08
8287 2/3/2005 003-001-S AM 4.17
8287 2/3/2005 003-002-N AM 2.83
8287 2/3/2005 003-003-S AM 4.51

Private Sub Command4_Click()
Dim DID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

DID = Me.cmbDriverID.Value
'stLinkCriteria = "[DriverID]=" & "'" & DID & "'"
stLinkCriteria = "[DriverID]=" & "" & DID & ""
'Check Drivers Schedule table for duplicate Driver Number
If DCount("DriverID", "DriverSchedule", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Driver Number " _
& DID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
'Go to record of original Driver Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub

Is there anyway possible to modify this routine or any sample available I
can use to
do my Search function

Thanks. Any help is appreciated




Penguin said:
Have you tried using a main/subform combination? On the main form have
a combo box that you can select the DriverID. Have this linked to the
subform and display the dates, routes, etc... on the subform. Once you
select the driver all data is displayed on the subform.

I am trying to build a search function on my form.

My table is called Driver Schedule. Sometimes on any given day a driver can
work 2 to 3 different routes to make up his daily schedule but never the same
route on any given
Day.

DriverID SchWorkDate Route. RouteCategory DailyHours
7091 2/3/2005 003-001-S PM 6.51
7965 2/3/2005 099-035-N PM 6.08
8287 2/3/2005 003-001-S AM 4.17
8287 2/3/2005 003-002-N AM 2.83
8287 2/3/2005 003-003-S AM 4.51

I would like to enter the Driver Id and hit the search button and get the
first record equal to the driver ID entered and display it on the form. Then
if this is not the record you need hit the search button again and search
again for the next record until you find the record you require editing or
reaching end of the table.

I found this sample on the Internet that works when the search is based on
one field.

Private Sub Command4_Click()
Dim DID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

DID = Me.cmbDriverID.Value
'stLinkCriteria = "[DriverID]=" & "'" & DID & "'"
stLinkCriteria = "[DriverID]=" & "" & DID & ""
'Check Drivers Schedule table for duplicate Driver Number
If DCount("DriverID", "DriverSchedule", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Driver Number " _
& DID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
'Go to record of original Driver Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub


Is there anyway this sub can be modified to do the search like I want to do
????

Any help is appreciated
Click to expand...
Click to expand...
 
Back
Top