Combo box

[Dmac]

Hello,

I have a combo box (cboPNumber) on my form that queries a tblLookUp for a
PNumber field. The number is inserted into PNumber field in tblMaster.
That all works fine. I have two other fields on the form that are txtName
and txtUnit.

What I am trying to do is insert the corresponding txtName and txtUnit data
into the respective fields on the form as well insert the data into the
fields in the tblMaster table when the PNumber combo box is updated.

I have tried using combo boxes for the for the txtName and txtUnit fields
and doing a Me.txtName.Requery on the PNumber Update Event. That does work,
however, the information isn't visible until you select it from the menu.
The same with the txtUnit combo box.

I have also tried using a List Box, but since the form is a continuous form,
the information is shown in all the rows of the form.

Any help would be appreciated.

 

[Guest]

Are the fields txtName and txtUnit also in tblLookup? If so the you should
not include them in tblMaster if their values are always determined by the
PNumber selected as this constitutes redundancy and leaves the table at risk
of inconsistent data being introduced. Only if their values in tblLookup can
change over time but you don't want those changes reflected in existing
records in tblMaster should you include fields in the latter.

For the first scenario one way is to use unbound text boxes on the form and
look up the values from tblLookup by means of the DLookup function in the
ControSource properties of the controls:

=DLookup("txtName",tblLookup","PNumber = " & [cboPNumber])
and
=DLookup("txtUnit",tblLookup","PNumber = " & [cboPNumber])

Another way is to include the txtName and txtUnit columns as extra columns
(either shown or hidden as you wish) in the combo box's RowSource, setting
its ColumnCount property to 3, and reference them via its Column property,
respectively:

=cboPNumber.Column(1)

=cboPNumber.Column(2)

Note that the Column property is zero based, so this references the second
and third columns in the control's RowSource query.

Another way would be to base the form on a query which joins the tables and
bind controls to the txtname ands txtUnit columns from tblLookup, setting the
control's Enabled property to False and their Locked property to True. The
PNumber column fro tblLookup should not be returned by the query of course.

For the second scenario (which I'd suspect is an unlikely one for this kind
of data, being more usual with currency values such as unit prices – see the
Northwind sample database for an example), in which you would need to have
the fields in tblMaster then assign the values to them in the AfterUpdate
event procedure of the combo box:

Dim strCriteria As String

strCriteria = "PNumber = " & Me.cboPNumber

Me.txtName = DLookup("txtName",tblLookup",strCriteria)
Me.txtUnit = DLookup("txtName",tblLookup",strCriteria)

I've assumed that PNumber in the above is a number data type. If its text
wrap the value in quotes, e.g.

strCriteria = "PNumber = """ & Me.cboPNumber & """"

Ken Sheridan
Stafford, England