Pass value of a combo box selection to a subform

[lachesis]

I have a form / subform where the main form holds Company details, and the subform holds Contact details.

On the main form I have a combo box with 3 columns: CoName, CoLocation, CoID. CoID is the bound column, but it is also HIDDEN. On the subform I want all Contacts associated with the Company [the value of CoID] selected in the combo box to display.

I have included this code snippet ([forms]![f_CompanyInfo]![cboCompany].[column(2)].[value]) in the criteria for the record source of the subform, but it is not working. I am persistently prompted for a parameter value when the form opens.

I think its one of two things:
1/ the syntax of the code snippet is wrong??
2/ the code snippet does not recognise combo box columns that are HIDDEN??

Any ideas on resolving this one?

cheers

 

[jimb0ne]

you don't really have to enclose everything in square brackets, you could just use
Forms!f_CompanyInfo.cbocompany.column(2).value

also you probably want a . instead of a ! after the formname.

 

[lachesis]

Jimbo, I wish it was that simple.

I have tested a pass of valid paramater values thru the SQL Query Builder of the subform, so I know it accepts values and processes & produces the right result.

It just doesnt work as an object inside the main form.

Hmmm....

 

[lachesis]

bump.

 

[ppkgoldengun]

I use the following as a validation rule on a subform field that is going against a main form combo box. Perhaps it will help. Note the Parent parameter. The code fails in the subform but works correctly when the main form is opened.

Also note that the field name is Combo0 and I don't go against the colum but the saved value. In the after update event of the combo box I also use a Me.Refresh so that the subforms are updated if the parent changes.

Hopes this give you some ideas.

>=[Parent].[Combo0]-6 And <=[Parent].[Combo0]

 

[lachesis]

huh?? say what!

 

[lachesis]

Ah, I see.

It was the incorrect syntax. Seems you can't specify the combo box column you want to draw data from, as in my version:

forms!f_CompanyInfo!cboCompany.column(2).value (wrong)

If the column specifier was removed it worked:

forms!f_CompanyInfo!cboCompany.value (right)

I'm assuming this worked bcz the bound column in the combo box, just happened to be the column I wanted to pass as a parameter to the record source of the sub-form. Although I'd like to be proved different for my own sanity...

cheers L.

 

[rhinomac]

Can somebody help me out on this one.
I believe this is kinda what I want to be doing.

I have a main form that contains a list box.
I want the on click properties of the list box to pass the selected parameters into a query subform that resides on the same form.

Heres what im trying so far. It doesnt work. Let me know if im completely off.

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click
Dim choice As Integer
Dim choice2 As String
Dim stDocName As String
Dim stLinkCriteria As String

choice = datelist
choice2 = projectlist
stDocName = &quot;resourcelookup&quot;
stLinkCriteria = &quot;[dateid] = &quot; &amp; choice &amp; &quot; and [projectid] = &quot; &amp; choice2 &amp; &quot; &quot;

DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria
'Forms!MainFormName!SubFormName!TextBoxName
'heres where i do not know how to call the subform

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

Thanks

 

[jimb0ne]

referencing the subform is fairly easy, basically it is just this....


subformcontrolname.form.textboxname

so you reference the frame that the subform is in, then reference the form property, then reference any control on the subform that you want to edit. Hopefully this helps, chances are you figured this out by now.

 

[volvoman]

wow, there are some really complex answers to this thread, I found a real simple way of doing this by trial and error (took me a couple of hours).

First, create an unbound form as the parent form. On this, you put a combo box with the usual query as the source, making the bound column the one you want to look for in the subform. (doesn't matter if it's the visible column or not)Name the combo box with a memorable name, such as companyselect. Also, turn off record selectors & nav buttons for this form.

Next, insert your subform and open up the properties sheet. Set the Link Child Fields Property to the value to match in the subform, and the Link Parent fields to your combobox's name (ie companyselect), not the name of the field from the table it's based upon. You will have to enter the link parent fields value manually, it won't appear in the drop down.

bob's your uncle, it will go straight to the records you want in the subform as soon as you select a value from the combo. The unbound parent is the key to this, it stops the form displaying records as such, and by doing this, the combo will only select stuff from a list, it won't change any data in the underlying table.

 

[rhinomac]

WoW!
Never thought of that...
I will try and let you know how it goes.
Seems pretty logical though.
Thanks for all your help.-
Rhinomac