I am new to this forum and have a problem that has bugged me for a long time.
The database I have is generally a simple affair, but there is one thing that is causing me real problems. The primary key for the main table, which supplies the fields to the main data entry form is based upon a 3 digit reference, which is unique to each customer record. I also have a linked table, which links to another database elsewhere on our servers. I have created a query to bring up the data from the linked table that I require (3 columns of data).
The main database is a library of top-level customer-specific data. The problem I am trying to solve is to automatically update 2 other fields from a linked table/query. Each customer has a unique 3 digit code (e.g. ABN). Each customer will then have multiple sites, each with a unique number and site name (e.g. T123456 / Oxford Street). The query I have created from the linked table sorts the 3 digit code, unique number and site name into a list.
What I am trying to do is when a new customer is entered into the main data entry form, the record will automatically be updated (based upon the 3 digit code entered) with the list of appropriate unique numbers and site names, which correspond to the 3 digit code. Some customers will have several hundred sites, each with unique name and number.
I am totally lost as to how I best do this.
- How should the data best be listed - subform, memo,...?
- How would I even contemplate trying to sort the data so that the correct data is entered?
Any help and advice would be greatly appreciated.
The database I have is generally a simple affair, but there is one thing that is causing me real problems. The primary key for the main table, which supplies the fields to the main data entry form is based upon a 3 digit reference, which is unique to each customer record. I also have a linked table, which links to another database elsewhere on our servers. I have created a query to bring up the data from the linked table that I require (3 columns of data).
The main database is a library of top-level customer-specific data. The problem I am trying to solve is to automatically update 2 other fields from a linked table/query. Each customer has a unique 3 digit code (e.g. ABN). Each customer will then have multiple sites, each with a unique number and site name (e.g. T123456 / Oxford Street). The query I have created from the linked table sorts the 3 digit code, unique number and site name into a list.
What I am trying to do is when a new customer is entered into the main data entry form, the record will automatically be updated (based upon the 3 digit code entered) with the list of appropriate unique numbers and site names, which correspond to the 3 digit code. Some customers will have several hundred sites, each with unique name and number.
I am totally lost as to how I best do this.
- How should the data best be listed - subform, memo,...?
- How would I even contemplate trying to sort the data so that the correct data is entered?
Any help and advice would be greatly appreciated.
