Solved

Open form based on certain criteria

Posted on 2015-02-12
7
87 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 38

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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 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