Results 1 to 13 of 13

Thread: Open Form Help

  1. #1
    Join Date
    Dec 2006
    Posts
    30

    Unanswered: Open Form Help

    First of all, thanks for all of the help you've given me as I've worked on this database. I've learned a lot and owe most of that to this forum. Now, on to my question

    You'll see that I open my data entry form from the main menu. It currently opens as a blank form, even if there are already entries for the current date. I'd like it to display that data for editing if it exists. Can you point me in the right direction?

    Thanks!
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    In Design View for your data enry form, goto the form's Properties Box > Data tab and for Data Entry select No. Setting the Data Entry to Yes only allows new records, hence the "blank" form.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Dec 2006
    Posts
    30
    That's close, but I want it to be blank UNLESS there's already data for the current date in the system. Is that possible?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So presumably there's a datefield you can run a comparison on?
    Perhaps you should looka at making a query which picks out new data...
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As before, leave the Data Entry Property to No

    Code:
    Private Sub Form_Open(Cancel As Integer)
    Dim Hit As Integer
    
    'Search for the today's date in your table
    Hit = DCount("[YourDateFieldName]", "YourTableName", "[YourDateFieldName]= " & "#" & Date & "#")
    
    If Hit > 0 Then 'If today's date is found, go to that record
      Me.Recordset.FindFirst "[YourDateFieldName] = " & "#" & Date & "#"
    Else  'If today's date is NOT found, go to a new record
      DoCmd.GoToRecord , , acNewRec
    End If
    End Sub
    If today's date is found, Access goes to that record, otherwise it goes to a new record.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Dec 2006
    Posts
    30
    Should I use this code in the open button that calls the form or in the form itself?

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If you look at the code again, you'll see it goes in the Form_Open event, so it goes in the code window of the form you're opening.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Dec 2006
    Posts
    30
    That's what I thought, but all it did (in Access 2003) was open the very first record. Maybe I'm asking for more than Access can deliver.

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    What exactly do you mean by
    all it did (in Access 2003) was open the very first record
    The very first record of the recordset, or the first record with today's date? Is there more than one record with a given day's date?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Dec 2006
    Posts
    30
    First record of the recordset, which is dated 10/19/2005.

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    My code assumes that your date field actually holds a date, which is to say that the field is defined as date datatype in your table. Is it possible the field holding your date is actually defined as a text field?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  12. #12
    Join Date
    Dec 2006
    Posts
    30
    No, it's defined as Date/Time.

  13. #13
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Assuming you've replaced YourDateFieldName and YourTableName with the actual names of your objects, I don't have a clue. The code works just as you wanted when I run it here. Maybe someone else will have an idea.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •