Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004

    Question Unanswered: Access rookie NEEDS help!!

    I am fairly new to Access and have been given the responsibility of developing a database to keep track of security badges. For auditing purposes, the database must keep track of the dates that the badge data is verified, so I created a table specifically for reauthorization and termination dates (Updates).

    The problem I am having is on the data entry form when new badge data is entered into the database. The information is entered on the form (continuous form), and the user is to click on 'Submit' once all the info is entered. The submit button contains a macro that will print out a report so that upper management can sign for approval on badge access (which works fine), and is then supposed to (using an append query) update the Updates table with the badge number & date of authorization. The append query works fine if I run it alone and manually enter a badge number...but doesn't when ran within the macro. Below is the SQL for the append query:

    INSERT INTO Updates ( CardNum, LastReauth )
    SELECT BadgeData.CardNum, BadgeData.RequestDate
    FROM BadgeData LEFT JOIN Updates ON BadgeData.CardNum = Updates.CardNum
    WHERE (((BadgeData.CardNum)=[Forms]![NewBadgeRequest1]![Card Number]));

    Can someone PLEASE help a lost soul???

  2. #2
    Join Date
    Mar 2004
    Tulsa, OK
    Sounds like the problem is with the macro. How is the macro written & what happens when the macro runs?
    Thanks in advance or hope this helps.

  3. #3
    Join Date
    Aug 2004
    Here's the code:

    Function SubmitApprovalForm()
    On Error GoTo SubmitApprovalForm_Err

    ' Hourglass
    DoCmd.Hourglass True
    ' Open Approval form
    DoCmd.OpenReport "ApprovalForm", acViewPreview, "ApprovalForm Query", "[BadgeData]![Date Entered]=[Forms]![NewBadgeRequest1]![Date Entered]", acNormal
    ' Select New Request form
    DoCmd.SelectObject acForm, "NewBadgeRequest1", False
    ' UpdateAtSubmit
    DoCmd.OpenQuery "UpdateAtSubmit", acViewNormal, acEdit
    ' Close new badge form
    DoCmd.Close acForm, "NewBadgeRequest1"
    ' message
    MsgBox "New Badge requests have been sent to Data Securoty & Controls. Please print approval forms.", vbInformation, "New Badge Request Received"

    Exit Function

    MsgBox Error$
    Resume SubmitApprovalForm_Exit

    End Function

    Nothing error message, and no data is appended to the Updates table (but the report comes out fine).

Posting Permissions

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