Auto Complete

[Steven Brookes]

Been racking my brain for this one but cant think!!

I have a form with 'Account Ref' as one field (and a company name field)
which is a lookup from a master table which has account ref, customer name,
address.etc etc etc.
the lookup returns account ref and company name. Basically i want to only
return the account ref in the account ref field and then auto populate the
company name field.

im not hot on sql so if you could talk me though that would be grande!!

thanks in advance for any assistance.

 

[Roger Carlson]

It depends why you are doing this. If you want to auto populate bound
fields (fields where the data will actually be stored in the table) the
answer is -- *you shouldn't*. You've got the company information stored in
the lookup table and you should do a Join whenever you want the information.

If you're doing it for *display purposes only*, then there are a number of
possible solutions:

1) Base your form on a Join of the master table and the look up table. Make
sure the join *field* that you put on the form is from the lookup table.
Once you type in the account number, the company name and all the other
fields will automatically "populate" (really, they're just being displayed).
You MUST lock these textboxes because if your user modifies anything there,
it will be modified throughout your database. You can also use a Combo Box
with this approach to find the account number.

2) You could use a Combobox to pull up the account number and have invisible
columns in the Row Source (by setting the width of the columns to 0). Then
you can use the .Columns property of the combo to set unbound text boxes to
the values you want. This requires a little bit of VBA code in the
AfterUpdate event of the combobox.

3) You could also create each of your textboxes with a DLookup in the
Control Source property so that when you type a value in the account number,
the appropriate value will appear in the textboxes.

Personally, I like the first solution.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Steve Schapel]

Steven,

If you mean you have a company name *field* in the table that the form
is based on, and you are trying to write the data to that field, then
this is probably an incorrect approach. If, on the other hand, you mean
you want to *display* the company name associated with the account ref
that you have entered, then there are a number of ways this can be
approached. Please have a look at this article, and see if it gives you
the information you need:
http://accesstips.datamanagementsolutions.biz/lookup.htm

 

[Steven Brookes]

ok, i think iunderstand but still not sure how to implement. ill try to
explain better:

i have one table called 'sales_ledger' - this contains all customer details
I have a new form called 'Jobs'. which is a data input form (to raise a new
job)
what i want is for when a user types or 'looksups' from the 'account ref'
field an account ref i.e ABC201, then the company name field is auto
populated, (eventually together with address and anything else i choose from
the 'sales_ledger') once the rest of the form is completed this then DOES
need to be saved in a table called 'jobs' the basis (at present) of the form.

hope this makes it clear.

thanks again for your help

 

[Steve Schapel]

Steven,

Can you confirm that each customer has a distinct 'account ref'? So
when we see ABC201, we will always know for sure that we are talking
about Fred Smith Company, Blabla Street, Sometown? And this data is all
listed in the sales_ledger table? If so, then that's excellent.

Then in your Jobs table, all you need is the Account Ref. The other
customer information DOES NOT need to be saved to the jobs table. In
fact, to do so would flout basic database design principles. The
ability to refer to the customer in the Jobs table according to their
Account Ref is the core reason to be using a relational database in the
first place. You absolutely should not have fields in the Jobs table
for company name or address or any other company related data, and if
you have such fields in there now, then the answer is to delete them
altogether.

Hope that makes sense.

 

[Steven Brookes]

brilliant, that makes sense!

yes, each account ref is unique to company name. and yes all details are in
sales ledger. your right no need to store in jobs.

so how and where do it get the company name to auto populate after user
enters abc201 into account ref, or is is better to select acb201 from
dropdown box.

 

[Steve Schapel]

Steven,

Yes, I would recommend to use a combobox for the entry of the account
ref on the Jobs form. That way, you can ensure that it is a valid
entry, no typo errors.

To get the company name on the form, you use one of the methods outlined
in the article I pointed you to, or as explained in Roger's earlier
reply. In this example, I would agree with Roger, that using a query is
a good approach. What this means is that you make a query based on the
sales_ledger and jobs tables, joined on the account ref field. And then
use this query (instead of just the jobs table) as the Record Source of
your Jobs form. Then you have the company name (from the sales_ledger
table) available in the query, and hence can be included on the form.
And, as Roger says, in this case, set the Locked property of the company
name control on the form to Yes, as you don't want your users beign able
to delete or edit it.

See how you go with that.

 

[Steven Brookes]

Steve,
I STILL cant get my head round this. Can you go step by step?? sorry!!

Steve

 

[Steve Schapel]

Hi Steven,

Sure. Let's start with a query.

1. Do you know how to make a query? Make a new query, and add both the
sales_ledger and jobs tables. In the query design window, create a Join
between the two tables, by clicking the Account Ref field on one of the
tables, and drag and drop to the Account Ref field on the other table.
Both tables have an Account Ref field, right?

2. Then put all the Jobs fields (including Account Ref) into the query
design grid, and also the fields from the Sales_ledger table that you
desire to see on the form, e.g. Company name.

3. Close, Save, and Name the query.

Let us know when you have done this. If you have a problem, please let
us know which step, and what specifically you need help with.

Then we will look at the next step.

By the way, can you confirm that the Account Ref field in the
Sales_ledger table is the Primary Key, or if not, that it at least has a
unique index?

 

[Steven Brookes]

done! that bit i kinda new, apart from adding all of the fields, which is
prob where i was going wrong. Yes there is an account ref field in both
tables which are linked.
the account ref in the salesledger is the primary key but not in jobs,
however there is an index.

thanks so much for your help!

 

[Steve Schapel]

Steven,

Right. Now you can make a form, based on this query.

When you get to the control on the form for the Account Ref, make it a
combobox, whose Row Source is the Sales_ledger table.

When you select an Account Ref in this combobox, the associated Company
Name etc should automatically be shown.

Just remember the precaution already mentioned, to set the Locked
property of the Company Name control on the form to Yes (and also any
other fields derived from the ledger table).

Ok?

 

[Steven Brookes]

forgot to say, thanks for all your help on this.

Happy new year

 

[Steve Schapel]

Steven,

I guess this means you got it working properly. Which I am happy to
know. Best wishes for the rest of the project.