subform recordsource not updating 3

[rss0213]

Hi. I have a form with two subforms, and I'm trying to link the recordsource in Subform2 to a field in Subform1. (Subform1 is linked to data in the main form.) So I have the following code to accomplish this:

strSQL = "Select * " & _
"From RemittanceLocation " & _
"Where [UniqueName] = '" & [SupplierLocation].Form!UniqueName & "'"

Forms![Supplier]![RemittanceLocation].Form.RecordSource = strSQL

varLines = Me.RemittanceLocation!RemitToAddressPostalAddressLines.Value

SupplierLocation is Subform1, RemittanceLocation is Subform2. When I run this code, I get error 94: "Invalid use of Null" on the last line of code. (The "Value" of RemitToAddressPostalAddressLines is null.)

Yet, when I create a query with the value stored in strSQL and run it, I get results. So why isn't the recordsource of this subform getting updated?

I see the following in the immediate window:

?Me.RemittanceLocation!RemitToAddressPostalAddressLines.Value
Null
?Forms![Supplier]![RemittanceLocation].Form.RecordSource
Select * From RemittanceLocation Where [UniqueName] = 'PC0000016'

Thanks!
Scott

 

[1DMF]

is the field you are trying to reference on the subform? and are you trying to reference it from the main form or other subform?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

what do you get from ?Me.RemittanceLocation!RemitToAddressPostalAddressLines

without .value on the end?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[rss0213]

Yes, the field I'm trying to reference is on subform2 (RemittanceLocation).

I'm trying to make the recordsource of subform2 be a record in the RemittanceLocation table that contains a particular value that matches a field on subform1.

This code is in the Current event of the main form (Supplier). The error occurs when I try to open the form.

(I know it might be confusing that I have forms and tables with the same name. I need to change this...)

Ahh... just saw your second post... It is ALSO null. Does this mean the 2nd subform is not loaded at the time the query is executed?

Thanks!
Scott

 

[dhookom]

I would place a hidden text box on the main form txtUniqueName and then code in the On Current event of the Subform1 like:

  Me.Parent.txtUniqueName = Me.UniqueName

Then just use the Link Master Child of the subform2
Link Master: txtUniqueName
Link Child: UniqueName

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

So you are trying to get a value from the sub form via the main supplier form?

then you need to use the following syntax I beleive...

Forms!main_form_name.sub_form_name.form.field_name

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

Do what Dhookom suggests

 

[rss0213]

Well, I added the hidden text box as you suggested, but I don't see the value that I added in the list of "Master" fields. I tried saving the changes, closing the form and re-opening, but I still don't see the new text box in the Master fields list. Is it possible that this list is only populated with fields from the recordsource of the main form?

Thanks!
Scott

 

[1DMF]

is it enabled? did you just alter the display attribute?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[rss0213]

Enabled=Yes

Thanks!
Scott

 

[1DMF]

from where are you tryng to populate it?

I'm still unsure where you are trying to set the record source of subform2.

what form are you trying to execute this code 'on current'?

strSQL = "Select * " & _             "From RemittanceLocation " & _             "Where [UniqueName] = '" & [SupplierLocation].Form!UniqueName & "'"          Forms![Supplier]![RemittanceLocation].Form.RecordSource = strSQL

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[rss0213]

Sorry, I stated this in my second post:

"This code is in the Current event of the main form (Supplier)."

Thanks!
Scott

 

[1DMF]

so should this be ..

  strSQL = "Select * " & _             
"From RemittanceLocation " & _             
"Where [UniqueName] = '" & me.SupplierLocation.Form.UniqueName & "'"

Also what do you see if you then

msgbox(strSQL)

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

going back to Duane's suggestion. The text box will not appear in your drop down. You type the link master and link child fields in manually.

 

[rss0213]

I'm trying dhookom's suggestion, so I'm not using the SQL string anymore.

Thanks!
Scott

 

[1DMF]

On a side note MajP / Duanne.

Are there particular benefits / advantages from creating a master/child link relationship vs coding a recordsource change?

I used both methods depending on what i'm doing but have always wondered if it matters which way you do it?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[rss0213]

Thanks, MajP. That worked. Works beautifully now. I actually found a link to an article about this here:

http://www.fmsinc.com/MicrosoftAccess/Forms/Synchronize/LinkedSubforms.asp

It's a little different in that it suggests setting the control source of the hidden field in the main form to the field in subform1 that will be used to link to subform2. But it accomplishes the same thing. Thanks to all for helping!!

Thanks!
Scott

 

[MajP]

I always do what Duane suggested. However, there is a mistake in that article, that states you can not link to a field in another subform. If you go to the Northwind sample database and look at the orders form there is an example where you can link this way. It requires a requery to make it work.

 

[1DMF]

Glad you got it sorted!

Star for duanne & MajP me thinks!

though I'm still curious why you could not get the value of a field in subform1 and then update the recordsource in subform2 via a query built up from the string concatenation of the subform1 field.

Either way i like the idea propoesed by the link you posted.

It dynamically handles the subform requeries for you.

so subform1 has a direct master/child relationship link and subform2 bases its relationship on the hidden text field that has a control source relating to the required field from subform1.

neat trick to bear in mind for future projects me thinks!






"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[rss0213]

Strange... I tried using requery after I set the record source (see above code), and it didn't work for me.

Oh well, I think it's better to do as much as you can without code. Makes it easier for less-techy folks to support. So Duane's solution is better anyway. Thanks again.

Thanks!
Scott