Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Sep 2001
    Thanked 0 Times in 0 Posts

    Using SQL To Append Parameter Values From a Form (97 SR2)

    I'm trying to set up a log file to record the type of information that users are requesting from within the database. The log file is to be used in conjunction with system security, identifying what reports are most useful, time of day that requests are made, etc.

    I'm not sure if I'm on the right track (i.e. can SQL statements be used for this purpose?) as I am trying to append the selection criteria values that the user has selected from a form. I'm not having problems appending current user, date/time, or text information.

    Here's a sample of my code:

    Dim strSQL As String

    strSQL = "INSERT INTO tblUserRequestLog (Userid,ReqDate,ReqTime,ToDept1,FromMgr)" _
    & "VALUES (CurrentUser(),Date(),Now(),[Forms]![frmSelectViewData]![lstSelectViewDataDept],'temp')"

    CurrentDb.Execute strSQL

    My problem appears to be with "[Forms]![frmSelectViewData]![lstSelectViewDataDept]" updating field "ToDept1". Access responds with the message "Too few parameters. Expected 1." I have tried other methods (i.e. "Me![lstSelectViewDataDept]") with no success.

    Is this the best way to handle this situation or is there a better way to capture information from a form and record onto a table?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Using SQL To Append Parameter Values From a Form (97 SR2)

    Try this:
    <pre>strSQL = "INSERT INTO tblUserRequestLog (Userid,ReqDate,ReqTime, " _
    & "ToDept1,FromMgr) " _
    & "VALUES (CurrentUser(),Date(),Now(), " _
    & [Forms]![frmSelectViewData]![lstSelectViewDataDept] & ",'temp')"</pre>

    If the value in the form control is text rather than numeric, you'll need to add single quotes like this:
    <pre>strSQL = "INSERT INTO tblUserRequestLog (Userid,ReqDate,ReqTime, " _
    & "ToDept1,FromMgr) " _
    & "VALUES (CurrentUser(),Date(),Now(), '" _
    & [Forms]![frmSelectViewData]![lstSelectViewDataDept] & "' ,'temp')"</pre>

    There are other ways to capture information, notably through automation, but if this is working there isn't any overwhelming need for another method.

Posting Permissions

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