Append Query

[rhig]

Im trying to set up a query to copy the value of a record in a field to another database table.
Both tables contain the field customer ID and are forms. I want to be able to create a button which runs a query to copy the customer ID value of the current record shown in the form to another form. Im having trouble creating a query which will append the data, could anyone give me the SQL for a solution?

Table 1 is called customers and contains the field "customer ID" which I want to copy from.
Table 2 is called Loans which I want to recieve the customer ID too into a field named "customer ID"

Many Thanks, rhig

 

[Fornatian]

Surely you need to create a relationship between the customers table and the loans table as follows:


CUSTOMERS TABLE
CustomerID (Primary Key)
FName
MName
LName
etc....

LOANS TABLE
LoadID (Primary Key
CustomerID (Long Integer) - linked to Customer ID in above table
LoanStart
LoanAmount
LoanInterest
LoanPeriod
etc...

As each customer can have many loans you need to create a one-to-many relationship between CustomerID(Customer) > CustomerID(Loans)

This is a bit simplistic but you get the idea. Once a relationship is in place, you don't need to run queries to maintain the integrity, it is mostly self maintaining.

As a direct answer to your question:

If both forms are open you can add a button with the following command:

Forms("Form2").ControlName = Forms("Form1").ControlName

You should seriously consider implementing some relationships though if you haven't already done so.

 

[rhig]

Thanks very much for the reply
I had the relationships, should have mentioned it, I got an error I think is through my poor interpritation of your syntax, i set the buttin too Forms("Customers").Customer ID = Forms("Loan").Customer ID

The Customers form is called Customers and Loans is called Loan, should I be putting Customer ID into control name?

Thanks, rhig

 

[Fornatian]

rhig,

rather than sending a duplicate private message it is better to reply/enquire publicly on the same thread then others can track progress and offer their ideas.

anyway try:

("Customers").Customer_ID = Forms("Loan").Customer_ID

Wherever you have spaces in field names you must replace them with underscores in code. It is better to eliminate them at design stage all together by concatanating words and starting each word with a capital letter as in StartEachWordWithACapitalLetter, it still retains its readability and makes coding easier. You can always use the caption property for a user friendly control identifier.