Adding multiple Records

[Poloboy]

Hi Everyone,
Can someone show me the code on how add multiple record for the individual client. I'm writting a small program to keep track of client that renting out videos. I had 2 tables created, one is call "info" to store addresses and the other is to keep track of the checkout videos. I had created a relationship with AccountID. How do I code it so when client checkout multiple videos, and also copy records from one listview to the other listview. I'm working with ADO.

Thank you!

 

[Golom]

I'm not clear on where you have stored the videos that the customer is renting. Are they in one of the listboxes? If so

For n = 0 to List1.ListCount - 1
   SQL = "INSERT INTO Rentals (CustomerID, VideoID, RentalDate) " & _
         "VALUES ('" & CustomerID & "','" & _
                  List1.List(n) & "',#" & Now & "#)"
   ADOConnection.Execute SQL
Next

 

[Poloboy]

Thanks Golom, Got it!
One more question I hope you can help. I'm trying to load customer information into Listview. I got the loading info to show up in Listview but it shows multiple records of the same info into Listview. I uses the INNER JOIN. I hope i'm doing it right, here is my code(LMembers.Open "SELECT * FROM CustomerInfo INNER JOIN Payment ON CustomerInfo.Accountnumber = Payment.AccountNumber;", Cnn)
Everytime I apply an payment it shows up in my listview as another row of customer record of information. What I want is just to show just one customer information. It applied the payment record to the right table. My CustomerInfo table only has 3 records in it. It has a relationship AccountID. So when I apply a payment for that accountID, it then show up on the listview as another row of record. I hope I didn't get off track. Please help!

 

[Golom]

If you want just Customer information then just use the customer table. You are getting multiple records for a customer because you have multiple payments for them.

Try something like

LMembers.Open "SELECT * FROM CustomerInfo;", Cnn

 

[Poloboy]

but I want to display some info from the other table on a listview as well and couple of label boxes...

 

[Golom]

OK. What other information?

You can "Group By" the fields coming from the "CustomerInfo" table but you then need to make a decision about which aggregate functions to use to select values from the "Payments" table to go with those individual customer records.

If you want (for example) the total payments, then use SUM. If you want to see the date of the last payment then use MAX(SomeDate) etc.