Adding new record to subtable in a form? (1 Viewer)

KaylaHansa

New member
Local time
Today, 12:48
Joined
Mar 22, 2020
Messages
18
I have a main form which contains a related subform (one to many). I also have another separate form containing fields from the before mentioned subform, designed to be a dialog that is Data Entry only. I have a button on the main form. How do I program it's on click event to open the dialog form and allows the user to enter in a new record, while still maintaining the relationship to the main table? (For example, the main form would be "Customer" and the dialog form would be to "add a new system to customer".) I tried doing it with macros but it will lose the relationship with the current record on the main form, requiring the user to manually enter the ID of the customer to which they are trying to add the new system.
 

zeroaccess

Active member
Local time
Today, 14:48
Joined
Jan 30, 2020
Messages
671
Hello Kayla,

It sounds like you need a WHERE condition.

In a macro it would look like:
Where.png


Change [User ID] to your primary key field name.

The VBA would be:

NGINX:
        DoCmd.OpenForm "FrmUserDetails", acNormal, , "[User ID]=" & [User ID], acDialog
 

KaylaHansa

New member
Local time
Today, 12:48
Joined
Mar 22, 2020
Messages
18
The problem with that is if the dialog form is set to Data Entry then it will lose the connection and open completely blank, even with the macro. And if it is not set to Data Entry it will keep the connection but will display the first current record instead of adding a new one. (Instead of allowing the user to add a new system to the customer they were just viewing, it will display the customer's first system that has already been added.)
 

KaylaHansa

New member
Local time
Today, 12:48
Joined
Mar 22, 2020
Messages
18
I'll haven't tried the VBA yet, but I assume it will work the same way?
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:48
Joined
Sep 21, 2011
Messages
14,046
I believe I accomplished this by using OpenArgs.
If OpenArgs exist and it is a new record (which in your case it sounds like it always will be?) then set the key field from the OpenArgs value.

That would be one way.?

Code:
Private Sub cmdSubmitter_Click()
' Save the record first so the name appears in next form
If Me.Dirty Then
    Me.Dirty = False
End If

'Now check in form being called
DoCmd.OpenForm "frmSubmitterClient", , , "ClientID = " & Me.ClientID, , acDialog, Me.ClientID
Me.lstSubmitter.Requery
End Sub

and in the form
Code:
Private Sub Form_Current()
If Me.NewRecord Then
    Me.ClientID = Me.OpenArgs
End If
End Sub

No idea as how to do it with a macro, sorry.
 

zeroaccess

Active member
Local time
Today, 14:48
Joined
Jan 30, 2020
Messages
671
I'll haven't tried the VBA yet, but I assume it will work the same way?
Yes, it's the equivalent.

This sounds like a design issue and there are solutions. For example, you may want two ways to get to the popup form: one button to add a new record, and another to view existing.

I use a read-only record list with a link to open each record, in addition to a "New" button.
 

Attachments

  • List.png
    List.png
    17 KB · Views: 87

KaylaHansa

New member
Local time
Today, 12:48
Joined
Mar 22, 2020
Messages
18
I tried the OpenArgs and it worked perfectly! Thank you :giggle:
 

Users who are viewing this thread

Top Bottom