Referencing from a combo box

[Tony Williams]

I have a form (frmmain) and a subform (subform1). On the subform is a combo
box (cmbname) based on table tblindividual. This shows two fields txtsurname
and txtfirstname (in that order). I have another control on the subform
txtmemnbr. I want this to be a lookup where its value is the same as a field
in tblindividual called txtmemnumber where the txtsurname and txtfirstname
from tblindividual are the same as those selected in the combo box. How do I
construct the Dlookup formula to check both fields?
Thanks
Tony

 

[Peter Hibbs]

Tony,

If I understand you correctly I don't think you need a DLookup to do
this.

Assuming that the combo box (i.e. the RowSource) is based on a query
which shows the data from table tblIndividual you can just add the
txtMemNumber field to the query and then in the After_Update event of
the combo box you can copy the value from the combo box to the field
txtmemnbr. Something like this :-

Private Sub cmbName_AfterUpdate()
txtMemnbr = cmbName.Column(1)
End Sub

This assumes that column 0 holds the Surname & FirstName and column 1
holds the member number. You would also need to increase the Column
Count property by 1 and hide the second column if you don't want the
users to see it.

Peter Hibbs.

 

[Tony Williams]

Hi Peter. The txtsurname and txtfirstname are in different columns but I
think i understand what you're saying. I need to add txtmemnumber to my
combobox and then reference that in the Afterupdate of the combobox to give
me txtmemnbr. I'll play around with that and come back if I need any help.
Cheers
Tony

 

[Jeanette Cunningham]

I have a form (frmmain) and a subform (subform1). On the subform is a combo
box (cmbname) based on table tblindividual. This shows two fields
txtsurname
and txtfirstname (in that order). I have another control on the subform
txtmemnbr. I want this to be a lookup where its value is the same as a
field
in tblindividual called txtmemnumber where the txtsurname and txtfirstname
from tblindividual are the same as those selected in the combo box. How do
I
construct the Dlookup formula to check both fields?
Thanks
Tony

 

[Jeanette Cunningham]

Hi Tony,
instead of going to the extra trouble of creating a lookup, just make the
row source of the combo a query using the memnumber, the surname and the
firstname.
In the query put memnumber as the first column and make the 1st column of
the combo hidden. Set the comob's bound column to 1.
Whatever name the user chooses from the combo, you will always be able to
get the memnumber. The memnumber will be equal to the value of the combo.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia