Automatic population of a field

  • Thread starter Kevin Anthony Jones
  • Start date
K

Kevin Anthony Jones

To whom it may concern,

I'm sure that this is an easy procedure, but for some reason I can't seem to
find the solution. I have a table which has both the "Vendor Name" and
"Agreement#" fields. From there, I have a larger table and I would like to
be able to select a vendor from a drop down menu (using the combo box, which
I have done), and have the Agreement# automatically associated with it.
Thanks in advance for your advice and suggestions.

KAJ
 
A

Aaron Howe

Firstly make sure those fields are linked in your
Relationships window, so that there is a definite link
between the Agreement Number and the Vendor Name otherwise
you can't perform the lookup. Then enter all of that
information into a query and check it looks okay - i.e.
that all the Vendors match their agreement numbers (if you
want the agreement number filled in automatically, make it
an Autonumber field in the table).

You'll want to make sure that your combo box includes all
the information you need (vendor AND agreement number even
though you'll hide the agreement number). Achieve that by
using the wizard as that's the easiest way, and by
selecting the fields you need, then resizing them out of
view in the preview window the Combo Box Wizard gives you
(the same way you would resize a column in Excel).

If that works out, use some code on the On Change event of
the combo box (right click it > Properties > Event then
choose Event Procedure and click the ellipse box next to
that). The code will be something like this:

Private Sub YOURCOMBO_OnChange()
Me.AGREEMENT = Me.YOURCOMBO.Column(2)
End Sub

Replace YOURCOMBO with the name of your Combo Box (right
click, Properties, Other, Name), AGREEMENT with the name
of the text box you want the agreement in. In this
example the information was in column two of the Combo
Box, even though it was hidden from sight.

I hope that helps, I've used my own experience to explain
it as best I can. It's most likely not the cleanest way
or the way with the most finesse but it should get you
want you want ;-)

Cheers
Aaron
 
K

Kevin Anthony Jones

I got everything up until the part when I'm supposed to enter the code. I do
not have an On Change event when I look at the properties of my combo box in
the table. The only tab that I have available to me is the "General" tab.
Is there somewhere else where I should be entering the code?

BTW, I have Access 2002. Thanks.

KAJ
 
A

Aaron Howe

Ah, it's the combo box in your *form* that you need to
look at, the table won't perform those functions for you ;-
)
 
K

Kevin Anthony Jones

Mr. Howe,

For some reason I still can't seem to get it working. I followed your
instructions, but either it won't work or a bug message appears. You
mentioned that you used your own experiences to explain this to me. Is it
possible that you could E-mail me the final product of what you did or send
screencaptures of the prcoedure so that I can validate where I went wrong?
You can send all references to my E-mail ([email protected]).

Thanks again.

KAJ
 
Top