Update subform based on values in mainform

[Hutchy]

Hello

I am using Access 2007.

Mainform name: Visitors
Fields:
Visitor ID, Company ID, Last Name, First Name, Gender, Telephone No, Next of Kin,
Package (This is a combo box with 2 columns and 5 rows),
Status (This is a combo box with 1 column and 2 rows),
Reservation Date, Duration of Stay

Subform name: Visitors Payments Subform
Fields:
Payment ID, Cost, Discount, Final Cost

I added a button, Generate Payment - with controlname generatepayment, that when pressed on creating a new record, it will update the fields in the subform.

I want the Payment ID to be checked for the highest number and then incremented it by 1.

For the Cost: I want to check if the value in Status is "Resident" or "Non Resident".
If it is "Resident", then Cost will be the value at Duration of Stay * 15500, else it will be the value at Duration of Stay * 18002.

For the Discount: I want the value of Cost * the value at Package's column 1.

For the Final Cost: I want Cost - Discount.

Here's what I had, but kept getting an error:

Private Sub generatepayment_Click()

If Me.NewRecord = True Then
    Me!Visitors_Payments_Subform.Form.Payment_ID = Nz(DMax("Payment_ID", "Payments"), 1) + 1
    
    If Me.Status = Resident Then
        Me!Visitors_Payments_Subform.Form.Cost = 15500 * Me.Duration_of_Stay
    Else
        Me!Visitors_Payments_Subform.Form.Cost = 18002 * Me.Duration_of_Stay
    End If
    
    Me!Visitors_Payments_Subform.Form.Discount = Me!Visitors_Payments_Subform.Form.Cost * Me.Package.Column(1)
      
    Me!Visitors_Payments_Subform.Form.Final_Cost = Me!Visitors_Payments_Subform.Form.Cost - Me!Visitors_Payments_Subform.Form.Discount
End If

End Sub

 

[JHB]

What error do you get code/message and where do you get it, (code line)?

 

[Hutchy]

I get this error:

 

[JHB]

Subform name: Visitors Payments Subform
Fields:
Payment ID, Cost, Discount, Final Cost

Are you sure that both the field name in the table and the control name on the form is Payment_ID, (check it)?

 

[Hutchy]

Are you sure that both the field name in the table and the control name on the form is Payment_ID, (check it)?

It's Payment ID with a space and not an underscore.
Everything that has an underscore, I put it there, they are all with a space.

 

[Hutchy]

Never mind.

I deleted all the spaces from all the names that I've used.
It's working now.

What I have now is:
Mainform name: Visitors
Fields:
VisitorID, CompanyID, LastName, FirstName, Gender, TelephoneNo, NextofKin,
Package (This is a combo box with 2 columns and 5 rows),
Status (This is a combo box with 1 column and 2 rows),
ReservationDate, DurationofStay

Subform name: VisitorsPaymentsSubform
Fields:
PaymentID, Cost, Discount, FinalCost

Here's the working code:

Private Sub VisitorID_AfterUpdate()

    If Me.NewRecord = True Then
        Me!VisitorsPaymentsSubform.Form.PaymentID = Nz(DMax("PaymentID", "Payments"), 1) + 1
    End If
    
End Sub

Private Sub generatepayment_Click()

    If Me.Status = "Resident" Then
        Me!VisitorsPaymentsSubform.Form.Cost = 15500 * Me.DurationofStay
    Else
        Me!VisitorsPaymentsSubform.Form.Cost = 18002 * Me.DurationofStay
    End If
    
    Me!VisitorsPaymentsSubform.Form.Discount = Me!VisitorsPaymentsSubform.Form.Cost * Me.Package.Column(1)
      
    Me!VisitorsPaymentsSubform.Form.FinalCost = Me!VisitorsPaymentsSubform.Form.Cost - Me!VisitorsPaymentsSubform.Form.Discount

End Sub

 

[JHB]

It's Payment ID with a space and not an underscore.
Everything that has an underscore, I put it there, they are all with a space.

Therefore you've the problem, (don't use spaces in field name), you can't just replace a space with a underscore. If you have field name with spaces then surround the field name with [] like, [Payment ID].

 

[Hutchy]

Therefore you've the problem, (don't use spaces in field name), you can't just replace a space with a underscore. If you have field name with spaces then surround the field name with [] like, [Payment ID].

Wow!
I didn't know that. Thanks a lot!