Pass PK to popup form as FK - Modal (1 Viewer)

Zydeceltico

Registered User.
Local time
Yesterday, 21:48
Joined
Dec 5, 2017
Messages
843
Hi All -

In the attached DB I have a form - frmInspectionEvent - which should be thought of as "the Main Form." It records Date/Time, Job number, and generates a primary key ID.

There are several tables linked to tblInspectionEvent via lnspectionEvent_PK in that table and InspectionEvent_FK in all of the linked tables.

On form frmInspectionEvent there are a few buttons. Each button opens a different type of inspection form as a popup. I do not want to do these in a tabbed control. I have already done that - it works great - but there is an obstacle "down the road" that I can see that makes this other idea more suited to my needs.

Clicking any one of the buttons opens a specific form whose linked table has a field that should record InspectionEvent_PK from frmInspectionEvent as InspectionEvent_FK.

The idea is that the user decides to do an inspection; opens frmInspectionEvent; makes selections; clicks "General"; enters details; closes "General"; clicks another button to open a more specific inspection form; enters data, clicks a "Save/Exit" button on the second form and everything closes with an entry made in tblInspectionEvent, tblInspectionGeneral; and an entry made in tblWhateverOtherInspectionFormWasSelected.

How do I do this?

How do I share the PK from the initial opening of frmInspectionEvent with whichever other forms are opened?

Thanks in advance!

Tim
 

Attachments

  • TIms QCDB - C - 3-6-18.zip
    207.3 KB · Views: 118

June7

AWF VIP
Local time
Yesterday, 17:48
Joined
Mar 9, 2014
Messages
5,423
Can pass data to another form via OpenArgs argument. Or the opening form can reference back to calling form. Or can set global variable or TempVars. Take your pick. I like to use OpenArgs.

DoCmd.OpenForm "AddEditSpecifications", , , , , , "Administration"
Code:
Private Sub Form_Load()
With Me
If Len(.OpenArgs & "") > 0 Then
    If .OpenArgs = "Administration" Then
…
 

Zydeceltico

Registered User.
Local time
Yesterday, 21:48
Joined
Dec 5, 2017
Messages
843
I'm reading up on OpenArgs and how to use it.

In the interim I made a really simple db (attached) to use to try to explain the full functionality of what I am after but do not know how to accomplish.

When you open it you'll see tblParent, tblChild1, and tblChild2.

tblParent has field Parent_PK (autonumber). Both tblChild1 and tblChild2 have a field called Parent_FK.

The idea is to open frmParent and enter a chore from the cbo dropdown "Chores." DateTime is set to "=Now()."

After a chore is selected, either Child One or Child Two needs to be selected by clicking the appropriate button which opens a form for either one where their performance can be rated. Both of these forms are set to Modal.

We'll assume we're working with Child One. Data should be entered on frmChildOne and the "Save" button should be clicked which should result in three things happening:

1) a record should be made in both tblParent and in tblChild1 and they should be linked/related on Parent_PK and Parent_FK respectively;
2) frmChildOne should close;
3) frmParent should requery for a new chore to be assigned and a new child chosen. DateTime would be reset.

I would love to see the requisite VBA in this simple example because I think I am going to use something similar in a lot of areas of my real DB. Please be specific in where the various bits of VBA need to be located. I am fairly decent with "reading" VBA but I am not proficient at all with writing it or - more critically - placing it in the right event - unless directions are clear.

Like I said - I know I can do something similar with a tab control but there are required operations in the real db where it really needs to be from a popup form not a tab.

And just to clarify - because I don't get it - after I have opened frmParent but before I have saved a new record - when is a new entry or new value available for Parent_PK to pass around to other forms/procedures/tables? I have assumed it wouldn't exist until after a new full record is saved into tblParent. But it is pretty clear to me that is not actually correct.

And as always - Any and all guidance is hugely appreciated!!

Thank you in advance,

Tim
 

Attachments

  • ChoresDB.zip
    53.4 KB · Views: 119
Last edited:

bdra2778

Registered User.
Local time
Yesterday, 18:48
Joined
Feb 14, 2019
Messages
34
Hello Zydeceltico, attatched file include tree ways using OpenArgs argument, opening form reference back to calling form or with global variable.

Note: Open first "Tabla1" form.

Regards
 

Attachments

  • Passing PK in form.zip
    42.8 KB · Views: 105

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:48
Joined
May 21, 2018
Messages
8,463
Parent Form
Code:
Private Sub cmdOpenChildOne_Click()
  Me.Dirty = False
  If Not IsNull(Me.Parent_PK) Then
    DoCmd.OpenForm "frmChildOne", , , , , acDialog, Me.Parent_PK
  Else
    MsgBox "Parent Record Not Saved"
  End If
End Sub


Child Form
Code:
Private Sub Form_Load()
  Dim rs As DAO.Recordset
  If Not Trim(Me.OpenArgs & " ") = "" Then
    'See if record exists
    Set rs = Me.Recordset
    MsgBox Me.OpenArgs
    rs.FindFirst "Parent_FK = " & CLng(Me.OpenArgs)
    If rs.NoMatch Then  'it does not exist so you need to create it
      DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
      Me.Parent_FK = Me.OpenArgs
    End If
  End If
End Sub

My assumption is that the child record may already be created or not. So I wrote the code if the child exists go to that record, if not go to a new record and set the parent_FK based on the open args passed in. To do this the child form needs to not be in data entry mode.

I have never understood the "Save Record" concept since it really does not exist. You can undo a record, but I do not get the need to "save a record" since it is going to happen.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:48
Joined
May 21, 2018
Messages
8,463
FYI only reason to use openargs if the form is open acdialog. Just because it is modal will not stop code execution in the calling form and the calling form can still set the called forms properties. Only acdialog stops code execution.
 

Zydeceltico

Registered User.
Local time
Yesterday, 21:48
Joined
Dec 5, 2017
Messages
843
My assumption is that the child record may already be created or not. So I wrote the code if the child exists go to that record, if not go to a new record and set the parent_FK based on the open args passed in. To do this the child form needs to not be in data entry mode.

Hey MajP!

The function of these forms will always be data entry. So no, no Child records will exist. The entire purpose is to "Add New" to both Parent and Child. I have a different system for editing existing records.

Is there an edit or adjustment to the VBA that reflects data-entry-only model?

Mostly for my own information. I'm going to use your flexible code in the real db.

Thanks!

Tim
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:48
Joined
May 21, 2018
Messages
8,463
You would just get rid of the check for an existing record because it cannot exist.
Code:
Private Sub Form_Load()
  If Not Trim(Me.OpenArgs & " ") = "" Then
      DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
      Me.Parent_FK = Me.OpenArgs
  End If
End Sub
 

Zydeceltico

Registered User.
Local time
Yesterday, 21:48
Joined
Dec 5, 2017
Messages
843
I have never understood the "Save Record" concept since it really does not exist. You can undo a record, but I do not get the need to "save a record" since it is going to happen.


So after I have entered data on one of the Child forms, I want to click a button to "save" and EXIT from the Child form and requery the Parent form.

How do I do that part?
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:48
Joined
May 21, 2018
Messages
8,463
Since I open the form using ACDIALOG
Code:
 If Not IsNull(Me.Parent_PK) Then
    DoCmd.OpenForm "frmChildOne", , , , , acDialog, Me.Parent_PK
  Else

Code execution in the calling form is stopped until the dialog form is closed
So you can in the calling form add me.requery which will happen when the called form is closed. Normally you want to requery and return the the current record

dim PK as long
....
'call pop up
...
'Code resumes in calling form once pop up closed
PK = me.parent_PK
me.requery
'need to return to the record you were on
me.recordset.findfirst "parent_pk = " & PK
 

plog

Banishment Pending
Local time
Yesterday, 20:48
Joined
May 11, 2011
Messages
11,613
The Relationship Tool is my porn, I can't pass up a thread that posts a database without checking it out. Your's did not disappoint. I didn't read any posts of this thread, so I don't know what the issue is or what people are doing to help you resolve it, but you need to fix your tables.

Here's the big issues

1. Circular paths among tables. There should only be one way to trace a path between tables in the Relationship tool. I can trace 7 paths between tblFinalProducts and tblInspectGeneral without going through the same table twice. That's wrong, you have too many relationships.

2. Storing values in names. You do this in a lot of places so I will use tblWelderSettings9 as the example. The field name [P2Weld3Amperage] contains data that should be stored in fields, not in the name. You have a lot of fields in this format:

LetterNumber + Activity + Number + Metric
P2 + Weld + 3 + Amperage

All of those pieces of data should be stored as values in a table, not in the field name. Whatever the LetterNumber portion represents should be the field name and "P2" should be the value stored in it. [Activity] should be a field name and "Weld" should be the value stored in it. Whatever the number represents should be a field name and 3 should be stored in it. [Metric] should be a field name and "Amperage" should be stored in it. Then you set up a new field to hold whatever value is in P2Weld3Amperage now.

Instead of all those fields in tblWelderSettings9 that conform to that naming convention, you should accomodate that data with rows in another table. Tables should accomodate data vertically (with more rows) not horizontally (with more columns).

Again, didn't read what this issue was, just know it relates to forms. In my experience, I bet an underlying issue was your tables being improperly structured. Also know, even if it isn't exactly related to this issue, you will have issues in the future if you proceed with poorly structured tables.
 

Zydeceltico

Registered User.
Local time
Yesterday, 21:48
Joined
Dec 5, 2017
Messages
843
Since I open the form using ACDIALOG
Code:
 If Not IsNull(Me.Parent_PK) Then
    DoCmd.OpenForm "frmChildOne", , , , , acDialog, Me.Parent_PK
  Else

I am grateful but have to apologize - this is where my lack of experience comes in: How do those two bits of code fit together? I know you tried to show me - but I'm certain I don't get it. I understand the logic 100% just don't get how the puzzle pieces fit together.

Is it like this:

dim PK as long
Private Sub Form_Load()
If Not IsNull(Me.Parent_PK) Then
DoCmd.OpenForm "frmChildOne", , , , , acDialog, Me.Parent_PK
Else
If Not Trim(Me.OpenArgs & " ") = "" Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.Parent_FK = Me.OpenArgs
End If
End Sub

Like I said...... <blush>

Truly grateful for the assist and patience.

Tim
 

Zydeceltico

Registered User.
Local time
Yesterday, 21:48
Joined
Dec 5, 2017
Messages
843
Hi plog!

Regarding "circular paths" - got it. Thanks.

2. Storing values in names. You do this in a lot of places so I will use tblWelderSettings9 as the example. The field name [P2Weld3Amperage] contains data that should be stored in fields, not in the name.

"P2Weld3Amperage" is actually the name in shorthand. It's "the amperage of the 3rd test weld on pinch gun two" which is likely a single numeric value of 9.75 or something like that.

Am I misundestanding?


As always - thanks for the feedback and insight!

Tim
 

Zydeceltico

Registered User.
Local time
Yesterday, 21:48
Joined
Dec 5, 2017
Messages
843
Parent Form
Code:
Private Sub cmdOpenChildOne_Click()
  Me.Dirty = False
  If Not IsNull(Me.Parent_PK) Then
    DoCmd.OpenForm "frmChildOne", , , , , acDialog, Me.Parent_PK
  Else
    MsgBox "Parent Record Not Saved"
  End If
End Sub


Child Form
Code:
Private Sub Form_Load()
  Dim rs As DAO.Recordset
  If Not Trim(Me.OpenArgs & " ") = "" Then
    'See if record exists
    Set rs = Me.Recordset
    MsgBox Me.OpenArgs
    rs.FindFirst "Parent_FK = " & CLng(Me.OpenArgs)
    If rs.NoMatch Then  'it does not exist so you need to create it
      DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
      Me.Parent_FK = Me.OpenArgs
    End If
  End If
End Sub

So - baby-stepping into this - I placed the code above on the cmdOpenChildOne_Click() on frmParent and the Form_Load on the "On Load" event of frmChildOne.

I open frmParent, make a selection from cboChores and get the message shown in the attached pic. All it shows is the newly-created Parent_PK and an "OK" button. If I click the "OK" button, THEN frmChildOne opens.

What do I do? Remove this line: MsgBox Me.OpenArgs?

Thanks,

Tim
 

Attachments

  • Capture.JPG
    Capture.JPG
    26.8 KB · Views: 132
  • ChoresDB.zip
    43.5 KB · Views: 114

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:48
Joined
May 21, 2018
Messages
8,463
Sorry that was put in there for demonstration purposes to show how to pass and then handle the value.
 

Zydeceltico

Registered User.
Local time
Yesterday, 21:48
Joined
Dec 5, 2017
Messages
843
Sorry that was put in there for demonstration purposes to show how to pass and then handle the value.


No worries - it was informative! Good for me to see.

I almost have it worked out to the current point of discussion. Have my requery in place now too.
 

Zydeceltico

Registered User.
Local time
Yesterday, 21:48
Joined
Dec 5, 2017
Messages
843
So if the user has opened frmParent and made a selection from cboChores and gone on to open frmChildOne - how can I stop the user from closing frmChildOne without entering any data?

OR - if they do close frmChildOne without entering data an undo happens on frmParent - with a new message box that says "Are you sure?" :)

Are you able to suggest a different type of error or mistake handling?
 

Attachments

  • ChoresDB.zip
    86 KB · Views: 92

Zydeceltico

Registered User.
Local time
Yesterday, 21:48
Joined
Dec 5, 2017
Messages
843
In other words, what code do I need to make it so if the user closes frmChildOne without entering data, the new record in tblParent is deleted?

If I close frmChildOne without entering data no data is written to tblChild1 but data is recorded in tblParent.

How can I stop this from happening?

Given that I currently have this code on cmdOpenChildOne_Click?

Code:
Private Sub cmdOpenChildOne_Click()
  Me.Dirty = False
  If Not IsNull(Me.Parent_PK) Then
    DoCmd.OpenForm "frmChildOne", , , , , acDialog, Me.Parent_PK
    Me.Requery
  Else
    MsgBox "Parent Record Not Saved"
  End If
End Sub

....particularly the "Me.Requery" that you helped me add? Like "Requery - UNLESS........."
 

Users who are viewing this thread

Top Bottom