Pass Combo Box and Option Values using Insert Query

[NTF73]

Hi,
I'm really new to this in fact this is my fist attempt at VBA code!
I have a Form to keep track of Dance Students attendance.

ClassID (Bound txtBox)
DancerID (Bound Combo)
Price (Bound txtBox)
Option1 Absent
Option2 ToPay
Option3 Paid
Method (unbound Combo Value List of Cash & Cheque)
Update (Command Button)

When I Click the Update button it runs an INSERT query to update 1 of 2 tables (Absent) (Payment) using the following code:

Option Explicit
Private Sub cmdUpdateAttend_Click()

Dim DancerID As Integer
Dim ClassID As Integer
Dim ToPay As Integer
Dim Price As Currency
Dim Method As String

DancerID = Me.DancerID.Value
ClassID = Me.ClassID.Value
ToPay = Me.frAttendance.Value
Price = Me.Price.Value
Method = Me.cboPaymentType.Value

If Me.frAttendance.Value = 1 Then

DoCmd.RunSQL "INSERT INTO Absent (DancerID, ClassID)" & _
" Values (DancerID, ClassID);"
Else
If Me.frAttendance.Value = 2 Then

DoCmd.RunSQL "INSERT INTO ClassPayment (DancerID, ClassID, ToPay, Method, Price)" & _
" Values (DancerID, ClassID, ToPay, Method, Price);"
End If
End If
End Sub

Option1 works fine and updates the Absent table as desired but when I choose Option2 (ToPay) and click Update, I have to enter a parameter value for ToPay and Method. How do I pass the ToPay and Method Values from the Option Group and Combo Box?

Can anybody point me in the right direction?
Thanks

 

[DCrake]

Simple Software Solutions

First suggestion don't declare variable the same name as fields in you table, Access gets confused. This is the root of your problem.

 

[NTF73]

OK - I changed all the names of the variables prefixed with i=integer, s=string etc. Now when I click update, I am prompted to enter values for all fileds. Why is this?

 

[NTF73]

Think I was going about it all wrong!

Chaned my Code to:

Private Sub cmdUpdateAttend_Click()
If Me.frAttendance.Value = 1 Then
DoCmd.RunSQL "INSERT INTO Absent (DancerID, ClassID)" & _
" Values (Forms!Attend!DancerID, Forms!Attend!ClassID);"
End If

If Me.frAttendance.Value = 2 Then
DoCmd.RunSQL "INSERT INTO ClassPayment (DancerID, ClassID, ToPay, Method, Price)" & _
" Values (Forms!Attend!DancerID," & _
" Forms!Attend!ClassID," & _
" Forms!Attend!frAttendance," & _
" Forms!Attend!cboPaymentType," & _
" Forms!Attend!Price);"

End If
End Sub

It works fine now