Create a button in a form to add new record that will be saved in other table

[pauld118]

Hello,

I have a form that have three tabs. In one of the tabs I have many fields that belog to a table (InspectionTable) that uses a foreing key that relates it to the MasterTable of the database.

I would like to create a button in a form to allow users to add a new records in the InspectionTable after having added a record. After clicking this button the fields from the InspectionTable should pop-up in the tab.

I am having problems, because the form in based on the MasterTable that is related to the InspectionTable using a foreing key. How can I create the button? I am using Access 2007

Any help???

 

[Jeanette Cunningham]

You can create a form based on the Inspection table and set its Allow Additions to True.
Put a button back on the form with tabs with code like this:
----------- start code
DoCmd.OpenForm “InspectionFormName”, , , , , acDialog
When user clicks the close button on the Inspection form, your code does something like this:
If Me.Dirty = True Then
Me.Dirty = False
End If
Me.[FormWithTabsName].[SubformControlName].Requery
---- end code
Note: Make sure that [SubformControlName] is the name of the subform control name which contains the subform based on the inspection table.

 

[pauld118]

That worked really well.

But now I have the problem that when the inspections forms opens I cannot add data because the foreign key (CaseID) in the inspection table is empty.

The CaseID is the primary key in the MasterTable which is the foreing key in the inspection table. There is a column on the inspection table called CaseID (foreign key), indeed. Is there a way to automatically populate that column (foreing key) with the number that access automatically add to any new record on the Master Table???

An update query may be made but I don't have any idea of such VBA code. HELP PLEASE!!!

 

[Jeanette Cunningham]

You can pass the CaseID as OpenArgs with the open form method.
When the form opens it sets the Default Value of CaseID to what is passed in open args.
In the before update of the opened form, set the value of CaseID to the default value.

DoCmd.OpenForm “InspectionFormName”, , , , , acDialog,Me.CaseID


Code the open event of the inspection form like this:
If Not IsNull(Me.OpenArgs) Then
Me.CaseID.DefaultValue = Me.OpenArgs
End If


On the before update event of the inpsection form,
Me.CaseID = Me.CaseID.DefaultValue

 

[pauld118]

When I do this the following error pops up

The action or method requires a Form Name argument.

Any idea why???

 

[pauld118]

When you suggested the below code and wrote the note about the subcontrol name, to what did you refer?? That might be the thing that I am doing wrong

----------- start code
DoCmd.OpenForm “InspectionFormName”, , , , , acDialog
When user clicks the close button on the Inspection form, your code does something like this:
If Me.Dirty = True Then
Me.Dirty = False
End If
Me.[FormWithTabsName].[SubformControlName].Requery
---- end code
Note: Make sure that [SubformControlName] is the name of the subform control name which contains the subform based on the inspection table.

 

[Jeanette Cunningham]

It you look closely at a subform on a main form or on a tab control - you can see that the subform itself is inside something that looks a bit like a very large textbox.

To see this more clearly, in design view, first click on the main form.
Now click once only on the subform - you should be able to see that only the outline around the subform is selected - what you can see selected (the bit that looks like a very large textbox) is the subform control name.
On the property dialog, have a look to see the name for the subform control.

Now click on the subform, you will see that the subform control is not selected. Have a look on the property dialog to see the name of the subform.

 

[pauld118]

I apologize Jeannette but is not working. This is what I am doing...

On the MainForm:

Before update event
DoCmd.OpenForm “InspectionFormName”, , , , , acDialog,Me.CaseID

Button on the tab --- Click Event
DoCmd.OpenForm “InspectionFormName”, , , , , acDialog
If Me.Dirty = True Then
Me.Dirty = False
End If
Me.[ECDataEntry].[TabCtl415].Requery]

On the Inspection Form

Before Update event
Me.CaseID = Me.CaseID.DefaultValue

Open Event
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Me.CaseID.CaseID = Me.OpenArgs
End If
End Sub

The error that pop up is this,

The action or record requires a Form Name argument

,when I click debug on the MainForm, this what is highlighted in the code

DoCmd.OpenForm “AddEActions”, , , , , acDialog, Me.CaseID

It also highlight the DefaultValue text in one of the codes written above.

:/

 

[Jeanette Cunningham]

This has become confused - hard to explain so much in a post. It would be easier if you posted this database as an attachment and I will have a look at it. Remove any sensitive data and replace with dummy data before you send it.