Open form based on certain criteria

I have a form that uses a table as it's record source.  

If the table has a record already in it where RecordDate (not a key field) is equal to today's date, I want the form to open and display the record that already exists with today's date in RecordDate.

But if NO records exist where RecordDate is equal to today's date, then open the form for a new record to be entered.

How can this be done?

--Steve
SteveL13Asked:
Who is Participating?
 
Helen FeddemaConnect With a Mentor Commented:
Try this, replacing the field name Birthdate with the date field from your table:

Private Sub Form_Load()
  
   Dim rst As DAO.Recordset
   Dim strSearch As String

   strSearch = "[Birthdate] = " & Chr(35) & Date & Chr(35)
   Debug.Print "Search string: " & strSearch
   
   'Find the record that matches today's date
   Set rst = Me.Recordset
   rst.FindFirst strSearch
   
   If rst.NoMatch = True Then
      'Not found; go to new record
      DoCmd.GoToRecord record:=acNewRec
   End If

End Sub

Open in new window

0
 
IrogSintaCommented:
Just set the RecordSource of your form to
Select * From TableName Where RecordDate = Date()

Ron
0
 
SteveL13Author Commented:
That isn't working.  The form opens for NEW data entry for today.  Just to restate the question:

If the table has a record already in it where RecordDate (not a key field) is equal to today's date, I want the form to open and display the record that already exists with today's date in RecordDate.

 But if NO records exist where RecordDate is equal to today's date, then open the form for a new record to be entered.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Jeffrey CoachmanMIS LiasonCommented:
This works for me:
Private Sub Form_Open(Cancel As Integer)
    If DCount("RecordDate", "YourTable", "RecordDate=" & "#" & Date & "#") >= 1 Then
        Me.Filter = "RecordDate=Date()"
        Me.FilterOn = True
    Else
        DoCmd.GoToRecord , , acNewRec
    End If
End Sub

Open in new window


JeffCoachman
0
 
IrogSintaCommented:
That isn't working.  The form opens for NEW data entry for today.
So do you have a record in your table with today's date?  If you don't then opening the form for New data entry is what you want, right?  If you do, then does your RecordDate field include the time?  If it does then use this:

Select * From TableName Where DateValue([RecordDate]) = Date()
0
 
PatHartmanCommented:
If the first suggestion isn't working, perhaps you have used Now() rather than Date() to populate the date fields so that the field contains time as well as date.
0
 
Helen FeddemaCommented:
The other suggestions would filter the form for the record with today's date (or make a new record); mine just goes to that record (if there is one), or goes to a new record otherwise.  It is up to you whether you want to filter the form to just show the one matching record, or go to that record, while still allowing navigation to other records on the form.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.