Code:
Public Function memberJumbula()
RefreshDatabaseWindow
DoCmd.SetWarnings False
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim strSql As String
Dim eid As Long
Dim firstname As String
Dim lastname As String
Dim name As String
Dim amountdue As Currency
Dim amountpaid As Currency
Dim registrationfee As Currency
Dim creditcardfee As Currency
Dim discount As Currency
Dim balance As Currency
Dim count As Integer
Set db = CurrentDb
db.Execute "DELETE * FROM Symposium_member"
db.Execute "CREATE TABLE JumbulaTemp " & _
"(eid NUMBER, firstname VARCHAR, lastname VARCHAR, name VARCHAR, amountdue CURRENCY, amountpaid CURRENCY, registrationfee CURRENCY, creditcardfee CURRENCY, discount CURRENCY, balance CURRENCY);"
RefreshDatabaseWindow
Set rs = db.OpenRecordset("Jumbula")
Set rs1 = db.OpenRecordset("JumbulaTemp")
rs.MoveFirst
count = 0
Do While Not rs.EOF
If rs.EOF Then Exit Do
count = count + 1
eid = rs("[Participant Member ID (Seven digits)]")
Debug.Print "EID = " & eid
firstname = rs("[Participant First name]")
lastname = rs("[Participant Last name]")
name = firstname & " " & lastname
Debug.Print "Order amount= " & rs("Order amount")
amountdue = DSum("[Order amount]", "Jumbula", _
"[Participant Member ID (Seven digits)] =""" & eid & """")
'fails at this line
amountpaid = DSum("[Paid amount]", "Jumbula", _
"[Participant Member ID (Seven digits)] =""" & eid & """")
registrationfee = Nz(DSum("Registration Fee", "Jumbula", _
"[Participant Member ID (Seven digits)] =""" & eid & """"))
creditcardfee = Nz(DSum("Credit Card Convenience Fee", "Jumbula", _
"[Participant Member ID (Seven digits)] =""" & eid & """"))
discount = DSum("[Total discount]", "Jumbula", _
"[Participant Member ID (Seven digits)] =""" & eid & """")
balance = DSum("Balance", "Jumbula", _
"[Participant Member ID (Seven digits)] =""" & eid & """")
Debug.Print "count =" & count & " eid = " & eid & ", " & amountdue & ", " & amountpaid & ", " & registrationfee & ", " & creditcardfee & ", " & discount & ", " & balance; ""
DoCmd.RunSQL "INSERT INTO JumbulaTemp" & _
"(eid, firstname, lastname, name, amountdue, amountpaid, registrationfee, creditcardfee, discount, balance)" & _
"VALUES ('" & eid & "','" & firstname & "','" & lastname & "','" & name & "','" & amountdue & "','" & amountpaid & "','" & registrationfee & "','" & creditcardfee & "','" & discount & "','" & balance & "')"
rs.MoveNext
Loop
'copy to Symposium_member
rs1.MoveFirst
Do While Not rs1.EOF
If rs1.EOF Then Exit Do
eid = rs1("[eid]")
firstname = rs1("[firstname]")
lastname = rs1("[lastname]")
name = rs1("[name]")
amountdue = rs1("[amountdue]")
amountpaid = rs1("[amountpaid]")
registrationfee = rs1("[registrationfee]")
creditcardfee = rs1("[creditcardfee]")
discount = rs1("[discount]")
balance = rs1("[balance]")
'unit = rs2("[Unit_Number]")
DoCmd.RunSQL "INSERT INTO Symposium_member" & _
"(eid, firstname, lastname, name, amountdue, amountpaid, registrationfee, creditcardfee, discount, balance)" & _
"VALUES ('" & eid & "','" & firstname & "','" & lastname & "','" & name & "','" & amountdue & "','" & amountpaid & "','" & registrationfee & "','" & creditcardfee & "','" & discount & "','" & balance & "')"
rs1.MoveNext
Loop
rs.Close
rs1.Close
strSql = "DROP TABLE [JumbulaTemp];"
DoCmd.RunSQL (strSql)
RefreshDatabaseWindow
DoCmd.SetWarnings True
End Function