Results 1 to 5 of 5
  1. #1
    KComfort is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    4

    Look for Record - if it doesn't exist create a new one

    Situation:
    2 Tables:
    Details - Has Details from an Excel Import
    Complete - Was Created to Capture Information from a Form in Regards to the Details from Excel
    Tables are Linked with a Common Unique Field called ECN.
    Process:
    User Selects ECN Number from a Combo Box - Hits Enter - 2nd Form Opens with all Details - Hits Enter - User then Select What type of ECN From is Needed/Assigned. From there is where my issue start
    Problem - I would like Access to Check the Complete Table to see if the Unique ECN Number is Present -
    ----- If Yes - THEN Open the Record to Edit
    ------If NO - THEN Create a New Record for the ECN
    I have tried:


    Code:
    Private Sub Form_Load()
    Me.ECN = [Forms]![4Completion]![ECN]
    Me.Type = [Forms]![4Completion]![Type]
     
    Dim StrCrit As String
    Dim db As Database
    Dim rst As Recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Completion")
     
    rst.FindFirst (StrCrit)
    If rst.NoMatch Then
    rst.AddNew
    rst!ECN = Me.ECN
    Else
    rst.Edit
    End If

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    You didn't set your StrCrit. I would approach it more like this (I'm assuming that the ECN field is a numeric data type) :

    Code:
    On Error GoTo ErrHandler
        Me.ECN = [Forms]![4Completion]![ECN]
        Me.Type = [Forms]![4Completion]![Type]
         
        'Dim StrCrit As String
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
        Set db = CurrentDb
        Set rst = db.OpenRecordset("SELECT * FROM Completion WHERE ECN=" & Me.ECN)
        
        If rst.BOF And rst.EOF Then
            'no existing record was found
            rst.AddNew
            rst!ECN = Me.ECN
        Else
            'an existing record was found
            rst.Edit
        End If
        
        '----- work with your rst here
        
        
        rst.Update
        rst.Close
    
    ExitHandler:
        Set rst = Nothing
        Set db = Nothing
        Exit Sub
        
    ErrHandler:
        MsgBox Err.Description, , Err.Number
        Resume ExitHandler

  3. #3
    KComfort is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    4
    Sorry for the delay - but this didn't work I will still overwrite the record and not add new.
    Am I thinking / going about this the wrong way?

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Based on the code you posted I assumed you were trying to work with the record set via vba. Are you trying to open a form at this new record?

    If that's the case then yes we were going about this in the wrong way.

    Step 1) From your code in the prev form that launches the new form, do a dlookup to check if the ECN exists in the completion table. If it does then open the next form filtered by that ecn value. If it does not then open the next form in acFormAdd mode and provide the ecn value to the OpenArgs. Something like this:
    Code:
    Public Sub OpenCompletionForm()
        
        If DLookup("ECN", "Completion", "ECN=" & ECN_VALUE_HERE) Then
            'dlookup found an existing record in the completion table with a matching ECN value
            DoCmd.OpenForm "FORM_NAME_HERE", , , "ECN = " & ECN_VALUE_HERE, acFormEdit
        Else
            'dlookup didn't find a matching record so create a new one
            DoCmd.OpenForm "FORM_NAME_HERE", , , , acFormAdd, , CStr(ECN_VALUE_HERE)
        End If
        
    End Sub
    Step 2) In the last form, on the load event, check if openargs are present. If they are then set the default value for the ECN control to the value of the open args. Something like this:
    Code:
    Private Sub Form_Load()
        If Len(Me.OpenArgs) > 0 Then
            Me.ECN.DefaultValue = Me.OpenArgs
        Else
            Me.ECN.DefaultValue = ""
        End If
    End Sub

    I'm making lots of assumptions here, like ECN being a long data type for example. Inspect the code and make any necessary adjustments for field names, datatypes, etc. If this isn't enough to point you in the right direction you can upload a zipped copy of your db and I can take a look at it.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    "Type" is definitely a word I would not use for any object.

    http://www.allenbrowne.com/AppIssueBadWord.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 4
    Last Post: 09-24-2017, 09:13 PM
  2. Create a field, if it doesn't already exist
    By jeffatwork in forum Access
    Replies: 3
    Last Post: 03-02-2016, 08:05 PM
  3. record doesn't exist
    By slimjen in forum Forms
    Replies: 7
    Last Post: 08-13-2014, 11:07 AM
  4. Replies: 4
    Last Post: 12-14-2012, 06:33 PM
  5. VBA to create PDF and folder if doesn't exist!
    By crxftw in forum Programming
    Replies: 2
    Last Post: 08-08-2011, 08:53 AM

Tags for this Thread

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