Automate Combobox Selection based on current Time()

[LarryB]

I have a form with continuous sub form.

The main form contains a combobox, populated from a query which pulls in specific data (time) from a table

The subform is linked to the combo box on the main form. Based on the combobox selection, the subform updates with associated records with the combobox selection

I would like to add additional functionality in the form load event, that would read the current time and identify the nearest value in my combobox.

I have tested the code below behind a button and it works

If Time() > "13.00:00" And Time() < "14:30:00" Then
MsgBox "The Time is " & Time()
cboPricingCADeadline.Value = "14:30:00"
Else
cboPricingCADeadline.Value = "N\A"
End If

Would a loop through the recordset of the Combobox be best used here? Set the first and second values of the recordset to variables, query the time and then return the value if statement is true, or move to the next record in the rs replacing the first and second variable values

eg
If value1 > time() and value 1 < value2 then
cboPricingCADeadline.Value = value1
end if

If this is a good lead, how do I go about setting up my recordset?

Any other ideas welcome

Many thanks

Larry

 

[LarryB]

Don't tell me!! Getting there! Recordset is setup and I can loop through each value and display it utilizing recordset.Fields("Field Name")

Dim sFirst As String
Dim sSecond As String
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT [qryfrmDubSignOffSelectTime].[Pricing_CA_Deadline] FROM qryfrmDubSignOffSelectTime ORDER BY [Pricing_CA_Deadline]")

If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst

sFirst = rs.Fields("Pricing_CA_Deadline")
Do Until rs.EOF = True

MsgBox rs.Fields("Pricing_CA_Deadline")
'If sFirst > Time() And sFirst < sSecond Then
' cboPricingCADeadline.Value = sFirst
'Else
'cboPricingCADeadline.Value = "N\A"
'End If
rs.MoveNext
Loop
End If

rs.Close
Set rs = Nothing

 

[MarkK]

I wouldn't do this with a loop. I would write a query that subtracts Now() from the stored date/time, and find the nearest that way, like . . .

SELECT TOP 1 YourStoredDateTime
FROM YourTable
ORDER BY Abs(Now() - YourStoredDateTime)

. . . so that returns the nearest stored date/time. Then, set the value of the combo to that value, like . . .

   With currentdb.openrecordset( _
      "SELECT TOP 1 YourStoredDateTime " & _
      "FROM YourTable " & _
      "ORDER BY Abs(Now() - YourStoredDateTime)" & _
   )
      me.cboDateTime = .Fields(0)
      .close
   End With

. . . so you set the value of the combo to the value of the field returned by the recordset.

Makes sense?

 

[LarryB]

Hi Mark,

Many thanks for your suggestion and I think that will suit me as it will get upcoming deadlines if they are approaching.

I do have a loop working, but it is only showing me the latest past deadline

    Dim sFirst      As String
    Dim sResult     As String
    Dim rs          As DAO.Recordset
     
    Set rs = CurrentDb.OpenRecordset("SELECT 'MyData')
    
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True
            sFirst = rs.Fields("MyField from MyData")
            If sFirst < Time() Then
                sResult = sFirst
            End If
            rs.MoveNext
        Loop
    End If
    rs.Close
    Set rs = Nothing
     
    MyFormCombobox.value = sResult

I should be able to add an additional variable to get the next upcoming deadline, but I'll get cracking on your suggestion

Cheers

Larry

 

[LarryB]

Update to the above code......

I have utilized the DateAdd function to add 30 minutes to the current time

The cDate function then changes my field string value to time, and compares against the time() + 30 minutes variable I set earlier. This allows my form to show upcoming deadlines 30 minutes in advanced

    Dim sFirst        As String
    Dim dTime       As Date
    Dim sResult     As String
    Dim dDate       As Date
    Dim rs             As DAO.Recordset
     
    Set rs = CurrentDb.OpenRecordset("SELECT 'MyData')
     
 'adds 30 minutes to current time
    dDate = DateAdd("n", 30, Time())
  
     If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True
            sFirst = rs.Fields("MyField from MyData")
            If sFirst <> "N\A" Then
                If sFirst < Time() Then
                    sResult = sFirst
                ElseIf sFirst > Time() And CDate(sFirst) <= dDate Then
                    sResult = sFirst
                End If
            End If
            rs.MoveNext
        Loop
    End If
    rs.Close
    Set rs = Nothing
    
    MyFormCombobox.value  = sResult

Mark as solved!