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

 

[dhookom]

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.

I think this opens too many possibilities for maintenance. What if you want to change the subform recordsource sorting or something? You would then need to change the code.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Yeah, I guess it's a bit different for me as I am the IT department and so changing code is no biggie, I write every inhouse / outhouse and subhouse system we have!

I've never thought about someone having an MS access database system where there isn't any VBA code involved, I wouldn't be able to do half the stuff I do without VBA and office automation, so steering clear of code doesn't cross my mind!

But i guess that means there is some stuff I do with code, that really I should let MS access handle.

Hey ho, you live and learn!

"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]

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

The subform is very powerful. If you code it there are a lot of things you have to do/remember to do.
1)Filter on these events: OnCurrent, and afterupdate
2)Creating a new record in a subform you need to default the foreign key to the master field.
3)Handle a null value in the master field
and there are probably some more.

However there may be some flexibility in doing it yourself. The biggest one is if you want to show all records in the subform when nothing is in the master field.

 

[1DMF]

cool, I'm just about to create a new form for some user requests and this time I'll ensure i'll use child/master!

Cheers guys & gals.

"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