How do I select a record and if no record exists go to a new recor

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JPGeorge - 10 Nov 2005 06:06 GMT

(Newbie request)

I have defined a table where IDNumber is Indexed (Dups OK) and ReportDate is
not indexed, but I want to create a query that returns the other data based
on these fields and updates the form. If there is no record for that
IDNumber and ReportDate, I want it to select a new record to update.

Thanks in advance.
JPGeorge

Reply to this Message

[MVP] S.Clark - 10 Nov 2005 17:48 GMT

You're question is too ambiguous. Please post the table structure.

Also, what on the form is being updated? A field or the underlying data in
it's recordsource?

Signature

Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

> (Newbie request)
>
[quoted text clipped - 7 lines]
> Thanks in advance.
> JPGeorge

Reply to this Message

JPGeorge - 10 Nov 2005 23:16 GMT

I have 2 tables in another Access Db that are linked to the current Db for
security reasons. Table1 = Employees! with IDNumber Indexed with no dups,
and RecordDates indexed with dups and other misc personnel info.

Table2 = 100C! with IDNumber (Indexed w/dups), RecordDate (Not indexed) and
approx 110 other fields to collect time and hours and counts.

I am trying to set up a master form that Identifies the user using
"=CurrentUser()" but the user is able to input the date for which they wish
to record data. This would check the supporting select query to determine if
a record exists for that user on that day. If it does, it would open the
record in subforms for further editing. If not, it would open a "New"
record, in the subforms.

> You're question is too ambiguous. Please post the table structure.
>
[quoted text clipped - 12 lines]
> > Thanks in advance.
> > JPGeorge

Reply to this Message

[MVP] S.Clark - 11 Nov 2005 16:48 GMT

You could do something like:

If nz(Dcount("*", "TableName", "EntryDate=" & Date() & "AND UserID=" &
CurrentUser(),0) = 0 then
docmd.OpenForm "formname",,,,acFormAdd
end if

Signature

Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

>I have 2 tables in another Access Db that are linked to the current Db for
> security reasons. Table1 = Employees! with IDNumber Indexed with no dups,
[quoted text clipped - 31 lines]
>> > Thanks in advance.
>> > JPGeorge

Reply to this Message