Open form to a record with today's date (1 Viewer)

Wayne · May 25, 2017

I have built a database for my service business, in which I enter all the service orders as they come in. They come in sometimes up to two months in advance of the actual date of service. When I open the form, is there a way I can open it up to today's service date, or in lieu of no scheduled service calls for today's date, to the next sequential date where we do have a service call?

Currently, I have it set up to open at the last record, however, then I have to use the search feature to bring it back to today's date.I have searched the forum up and down, but can't seem to find the right help. Can anyone shed some light? Please bear in mind, there could be multiple records with today's date, or none at all.

Ranman256 · May 25, 2017

Can you put a text box on the form, set the default value =date?

Wayne · May 25, 2017

The Orders form is opened from the main menu (navigation) form. I have a hidden date field that is set to today's date (in red below), and I refer to it it the command button that opens the orders form. See code below:

Code:

Private Sub btnOpenOrders_Click()

    On Error GoTo Err_btnOpenOrders_Click

    DoCmd.OpenForm "frmOrders", acNormal, , , , acWindowNormal, "[ServiceDate] >= " & Me.[COLOR="Red"]txtTodaysDate[/COLOR]
    DoCmd.Close acForm, "frmMainMenu"
    
Exit_btnOpenOrders_Click:
    Exit Sub
    
Err_btnOpenOrders_Click:
    MsgBox Err.Description, vbInformation, "Crate & Pack"
    Resume Exit_btnOpenOrders_Click
    
End Sub

It does open the form, but only goes to the first record. Any thoughts?

pbaldy · May 26, 2017

Not sure what position that's in. It would need delimiters anyway. Try:

http://www.baldyweb.com/wherecondition.htm

Wayne · May 26, 2017

I put in the delimiters for the date field, but it still only opens to the first record in the database. See updated code below:

Code:

Private Sub btnOpenOrders_Click()

    On Error GoTo Err_btnOpenOrders_Click

    DoCmd.OpenForm "frmOrders", acNormal, , , , acWindowNormal, "[ServiceDate] >= #" & Me.txtTodaysDate & "#"
    DoCmd.Close acForm, "frmMainMenu"
    
Exit_btnOpenOrders_Click:
    Exit Sub
    
Err_btnOpenOrders_Click:
    MsgBox Err.Description, vbInformation, "Crate & Pack"
    Resume Exit_btnOpenOrders_Click
    
End Sub

Not sure what I am missing here.

pbaldy · May 26, 2017

Again, you have it in the wrong position; you have it in the OpenArgs position. Did you notice where it was in my link?

Wayne · May 26, 2017

Thanks Paul. I missed that. I moved it to the Where Condition spot, and it worked perfectly. Thanks for the help.

pbaldy · May 26, 2017

Happy to help!

pbaldy · May 26, 2017

By the way, you can probably just do:

"[ServiceDate] >= Date()"

and skip the textbox, unless you want it more flexible than just today's date.

Wayne · May 26, 2017

Thanks, that works too. But I have discovered a minor problem. As the Where condition is used, it opens a filtered set of records, and displays nothing before today's date. No surprise here, that's what I told it to do.

I was looking at your code for having all records displayed. Where would I put that code? If I remove the Where Condition from the calling form, would that RecordSet code go in the OnOpen Event of the called form? If not, where would it go?

Wayne

pbaldy · May 26, 2017

No, it would be used to open the form instead of what you have now. Never tried exactly what you're doing, but try:

rs.FindFirst "[ServiceDate] >= Date()"

Wayne · May 26, 2017

Hi Paul,

Tried this code, and it opens up all records, but it goes to the first record in the database. Did I code it right?

Code:

Private Sub btnOpenOrders_Click()

    On Error GoTo Err_btnOpenOrders_Click
    
    Dim rs As Object
            
    DoCmd.OpenForm "frmOrders"
    
    Set rs = Forms!frmOrders.RecordsetClone
    rs.FindFirst "[ServiceDate] = Date()"
    
    If rs.NoMatch Then
    rs.FindFirst "[ServiceDate] > Date()"
    End If
    
    Set rs = Nothing
    
    DoCmd.Close acForm, "frmMainMenu"
    
Exit_btnOpenOrders_Click:
    Exit Sub
    
Err_btnOpenOrders_Click:
    MsgBox Err.Description, vbInformation, "Crate & Pack"
    Resume Exit_btnOpenOrders_Click
    
End Sub

I appreciate your help.

Wayne

pbaldy · May 26, 2017

You missed the line that moves the form to the desired record:

Forms!frmEmployeesDetail.Bookmark = rs.Bookmark

Wayne · May 26, 2017

I need new glasses! It works perfectly. Thanks once again.

Wayne

pbaldy · May 26, 2017

No problemo!

Open form to a record with today's date (1 Viewer)

Wayne

Crazy Canuck

Ranman256

Well-known member

Wayne

Crazy Canuck

pbaldy

Wino Moderator

Wayne

Crazy Canuck

pbaldy

Wino Moderator

Wayne

Crazy Canuck

pbaldy

Wino Moderator

pbaldy

Wino Moderator

Wayne

Crazy Canuck

pbaldy

Wino Moderator

Wayne

Crazy Canuck

pbaldy

Wino Moderator

Wayne

Crazy Canuck

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread