Search Function On Form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dskanes - 13 Feb 2005 03:55 GMT

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

Signature

dskanes

Reply to this Message

Penguin - 13 Feb 2005 05:11 GMT

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.
>
[quoted text clipped - 51 lines]
>
>Any help is appreciated

Reply to this Message

dskanes - 15 Feb 2005 15:33 GMT

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

> 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
[quoted text clipped - 56 lines]
> >
> >Any help is appreciated

Reply to this Message