Results 1 to 3 of 3
  1. #1
    Stephenson is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    North West

    Open form based on multiple criteria and create new record if they don't exist

    Hello all,

    I'm working on a job not single form, frmJobNote that I'm opening with a command button from from, frmNew. I'm using the following to open frmJobNote.

    Private Sub cmdJobNote_Click()     strDocName = "frmJobNote"
        DoCmd.OpenForm strDocName
        With Forms(strDocName)
        .txtFrmTitle.Value = Me.sfrmTCNewJob!cboJobID.Column(1) & " Notes"
        .txtJobID.Value = Me.sfrmTCNewJob!cboJobID
        .txtNoteDate.Value = Now()
        End With
    End Sub
    This works, but not for what I'm attempting. I would like frmJobNote to open to the record that matches the current date and job from frmNew. If there is not an existing record for this date and job combination I would like a new record created for this combination. Aside from being new with VBA, I'm not familiar with coding for dates.
    Can anyone help me create the code for a command button to open a form and look for an existing record for both the JobID and the current date, then create a new record if this does not exist?

    Thank you for having a look,


  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Anchorage, Alaska, USA
    I think you might have a hard time searching for a date because you are using NOW() instead of DATE(). The NOW function time down to the second.
    An example of now would be "10/8/2016 5:26:18 PM".

    What is the record source for the form "frmJobNote"?
    If it is a table, I think it might be easier to search using a record set in VBA, rather than opening a form.
    You could open a record set using criteria, then if no records are returned, add a new record.
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  3. #3
    Stephenson is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    North West
    Hello ssanfu,

    Using DATE() is a great idea, I'll make the change. frmJobNote use table tblJobNote as its record source.

    Thank you for the reply

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-01-2014, 10:57 AM
  2. Open form based on search criteria
    By cactuspete13 in forum Forms
    Replies: 7
    Last Post: 03-06-2013, 10:17 AM
  3. If record does not exist, open add new record form
    By RobertIngles in forum Programming
    Replies: 2
    Last Post: 02-01-2011, 08:47 AM
  4. Replies: 1
    Last Post: 07-02-2010, 03:55 AM
  5. Replies: 1
    Last Post: 02-03-2010, 08:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums