Solved

Open form based on certain criteria

Posted on 2015-02-12
7
82 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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 34

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 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now