06-11-13, 08:40 #1Registered User
- Join Date
- Feb 2013
Unanswered: Open form to existing or new record
I have a simple database that has a date as a field [Date of] that is unique as I want to allow only a single record for any given day.
The form "frmMain" is set to load when database opens and the [Date of] field is set to default to the current date. Sometimes there may be already a record for the current date and sometimes not. What I need to achieve is for the existing record (if there is one) to open for editing and if no existing record a new record to open.
My code is as follows but does not work. Can any of you very knowledgeable people please offer some advice?
Private Sub Form_Load() If (Date) = ([date of]) Then DoCmd.GoToRecord acDataForm, "frmMain", acExistingRec Else DoCmd.GoToRecord acDataForm, "frmMain", acNewRec End If End Sub
Last edited by michael1967; 06-11-13 at 08:55.
06-11-13, 09:39 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
so defien a unique index on that column for the table. that will force uniqueness.
then you need to handle errors withing the form. youmay be happy with the standard error behaviour in the form, if not write your own error trappign routineI'd rather be riding on the Tiger 800 or the Norton
06-11-13, 10:05 #3Grumpy old man (training)
Provided Answers: 10
- Join Date
- Sep 2006
- Surrey, UK
The phrase "does not work" is one of the less useful combinations of words to employ on a forum like this. We have a fairly good idea that something does not work, or you would not be posting about it.
It's more useful to post what you want the code to achieve ("I wrote this to jump to the record relating to the current day, and to create one if it didn't already exist."), and what it does ("To my surprise, every time I open the form, a klaxon sounds three times and my living room fills with raspberry custard."). If you can include error messages as well, so much the better.
Looking at the online documentation for GoToRecord, I don't think that this is the correct action to use. Certainly acExistingRecord is not a valid argument for it. You might be better by checking to see if the record exists, and then either going to a new record, or using the FindRecord action to find it.10% of magic is knowing something that no-one else does. The rest is misdirection.
Beers earned: 1