Passing values from one form to another (1 Viewer)

[ivonsurf123]

Hello,

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

I have to forms (A, B): I have data on Form A I want to pass to form B but this is how:

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

2) after filter data in Form A, It has a button same Form A when click will open Form B, but also I want to pass that filter data too as new record to complete the rest of the blank fields in form 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?

Many Thanks!

 

[theDBguy]

Hi. OpenArgs would be the primary way, but you said you've already tried it. You may have to show us how, so we can tell you why it didn't work. Another way is to keep FormA open and simply refer to its data from FormB by using a Forms!FormName.ControlName reference.

 

[plog]

I want to pass that filter data too as new record to complete the rest of the blank fields in form B.

I wouldn't pass the data, I would create the record and open FormB to that new record.
Here's the broad strokes of the VBA which would live on FormA:

1. Validate data - make sure FormA has data and its valid (whatever that means)
2. DoCmd.RunSQL - run a query with the data from FormA to create the record.
3. DMax() - get the highest ID from the table you just did the INSERT INTO on
4. DoCmd.OpenForm - open FormB filtering it to the id of the record you created.

 

[MajP]

Why can't you simply use the filter as a where criteria in the Docmd.openform method?

 

[ivonsurf123]

Thank you all, I will recreate the open args I did, and follow the rest of your advises, keep you post soon.

 

[ivonsurf123]

@theDBguy

This was my open args:

Form A:

Private Sub cmd_AddApplicant_Click()

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

End Sub

Form 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

 

[theDBguy]

@theDBguy

This was my open args:

Form A:



Form B:



Error Message:

Run time error 94
Invalid use of Null

Which lines gets highlighted with the error?


What happens if you tried this?

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

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



Debug.Print Me.OpenArgs
Debug.Print varSplitString(0)
Debug.Print varSplitString(1)
Debug.Print varSplitString(2)
Debug.Print varSplitString(3)

End Sub

I think I just realized your problem when I was typing the above test. Hint: Check out how I declared the variable varSplitStrings.

 

[MajP]

you will get a type mismatch doing that. Split returns an array of strings and it will not cast.

Dim sSplitString() as string
sSplitString = Split (Me.OpenArgs, "|")

 

[ivonsurf123]

The line Which gets the error is:

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

I switch for a String as per MajP, but gave me same error message:

Run time error 94
Invalid use of Null

Also changed for :

Dim varSplitString() as Variant

But, same error.

 

[theDBguy]

The line Which gets the error is:

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

I switch for a String as per MajP, but gave me same error message:

Run time error 94
Invalid use of Null

Also changed for :

Dim varSplitString() as Variant

But, same error.

And what was the result of the following line in my "test" code?

Debug.Print Me.OpenArgs

If it's blank, you might try it this way.

Debug.Print Nz(Me.OpenArgs,"Null")

 

[MajP]

Invalid use of Null

Dim sSplitString() as string
debug.print "Open Args: " & me.openargs
sSplitString = Split (Me.OpenArgs, "|")

Looks to me like you are passing in null open args.

Dim varSplitString() as Variant

As I said you HAVE TO declare the array as a string, stop declaring it as a variant.

 

[ivonsurf123]

MajP,

I did declare the array, both ways as variant and as String:

Dim varSplitString() as Variant

Dim sSplitString() as String

same error

 

[ivonsurf123]

@theDBguy

I did this way:

Debug.Print Nz(Me.OpenArgs,"Null")

printed: Null with the same error pop up.

 

[MajP]

Field1 & "|" & Field2 & "|" & Field3 & "|" & Field4

Actually not sure of the problem. There is something else going on I am not seeing.

Even if all those fields are NULL you would still pass in a string "|||", and that string would definitely split. Where is the error?

 

[MajP]

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

Can you double check your call. There should be no way your args are null. Any chance you are testing by simply opening the form and not calling it from code?

 

[theDBguy]

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

Can you double check your call. There should be no way your args are null. Any chance you are testing by simply opening the form and not calling it from code?

I second this sentiment. Please double-check as requested. Thank you.

 

[ivonsurf123]

MajP

sorry the private Sub in form A is below.."frm_B"... not "frm_A"

Private Sub cmd_AddApplicant_Click()

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

End Sub

 

[MajP]

Sub in form A is below.."frm_B"... not "frm_A"

The fact that you had the wrong form listed hints to me that you are typing this into the forum and not pasting your real code or you are not really executing that code. Please paste the real code and ensure you are really executing the calling code.

However whenever you use open args on a form you should check for Null or empty string in the onload event. This way you can still open the form without passing a value.

If not trim(me.openargs & " ") = "" then
  do something with the openargs
else
  msgbox "No Arguments Passed"
end if

Also where exactly is the error. Maybe it is not where we think it is in the split.

 

[The_Doc_Man]

ivonsurf reports this code to create the OpenArgs argument.

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

Shouldn't this be something like:

DoCmd.OpenForm "frm_A", , , , acFormAdd, , "'" & Nz( Field1, "" ) & "|" & Nz( Field2, "" ) & "|" & Nz( Field3, "" ) & "|" & Nz( Field4, "" ) & "'"

The presence of operators makes the OpenArgs value an expression so if you have nulls in an expression, Access gets a little wonky. But you want to force this as a string, so you should have leading and trailing quotes, I think.

Supposedly, you can concatenate nulls into a string, but I've always found that a little bit of prevention via NZ helps when you expect to have nulls. And the error, "Invalid use of null" that is being returned would make sense if the string was built in a way to allow nulls to creep in and confuse the result. Yes, I know it SHOULD work, but what the heck, try prefixing and suffixing single quotes around the intended string. Simple enough experiment that it might be worth it.

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. I thought only variants could be null for anything except for child fields in parent-only SQL outer JOINs. So somewhere in there, the OpenArgs string is getting garbled. (I think.)

Therefore, I'm not surprised that the SPLIT function is balking. The test that was run in post #13 clearly showed via debug.print that the OpenArgs property came back bad. So it seems it must be malformed.

I would bet that if you could tweak the creation of that OpenArgs string that the test would return something useful and SPLIT would work just fine.

Just to be thorough, I checked a couple of web references. See next line for link:

https://stackoverflow.com/questions/258556/openargs-is-null-error

Apparently, someone else was surprised to see a NULL come back from OpenArgs, but they found an explanation of sorts having to do with HOW the form was actually opened. If the form was FIRST opened in design mode and THEN switched to Form mode, the properties are not quite as expected.

Found a second reference with the same comments:

https://social.msdn.microsoft.com/F...4190cc2/why-open-args-is-null?forum=accessdev

Here is a third reference with a different reason having to do with the fact that what is being sent as an openargs value comes from a record that hasn't been saved yet.

http://www.utteraccess.com/forum/index.php?showtopic=1896938

 

[MajP]

Supposedly, you can concatenate nulls into a string, but I've always found that a little bit of prevention via NZ helps when you expect to have nulls. And the error, "Invalid use of null" that is being returned would make sense if the string was built in a way to allow nulls to creep in and confuse the result

No. Null & String, always, always returns a string. There is no way to get a null

Null & "|" & Null & "|"

=

"||"

Null + String equals null. That is why I want to see the real code used.

Null + "|" + Null + "|"

= Null