Check for record then make new if doesn't exist

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I have a set of forms for users to enter date related information. The
main data entry form works fine and I have a dialog pops up on open, search
or create new, with a calendar control to select the required date. From my
'search' or 'create new' command buttons I have everything working nicely.
However, there is one area I'm stuck. When the form first opens I want the
user to pick their date from the dialog, then use code to check if it already
exists. If it exists, I want to go to that record. If it doesn't exist, I
want to create a new record, then go to it. I can do either the going to a
record, or creating a new record just fine. The only bit I'm stuck on is
checking if the date already exists. If I can come up with a true or false
from that then a simple IF statement will lead to the correct action. Any
ideas?

Thanks (and I hope I've explained it well enough - if not please ask me to
clarify!)
 
Hi Steve,

Try using dlookup to check if the date exists... eg:

dlookup("[IDFieldOfRecord]", "TableName", "[DateFieldToCheck] = #" &
format(SelectedDate, "yyyy/mm/dd") & "#")

If that returns the IDField, you have that date in the table already. Use
this ID field to move to the correct record. If it returns Null, you don't
have the date already, so use an insert (or whatever) to add the new record.

Hope this helps.

Damian.
 
If EXISTS (SELECT mMyKey FROM .......WHERE MyKey = [wot I want];)

Or try to fetch it and error trap not found.
 
Thanks, Damian.

I hadn't thought of Dlookup. I wasn't sure if it would work, as a query
narrows down the results to a single user, who may have the same dates (or
not) as other users, but Dlookup could be run on the query. It was then
simple to return the search date from DLookup and use that as the parameter
either in the find sub routine or the new record sub routine.

I ended up with:

datCheck = Dlookup("[Date]","qrySelectRecords","[Date] = #" &
Format(datSearch,"dd/mm/yyyy") & "#")

IF IsNull(datCheck) then
new record code
END IF
 
Back
Top