List Box Help

[vcarrill]

Hello,

I would like for the Process Area and Description to auto populate based on what I enter under Cert Code.

I am unsure how to do this with a List Box?

Appreciate the support, thank you

 

[CJ_London]

Do you mean listbox or combobox? A list box can show the values anyway

And when you say populate - do you mean to simply display the values or are you wanting to populate fields in a table? If the latter, why?

 

[vcarrill]

What I mean is, Id like to offer the certs as a list and when a particular cert is selected, I need the Process Area and Description to be tied to it so it can be seen upon selection. At some point I would like to query which certs are available by area, etc.

 

[CJ_London]

still not clear what you mean

but with a listbox you would have a rowsource along the lines of

SELECT CertPK, Cert, ProcessArea, CertDescription
FROM tblCerts

Note I have removed spaces and renamed fields (you need a primary key field (certPK) and Description is a reserved word

then in your listbox control, set the column widths as required, hiding the CertPK column (set width to 0)

 

[bob fitz]

What I mean is, Id like to offer the certs as a list and when a particular cert is selected, I need the Process Area and Description to be tied to it so it can be seen upon selection. At some point I would like to query which certs are available by area, etc.

You could have a combobox from which you choose a cert. You could have the other details as hidden coloumns in the combobox. A textbox on the form could reference the appropriate column of the combobox to return the required data.

 

[SHANEMAC51]

What I mean is, Id like to offer the certs as a list and when a particular cert is selected, I need the Process Area and Description to be tied to it so it can be seen upon selection. At some point I would like to query which certs are available by area, etc.

when I need to see several fields when selecting (often long ones), I use a dialog form instead of a list with the ability to select by any field, including by partial value and a combination of several fields

 

[vcarrill]

Do you mean listbox or combobox? A list box can show the values anyway

And when you say populate - do you mean to simply display the values or are you wanting to populate fields in a table? If the latter, why?

Display the values. What I am trying to do is create a repository to capture certifications by employee. So when a particular cert is chosen, the end user needs to see area and description.

 

[CJ_London]

from your description you need 3 tables

tblEmployees
empPK
empFirstName
empLastName
...
...

tblCertificates
CertPK
Cert
ProcessArea
CertDescription

and finally to join these together

tblEmployeeCerts
empCertPK
empFK
CertFK
CertDate
ExpDate

so per my example post#4 on a form you would use that rowsource in a combo for the CertFK field in tblEmployeeCerts

 

[Pat Hartman]

Copying the fields from the list box or combo is poor practice UNLESS you want to be able to modify them. For example. When you select a product for an order, you normally copy the unit price into the order. That allows you to discount it on the order if you need to. It also keeps the price on an order from changing if the price in the product table changes. If you are running a history report, you don't want something you sold last year to show with this year's price.

When I want to use a combo but show extra fields on the form, I modify the RecordSource query of the form to use a left join to the lookup table. That allows me to bind controls on the form to the lookup table. When you do this it is IMPERATIVE that you set the lookup control's locked properties to Yes to prevent accidental updates. So you have two situations, the price field will most likely be copied to the order details table but the product description will not. It will just be visible for reference on the details form.

Here's an example that shows the various ways to do this depending on whether or not you need to copy or just view the lookup data. Look at the 5th example.