record selector

[frys]

I have a calendar entering a date into a text box. what i want to do is have that date search for a record with the same date and if it finds it go to that record. if it does not find that date in any records then start a new record with that date. please help :)

thanks

[ChipR]

If you are using 2007, the automatic little calendar that pops up when you are in a text box set as date doesn't trigger an update event when a date is selected. So just make a "Find" or "Go" button the user can click after they enter a date.

Then in Button_Click() do

 
        Set rs = Me.RecordsetClone
        rs.FindFirst "NameOfDateFieldInTable = #" & txtDateBoxNameOnForm & "#"
        If Not rs.EOF Then                'if one was found
            Me.Bookmark = rs.Bookmark    'move to it
        Else
            'move to a new record, I don't remember the syntax at the moment
        End If
 

[frys]

saddly i am on Access 2003

[ChipR]

That's ok, it works the same way. You have a module that makes a calendar for you when you click the button right? That just means that you can trigger this code at the end of that button's code, or with a new button. I'm assuming that your form is based on the recordset you want to move around in, which would mean you probably have the text box in the form's header or footer. Is that right?

[NeoPa]

@ChipR
Try out the following for that :

DoCmd.GoToRecord Record:=acNewRec

[frys]

This is beautiful thanks.. oly difficulty im running in to is that when a date is not found it is going to the very first record. not sure why

DoCmd.GoToRecord Record:=acNewRec

Isn't working

DoCmd.GoToRecord , , acNewRec

also is not working ..

perhaps i am missing some syntax

[ChipR]

DoCmd.GoToRecord definitely works for me. Is your form's Data set to Allow Additions?

[frys]

It is

i am unsure what to say. everything else works like a charm

i have the form set up as such

Main Form
Date_ID Auto number (which thinking about is redundant since there can only be 1 record per day)
Date Date/time (indexed(no dups))

Sub form

Meetings_ID Autonumber (identifies the meeting that was held)

Sub Sub Form
attendance_ID Number (this is linked to a relationship identifing each employee at the meeting)


But i cannot get it to jump to a new record

[ChipR]

I can get the same effect from

DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acNext

but I doubt it will work for you if acNewRec doesn't.
Can you add a record manually with this form?

[frys]

yep manually works fine

I have trialled taking the "else"statement out completely and it does the same thing..

its as if it is skipping the docmd. line all together


when i take out this "Not"

in the line

If not rs.eof then

well obviously it doesn't find anything. but goes to a new record

[ChipR]

Well I didn't know that it would do that but if that is the case you could drop the whole If statement and just say Me.bookmark = Rs.bookmark.
Then, if it did find a record it would go to it, and otherwise it goes to a new one?

[frys]

that is a giant negative..

this is frustrating i have rewrote the statement or tried to 10 times..

Granted im learning the syntax as i go but it makes sence to me


if you fond this go to it

if not go to a new record... not that hard if you ask me ,,, i just cant wrap my head around it

[frys]

ok i figured it out

i think

the code was right except 1 part

Set rs = Me.RecordsetClone
        rs.FindFirst "NameOfDateFieldInTable = #" & txtDateBoxNameOnForm & "#"
        If Not rs.nomatch Then                'if one was found
            docmd.gotorecord,,ac newrec
        Else
            Me.Bookmark = rs.Bookmark    'move to it
        End If
 

I highlighted the nomatch basiclly the rs.eof was saying yes there was a date.. so it the argument was true

the rs.nomatch says if there is no match go to new record else go the the record that matches

[NeoPa]

@frys
Let me just clarify this.

Are you now saying that it was never the case that the code itself (as shown) didn't work, but simply the code that it was within never allowed it to be tested (Like Chip I was very surprised at your statement that it didn't work for you.)?

[frys]

the problem lies in the

 if not rs.eof then

this statement says if there is no date then... but clearly there is a date in the table. so it returns true.. after that it didn't know where to go

i simply rewrote it to say

 if rs.nomatch then 
docmd.gotorecord,,ac newrec
        Else
            Me.Bookmark = rs.Bookmark    'move to it 

saying while looking in my table if there is no match go to a new record. But, if a record is found go to it.

[ChipR]

Well, I was thinking that if the FindFirst failed to find anything, the recordset was left with the position eof, but I just realized the only place I used that in my code was where it could never fail to find something. Everywhere else I used If Not rs.NoMatch. Sorry for misleading you, and thanks for the heads up!

[NeoPa]

I'm getting a little confused here :S

My last question (post #14) was directed at frys (in response to the quoted comment) as I felt they had expressed themselves quite unclearly and the confusion could do with being cleared up (an answer would be appreciated). I will assume my understanding is correct unless I hear otherwise, but others finding this thread may easily be confused reading it the way it stands.

[frys]

@NeoPa

sorry .. yes you are right .. it was never the code that was incorrect

DoCmd.GoToRecord Record:=acNewRec

but rather the code that it was in

sorry i confuse myself at times