Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2013

    Unanswered: Open form to existing or new record

    Hello All,

    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
    DoCmd.GoToRecord acDataForm, "frmMain", acNewRec
    End If
    End Sub
    Thank you in advance.

    Last edited by michael1967; 06-11-13 at 08:55.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    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 routine
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 18
    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: 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts