Select a Datasheet Row -- Populate a 2nd SubForm based on that row?

[mbrost]

Hi,

I'm not even sure if this is possible to do (I havn't found anything on the Net yet), but I have a Form displaying Customer information, and the vending machines that customer has.

The main form (frmAccount) has 2 SubForms:
1) AddressSubForm (is a Datasheet), listing the many addresses the one account has.
2) VendingMachineSubForm (is a Continuous Form) and displays the machines related to the account.

What I want to do is select an address in the datasheet, and have the MachineSubForm populate with what machines are located at that address. Can this be done?

The Machines and Addresses link by AddressID. Both subforms are built on queries.

Thanks for any help/insight!

Mike

 

[mbrost]

bump -- anyone have an idea??

 

[RuralGuy]

Yes. On the Machine SubForm you set the "Link Master Fields" to the AddressID field of the AddressID SubForm. ie:

Me.Parent.AddressIDsubFormControlName.Form.AddressIDcontrolName

Not tested but you should get the idea.

I took a look at how I did this in one of my systems and I used an invisible textbox on the main form with the control source set to SubFormControlName.Form.ControlNameToSyncTo. Then I set the "Link Master Field" to the InvisibleTextBoxName. You can also just go directly to the other SubForm but I think you may have to go through the Forms collection.

 

[mbrost]

Yes. On the Machine SubForm you set the "Link Master Fields" to the AddressID field of the AddressID SubForm. ie:

Me.Parent.AddressIDsubFormControlName.Form.AddressIDcontrolName

Not tested but you should get the idea.

Thanks for the tip RuralGuy, but that doesn't work -- Access wont allow me to type that path into the Link Master Fields -- you need to choose from a list only.

Both subforms are based on queries, and I tried to put some logic in the WHERE clause of the Machine SubForm like:
WHERE tblMachine.AddressID = [Forms]![frmAccount]![frmAccountAddresses_Sub].[Form]![AddressID]

This query works if I manually type in the AddressID's when I run the query separate, but does not work at all when using in a form -- am I on the right track though??

I'm really hoping Access can handle something like this!

Thanks for any help!

Mike

 

[RuralGuy]

I now find the invisible linking TextBox on the Main form the best approach. Just for clarification: you do not have to pick from a list to fill in the "Link Child Fields" and "Link Master Fields".

Put a TextBox on the Main form (frmAccount) and let's call it "txtLink". You can set it to invisible later after we get it working properly. Set the Control Source of txtLink to: AddressSubForm.Form.AddressID

AddressSubForm needs to be the name of the SubFormControl on frmAccount, not the name of the SubForm; which are often the same but not necessarily. If you get a #Name? in the textbox it almost always means Access is confused on the source and that is usually caused by the control and the field it is bound to having the same name. I usually precede a control name with a prefix of the type of control: txtTextBox. Once you get the txtLink working we can concentrate on the other SubForm. Click around on the various records in the "AddressSubForm " to make sure the txtLink TextBox is working and then move on the the Link Child/Master stuff.

Set the "Link Child Forms" of the VendingMachineSubForm to: AddressID
Set the "Link Master Forms" of the VendingMachineSubForm to: txtLink

 

[ScottGem]

I would do this differently. Your machines table should have an FK with the AddressID for the account. I would have have an UNBOUND Listbox on the main form that list the addresses for that account rather then a subform. I would then have the vending machine subform be filtered for the selected address. Do a requery of the subform in the After Update of the Listbox.

 

[mbrost]

Put a TextBox on the Main form (frmAccount) and let's call it "txtLink". You can set it to invisible later after we get it working properly. Set the Control Source of txtLink to: AddressSubForm.Form.AddressID

I've given this a quick try, but am having difficulty mapping a text field on a form to have a value of the subform on it.

I've given this txtLink's Control Source name of: frmAccountAddresses_Sub.Form.AddressID
where "frmAccountAddresses_Sub" is the name of the subform where I want to capture the Address ID. I get the #Name? error though. I can't find any other settings that would make a diff -- the "Name" of the txt box is set to txtLink

This idea will work perfect if I can just get the field to map

 

[RuralGuy]

You need the SubFormControlName not the name of the SubForm. You'll know you are on the SubFormControl when the property sheet data tab has the "Link Child Forms" on it. The Other tab has the name of the Control. I think you are using the SubForm name. It is a little tricky to click on the SubformControl but when you are there the pull tabs appear to stretch the control.

 

[mbrost]

Does it matter if my Source Object and Name have the same name for that subform? the name for both is frmAccountAddresses_Sub.

So, the text field on the frmAccount is mapped to frmAccountAddresses_SubObj.Form.AddressID

the frmAccount has the 2 subforms on it:
frmAccountAddresses_Sub
frmAccountMachine_Sub

I really dont know what else to try, your instructions are clear, but this mapping isn't working.

 

[RuralGuy]

Change the AddressID TextBox name to txtAddressID. Access can get confused when the control name and the control source name are the same.

What is this?

So, the text field on the frmAccount is mapped to frmAccountAddresses_SubObj.Form.AddressID

You said the name of the SubFormControl was frmAccountAddresses_Sub not frmAccountAddresses_SubObj

Is this just a type? You are very close. I think this go around will get the txtLink to work.

Your final reference for txtLink will probably look like:

frmAccountAddresses_Sub.Form.txtAddressID

 

[mbrost]

What is this?


You said the name of the SubFormControl was frmAccountAddresses_Sub not frmAccountAddresses_SubObj

yes, typo -- whoops, they are the same name, just the _Sub part. I'll try the above then let you know how I make out

 

[mbrost]

Still doesn't work for me, I have it laid out as you instructed. I did attach the DB -- the Form is frmAccount, the txtLink is in red and the subform it should reference is right above it called "Customer Addresses".

Can you see what the issue is??

Thanks again for any help!

Mike

 

[RuralGuy]

Well that took a bit to locate. The control source of txtLink *must* start with an equal sign "=". That should move things to the next level.

 

[mbrost]

Awesome thanks, that worked

So, now I guess it's as simple as linking the Machine subform to the txtLink field?? I'll give it a shot.

edit:
This 2nd part worked too -- whoo hoo -- thanks again RuralGuy, you're a life saver!

Mike

 

[RuralGuy]

Your welcome and thanks for posting back with your success.

 

[JoeCruse]

RuralGuy and mbrost,

I wanted to thank you guys for this thread, as it helped me solve my own subform issue. The Search function is VERY handy!

I have used Access for a few years as a workout log, but my original setup was NOT normalized at all. I've been going over a lot of my older stuff and correcting situations like this, both for work and little stuff of my own, like this workout log. I searched all over for a decent example of a workout log for lifting weights, with a system for recording both the exercises in each workout, and the sets, weights used, and repetitions performed in each set for each exercise. I knew the best way would be with 3 tables, but could NOT, for the life of me, figure out how to do the subform setup, so I went with 2 tables for a little bit, which sucked.

With the information in this post, I was able to use the 3 table setup, and put together the main workout form with the 2 subforms, fsublifts and fsubsetsreps. It works like a charm! The only issue I had was setting the control source of the invisible text box on the main form. You are right about confusing Access on names of controls. It took me a long time to track down why I kept getting "#name?", but I had a subform name in wrong.

When I get everything done, I will post a copy as an example of an exercise application, in case someone else wants another way to do it.

Thanks again!