Beginner needs assistance with small access project!

[DoubleDufus]

I have created a table and form used to enter and store customers data. I had planed to create a query using this table, and use it as a lookup in another form which will store it's data in another table associated with this second form. I wanted to use the lookup to see the customer's last name, first name, customer number and once the selection is made, all the data from the first table's fields would populate the second tables fields where the field names are identical?

A'm a beginner so be gentle.

The file is only 247k if anyone might be interested in taking a gander.

Thanks in advance for any help!

John

 

[mickeyg]

John,

Why do you want to want to "copy" data from one table to another? If you are storing the same data in two places, you would have to update the data in both places. For example if the customer name or address changed.

In Access (and any relational database), you should design it so your customer info will be in one table, such as tblCustomers. Then in that table you create a field CustomerID that can be of type autonumber or manually set by the user.

Then in any other table, such as tblOrders, you just use a query to relate tblCustomer.CustomerID to tblOrders.CustomerID.

A little more info as to what you are trying to achieve in the way of data entry and reporting needs would be helpful. I wouldn't want to mislead you and don't want you headed down a long, winding path.

Mickey

 

[DoubleDufus]

Thanks for the reply.

The accurate customer data is stored in one table. Using a form the user can add, delete or correct customer data.

When the invoice is created, I want to store this customer data (how it existed at the time of the invoice generation) along with other data for a history record.

There would never be a need to correct this second table as I want it to reflect the information as it was at the time of sale.

When I create the invoice I would like to use a lookup to find the right customer in the customer table using maybe just a few fields for positive Identification. Then all the rest of the customer data would flood the duplicate fields in the invoice/form and therefore the history table.

Thanks again!
John

 

[mickeyg]

Ahhh I see. You just want to dump the current info to another table for archive/historical purposes.

You can create unbound textboxes or comboboxes at the top of your form. Then add a "Find" button that will find your customer based on the values input in the above fields.

Take a look at this thread
Get selection criteria from a form

http://www.tek-tips.com/faqs.cfm?spid=181&sfid=68&CFID=17331118&CFTOKEN=91343691

Hope this helps,
Mickey

 

[DoubleDufus]

Hello and thanks again Mickey!

I'm new so please forgive my poor explainations.

The Invoice Entry Form is associated with the History Table only. I can't see how to associate it with more than one table at a time.

Since the Customer Data Table is not associated with this Invoice form, combo or text boxes will only pull up one field, not an entire customer record, form the Customer Data Table.

I was trying to find a way, for example to make the combo box look at the Customer Data Table, Select the corect last name and have the balance of this record fill the Invoice Entry form and therefore the History Table.

Unfortunatly I can only get on field to fill which is the last name field.

John

 

[solo7]

John
It may well be worth your while to check out the old F1, with the subject matter being 'Relationships'.

I'm no expert, but if you want to E-mail your DB - I'll try and help. No promises mind !! X-)

dave.steadman@virgin.net