Lookups

[KEV999]

I have a table ADDRESSPOINTS with fields ADDRESS, X and Y.
This contains thousands of existing records of addresses and their associated X and Y mapping coordinates.
I have a table INCIDENTRECORDS with fields INCNUMBER and NAME.
I want to be able to create records in the table INCIDENTRECORDS via a form INCFORM by selecting from the appropriate pre-listed addresses and then entering data in the fields INCNUMBER AND NAME.
I then want a query GISDATA to contain fields ADDRESS, X, Y, INCNUMBER and NAME.
This will then be used to link across and automatically display the locations of the incidents within a GIS mapping system.
I can cope with the GIS bit - but any help in how to set up the internal Access database lookups would be gratefully received!
Thanks.
Kev

[beacon]

How do you want to lookup the info? Do you want to use a form to open a query or a report?

Are you also saying that you want a combobox on the data entry form that will show the user all the available addresses they can choose from?

[KEV999]

Hi, thanks for the prompt response.
I've managed to get the addresses to show up in a combo box within the form.
By starting to type in the address it matches them and lets you choose the one you want. That bit is fine.
Where I'm stuck is getting the info from the table where the addresses are into the query along with the rest of the data entered.

[beacon]

What is the recordsource for your form?

If you make the recordsource of the form the same as the query that you want the results to go to...and then make the control source on each of the controls on your form (textbox, combo, etc.) equal to the query as well, you should be able to do the data entry without any problems.

Does that make sense? I hope this doesn't come across as condescending...wanted to start basic in case you aren't that familiar with Access.

[KEV999]

Many thanks. Please be as condescending as you wish - I'm very new to Access! Could you even explain how to do this a little more simply.......!

[beacon]

Haha...I always feel like it's important to say that so I don't make cyber enemies.

I then want a query GISDATA to contain fields ADDRESS, X, Y, INCNUMBER and NAME.

First off, have you created the the above query? If not, do that first.

Open your form in design view. Right click somewhere on the form and choose Properties. In the dropdown box at the top of the Properties dialog box, select Form. Amongst the properties listed for the form, look for RecordSource...what does it say? If it doesn't have GISDATA in it, select it from the RecordSource list. (RecordSource is where your form is supposed to link to. Essentially it's the location you want your data to go to.)

Then click on each of the controls on your form (excluding labels) and make sure there is a ControlSource. (The ControlSource is the specific place in the RecordSource where you want the data entered in the control to go to. For instance, if you have a text box for a date without a control source, it won't get put on any table. It's like writing without any paper or playing air guitar...your work doesn't do anything)

Once you've done that, the data should enter on your query, considering the relationships between your tables is well-defined.

Let me know if there's anything else I've failed to address.

[NeoPa]

I don't see anywhere in your data layout anything which will allow you to associate an address with an incident.

What you normally see in situations such as these is a field in what is essentially your lookup table (ADDRESSPOINTS) which can act as a Primary Index, and a similar field in your data table (INCIDENTRECORDS) which acts as a Foreign Index. This way, your query can link the two tables together such that any record in [INCIDENTRECORDS] matches up with only the one matching record in [ADDRESSPOINTS].