Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009

    Question Unanswered: Exit or Clear a form without saving

    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:

    1. Client (cboClient)
    2. Employee (cboEe)
    3. Claimant (cboClaimant)

    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
    End If
    End Sub
    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
        Exit Sub
        MsgBox Err.Description
        Resume Exit_btnClear_Click
    End Sub

    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.

  2. #2
    Join Date
    May 2005
    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.
    Me.Geek = True

  3. #3
    Join Date
    Jan 2009
    That is a good idea for all the forms i have right now. Do you have some sample code to take the dummy info into the record?

  4. #4
    Join Date
    May 2005
    Usually I build a SQL string dynamically in the OnClick event, and then do a
    docmd.setwarnings false
    docmd.runsql strSQL
    docmd.setwarnings true
    You have to do the setwarnings thing or the user gets a "Are you sure you want to append this record to the table" type message after they click the save button. For help on building the string, I'd recommend Martin Green's site, in particular his article Access and SQL Part 4: Building Queries "On the Fly".
    Me.Geek = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts