Combo Box

[shhsecurity]

When I search for records in my database they do not come up after I click on
them in the combo box. The fields I am searching on are the date and the
shift. There are 3 similar dates listed with 3 different shifts. So when I
start the search and enter the (example date) data in the field, this comes
up:

12/12/2004 0800-1600
12/12/2004 1600-2400
12/12/2004 2400-0800

but clicking on the 2400-0800 will bring up the 0800-1600 record. And If I
try to click on the 1600-2400 listing nothing happens. One record will only
pop up when clicking on a listing at that specified date. What am I missing?

 

[John Vinson]

When I search for records in my database they do not come up after I click on
them in the combo box. The fields I am searching on are the date and the
shift. There are 3 similar dates listed with 3 different shifts. So when I
start the search and enter the (example date) data in the field, this comes
up:

12/12/2004 0800-1600
12/12/2004 1600-2400
12/12/2004 2400-0800

but clicking on the 2400-0800 will bring up the 0800-1600 record. And If I
try to click on the 1600-2400 listing nothing happens. One record will only
pop up when clicking on a listing at that specified date. What am I missing?

Please post the SQL of the combo box's RowSource query, and the VBA
code you're using in the combo's AfterUpdate event.

John W. Vinson[MVP]

 

[shhsecurity]

Please post the SQL of the combo box's RowSource query, and the VBA
code you're using in the combo's AfterUpdate event.

John W. Vinson[MVP]

The [3] is the date field in that table.

SELECT DAILY_REPORT.[3]
FROM DAILY_REPORT;

--------------------------------------------------------------
Private Sub Combo228_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[3] = #" & Format(Me![Combo228], "mm\/dd\/yyyy") & "#"
' ORDER BY DATE
Me.OrderByOn = True
Me.Bookmark = rs.Bookmark
End Sub

 

[John Vinson]

Private Sub Combo228_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[3] = #" & Format(Me![Combo228], "mm\/dd\/yyyy") & "#"
' ORDER BY DATE
Me.OrderByOn = True
Me.Bookmark = rs.Bookmark

You're searching for the pure date, mm/dd/yyyy. If the (misnamed!)
field [3] contains a time portion you'll find nothing at all; a
Date/Time value consists of a Double Float number, with the integer
portion being days since midnight, December 30, 1899 and the
fractional portion being time. A pure date is equivalent to midnight
at the start of that day.

What is actually contained in [3]? Just the date? the date and the
time?


John W. Vinson[MVP]

 

[shhsecurity]

Private Sub Combo228_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[3] = #" & Format(Me![Combo228], "mm\/dd\/yyyy") & "#"
' ORDER BY DATE
Me.OrderByOn = True
Me.Bookmark = rs.Bookmark

You're searching for the pure date, mm/dd/yyyy. If the (misnamed!)
field [3] contains a time portion you'll find nothing at all; a
Date/Time value consists of a Double Float number, with the integer
portion being days since midnight, December 30, 1899 and the
fractional portion being time. A pure date is equivalent to midnight
at the start of that day.

What is actually contained in [3]? Just the date? the date and the
time?


John W. Vinson[MVP]

Data Type: Date/Time
Default Value: Date()
Required: Yes
Indexed: Yes (Duplicates ok)

I'm thinking that I have to change the Data Type to just the date then?

 

[John Vinson]

Data Type: Date/Time
Default Value: Date()
Required: Yes
Indexed: Yes (Duplicates ok)

I'm thinking that I have to change the Data Type to just the date then?

I'm confused.

If you have just the date in the field, and you're using the combo box
to find the date; and you have three records for that date; how can
you possibly expect it to choose any particular one of those three
records?

You may need to use the Date *and* time in the same field. That is,
if the field actually contained #12/21/2004 08:00:00#, and that value
were displayed in the combo box, you'ld go to the right record.

John W. Vinson[MVP]

 

[shhsecurity]

I'm confused.

If you have just the date in the field, and you're using the combo box
to find the date; and you have three records for that date; how can
you possibly expect it to choose any particular one of those three
records?

You may need to use the Date *and* time in the same field. That is,
if the field actually contained #12/21/2004 08:00:00#, and that value
were displayed in the combo box, you'ld go to the right record.

John W. Vinson[MVP]

I do have the date and time in the same search combo box field. When I
click on the time of 0800-1600 or 1600-2400 it still only gives me the
2400-0800 record for that date. What is the problem here?
Properties for the combo box:

Private Sub Combo324_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[3] = #" & Format(Me![Combo324], "mm\/dd\/yyyy") & "#"
Me.Bookmark = rs.Bookmark
End Sub

Properties for the table:

Default value - Date() And Time()
Required - Yes
Indexed - Yes (Duplicates OK)

 

[John Vinson]

I do have the date and time in the same search combo box field. When I
click on the time of 0800-1600 or 1600-2400 it still only gives me the
2400-0800 record for that date. What is the problem here?
Properties for the combo box:

Private Sub Combo324_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[3] = #" & Format(Me![Combo324], "mm\/dd\/yyyy") & "#"
Me.Bookmark = rs.Bookmark
End Sub

Properties for the table:

Default value - Date() And Time()
Required - Yes
Indexed - Yes (Duplicates OK)

Date in one field, time in a different field? Or a single date/time
field? The FindFirst action *IS NOT LOOKING FOR A TIME* - it will find
the first record where the field (mis)named [3] contains the date
portion of whatever date/time value is in Combo324 (which you might
also want to rename), since you are explicitly and specifically
formatting that value to a date only. If [3] contains both date and
time, it will find only records where the time is midnight (zero after
the decimal).

The code is doing precisely what you are asking it to do: find the
first record on that date. Since you're not asking it to find a
particular time, it is not doing so.

John W. Vinson[MVP]

 

[shhsecurity]

Well so that leads me now to the problem of what exactly do I do now? What
statements do I need to put in the code to get the right record to show up?

I do have the date and time in the same search combo box field. When I
click on the time of 0800-1600 or 1600-2400 it still only gives me the
2400-0800 record for that date. What is the problem here?
Properties for the combo box:

Private Sub Combo324_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[3] = #" & Format(Me![Combo324], "mm\/dd\/yyyy") & "#"
Me.Bookmark = rs.Bookmark
End Sub

Properties for the table:

Default value - Date() And Time()
Required - Yes
Indexed - Yes (Duplicates OK)

Date in one field, time in a different field? Or a single date/time
field? The FindFirst action *IS NOT LOOKING FOR A TIME* - it will find
the first record where the field (mis)named [3] contains the date
portion of whatever date/time value is in Combo324 (which you might
also want to rename), since you are explicitly and specifically
formatting that value to a date only. If [3] contains both date and
time, it will find only records where the time is midnight (zero after
the decimal).

The code is doing precisely what you are asking it to do: find the
first record on that date. Since you're not asking it to find a
particular time, it is not doing so.

John W. Vinson[MVP]

 

[shhsecurity]

Oh sorry and also, the date, eg. 0800-1600 I am referring to is actually a
text field.
[3] being the date field and [2] being the text field where users would
input their shift time.

I do have the date and time in the same search combo box field. When I
click on the time of 0800-1600 or 1600-2400 it still only gives me the
2400-0800 record for that date. What is the problem here?
Properties for the combo box:

Private Sub Combo324_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[3] = #" & Format(Me![Combo324], "mm\/dd\/yyyy") & "#"
Me.Bookmark = rs.Bookmark
End Sub

Properties for the table:

Default value - Date() And Time()
Required - Yes
Indexed - Yes (Duplicates OK)

Date in one field, time in a different field? Or a single date/time
field? The FindFirst action *IS NOT LOOKING FOR A TIME* - it will find
the first record where the field (mis)named [3] contains the date
portion of whatever date/time value is in Combo324 (which you might
also want to rename), since you are explicitly and specifically
formatting that value to a date only. If [3] contains both date and
time, it will find only records where the time is midnight (zero after
the decimal).

The code is doing precisely what you are asking it to do: find the
first record on that date. Since you're not asking it to find a
particular time, it is not doing so.

John W. Vinson[MVP]

 

[John Vinson]

Well so that leads me now to the problem of what exactly do I do now? What
statements do I need to put in the code to get the right record to show up?

Ok, include the time in the search:

Private Sub Combo228_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[3] = #" & Format(Me![Combo228], "mm\/dd\/yyyy") _
& "# AND [4] = '" & Me![Combo228].Column(1) & "'"
' ORDER BY DATE
Me.OrderByOn = True
Me.Bookmark = rs.Bookmark
End Sub

assuming that the time field is 10 and that the combo box has the
matching time value in the second column (the Column property is zero
based).

John W. Vinson[MVP]