Auto Populate based on selection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rich Ellis - 09 May 2007 21:52 GMT

Hi,

I have a form which is linked to a table(tblAntibody) for data entry.
I have another table(tblCatalogPartNumbers) with no relationship to
the first which contains all of our codes and has four coulmns
CatalogNumber, UnqualifiedNumber,NIPartNumber, and PIPartNumber. All
of these columns store thier data in tblAntibody.

On the form CatalogNumber is a combo box that has it's selection
criterial set to tblListCatalogPartNumbers. What I need to happen is
when a user selects a catalog number from the combo box, I need the
other 3 fields to auto populate from tblCatalogPartNumbers.
CatalogNumber is the primary key in tblCatalogPartNumbers.

Any help would be appreciated.

Reply to this Message

Al Campagna - 09 May 2007 22:38 GMT

Rich,
Since the CatNo appears to be the key field, you would not "save" the 3 other field
values... just diplay them. Since you've captured CatNo, you can always re-derive the Unq
Nip and Pip in any subsequent form, query, or report, from tblCatalogPartNumbers.
Never (well almost never) save a value in a table that can be re-derived from values in
existing data.
Ex. [cboCatNo] Combo column setup (probably just like you have them now)
CatNo Unq Nip Pip
123-425 1234 3333 4444
(bound to
CaTno)

An unbound calculated control with...
= cboCatNo.Column(1)
will display "1234"

An unbound calculated control with...
= cboCatNo.Column(2)
will display "3333"
.......etc......
(combo columns are numbered 0,1,2,3, etc...)

Signature

hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

> Hi,
>
[quoted text clipped - 11 lines]
>
> Any help would be appreciated.

Reply to this Message

Rich Ellis - 10 May 2007 19:08 GMT

Ok I had to make tblCatalogPartNumbers becuase a user will update the
tblCatalogPartNumbers through a seperate form. frmOrderEntry is the form
being used to input data into tblAntibody. On frmOrderEntry CatalogNumber is
a combo box with the list source as tblListCatalog PartNumbers and the other
3 fields are text boxes. When the user chooses a catalog number I would like
the other 3 fields to auto poulate with the values that correspond to the
catalognumber in tblListCatalogPartNumbers.

Yes tblCatalogPartNumber records are static once a record is entered into
tblCatalogPartNumber.

> Rich,
> Since the CatNo appears to be the key field, you would not "save" the 3 other field
[quoted text clipped - 33 lines]
> >
> > Any help would be appreciated.

Reply to this Message

Al Campagna - 11 May 2007 04:25 GMT

Rich,
I don't know why you're "re-describing" your setup... instead of just telling me if you
were successful, or not, in implementing my suggested solution..
Did you try my suggestion?
On my website I have a A2K and A97 sample file called Combo Updates Multiple Fields.
It shows a sample form with my suggested solution at work.

Signature

hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

> Ok I had to make tblCatalogPartNumbers becuase a user will update the
> tblCatalogPartNumbers through a seperate form. frmOrderEntry is the form
[quoted text clipped - 46 lines]
>> >
>> > Any help would be appreciated.

Reply to this Message

Carl Rapson - 09 May 2007 22:39 GMT

> Hi,
>
[quoted text clipped - 11 lines]
>
> Any help would be appreciated.

Does a CatalogNumber always correspond to the same UnqualifiedNumber,
NIPartNumber, and PIPartNumber? If so, you should only need to store the
CatalogNumber in tblAntibody, because you can always use it to look up the
other 3 values whenever you need them. That way, the other 3 values aren't
stored redundantly in both tables.

Carl Rapson

Reply to this Message

ruthhays@gmail.com - 10 May 2007 19:56 GMT

Hi all, don't mean to barge in on your conversation, but I'm actually
using the same technique to display values on a form. And I have a
question regarding how this method displays existing records.

I'm working in Access2K with an existing database and I'm modifying
forms to improve data accuracy. The Job form where I'm using the combo
is used for data entry and the form is edited as the job progresses. I
added a new field to the Jobs table called [UACID] (Unique Aircraft
ID). It links to a table [ACType] that holds [UACID] (an autonumber
field), [CustomerID] (Text), [AircraftNo] (Text), [AircraftType]
(Text) and [AircraftSeries] (Text).

The query grid for the combo holds these values: ACUID (bound column),
ACID (an expression field made up of [CustomerID]&"-"&[AircraftNo]),
[AircraftNo], [AircraftType], and [AircraftSeries]. There is a
criteria of Forms![JobForm]![CustomerID] in the Customer ID fields so
that only aircraft associated with that customer will display in the
combo box.

Everything works fine when the information is entered fresh, or if an
aircraft is reselected for an existing record. However, for existing
records the aircraft combo (and the related fields displaying Type and
Series from the combo columns) appear blank even though ACUID is
populated in the source record. Ideas?

Thanks in advance!
Ruth

Reply to this Message

Carl Rapson - 10 May 2007 22:34 GMT

> Hi all, don't mean to barge in on your conversation, but I'm actually
> using the same technique to display values on a form. And I have a
[quoted text clipped - 23 lines]
> Thanks in advance!
> Ruth

When you add a new record or change the aircraft for an existing record, is
the UACID actually saved in the Jobs table or is it just displaying in the
combo box? The reason I ask is, it sounds like your combo box isn't actually
bound to the UACID field in the Jobs table. To check this, look at the
Control Source property on the Data tab of the Properties window for the
combo box. Does it say UACID? Is your Jobs form based on the Jobs table
directly or on a query? If it's a query, did you also add the new UACID
field to the query as well?

Also, it sounds like the Type and Series controls are being populated in the
AfterUpdate event of the combo box (that's why they show up when you select
a new UACID). The same code to populate the Type and Series controls should
be in the Form_Current event as well, so the controls will be populated
whenever you display an existing record.

Carl Rapson

Reply to this Message