Conditional SQL???

[Neowynds]

My next task in working with my database is to Set up a drop down Combo box which I have already done with its source type being a value list. What my manager wants is when I pick a job site from the drop down box, he wants the address to automatically populate the address field. I have no clue as to how to do this or where to begin. I thought maybe an IF string but dont know if that allowed in SQL or even where I would have to put such a code. Possibly I will have to make it a query? I dont know. Please help.

Neowynds

 

[dhookom]

First of all, I would never create a lookup field in a table design. All data entry should be done with forms where you add combo boxes. Also, I would rarely use a value list since there are very few instances where the list will never change. Lookup tables are much better since you can add, edit, and delete values from the drop down.

A lookup table can contain the address information. However, it isn't generally a good idea to store duplicate information. You can include columns in your Row Source of a combo box that contain the address information. When the combo box is updated, you can use code to set other field values to the various columns from the combo box.
Me.txtAddress = Me.cboCustomer.Column(2)


Duane
MS Access MVP

 

[Neowynds]

OK I probably wasnt clear enough. Users of this database fill out the database through form view. The reason I am using a value list is because the job sites that this database will be used for are finite and known. What I need is when I pull the drop down box to a particular job site the address of that site needs to populate in a seperate address field. I was thinking that I would have to make a seperate table containing the site and address locations and have the drop down box populate from there, as well as get the address info for that particular site. I have tried linking the drop down box to the external data in the second table, but as I am a Novice in Access,have been so far unsuccessful.

Neowynds

 

[dhookom]

Make your separate table with a primary key value as well as other fields you will need to use in your application. I recommend storing only the primary key value in your related table. If you take a look at the sample Northwind that can be installed with Access, you will get a better understanding.

Is there a reason why you would need to store duplicate address information? If the row source of your combo box contains address fields, you can display this information on your form using a text box with a control source of:
=cboSite!Column(1)
Column 1 is actually the 2nd column in the combo box row source.

Duane
MS Access MVP

 

[Neowynds]

Once again I think I have not been descriptive enough as to what I need to accomplish:

This database is being used for Security Incident Reports (similar to a police report) The different security posts will be using this database to file thier reports on. They will enter the data into the form view and when they pull thier Security post down from the Post: field my manager wants the address of that post to auto populate.For example

Security Post is Podunk post, in the address state zip country fields need to automatically populate with the following information

Address: 1234 Blah Lane
City: Podunk
State/Province: California
Postal Code: 91702
Country: USA

I have made a seperate table with my addresses in it,titled Posts. I have my Primary Key on that table being the Posts: field. I have successfully linked this to a combo box in Table1 labeled Posts: and in the form view when I click the pull down menu I get all the posts I have entered into the second table. However this is where I find myself stuck not knowing how to make the address automatically populate.

Hopefully I explained my problem a bit better in this post.

Thank you for your patience with a relative newbie.

Neowynds

 

[dhookom]

Good work so far. Your combo box should have all the fields included that you will want to "update" on your current form record. For instance:
SELECT Post, Address, City, State, PostalCode, Country
FROM tblPostAddresses
ORDER BY Post;

Then assuming in the current form, you have text boxes like
txtAddress, txtCity,...

In the after update event of your combo box, use code like:
Me.txtAddress = Me.cboPost.Column(1)
Me.txtCity = Me.cboPost.Column(2)
etc.

Duane
MS Access MVP