auto fill form based on combo-box selection

[Guest]

Instead of creating several forms with different default values, I would like
to "auto-fill" some of the fields based on the value selected on the first
combo-box on the form. The defaults would come from a table. For example
tblSpecs would contain RecipeName, SprayArea, GDL_Material, etc. On my form
when the user selects RecipeName, the vlaues for SprayArea, GDL_Material, etc
would auto fill with the corresponding values from the table where
RecipeName=RecipeName. I think this is probably do-able but I don't have much
coding experience. Help is greatly appreciated.

 

[Jeff Boyce]

If I'm understanding your description, you want to be able to display
corresponding values once a selection is made. If the ONLY thing you are
doing is displaying those looked-up values, and are not planning on
(re-)saving them (i.e., the controls are unbound), then you could easily do
that by:

include the values you will want shown as fields in the query you use to feed the combo box
create an AfterUpdate event for the combo box
in that event, add something like:

Me!txtSprayArea = Me!cboRecipeName.Column(1)
Me!txtGDL_Material = Me!cboRecipeName.Column(2)
...
Check Access HELP on using .Column(n) -- it is zero-based, so you start
counting at "0".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

No, I plan to save the data, either the default (pre-filled) value or a
changed value if the user over-rides the default.

 

[Jeff Boyce]

So it sounds like you are considering re-storing the same data multiple
times. This sounds more like what you'd do with a spreadsheet than a
relational database.

Have you looked into relational database design, and "normalization"? You
really won't be able to get the best use of Access' features and functions
if you feed it 'sheet data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I see it more as "target" or "spec"(pre-filled value) and Actual (saved value).
The supervisor will maintain the Spec table to communicate what the target is
and the operator will enter the actual data.

 

[Guest]

I tried the following code to do what I'm attempting:
Me.CRecipe.Value = Me.ProductSpec.Column(0)
Me.MembraneType.Value = Me.ProductSpec.Column(1)
Me.ActiveArea.Value = Me.ProductSpec.Column(2)

CRecipe and MembraneType are text fields and they update fine.
ActiveArea is numeric and it won't update.
Do I need to add some qualifier or something?

Help is appreciated.

 

[Jeff Boyce]

Have you run your code in debug mode to confirm that
Me.ProductSpec.Column(2) is returning what you expect it to?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I'm not that familiar with running in debug but it looks like the value I'm
getting for ActiveArea is "Null". It appears that the only time I get a value
is when I set Column to 0 or 1. That's probably a clue to what's wrong but I
don't get it.

The combo box is based on a query of tblProdSpecs. The table fields are:
Spec_ID - Autonumber
SpecID - Text
CRecipe - Text
ActiveArea - Number
MembraneType- Text

The query contains all of the above except for Spec_ID with Bound Column set
to 1.

 

[John W. Vinson]

I'm not that familiar with running in debug but it looks like the value I'm
getting for ActiveArea is "Null". It appears that the only time I get a value
is when I set Column to 0 or 1. That's probably a clue to what's wrong but I
don't get it.

The combo box is based on a query of tblProdSpecs. The table fields are:
Spec_ID - Autonumber
SpecID - Text
CRecipe - Text
ActiveArea - Number
MembraneType- Text

Column(9) means the value of the tenth field (it's zero based) in the combo's
Row SOurce. I see only five fields here, and it's not clear what the
ColumnCount property of the combo might be.

What are you INTENDING for Column(9) to return?

John W. Vinson [MVP]

 

[Guest]

Thanks, that was the clue I needed. My Column Count was set to 2, once I
upped the number, all appears to be fine.

 

[kodyleonard]

This allowed me to resave the data into a separate table so I could use it for reports based on entries with the form. It also explains why some of the steps are taken and look the way they do:

http://bluescreensolutions.blogspot.com/2012/05/access-auto-populate-fields-in-form.html