Auto-fill

[Malashaan]

I'm back with another question. I've been playing around with a test project to try and get to grips with the bones of how access works. I've got a table that stores ZIP/post codes along with City/Country etc. I've got this linked to another table via the ZIP/Postal codes - essentially I have an address table and a "customers" table. I created a form with the wizard and added text boxes to add "customer name" and "ZIP/Post code" (written to the main table) and then text boxes for city and country. These automatically autofill, which makes sense and is what I would want. What I can't for the life of me figures out is where the code/setting telling Access I want these to be autofilled actually is. I want to be able to create such text boxes in design view without using the wizard and I can't figure it out.

Any guidance would be appreciated.

EDIT: I notice that the "Record Source" for my form is a long expression listing all the text boxes and it's using "INNER JOIN" to access the second table with the address information. Is this a good way to go about things or have I just stumbled on a bad way of patching things together so it works?

I tried making a table from a query as seems to be generally recommended. I had just typed out that this worked for viewing records but not adding new one. I just went back and tried it again and it worked though.

Anyway, I think this is the way to go but thoughts would be appreciated.

 

[vbaInet]

I'm guessing you have a combo box which on selecting a customer ID or name, displays the associated customer's details?

If that's the case, look in the After Update event of the combo box. This can be found in the Property Sheet under the Events tab.

 

[ecraftsman]

The inner join is actually linking the information from both tables; All of the fields are part of a logical record. Short answer: the query is linking the tables. Yes, using a wizard is the best way to start; it usually gets you very close to what you want and then you can modify from there. Use the field list to add text boxes, drag the field name from the box to your form.

 

[Malashaan]

I'm guessing you have a combo box which on selecting a customer ID or name, displays the associated customer's details?

If that's the case, look in the After Update event of the combo box. This can be found in the Property Sheet under the Events tab.

I could do that, in fact I have had that at times. Like I said, I'm just experimenting to see what happens so what I actually have right now isn't in any way usual or "good" design. I have a box which I (the user) can type a ZIP code in (I'm using American addresses just because most tutorials do) and the City and State fields auto fill. If the ZIP code isn't one the database recognizes (I only entered a handful) then there's a run time error. I was expecting to see something in the After Update event like you say but there isn't anything. It seems to automatically autofill the fields from the secondary form with no specific instructions beyond the fact they are from a linked table. If this is the case (which does make some kind of sense) it makes life fairly straight forward but it just surprises me - it means I can't manually over ride the autofill even if I find some bizarre reason for wishing to do so.

 

[vbaInet]

Do any of the events on the Property Sheet of the textbox have something written in it like "[Event Procedure]" or "[Embedded Macro]" or something else?

Is the form a Continuous form?

It would be easy to spot if you attach the db then I could advise.

 

[Malashaan]

Here's a simple example of the behaviour I'm interested in. If one enters a ZIP code (one of the 4 I've included in the ZIP codes table anyway) the City and State autofill. I can't quite get my head round exactly what's causing the autofill. It's probably obvious and I've been looking at it too long. Things that "just work" make me uncomfortable.

 

[vbaInet]

Don't worry, it's not a strange behaviour. It's the way you the tables are joined so when you enter a postcode it will loop up the values and display the associated records from the Zip codes table. Your form shouldn't really have the zip codes table.

 

[Malashaan]

Thanks. So what I'm trying to figure out is what the "good practice" way to code a form which autofills details from a second table is. As I understand it the main table shouldn't have the city and state information because they are entirely dependent on the ZIP code. For the same reason it makes sense for these fields to autofill once the ZIP is entered. The way that database is set up is the way I stumbled upon of making it happen.

Apologies if this questioning seems rather intense/demanding. I've got fairly good at "making things work" in access; I'm trying to elevate myself to understanding the program rather than knowing it if that makes sense?

 

[vbaInet]

In that case you should look into using a subform.