Passing values from one Subform to another

[ivonsurf123]

Hello,

Hope you can give some ideas, because I have tried to find one and no success.

I have to Subforms (A, B): I have data on SubForm A I want to pass to Subform B but this is how:

1) SubForm A I have a combo box which filter the data I wan to pass to SubForm B

2) after filter data in SubForm A, It has a button same SubForm A when click will open SubForm B, but also I want to pass that filter data too as new record to complete the rest of the blank fields in Subform B.

I have tried with Open args, MasterField ID, child field ID, but did not work. Can you bring me some ideas how to execute this action, please? I open a prior post but the approach was wrong I was referring to Forms when actually they are SubForms.

this is my open args:

SubForm A

Private Sub cmd_AddApplicant_Click()

DoCmd.OpenForm "Subfrm_B", , , , acFormAdd, , Field1 & "|" & Field2 & "|" & Field3 & "|" & Field4

End Sub

SubForm B

Private Sub Form_Open (Cancel As Integer)
Dim varSplitString as Variant

varSplitString = Split (Me.OpenArgs, "|")

Me.Field1.value = varSplitString(0)
Me.Field2.value = varSplitString(1)
Me.Field3.value = varSplitString(2)
Me.Field4.value = varSplitString(3)

End Sub

Error Message:

Run time error 94
Invalid use of Null

Many Thanks!

 

[theDBguy]

Hmm, this topic sure looks familiar...

 

[MajP]

I open a prior post but the approach was wrong I was referring to Forms when actually they are SubForms.

That sheds some light. You cannot pass openargs to subforms, because the only way to set the value is through the docmd.openform method which does not work on a subform. However, why not just clearly tell us what you are trying to do.

 

[ivonsurf123]

MajP

After searching more info realize why was giving all null, What I am trying to do is to pass some values from a Subform to another SubForm, but in secod Subform it need to pass on a new record with a click from Subform A.

I am still searching for more info about it, also I was wondering if that's possible with a Master field and Child field, but I'll check it out tomorrow.

Many thanks for all of you trying to help me in the prior post.

 

[theDBguy]

Ah, that explains it. Are the two subforms on the same form? If so, you should be able to simply refer to the first subform from within the first one. If they're not on the same form, would they both be open at the same time you need those values? If so, then you can use absolute referencing of the first form. Either way, a little more detail as requested would be nice indeed. Cheers!

 

[theDBguy]

Many thanks for all of you trying to help me in the prior post.

Hi. Just out of curiosity, what happened to the other thread? How come I couldn't find it anywhere?

 

[isladogs]

The other thread was deleted by the OP.
However, as it had 20 replies and could be useful to others, I have now restored and locked it. See https://www.access-programmers.co.uk/forums/newreply.php?do=newreply&noquote=1&p=1620562

 

[theDBguy]

The other thread was deleted by the OP.
However, as it had 20 replies and could be useful to others, I have now restored and locked it. See https://www.access-programmers.co.uk/forums/newreply.php?do=newreply&noquote=1&p=1620562

I'm pretty sure the discussion about Nulls would be useful to all.

 

[isladogs]

Do you want me to unlock that thread again?

 

[June7]

Exactly how are the recordsets of these two subforms related? You only want to pass filter criteria? Is the main form bound? These subforms linked to main form?

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[gemma-the-husky]

Assuming the "left" subform is the master and the "right" subform is the child, then the current event for the "left" subform could store the current recordID in a control on the container form.

me.parent!MasterRecordID = whatever

you can then use this value from the "right" subform with code. You can even use it a link field to treat the "right" subform as the parent, without code.

The right subform just updates automatically, as you change the active row in the left subform.

It's way of having a parent/child arrangement with 2 continuous forms.

 

[theDBguy]

Do you want me to unlock that thread again?

No need. It was just a side discussion anyway but still useful for others. Thanks.

 

[MajP]

In the previous thread DocMan pointing out what I consider to be a strange behavior of OpenArgs and not sure why it is designed this way.

If I look up form.OpenArgs, that property is mandated to be a string. Which is no biggie except that it is not a variant.

OpenArgs is actually a variant property and not a string, see below from MS

Determines the string expression specified by the OpenArgs argument of the OpenForm method that opened a form. Read/write Variant

https://docs.microsoft.com/en-us/office/vba/api/access.form.openargs

You can pass in through the docmd.Openform any value type (boolean, date, number, etc.). However, whatever you pass in gets cast and subtyped as a variant string. If you pass in Null or nothing the value of the property is vbNull. One strange thing is if you pass in an empty string "". The property does not get updated and remains Null.

You can test this in the forms on load event
Msgbox varType(me.openArgs) & " " & nz(me.openargs,"")

If you pass nothing, Null, or empty string it returns the value 1 (vbNull), anything else gets cast to string and returns 8.
so if you pass True the message box will show; 8 -1. where -1 is a string.

 

[gemma-the-husky]

Yes openargs is NOT a string.

you can test for it by

IsMissing(openargs). If it is passed but null, then IsMissing will be false, not true. Hence you have to use nz if it might be null, or you will get a RTE.

You can pass multiple values in the string, but you need a consistent string separator to deal with the array. eg with a pipe symbol

openargs = "12|ABC|30/12/2018"

dim myarray() as string
dim x as long
myarray = split(openargs,"|")
for x = 0 to ubound(myarray)
…
next

(hence the need for defensive code in case openargs is null)

 

[HillTJ]

Hi, i'm a newby but had a frustratingly similar problem.i essentially did what Gemma-the-husky suggests in post #11. It worked fine in that it generated a 1 to many relationship between a record in the first suform and the many records in the second subform. I then went on to set the default value of the link field (fk?) On the second subform to that value so that you can add a new record without having to know the link value, if that makes sense. I then hid that column in my data sheet display on the second sub form. Please discard my suggestion if i've misunderstood you, but i am trying!

 

[ivonsurf123]

Hello Everyone!

Just to keep you post. What I needed to do was to build a Form and not use the subform, I create a form where I add the New data, in the other hand I build a continuos form which I added into the Main Subform, so I can edit the new data from there instead going back and for to the Form. This is more like stylish for the User' view.

I work with Master Parent ID and child ID with a button add new data

MasterParentID = ChildID

DoCmd.OpenForm "frm_FormB",,,,acFormAdd

Anyways, After creating the form, Open args works better than never. Thank you so Much!