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

[Guest]

(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

 

[[MVP] S.Clark]

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?

 

[Guest]

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.

 

[[MVP] S.Clark]

You could do something like:

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