Basically, when the user types in a business name and on lost focus the
complete address is filled in.
The "lost" focus event is the wrong event here. You want to use the text box
"after update" event. The reason for this is that the user might want to
exit, or close the form. So they go and click on a nice big close buttion
you put on a form. However, doing that means that the lost focus event will
fire for your prompt text box (and that is NOT what you want). So, the user
should type ssomting into the contorl (text box), and then hit enter, or
tab. Thus, the contorls after update event will then fire (and, that event
does NOT fire if nothing in the contorl is changed...where as the lost focus
will always fire).
And, in fact as I will show, NO code needs to be written at all to do this,
so in fact you don't need to use any event at all!!
This will be a new record in a jobs table,
and on this form the user will enter the proper job information. I am
pretty
new to VBA.
As I mentoned, it is VERY import to answer the quesiton of "fill" in those
other fields. Database concpets don't change one bit if you are using
ms-access with ms-sql, or using ms-access with oracle, or using ms-access
with my-sql. And, datbase concpets don't change when writing web based
appltions to connect to a database, or writint an application in ms-access
that connects to a database.
The fact of the matter is, that the ONLY thing you are going to store in the
jobs table is the "id" of the customer, or whatever that main cleint table
you are working with. So, when you say "fill" in the contorls, you actaully
mean "dosplay" those fields values from the customer table, and you do not
mean "copy" the data from the customer table to the jobs table...right?.
If this was a Web or stand-alone application it would not be all
that tough since that is more of my specialty.
Now that we got this basic relational database concept figured out that
applies to all environments, the simple answer is THAT NO CODE IS NEEDED to
accomplish the above task. You simply build a combo box with the wizard that
stores the customer ID, but searches by company text name. This combo box
will be bound to the "customer id" field in the jobs tables. You then build
a sub-form with all of the fields that you requite to show from customers,
and drop that into the form.
So, as a human, you don't have to write ONE line of code to do this. This is
NOT a coding problem with ms-access. However, the above approach will not
work at all if you got the wrong approach to your database design, and by
"fill" you actually meant "copy" data into those other fields. What we want
to do is "display" data from the related table, and you don't need code to
do this.
Another approach to display data from the related table is to base the form
on a query that joins in the other fields from customer table. This approach
would not requite a sub-form, but would again just display the customer info
in fields that you place on the form (after of course the customer "ID"
field was entered as a result of searching for the customer name in the
combo box). This query for all particular purposes must be based on the
jobs table, and the customer information would be a left join in the query
builder.
