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.
Frank FreeseAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
If you use a combo and add it to an existing bound form, one of the options is to "find a record on this form".  The wizard will create an embedded macro.  You can convert it to VBA if you prefer by using the option on the ribbon that converts macros to VBA.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
Thanks again - I've already posted a question using a text box, but I'll look into this on the sideline
Frank FreeseAuthor Commented:
Thank you
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Jeffrey CoachmanMIS LiasonCommented:
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 FreeseAuthor Commented:
Thanks Jeff - this is some great stuff. Wish you'd tuned in earlier, but I'm not done with my form,
Jeffrey CoachmanMIS LiasonCommented:
ok,...then take it as supplementary info.
;-)

jeff
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.