Using Access 2007 - I have created a button that uses the OpenForm macro to open a form with the follow where condition:
[Student]![ID]=[Forms]![SelectStudent]![ID] And [Student]![AttendDate]=[Forms]![SelectStudent]![Today]
I'd like to have this same button create a new record with those two pieces of data (ID and AttendDate which are supplied on the initial form), if no record was found in the underlying table, and open that record on my form for editing. Currently, if no matching record is found, it opens a blank form which will allow you to manually enter the ID again, but I'd like it to already be there if possible.
I'm somewhat Access literate, but I'm certain the majority of people here could easily go way over my head quickly! Please be gentle.
I removed my macro and inserted the code you suggested in the code builder and it doesn't work. It returns an error saying the sub or function is not defined and highlights the ![Today] at the end of the line. Also, it wouldn't run at all until I inserted a "where" in front of what was my Where condition in the macro.
I tried with just one piece of the where condition to see if I could get part of it to work and it gives the same error with the ![ID] highlighted instead.
I'm not sure what's missing in the code, as I'm not super familiar with VBA, which is why I went the macro route. I am certainly willing to use the code, if I can find code that works!
That code was just intended to create a new record and then use your code. Where did you put that code? Is it behind a command button? This is where I would put it. First line of code creates a new record, then use your code for the next line. You said that it worked right? Not enough from you for me to suggest further.
Sorry for the confustion - yes, the macro on the button was already selecting my desired record (if found) and creating a new record (if not found). That part was working. What I was trying to do was auto populate the two fields that I was using as criteria for the WHERE clause if no existing record was found.
I got it to work using the default value field on the called form with this:
SelectStudent is the initial form with the button, so now if the WHERE clause is not satisfied, a new record is created and the default value is whatever the user entered on the SelectStudent form.
Just in case anyone stumbles upon this later with the same issue, I hope I explained it clear enough to follow : )