First time on this forum so bear with me. i will try to provide as much info as possible.
So here is the basics:
Building database for insurance company to administer HRA claims. What this envolves is:
- Creating Clients (have table: Clients)
- Enrolling Employee's and Dependents (have table for both because multiple dep per EE: Employees, Dependents)
- Entering Claims (have table, multiple claims and can be for EE or Dep: Claims)
I have a form to enter new and to view existing for each table. My problems are coming from the Claims. I have created the following Cascading comboboxes to narrow down the claim to the Claimant:
I have also inserted a check box to determine if the claimant is the EE or one of the Dependents. I wanted to be able to display the employee and the dependends in the Claimant combobox but i was unable to figure that one out so i had to let the User make the choice (maybe another question to be answered). Anyhow, the checkbox tells cboClaimant which query to pull its info from (qryEeEnroll or qryDepEnroll)
So here comes the problem:
As soon as i update cboEe, the Claim ID (which is an autonumber to keep track of claims) updates as well. This is fine and what i want it to do but i cannot exit the form without it saving after that point. I have put in a clear button but i cannot make the ClaimID clear back to (AutoNumber). So if i want to close or do something else, it automatically saves the record.
Ultimately i want to be able to start entering info, decide i don't want to and be able to exit the form without it saving.
Here is some of my code:
Private Sub cboClaimant_GotFocus()
Dim stDepSql As String
Dim stEeSql As String
stDepSql = "Select Dependents.EmployeeID,Dependents.LastName,Dependents.FirstName, [LastName] & ', ' & [FirstName] AS Expr1 FROM Dependents WHERE (((Dependents.EmployeeID)=Forms!frmClaims!cboEe)) ORDER BY [LastName] & ', ' & [FirstName];"
stEeSql = "Select Employees.EmployeeID,Employees.LastName,Employees.FirstName, [LastName] & ', ' & [FirstName] AS Expr1 FROM Employees WHERE (((Employees.EmployeeID)=Forms!frmClaims!cboEe)) ORDER BY [LastName] & ', ' & [FirstName];"
If Me.chkEe = Yes Then
Me.cboClaimant.RowSource = stDepSql
Me.cboClaimant.RowSource = stEeSql
btnClear (i have tried me.ClaimId = DefaultValue, me.ClaimId = 0, me.ClaimId = "", but all return errors)
Private Sub btnClear_Click()
On Error GoTo Err_btnClear_Click
Me.cboClient = ""
Me.cboEe = ""
Me.cboClaimant = ""
Me.ClaimantLast = ""
Me.ClaimantFirst = ""
Me.ServiceDate = ""
Me.ServiceDescription = " "
Me.ServiceAmount = ""
Me.InputDate = Date
Please Let me know if there is anymore info needed or any questions you may have. This is my first real Access project and i am learning as i go. I have some programming background (basics of java and c++) so i understand the thought process but am learning VBA and SQL now. Thanks for your time.
Maybe others may have a better suggestion, but to let the user enter in new records what I usually do is create a dummy form with unbound textboxes, comboboxes, etc. and have a "Save" button at the top of the form. When the user is ready (and when the form has validated that the required information is entered) they can click the button, at which time the records is created based on the dummy boxes. I never let them create the record until their data has been validated. This has the added bonus that if they decide to cancel their addnew, they just have to close the form (though I usually do prompt them to make sure that they don't want to save). Also, in a multi-user enviornment, using the form's in built addnew capability has caused troubles for me in the past.