asked on

Locating a record

Folks,
In my form I have an unbounded text control formatted as a short date. What I need to do is when I've selected a date locate a corresponding record associated with that date. I know I'll need code in the AfterUpdate event of the search box to find the record. If no record is found, I should be on a blank record and I'll be able to add the date in the bound field and create a new record. My question is the code needed.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Frank Freese

ASKER

Thanks again - I've already posted a question using a text box, but I'll look into this on the sideline

Frank Freese

ASKER

Thank you

Jeffrey Coachman

What if two records have the same date?
Most code to "find a record" will only return the "First" match

In any event, ...with your requirement of:

If no record is found, I should be on a blank record and I'll be able to add the date in the bound field and create a new record.

...it may be better if you "filtered" the form for the record. (instead of finding/moving to that record)
...and also the safer approach would be to put this code on a button click event), ...Like so:

Private Sub btnGo_Click()

'Date Validateion--------------------------------------------------------
    'Date is missing
    If IsNull(Me.txtDate) Then
        MsgBox "You must provide a date."
        Me.txtDate.SetFocus
        Exit Sub
    End If
    'Date not valid
    If Not IsDate(Me.txtDate) Then
        MsgBox "You must enter a correctly formatted date, ...ex: 10/14/2015"
        Me.txtDate.SetFocus
        Exit Sub
    End If
'-------------------------------------------------------------------------
    
    'If the date exists, filter the form for that record
    If DCount("RecordDate", "Sheet1", "RecordDate=" & "#" & Me.txtDate & "#") > 0 Then
        Me.Filter = "RecordDate=" & "#" & Me.txtDate & "#"
        Me.FilterOn = True
    'If the date does not exist, create a new record, insert the date, save the record and move to the next data entry field
    Else
        DoCmd.GoToRecord , , acNewRec
        Me.Recorddate = Me.txtDate
        DoCmd.RunCommand acCmdSaveRecord
        Me.Price.SetFocus
    End If
    
End Sub

Private Sub btnShowAll_Click()
    'Shows all the records
    Me.Filter = ""
    Me.FilterOn = False
End Sub

Open in new window

This works fine for me
simple sample db is attached
Database79.mdb

Frank Freese

ASKER

Thanks Jeff - this is some great stuff. Wish you'd tuned in earlier, but I'm not done with my form,

Jeffrey Coachman

ok,...then take it as supplementary info.
;-)

jeff