Insert new record where X doesn't exist - Access/VBA

Discussion in 'Programming & Software Development' started by LayZ, Jun 8, 2018.

  1. LayZ

    LayZ Member

    Joined:
    Sep 7, 2011
    Messages:
    74
    https://forums.overclockers.com.au/threads/help-with-update-statement-vba.1245643/

    As per above thread, thanks to you guys the button did work and I have since added a button to delete unneeded records for a given ID but really want to assign times to models with no previous records.

    Logic is like this:

    Insert new record(s) in to FINLDATA MODELS for each model listed in T_MODEL_LIST without a record in FINLDATA MODELS for a specific FINLDATA_ID

    [​IMG]

    ' Insert new record with ID and model code
    INSERT INTO [FINLDATA MODELS] (FINLDATA_ID, MODEL)
    ' Select all models in T_MODELS_LIST that don't appear in FINLDATA MODELS
    SELECT * FROM T_MODELS_LIST.MODEL
    WHERE NOT EXISTS (SELECT * FROM T_MODELS_LIST. WHERE [FINLDATA MODELS.MODEL = T_MODELS_LIST.MODEL)

    The MODEL_ID is a unique value that counts up with each new record added, for example if I manually type a new record in to this table now it will be something like 570001, if I go to another ID and add another record immediately it will have an ID of 5700002. The ID is what populates the form and everything I edit.

    I'm close but struggling with the logic, my fingers are cramping with the amount of copy pasting im doing...
     
    Last edited: Jun 8, 2018
  2. OP
    OP
    LayZ

    LayZ Member

    Joined:
    Sep 7, 2011
    Messages:
    74
    In the off chance someone reads this and is wandering the same thing - I managed to get it working, code is below:

    ' Define variables
    Dim IDToUpdate As String
    Dim m_strSQL As String

    ' Grab ID
    IDToUpdate = Me.FINLDATA_ID.Value

    ' INSERT ID and missing model
    m_strSQL = "INSERT INTO [FINLDATA MODELS] (FINLDATA_ID, MODEL)" & _
    "SELECT " & IDToUpdate & ", MODEL " & _
    "FROM T_MODELS_LIST " & _
    "WHERE NOT EXISTS (SELECT [FINLDATA MODELS].MODEL FROM [FINLDATA MODELS] WHERE ([FINLDATA MODELS].FINLDATA_ID = " & IDToUpdate & ") AND (T_MODELS_LIST.MODEL = [FINLDATA MODELS].MODEL))

    RunSQL m_strSQL

    ' Refresh the form display
    Me.F_EDIT_ENTRIES_MODEL_SUB.Form.Requery

    I made a simple SELECT query to select all models that weren't present and used that as the basis for the INSERT.
     

Share This Page