Hi, I have a form (called PersonalTaxPriceSheet) with an unbound textbox called SubTotal.
This SubTotal field is calculated directly on the form. For example, on the form if the user chooses Option 1, the SubTotal textbox would update to $44.95. If the user chooses Option 2, the SubTotal textbox will update to $50.95, etc. etc.
I also have a Table called PersonalTaxOptions. There is a currency field within this table called ServiceFee, which is basically $0 as default.
What I'm trying to do is have the ServiceFee field in the PersonalTaxOptions table update to whatever the SubTotal textbox was in the PersonalTaxPriceSheet form for the related client. The form and tables are joined by the client's ID number (named SIN in the table).
So far, I created a command button (which i named cmdInsertServiceFee), that, when clicked, will insert the SubTotal into the ServiceFee field in the underlying table (PersonalTaxOptions table); however, it doesn't insert the SubTotal into the related client's record. What it does is create a NEW record, and inserts only the SubTotal field only in the new record (leaving all the other fields in the table blank, and only the ServiceFee field updated).
How do I insert it into the RELATED client's record?
Here is my VBA coding of what I have:
Private Sub cmdInsertServiceFee_Click()
On Error GoTo Err_cmdInsertServiceFee_Click
Dim stInsertSubTotal As String
stInsertSubTotal = "INSERT INTO PersonalTaxOptions (ServiceFee ) VALUES (" & SubTotal & ");"
DoCmd.SetWarnings False
DoCmd.RunSQL stInsertSubTotal
DoCmd.SetWarnings True
Dim stDocName As String
Dim stLinkCriteria As String
Exit_cmdInsertServiceFee_Click:
Exit Sub
Err_cmdInsertServiceFee_Click:
MsgBox Err.Description
Resume Exit_cmdInsertServiceFee_Click
End Sub
The above worked in that it did insert the unbound SubTotal textbox into the underlying table's ServiceFee field; however, it didn't relate it to the client.
I tried adding a "WHERE" command in the SQL statement above, like so:
stInsertSubTotal = "INSERT INTO PersonalTaxOptions (ServiceFee ) VALUES (" & SubTotal & ") WHERE PersonalTaxPriceSheet.SIN=PersonalTaxOptions.SIN;"
This just gave me an error message when clicked. The error message stated the SQL statement was missing a semicolon. I don't think this was the problem though b/c when I added another semicolon at the end of the SQL statement, the code turned all red, and gave me another error message.
Any help is appreciated! Thanks!
--Duckster
This SubTotal field is calculated directly on the form. For example, on the form if the user chooses Option 1, the SubTotal textbox would update to $44.95. If the user chooses Option 2, the SubTotal textbox will update to $50.95, etc. etc.
I also have a Table called PersonalTaxOptions. There is a currency field within this table called ServiceFee, which is basically $0 as default.
What I'm trying to do is have the ServiceFee field in the PersonalTaxOptions table update to whatever the SubTotal textbox was in the PersonalTaxPriceSheet form for the related client. The form and tables are joined by the client's ID number (named SIN in the table).
So far, I created a command button (which i named cmdInsertServiceFee), that, when clicked, will insert the SubTotal into the ServiceFee field in the underlying table (PersonalTaxOptions table); however, it doesn't insert the SubTotal into the related client's record. What it does is create a NEW record, and inserts only the SubTotal field only in the new record (leaving all the other fields in the table blank, and only the ServiceFee field updated).
How do I insert it into the RELATED client's record?
Here is my VBA coding of what I have:
Private Sub cmdInsertServiceFee_Click()
On Error GoTo Err_cmdInsertServiceFee_Click
Dim stInsertSubTotal As String
stInsertSubTotal = "INSERT INTO PersonalTaxOptions (ServiceFee ) VALUES (" & SubTotal & ");"
DoCmd.SetWarnings False
DoCmd.RunSQL stInsertSubTotal
DoCmd.SetWarnings True
Dim stDocName As String
Dim stLinkCriteria As String
Exit_cmdInsertServiceFee_Click:
Exit Sub
Err_cmdInsertServiceFee_Click:
MsgBox Err.Description
Resume Exit_cmdInsertServiceFee_Click
End Sub
The above worked in that it did insert the unbound SubTotal textbox into the underlying table's ServiceFee field; however, it didn't relate it to the client.
I tried adding a "WHERE" command in the SQL statement above, like so:
stInsertSubTotal = "INSERT INTO PersonalTaxOptions (ServiceFee ) VALUES (" & SubTotal & ") WHERE PersonalTaxPriceSheet.SIN=PersonalTaxOptions.SIN;"
This just gave me an error message when clicked. The error message stated the SQL statement was missing a semicolon. I don't think this was the problem though b/c when I added another semicolon at the end of the SQL statement, the code turned all red, and gave me another error message.
Any help is appreciated! Thanks!
--Duckster
