Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Open form based on certain criteria

Posted on 2015-02-12
7
Medium Priority
?
88 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 39

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

715 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