Button to force a new record to be inserted for all related tables

[Milos]

Hi,

I have a Add New Record form that enters metadata for specific sites. When the data has been entered and the 'open data' button is clicked this will open to a data entry form. This data entry form has the metadata for that site along with 4 pages and subforms (each linked to a different table) for data entry.

Is there a way of forcing the 'open data' button to add a new record (e.g. an empty row with only the ID number populated) for every single table that relates to the different subforms if that ID number does not exist yet? Currently if the data entry person fails to click into to all pages/subforms and then save it will cause the site to be missing from a search form that I have created. I would like to force a record with that ID to be inserted into the different tables. This would only be required once as all tables are one-to-one related so will have the same number of rows when full entered.

I have 4 tables that I need to add a new record when the button is clicked: tblCond; tblPressure; tblSignificance; tblTypes. Just not really sure where to begin here.

The VBA code for my button so far is shown below:

Private Sub btnOpenPlot_Click()
If Not IsNull(Me![ID]) Then
    DoCmd.Save acForm, "Test"
    Me.Refresh
    DoCmd.OpenForm "Test2", , , "[ID] =" & Me![ID], , , Me![ID]
   
End If
End Sub

Any advice is appreciated
Milos

 

[Micron]

Save acForm only works if the form design was altered in design view. If it seems to be working for you, it has to be because of the Refresh. If you want to start new records in code, research how to write append queries in vba. To execute them, best to use .Execute method of the CurrentDb object. You could also use code to call an actual query rather than execute vba sql. If you do that, you'll probably need to refer to form fields in the query in order to get values from a form.

If by ID you mean to start a new record with an autonumber id from some other table, I presume you know that this ID has to be the foreign key value in the new record. You cannot write this ID number to an autonumber field itself.

 

[Milos]

Hi Micron,

All of my other tables (e.g. tblCond; tblPressure; tblSignificance; tblTypes) ID values are not autonumbers as that was making my DB difficult to manage when there was an error. They are numbers that are linked via Master and child relationship.

I managed to get the code to insert a new record into a different table. I have not managed to work out how to perform the logic if a condition ID does not exist when compared to Test ID then insert new record. I only want the row to be inserted when it does not exist, otherwise it may be duplicated.

The first line of code does not work but it is along the lines of what I am needing.

   If Me.ID <> Forms!subfrmCondition!ID Then

    MsgBox "New Record"
    CurrentDb.Execute "INSERT INTO tblCondition (ID) VALUES(" & Me.ID & ")", dbFailOnError
    Else
    MsgBox "Existing Record"
    End If

 

[Milos]

I figured out a really clunky method. Seems to work.

Private Sub btnOpenPlot_Click()
If Not IsNull(Me![ID]) Then
    DoCmd.Save acForm, "Test"
    Me.Refresh
    DoCmd.OpenForm "Test2", , , "[ID] =" & Me![ID], , , Me![ID]
   
    With Forms!Test2!subfrmCondition 'Page 4 New row needs to be made for the subform ID regardless if users fail to save data
        .SetFocus
        Forms!Test2!subfrmCondition![DE_Notes_Cond].Value = " "
        RunCommand acCmdSaveRecord
        Forms!subfrmTest2!subfrmCondition![DE_Notes_Cond].Value = ""
        RunCommand acCmdSaveRecord
    End With
    
    With Forms!Test2!subfrmType 'Page 1 to reset focus as though nothing happened
        .SetFocus
    End With

End If
End Sub

 

[Micron]

Hard to really follow well but glad you have a solution. I still think you do not need DoCmd.Save acForm, "Test" as it's doing nothing for you.
You should watch out for usage of bang (!) vs dot (.)
Not only does bang not invoke intellisense as a helper, whatever it follows it is not evaluated until runtime, whereas dot will be evaluated upon compile. So if field is named Receipts and you type Me!Reciepts this error will have no consequence until that line of code attempts to execute, whereupon it will raise an error and you might find things grind to a halt. On the other hand, Me.Reciepts will fail when you compile the code, giving you the opportunity to fix beforehand. There are few instances where ! is necessary, such as when dealing with recordsets, otherwise I avoid it altogether.

 

[Milos]

Hard to really follow well but glad you have a solution. I still think you do not need DoCmd.Save acForm, "Test" as it's doing nothing for you.
You should watch out for usage of bang (!) vs dot (.)
Not only does bang not invoke intellisense as a helper, whatever it follows it is not evaluated until runtime, whereas dot will be evaluated upon compile. So if field is named Receipts and you type Me!Reciepts this error will have no consequence until that line of code attempts to execute, whereupon it will raise an error and you might find things grind to a halt. On the other hand, Me.Reciepts will fail when you compile the code, giving you the opportunity to fix beforehand. There are few instances where ! is necessary, such as when dealing with recordsets, otherwise I avoid it altogether.

Thanks for the tips. I have removed the Save form as you suggested. I will definitely watch out for the bang(!) vs. dot(.) I still have lots to learn, I am a long time access user but a relatively new access designer!