Autolookk up fill in function ??

[Guest]

Hi All,

Please help. I had a database from my accounting system which contain
Customer Name and Address. I use access to build up a new table which had (
Customer Name, Customer Address, Remarks ). I convert this table into form by
using the form wizard. Now I want to do is to open the form , base on the
lookup function on the Name field which can look up the customer name from
the database from my accounting system. I want to be able to auto fill in the
address information to display on the form or table after I change the value
of the customer Name on each record.

Thanks in advanced.

Lam

 

[Joseph Meehan]

Hi All,

Please help. I had a database from my accounting system which contain
Customer Name and Address. I use access to build up a new table which had (
Customer Name, Customer Address, Remarks ). I convert this table into form
by using the form wizard. Now I want to do is to open the form , base on the
lookup function on the Name field which can look up the customer name from
the database from my accounting system. I want to be able to auto fill in
the address information to display on the form or table after I change the
value of the customer Name on each record.

I am not totally sure of all you want to do, but I do have two comments
based on what I think you are or may be doing.

" I had a database from my accounting system which contain Customer Name
and Address. I use access to build up a new table which had (Customer Name,
Customer Address, Remarks ). "

Normally you would not build a new table with information from an
existing table, rather you want to just refer to the original table for that
information. Making multiple copies of the same information tends to
increase the size of a database, slow it down and give you bad data (Like
when an address is changed on one place for the customer, but not the
other.)

"Now I want to do is to open the form , base on the lookup function on
the Name field which can look up the customer name from the database from my
accounting system.."

I would guess what you really want to do is to create a query and base
the form on the query. The Query can combine all the information you need
from one or more tables.

 

['69 Camaro]

Hi, Lam.

There are several ways to auto-complete a form. Depending upon the way your
form is constructed (i.e., with a subform or perhaps a combo/list box to
select each customer's name), you may be interested in a few tutorials. See
Tom Wickerath's easy, step-by-step tutorial on using a combo box to find a
record that's already in your database:

http://www.Access.QBuilt.com/html/find_a_record.html

Or see the tutorial on "How to 'auto-complete' a form, with and without
code" by using an auto-query to automatically fill in information for new
records:

http://www.Access.QBuilt.com/html/forms.html#AutoCompleteForm

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[Tom Wickerath]

Hello Lam,

I convert this table into form by using the form wizard.

Actually, you created a form, using the form wizard, which is based on your table. Your data is
still stored in the table. Form simply display existing records, and can be used to enter new
records.

Now I want to do is to open the form, base on the lookup function
on the Name field which can look up the customer name from
the database from my accounting system.

I have a tutorial that steps one through the process of creating a Find a Record combo box, using
the wizard. However, this is based on a bound form that simply finds the selected record in the
form's recordset.
http://www.access.qbuilt.com/html/find_a_record.html

My tutorial is not currently designed to open a new form to the selected value. If you want to
do that, you can use the WhereCondition of the OpenForm method, by passing in the primary key
value for the selected customer. The exact coding will depend on whether your Customer table
primary key is numeric or text. For example, you could attach code similar to this to the click
event of a command button (this example is for a numeric primary key):


Dim strCriteria As String

strCriteria = "pkcontactid = " & cboFindRecord!pkContactID
DoCmd.OpenForm "YourFromName", WhereCondition:=strCriteria

If your primary key is text, you'll need to include some single quotes. I think this is the
correct syntax for setting the strCriteria, although I haven't taken the time to test it (I tend
to use autonumber primary keys exclusively):

strCriteria = "pkcontactid = '" & cboFindRecord!pkContactID & "'"

I want to be able to auto fill in the address information to display on the
form or table after I change the value of the customer Name on each record.

Take a look at the After_Update event procedure for the combo box on the Orders form of the
Northwind sample database.


Tom
___________________________________________


Hi All,

Please help. I had a database from my accounting system which contain
Customer Name and Address. I use access to build up a new table which had (
Customer Name, Customer Address, Remarks ). I convert this table into form by
using the form wizard. Now I want to do is to open the form , base on the
lookup function on the Name field which can look up the customer name from
the database from my accounting system. I want to be able to auto fill in the
address information to display on the form or table after I change the value
of the customer Name on each record.

Thanks in advanced.

Lam