Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Open form based on certain criteria

Posted on 2015-02-12
7
Medium Priority
?
89 Views
Last Modified: 2015-02-18
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
0
Comment
Question by:SteveL13
7 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40606744
Just set the RecordSource of your form to
Select * From TableName Where RecordDate = Date()

Ron
0
 

Author Comment

by:SteveL13
ID: 40606915
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40606968
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 29

Expert Comment

by:IrogSinta
ID: 40607004
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
 
LVL 40

Expert Comment

by:PatHartman
ID: 40608554
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
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 2000 total points
ID: 40609852
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40609858
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question