Sub forms creating records

[cgibbons99]

I have a dtatbase where i hire out cars.say I create a new hire record in the hire form for 10 months. I would thrn like the program to create 10 records in the payment table that are all blank excluding hire id customer id payment id. Is this possible?

 

[JHB]

As you only ask if it is possible, so the answer is, yes it is possible.

 

[cgibbons99]

Would you know any way of doing it JHB, am really struggling eith this

 

[static]

Something like

for i = 1 to mths
    currentdb.execute "insert into payment (hireID,customerID,paymentID) values (" & hireID & "," & customerID & "," & paymentID & ")"
next

 

[JHB]

Would you know any way of doing it JHB, am really struggling eith this

More or less like static shows you.
Else post your database with some sample data in it, (zip it because you haven't post 10 post yet), + name of the form to use.

 

[sneuberg]

You could add a command button with code that executes an INSERT Statement ten times, something like:

Dim i As Long
For i = 1 To 10
    CurrentDb.Execute "INSERT INTO NameOfTable (HiredID, CustomerID, PaymentID) VALUES(" & Me.HireID & "," & Me.CustomerID & "," & Me.PayMentID & ")", dbFailOnError
Next i

There a lot of assumption in this example like that Me.HireID for example is a form control and is a number. Suggest you read about concatenating variables into SQL statements before trying to code this. Also if you want these records to show in a subform after inserting them you will need to requery the subform.

 

[cgibbons99]

Managed to upload it to here, Can I still get some help on this JHB I cant seem to work out how to put the code in. It needs to add x amount of records to payments table when a record is added to the hire table. ( if hire is for 4 months then it adds 4 records to the table)
Thanks in advance

 

[sneuberg]

I found that I could not add a new record with the Frm_Hire form. This was because the record source for this form included tables other than the tbl_Hire. I removed these table so that I could add new records and test the code that follows.

In order for the code to add the payment records, a record must exist in tbl_Hire table and that requires that the customer id and car id are entered into the form. The code checks for this and whether the form is on a new record. This could be done more elegantly by hiding the button when not on a new record and disabling it until the required fields are completed but I leave that for you to do if you want.

To use the following code just add a command button to the Frm_Hire form and copy and paste the code into the event subroutine.

If Me.NewRecord = False Then
    Exit Sub
End If

If Me.Customer_ID & vbNullString = vbNullString Then
    MsgBox "Please enter a customer id"
    Me.Customer_ID.SetFocus
    Exit Sub
End If

If Me.Car_ID & vbNullString = vbNullString Then
    MsgBox "Please enter a car id"
    Me.Car_ID.SetFocus
    Exit Sub
End If

DoCmd.RunCommand acCmdSaveRecord 'The record must exist in the tbl_Hire table before payment record can be added

Const SQL_INSERT As String = _
    "INSERT INTO tbl_Payments " & _
        "( [Hire_ID], [Customer_ID], [Car_ID]) " & _
    "VALUES " & _
        "( p0, p1, p2 )"
     
With CurrentDb.CreateQueryDef("", SQL_INSERT)
    .Parameters(0) = Me.Hire_ID
    .Parameters(1) = Me.Customer_ID
    .Parameters(2) = Me.Car_ID
    For i = 1 To 10
        .Execute dbFailOnError
    Next i
    .Close
End With
Me.Subform_Payments_List.Requery

 

[cgibbons99]

I cannot get this to work I copied the code into a button, then used build event and code builder to copy and paste. I then tested by creating a new record entered all vaid details and clicked button nothing happens

 

[sneuberg]

I've attached the database in which I tested the code. The button (Add Payments) is on the frm_hire form. Maybe you can figure out what's different. If not then upload your database and I'll try to figure out what's wrong.