vba reference to combobox column value

[fluffyozzy]

Hi guys and girls,

What is the correct way to refer to a combobox column in vba? I'm trying to reference a combobox column on a form as a parameter in dcount statement but it's not working. It's probably something really simple but totally stumped me.

This works:
CountOfID = DCount("[PersonID]", "tblResponse", "[PersonID] = Forms!frmMain!frmMainHold!frmPerson!PersonID AND [QstID] = 1")

But this doesn't and this is the one I need:

cmbValue = Forms!frmMain!frmMainHold!frmPerson!cmbOpenDataForm!Column(0)

CountOfID = DCount("[PersonID]", "tblResponse", "[PersonID] = Forms!frmMain!frmMainHold!frmPerson!PersonID AND Forms!frmMain!frmMainHold!frmPerson!cmbOpenDataForm = cmbValue")

Can you help please? Many thanks in advance

 

[isladogs]

Hi
See this link for the correct way to reference subform controls http://access.mvps.org/Access/forms/frm0031.htm

If you want the bound column value, you don't need to use the Column(0) type reference. Otherwise, do so counting from zero.

Hope that helps

 

[arnelgp]

if the field, PersonID is numeric:

cmbValue = Forms!frmMain!frmMainHold!frmPerson.Form!cmbOpenDataForm.Column(0)
CountOfID = DCount("[PersonID]", "tblResponse", "[PersonID] = " & cmbValue)

 

[fluffyozzy]

Thank you to both of you! isladogs, I should have had that link ages ago! Very useful. And arnelgp, thank you so very much, it all works perfectly. I wasn't thinking clearly at all this morning LOL. So, the final working solution is:

cmbValue = Forms!frmMain!frmMainHold!frmPerson.Form!cmbOpenDataForm.Column(0)

CountOfID = DCount("[PersonID]", "tblResponse", "[PersonID] = Forms!frmMain!frmMainHold!frmPerson!PersonID" & QstID = cmbValue)

Cheers guys x

 

[isladogs]

Glad you've got it working but I'm surprised that works.
The syntax for the PersonID part needs to be modified in the same way to include .Form.
Also you need to allow for number or text datatype as arnelgp explained

Assuming both fields are number datatype and combining into one line then I believe you need:

CountOfID = DCount("PersonID", "tblResponse", "PersonID = " & Forms!frmMain!frmMainHold!frmPerson.Form.PersonID & " & QstID = " & Forms!frmMain!frmMainHold!frmPerson.Form.cmbOpenDataForm)

 

[Galaxiom]

I'm surprised that works. The syntax for the PersonID part needs to be modified in the same way to include .Form.

The Form Property of the subformcontrol can be left out if you use the bang(!) operator because it is a reference to the default member.

However it is better to include the .Form. because it is Early Bound and any spelling mistakes in the control name will be picked up during compile. The bang is Late Bound so is only tested at run time.

 

[fluffyozzy]

Mmm.. Yes, both fields are number datatype. Just ran about 10 tests with what I have and it is doing what I wanted. To clarify, the person ID is not a combo, only the QstID is selected from a combo, PersonID is just an ordinary number field on the form

 

[fluffyozzy]

Galaxiom, good to know the proper convention, thank you. I've just tried it with my code with the way isladogs suggested and it works too. I learn more on forums than any course I paid through the nose for

 

[isladogs]

The Form Property of the subformcontrol can be left out if you use the bang(!) operator because it is a reference to the default member.

Thanks Greg. I've learnt something new.