Duplicate Data

[Guest]

Can Someone please help

I creating a database to help a friend with his courier business.

I have three tables: tblcustomers, tblServiceType, tblCustomerRates.

Most Customers use all the services provided by the courier and each
customer has unique pricing for the services offered.

This is what i want to do:
When I enter a new record in tblCustomers using a form I want the
tblCustomerRates to be updated with the new Customer and all the services. So
when i click on the command button to enter the rates for the customer it
opens a form for tblCustomerRates with all the services for that customer to
enter the rates.

Is it possible to do something like this? Any suggestions is welcome.

Thanks in advance

 

[Guest]

Jason,

It sounds to me like there is a natural one-to-many relationship between
Customers and CustomerRates that would be most easily implemented using a
main form based on the former and a continuous subform based on the latter,
linked by the CustomerID.

Sprinks

 

[Guest]

You need to run an SQL statement which inserts rows into the tblCustomerRates
table, inserting the current CustomerID in every row and each separate
ServiceTypeID from the tblServiceType table in each (your column names may
differ of course). This can be done in the Customers form's AfterInsert
event procedure with code along these lines:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = " INSERT INTO tblCustomerRates ( ServiceTypeID, CustomerID )" & _
" SELECT ServiceTypeID, " & Me.CustomerID & _
" FROM tblServiceType"

cmd.CommandText = strSQL
cmd.Execute

Set cmd = Nothing

Ken Sheridan
Stafford, England

 

[Guest]

Thank you for the help Ken, you have just saved me alot of time.
The Help is much appreciated.