Find value in table's date field on form load?????

[pmullan]

Hi Everyone,
I have been working on a Shift Turnover DB. When the Main form loads, I need to search "tblShift" to see if a record with the current date and shift type exist. If the record exist, it needs to load it in my Main Form. The tblShift fields are titled "Date" and "Shift". I am having no luck getting it to work and would appreciate some help. Thank you.:banghead:

 

[moke123]

to start change the name of your field from Date. Date is a reserved word. What have you tried?

 

[pmullan]

Thanks for the tip about the column header. I will change it tomorrow at work. I have been working with Recordset method. I am trying to do something like an If... Then... Else statement.

If tblShift.datefield = now then
main form will load the record in appropriate form fields
else
set focus to the shift field in the main form
end if

Not sure how to accomplish it. I am a newbie working with Access / VBA.
Thank you,

 

[Pat Hartman]

If you open the form from a menu, you can use DLookup() to determine if the necessary record already exists. If it does, use the Where argument of the OpenForm method to open the form to that record. If it doesn't exist, you can open the form in add mode to add the record.

You'll find this easier to do from outside the form because once the form is open, I don't know if you can switch it to add mode.

FYI - you probably don't want to use Now() for this. Now() is current date + time of day so it will almost certainly never match an existing record. You should be using the Date() function.

 

[pmullan]

Pat, unfortunately, after the splash screen, the main form loads and this is where the user selects the shift being worked and then populates the two tech fields and then moves on to the command buttons for whatever needs to be done.
When the Main form loads, I have to check the Shift Table (tblShift) to make sure there is not already an existing record for the current shift in the table. If the record does exist, I will need to populate the main form with the information from the table. If there is no record in the table, then the main form continues to load and the focus is set to the shift type field.
Hope this help explain what I am trying to accomplish. Thanks again.

 

[arnelgp]

try this code on your Main form's Load event:

Private Sub Form_Load()
    Dim rs As DAO.Recordset
    If DCount("*", "tblShift", "[Date]=Date()") = 0 Then
        DoCmd.GoToRecord acActiveDataObject, , acNewRec
        
        Me.[Shift Type] .SetFocus
    Else
        Set rs = Me.RecordsetClone
        With rs
            .MoveFirst
            .FindFirst "[Date] = #" & Format(VBA.Date, "mm/dd/yyyy") & "#"
            Me.Bookmark = .Bookmark
            .Close
        End With
        Set rs = Nothing
    End If
End Sub

 

[pmullan]

Hello ARNEIGP, Thank you for your input. I appreciate your help. I attempted to use your code. Unfortunately, it errors out at the "docmd.GoToRecord acActiveDataObject, , acNewRec" line. I get the "Run-Time error '2046'; The command or action 'GoToRecord' isn't available now. Any ideas? Patrick

 

[Gasman]

I am trying to do something similar and have working

Private Sub Form_Load()
Dim lngOwnerID As Long
If Not IsNull(Me.OpenArgs) Then
    Me.OwnerID = Me.OpenArgs

    lngOwnerID = Nz(DLookup("OwnerID", "tblRecord", "OwnerID = " & Me.OwnerID), 0)
    If lngOwnerID = 0 Then
        DoCmd.GoToRecord , , acNewRec
    End If
End If
End Sub

 

[arnelgp]

tell me, is main form an Unbound form?