Autopopulate

[Annemarie]

Hello.

I have been successful in having field 2 autopopulate based on entry in
field 1 combo box based on a query I built. This however, is the only
time I have gotten it to work. I have tried to do the same autopop in
a new form, checking and re-checking to make sure everything is the
same, but cannot get it to work more than that one time. What am I
doing wrong? I have many things I need to use this function for, and
it would be VERY useful if someone could help me out.

Thanks.

 

[Jeff Boyce]

Annemarie

You didn't indicate how you got this to work...

What do you mean by "autopopulate"? Is your "field2" an unbound control?

What code do you have that runs in the AfterUpdate event of the combo box?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Annemarie]

Ok..

I have a name (field 2) that corresponds with the number (field 1).
When I enter the number I would like the name to automatically show up
in the form. I have a library table with all of the numbers and their
corresponding names. I have a table that shows this information and
more for records.

I first got this to work by making a query linking the number from the
large table to the name from the library table. In the form i made the
number a combo box and have the row source as the library table. There
are no events, it just somehow populates itself. It was a surprise
when it started doing it, which is probably why I can't reproduce the
effects in other forms.

Sorry, I am a beginner with some access experience but with no Visual
Basic code experience...I am learning as I go.

Thanks.

 

[Jeff Boyce]

Annemarie

I'm still not real clear, but it sound like you want to see a name (?a
person, a business, a country, ...?).

There's an easier way to do it, if that's what you are after.

Use a combo box on a form. In the Row Source of the combo box, use your
table and include the ID field (?I'm guessing this is your "number"), and
the "name" field. By the way, don't name this field "name" -- this is a
reserved word in Access and it will get confused about what you mean.

Now set the column width of the first column (ID) to 0, and whatever width
you need for the second (the "name"). Set the first column to be your bound
column.

Now, when you drop the combo box down, you will only see the "name" values.
This combo box will actually store the ID, but display the name.

Most folks find it much easier to select by a name than by a code number
they have to remember or write down or ...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Annemarie]

OK. I tried that and it was better...but not quite what I wanted.

I would like to store both fields in my table. I would like to select
field "CNumber" from a combo box, which will then pull up "CName" in
another field on the form. They are related through the CLibrary table
which has two columns (CNumber; CName).

It looked great to be able to pull the CNumber with the CName next to
it in the same combo box, but then the box only stored one of the
values when selected. How do I have both displayed in the form and the
table by only selecting one?

I'm getting closer..

Thanks so much

Annemarie

 

[Jeff Boyce]

Annemarie

Don't! Since Access is a relational database, you don't have to store both
CNumber and CName in your table. In fact, doing so would make more work for
you! If both fields are in the table, you have to work out the code to make
sure they stay synchronized.

The "relational" way to do it is to store only the CNumber. If you need to
see which CName goes with it, that's what queries are for.

And via your form, even thought the combo box displays the CName, it is
storing the CNumber (if you set it up as I suggested) in the table.

Besides, why would someone want to use a CNumber when they could use
something that makes more sense (i.e., a name)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Annemarie]

The idea was to be able to see both in the form. Since users might
only have the CNumber (like an ID number) available, it would be
helpful to see the corresponding name as well on the form. I know you
can query the information...but for the user friendly format...we don't
want users to be building queries we would rather have the information
already there for them.

Also...on that related tables note I have another question...I built a
report that shows multiple entries that all share a common field (B).
Since the B and BName are stored in their own library table, I simply
put BName in the report as well to show the name (since the two tables
are related). When I did this, since there are three entries that all
relate to that (B), all of the multiple entries repeat three times
there are three 1's, three 2's, three 3's. I think this can be fixed
by changing the relationship of the two tables to a specific join...is
this true and which one, or would this mess with other functions these
two tables are joined for? Does this make sense at all? I will
experiment with it...

Thanks

Annemarie

 

[Jeff Boyce]

Annemarie

If you are saying you (your users) need TWO mechanisms for selection ("I
know CNumber; you know CName"), you need two mechanisms. A combo box gives
you one (for selecting).

If you only need the users to SEE the other field, you can easily do this by
setting the value of an unbound text box to be the column(n) of the combo
box, something like:

Me!MyNewTextBox = Me!MyComboBox.Column(n)

(where "n" is the n-1 column in the combo box's row source - .Column() is
zero-based)

I'm not tracking on how your "B" report relates to the underlying table
structure. Access tables store data, Access forms/reports display it. A
typical approach to generating a report is to build a query first. The
query pulls together all the fields (from all the tables) you need to see.
The report formats how the data will appear on paper.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Annemarie]

Jeff,

I got my report to work...thanks anyways though!

yes yes! I want users to See the field!!

Ok...So I made an unbound text box and put the control source as
Me!CCN Name = Me!CCN.Column(1)
What did I do wrong? It comes up with #Name? in the field. I tried
Column(2) as well.
Does it not go in the control source? If not, then where? Sorry I am
having so much trouble with this simple task...

almost there...thank you so much for your help!

 

[Annemarie]

I went with your previous suggestion to change column widths to show
different fields and it is working out perfectly. Thanks again for
your help.