Using Query instead of table.

[ansentry]

I have a form that is filled in by data from a table and is saved to a second table. This for creating an invoice from customer table, the reason that I do this is so that the invoice has the correct details at time of creation. If the customer address changes the previously created invoice holds the old address which was current at the time of creation.

This is the code I use to fill in the form:

Dim MyDB As Database
Dim CustomerList As Recordset

   	Set MyDB = CurrentDb()
   	Set CustomerList = MyDB.OpenRecordset("Customer")

    CustomerList.Index = "PrimaryKey"
    CustomerList.Seek "=", Screen.ActiveControl

    If CustomerList.NoMatch Then
        DoCmd.GoToControl "Street"
    Else
        Me![CUSTOMERID] = [CustomerList]![CUSTOMERID]
        
        Me![FName] = [CustomerList]![FName]
        Me![Title] = [CustomerList]![Title]
        Me![Street] = [CustomerList]![Street]
        Me![City] = [CustomerList]![City]
        Me![State] = [CustomerList]![State]
        Me![PostCode] = [CustomerList]![PostCode]
        Me![CompanyPhoneNumber] = [CustomerList]![CompanyPhoneNumber]
        Me![Terms] = [CustomerList]![Terms]
        DoCmd.GoToControl "CustomerID"
  End If

What I want to do is use a Query as the data source and lieu of a Table

I have tried using QueryDef and QueryDefs but I have not got anywhere.

Any help would be appreciated.



Regards,

 

[Mile-O]

Why don't you just normalise your tables so that the address is stored as it was at the time of creation?

i.e. a new table

AddressID (autonumber)
CustomerID (foreign key to customers table)
DateExpired (date field; defaulted at #31/12/2200#)
Address1
Address2
Address3
Address4

It'll need a tweak but when the address is changed you can set the DateExpired to the date it expired and create a new address which will apply henceforth.

 

[Rich]

Why not just create a complete new record for the customer and archive the original data?

 

[ansentry]

Re Using Query instead of Table.

Thank you both for your replies.

I would like to try it using the query as a recordset, can anyone point me in the right direction.

Regards,

 

[Rich]

Why don't you just normalise your tables so that the address is stored as it was at the time of creation?

i.e. a new table

AddressID (autonumber)
CustomerID (foreign key to customers table)
DateExpired (date field; defaulted at #31/12/2200#)
Address1
Address2
Address3
Address4

It'll need a tweak but when the address is changed you can set the DateExpired to the date it expired and create a new address which will apply henceforth.

Four address fields?

 

[ansentry]

Using Query instead of Table

Pat,

Thank you for your reply, could you please expand on your explanation please.

Something I forgot to mention in my original post was that the code I posted was in the after update of a combo box.



Regards,