By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

record selector

P: 29
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
Jan 13 '09 #1
Share this Question
Share on Google+
17 Replies


Expert 100+
P: 1,287
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

Expand|Select|Wrap|Line Numbers
  1.  
  2.         Set rs = Me.RecordsetClone
  3.         rs.FindFirst "NameOfDateFieldInTable = #" & txtDateBoxNameOnForm & "#"
  4.         If Not rs.EOF Then                'if one was found
  5.             Me.Bookmark = rs.Bookmark    'move to it
  6.         Else
  7.             'move to a new record, I don't remember the syntax at the moment
  8.         End If
  9.  
Jan 13 '09 #2

P: 29
saddly i am on Access 2003
Jan 13 '09 #3

Expert 100+
P: 1,287
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?
Jan 13 '09 #4

NeoPa
Expert Mod 15k+
P: 31,186
@ChipR
Try out the following for that :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.GoToRecord Record:=acNewRec
Jan 14 '09 #5

P: 29
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


Expand|Select|Wrap|Line Numbers
  1. DoCmd.GoToRecord Record:=acNewRec
Isn't working

Expand|Select|Wrap|Line Numbers
  1. DoCmd.GoToRecord , , acNewRec
also is not working ..

perhaps i am missing some syntax
Jan 14 '09 #6

Expert 100+
P: 1,287
DoCmd.GoToRecord definitely works for me. Is your form's Data set to Allow Additions?
Jan 14 '09 #7

P: 29
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
Jan 14 '09 #8

Expert 100+
P: 1,287
I can get the same effect from
Expand|Select|Wrap|Line Numbers
  1. DoCmd.GoToRecord , , acLast
  2. DoCmd.GoToRecord , , acNext
but I doubt it will work for you if acNewRec doesn't.
Can you add a record manually with this form?
Jan 14 '09 #9

P: 29
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
Jan 14 '09 #10

Expert 100+
P: 1,287
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?
Jan 14 '09 #11

P: 29
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
Jan 14 '09 #12

P: 29
ok i figured it out

i think

the code was right except 1 part

Expand|Select|Wrap|Line Numbers
  1. Set rs = Me.RecordsetClone
  2.         rs.FindFirst "NameOfDateFieldInTable = #" & txtDateBoxNameOnForm & "#"
  3.         If Not rs.nomatch Then                'if one was found
  4.             docmd.gotorecord,,ac newrec
  5.         Else
  6.             Me.Bookmark = rs.Bookmark    'move to it
  7.         End If
  8.  
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
Jan 14 '09 #13

NeoPa
Expert Mod 15k+
P: 31,186
@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.)?
Jan 15 '09 #14

P: 29
the problem lies in the

Expand|Select|Wrap|Line Numbers
  1.  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
Expand|Select|Wrap|Line Numbers
  1.  if rs.nomatch then 
  2. docmd.gotorecord,,ac newrec
  3.         Else
  4.             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.
Jan 15 '09 #15

Expert 100+
P: 1,287
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!
Jan 15 '09 #16

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 15 '09 #17

P: 29
@NeoPa

sorry .. yes you are right .. it was never the code that was incorrect
Expand|Select|Wrap|Line Numbers
  1. DoCmd.GoToRecord Record:=acNewRec
but rather the code that it was in

sorry i confuse myself at times
Jan 16 '09 #18

Post your reply

Sign in to post your reply or Sign up for a free account.