Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving a Reocrd (Access 2000)

    I have a subform that has a drop down box and a text field. The text field counts how many records there are based on the primary key of the parent form. I have an after update event that saves the record and recalculates the text field count. So everytime I add a record, I get a new number. I then reference this code on the parent form and based on the number of records I have the code insert information. My problems comes when I go to delete a record on the sub form. It does not recalculate the count. I have tried docmd.runcommand accmdsaveRecord but nothing seems to work when deleting. Is there anything I can do that will allow me to delete the record and recalculate my count? I am deleting the record by using the cut method. The toolbar does not exist because it is a modal and popup form. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving a Reocrd (Access 2000)

    I don't think that your problem has anything to do with, or can be solved by, saving a record. Probably, you need a Recalc somewhere. But you don't provide enough information. The role of the subform is not clear to me - why do you have a text box on a subform to count the number of records in the parent form? And what is the control source of this text box?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Saving a Reocrd (Access 2000)

    You would be better advised to put a Delete button on your subform and have a DeleteRecord routine in the code behind it. How are you calculating the recordcount now? Without seeing the code, it's hard to suggest a change that will make it more flexible.
    Charlotte

  4. #4
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving a Reocrd (Access 2000)

    The txtCountRecords is an unbound control on the sub form that calculates how many errors have been assigned. (hidden). The form is a "many" form that stores errors on related to a claim. My parent form has a button that audits the claim (onCLick) and if the number is greater than 0, it assigns an error and suspends it for review. If the count is 0 it does not assign the error and closes the claim out. My code on the parent form where the audit button sits is
    Private Sub cmdMarkAsAudited_Click()
    On Error GoTo err_cmdMarkAsAudited_click



    Dim strAnswerAudit As String

    If Forms!frmAuditorAuditIndividual.frmAuditorErrorInd ividual!txtCountOfErrors = "0" Then
    strAnswerAudit = MsgBox("Are you sure you want to audit this claim", vbYesNoCancel)
    If strAnswerAudit = vbYes Then
    Forms!frmAuditorAuditIndividual!auditor = DLookup("[user_name]", "ssd_owner_doc_flo_users", "[windows_logon]=getuser()")
    Forms!frmAuditorAuditIndividual!audit_date = FormatDateTime(Now(), vbShortDate)
    Forms!frmAuditorAuditIndividual!COMPLETED_BY = DLookup("[user_name]", "ssd_owner_doc_flo_users", "[windows_logon]=getuser()")
    Forms!frmAuditorAuditIndividual!completed_date = FormatDateTime(Now(), vbShortDate)
    chkExamError = "0"

    Else:

    DoCmd.CancelEvent

    End If
    ElseIf Forms!frmAuditorAuditIndividual.frmAuditorErrorInd ividual!txtCountOfErrors > "0" Then
    strAnswerAudit = MsgBox("Are you sure you want to audit this claim", vbYesNoCancel)
    If strAnswerAudit = vbYes Then
    Forms!frmAuditorAuditIndividual!auditor = DLookup("[user_name]", "ssd_owner_doc_flo_users", "[windows_logon]=getuser()")
    Forms!frmAuditorAuditIndividual!audit_date = FormatDateTime(Now(), vbShortDate)
    chkExamError = "-1"

    Else:

    DoCmd.CancelEvent
    End If
    End If

    exit_cmdMarkAsAudited_click:
    Exit Sub

    err_cmdMarkAsAudited_click:
    Resume exit_cmdMarkAsAudited_click


    End Sub

    Is there a different process I can use to count the records instead of counting them in the text box?

    Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Saving a Reocrd (Access 2000)

    DoCmd.CancelEvent is actually a macro action and is not the equivalent of the Cancel argument for an event. It isn't doing anything in your code and you can remove it. Your are setting chkExamError to a string value, but I don't see anywhere that you use it. If it is a checkbox on your parent form, its value is numeric, not a string. This code doesn't have anything to do directly with the subform count Hans question was how you were populating the count textbox in the first place.
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving a Reocrd (Access 2000)

    The reason why the ckhExamError is a string is because it is the only way to populate the underlying Oracle table I have linked. The code for the unbound txtcount is

    =DCount("*","ssd_owner_doc_flo_audit_errors","[foreign_id]=Forms![frmAuditorAuditIndividual]![id]") Can I skip this txt field and just put that is my if..then statement?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving a Reocrd (Access 2000)

    To be honest, I understand very little of what you are doing here, but it seems to me that the code could be simplified to

    Private Sub cmdMarkAsAudited_Click()
    On Error GoTo Err_cmdMarkAsAudited_Click

    If MsgBox("Are you sure you want to audit this claim", vbYesNo + vbQuestion) = vbYes Then
    Me!auditor = DLookup("[user_name]", "ssd_owner_doc_flo_users", _
    "[windows_logon]=getuser()")
    Me!audit_date = Date
    If DCount("*", "ssd_owner_doc_flo_audit_errors", _
    "[foreign_id]=Forms![frmAuditorAuditIndividual]![id]") = 0 Then
    Me!COMPLETED_BY = DLookup("[user_name]", "ssd_owner_doc_flo_users", _
    "[windows_logon]=getuser()")
    Me!completed_date = Date
    chkExamError = "0" ' False would be clearer
    Else
    chkExamError = "-1" ' True would be clearer
    End If
    End If
    Exit Sub

    Err_cmdMarkAsAudited_Click:
    MsgBox Err.Description, vbExclamation
    End Sub

  8. #8
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving a Reocrd (Access 2000)

    Simplifying really works. I was trying to recalculate the count on the sub form when I should have been calculating it on the parent form within my onclick procedure. Thanks again for your help. It works perfect and I don't have to keep the txtcount field on my sub form anymore.

    Thanks

Posting Permissions

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