Complicated possible list box

[LG]

I have a dilemma. I know access is not like excel but it's the only way for
me to keep track of data and get reports that I need. It is quite a
complicated process we enter data in and has useful info that we need to
report to acct manager, directors etc. The data that is stored is the Gov
related. I would like to minimize the amt of errors in inputting by having
them click on the agency and it auto populates the address, city, stat and
zip code. Is this possible? I have 3 tables 1 where the the info is stored
an another where reason for rejection is stored and another built with the
agencies and their info. The data that is entered also gets put into a mail
merge and letters are produced off certain info.
If you need more info let me know.
thanks in advance.

 

[Ken Snell [MVP]]

Yes, what you want to do is very possible to do, indeed. There are many ways
of doing this. Tell us about the form's setup (listbox or combobox being
used to select agency, what is the RowSource query's SQL Statement for the
listbox/combobox if used, the form's RecordSource query's SQL statement,
etc.).

 

[LG]

Frm fields are PreProc, Batch_ID, Agency, Address1, Address2, City, State,
Zip, Reason1, reason2, platform, date, Num_cl, correction, compelted,
The table I have currently built are TBL_GOV, TBL_REASON_CODES, TBL_AGENCY.
1 query set up now is they enter the reason code number in the form and the
query is built to input the text when they mail merge. Only the number is
held in the field in the table. I have tried to use the look up wizard for
the agency but that did not work well since sometimes they have addtional
agencies not listed.
The agency table fields are Agency, address1, address2, city, state, zip.

 

[Ken Snell [MVP]]

Assuming that none of the data in the Agency, address1, address2, city,
state, and zip fields are longer than 255 characters, here is how I'd do it.

Change the control for the Agency data on the form to a combobox. Use this
query as the combobox's RowSource property:

SELECT Agency, address1, address2, city, state, zip
FROM TBL_AGENCY
ORDER BY Agency;


Set the ColumnCount property of the combobox to 6. Set the ColumnWidths
property to this:
2";0";0";0";0";0"


Change the ControlSource property of the address1, address2, city, state,
and zip controls to these expressions (written in the same order as the list
of controls in this sentence) -- I am assuming that the name of the combobox
control is cboAgency, so replace my name with the real name if they are
different:
=[cboAgency].Column(1)
=[cboAgency].Column(2)
=[cboAgency].Column(3)
=[cboAgency].Column(4)
=[cboAgency].Column(5)


Now, when you select an Agency in the combobox, the address, etc. will
automatically fill in on the form.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/