Auto Populate based on selection

[Rich Ellis]

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.

 

[Al Campagna]

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...)

--
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."

 

[Carl Rapson]

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.

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

 

[Rich Ellis]

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
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...)

--
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,

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.

 

[ruthhays]

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

 

[Carl Rapson]

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

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

 

[Al Campagna]

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.
--
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
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
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...)

--
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,

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.