How to insert a record into a Subform - Access 2016 (1 Viewer)

[RickHunter84]

Hello there,

I hope everyone is doing well.

I've been trying to find a work around to add a record into a sub-form that is not by direct input into the sub-form, for example: I have the Main form that has the ID (autonumber), the sub-form is related via the MainID (ID_FK) and it has is own SubID.

The Vision: I want to have a pop-up window that is unbound, validate the user entry and then after validation, insert the data into the sub-form table. the sub-form is bound to a query that requeries once the popup form closes. The idea is to requery the form with each new entry, so the user can see previous entries.

The current situation: This works only when the main form is set to "Data entry: No" - when switched to Data entry: Yes - it doesn't work (Insert into doesn't add new information into table from sub-form).

Any thoughts on how to work around this issue?

thank you in advance.
Rick

 

[MajP]

make the pop up bound and set data entry to true. Open the popup acformadd, acdialog. Pass in the ID to the pop up in open args and set the the ID_FK. Requery when the pop up closes.

Docmd.open "Popupname",,,ACFORMADD, ACFORMDIALOG, Me.ID
me.subfrmName.form.requery

 

[RickHunter84]

make the pop up bound and set data entry to true. Open the popup acformadd, acdialog. Pass in the ID to the pop up in open args and set the the ID_FK. Requery when the pop up closes.

Docmd.open "Popupname",,,ACFORMADD, ACFORMDIALOG, Me.ID
me.subfrmName.form.requery

Thank you for your response.
I did what you explained, but still doesn't work. I have attached the test DB, I'm testing this out.

Rick

 

[MajP]

See if this is close. Some of the things you had designed were confusing to me on how you would want this to work.

 

[RickHunter84]

See if this is close. Some of the things you had designed were confusing to me on how you would want this to work.

Thank you for your help! I can get it to work the way you setup the form as well. The problem is when you set the PO header form to "Data entry: yes", then the problem of not showing the new record in the POdetails persists after closing down the popup window.

i figure out a way to do it, but it doesn't give me piece of mind. i found that if i remove the "enforce data integrity" from the relationship between POheader and POdetail, then it displays the entries entered via the popup. do you think this change is sustainable (removing the data integrity)? in a multiuser environment.

Rick

 

[MajP]

If doing this in data entry mode, you have to save the parent before adding a child. Need to set dirty to false. Works for me.

Me.Dirty = False
DoCmd.OpenForm "frmAddItem2", , , , acFormAdd, acDialog, Me.ID

Your other idea is bad.

 

[arnelgp]

you should Disable DataEntry.
the alternative is to use a Query that returns No record (see the main form recordsource).
i added a button for "New PO".

 

[RickHunter84]

If doing this in data entry mode, you have to save the parent before adding a child. Need to set dirty to false. Works for me.

Me.Dirty = False
DoCmd.OpenForm "frmAddItem2", , , , acFormAdd, acDialog, Me.ID

Your other idea is bad.

Thank you so much MajP! it works now. can you explain the openargs form_load event of the popup?

so when I click on the add item, I'm opening it with the form ID as reference, so then when the popup opens, the load events catches the variable sent with me.ID?

If Me.OpenArgs & "" <> "" Then Me.IDPO_FK = Me.OpenArgs --> here is asking if its not empty, then assign the value that was sent when the add item button was clicked? how should i interpret this command?

thank you again!

Rick

 

[MajP]

can you explain the openargs form_load event of the popup?

When you open a form ACDIALOG it makes it Popup and Modal (you can not leave the form). However ACDIALOG does one additional thing. Code execution stops in the calling form until the popup is closed (or hidden).
This is nice because your code in the calling form restarts after the pop up is close. So you can requery in the calling form. If code execution did not stop then once you called the pop up your requery would immediately happen before anything has changed.

The problem with opening ACDIALOG and the code stopping is that you cannot pass anything to the popup (because code is stopped). So you pass the code in the docmd.openform. IMO this is the only time you need openargs. If you did not open it acdialog you would be able to pass values from the calling form.

DoCmd.OpenForm "frmAddItem2", , , , acFormAdd, acDialog, Me.ID
'Code stops here
'Code resumes here when pop up closes
me.subfrmName.form.requery

Without acdialog

DoCmd.OpenForm "frmAddItem2", , , , acFormAdd
'Code continues to run so no need to pass open args just set value in next line
forms("frmAdditem2").ID_FK = me.ID

The problem with the first is you have to pass open args, the problem with the latter is you have to requery when the pop up closes, but you have no hook to do that. You do not want to hard wire your pop ups with code to requery the calling form. So now you have to add extra code to tell it what form called it and what to requery.

The pop up needs to know which PO the item relates to. In the On load event it checks to make sure something was passed in the open args, and then assigns that to the ID_FK. I do the check so when designing you can open the form without an error when something is not passed in.

if Me.OpenArgs & "" <> "" Then Me.IDPO_FK = Me.OpenArgs

You may be asking me why I did this Me.OpenArgs & "" <> "" instead of simply Me.Openargs = "" or isnull(Me.OpenArgs)
I am lazy and I cannot remember if open args is a string or a null when not passed. This case catches both.

For example if you have an empty text box on a form most likely it is null
if isnull([txtBoxName]) then

However sometimes you may have an empty string "" which is not null. This happens when you import or other coding cases. In that case
if [txtBoxName] <> ""

To catch both cases
a null & "" = ""
"" & "" = ""
so if ([txtBoxName] & "") = "" catches both cases where the textbox is null or has an empty string in it.

 

[MajP]

So as not to be lazy I tested

  If Me.OpenArgs = "" Then
    MsgBox " empty"
  End If
  If IsNull(Me.OpenArgs) Then
    MsgBox "Null"
  End If

The answer is if you do not pass in an argument the value of open args is NULL not an empty string. So you can simply do this
if not isnull(Me.OpenArgs) Then Me.IDPO_FK = Me.OpenArgs
Probably way more info then you cared for.

 

[RickHunter84]

So as not to be lazy I tested

  If Me.OpenArgs = "" Then
    MsgBox " empty"
  End If
  If IsNull(Me.OpenArgs) Then
    MsgBox "Null"
  End If

The answer is if you do not pass in an argument the value of open args is NULL not an empty string. So you can simply do this
if not isnull(Me.OpenArgs) Then Me.IDPO_FK = Me.OpenArgs
Probably way more info then you cared for.

Thank you so much for the details explanation MajP. I needed the explanation, so I can continue moving forward, and simplify my code even more.

thanks again!

Rick

 

[Pat Hartman]

I'm failing to understand the purpose of the popup. Wouldn't this have been easier with a bound subform?

 

[gemma-the-husky]

@RickHunter84

I can't see that this can ever work if data entry is true. A date entry form allows for entry of new records only. Inserting a record into a table by another means surely won't let you see that entry. I also can't see the point of an unbound form at all.

Maybe you could bind the subform to a query showing entries by the current user on the current day.