Results 1 to 6 of 6
  1. #1
    jewll is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2014
    Posts
    7

    Append Query Saving Multiple Records

    Hello Everyone,

    I apologise if that has already been asked/answered but...

    I have an append query (qryLogInTime) that runs when a form loads, it saves the information of the date and time that a username logs in to my database to a table (tblLogInTime).

    The issue is it will on the first occasion you open a form save 1 record of your login, the next time 2 records, then 4, then 8 and it will continue to double each time the form is opened.

    I don't know how to stop it.

    The public function that is used to get the username is: -

    Public Function GetUserLogIn()
    GetUserLogIn = Environ("UserName")
    End Function

    The VBA i've used for the on load function is: -



    Private Sub Form_Load()
    Dim User As String
    User = Environ("Username")
    Dim strDocName As String
    strDocName = "qryLogInTime"
    DoCmd.OpenQuery strDocName, acViewNormal, acEdit
    End Sub

    The append query that is ran: -

    Click image for larger version. 

Name:	qryLogIn.jpg 
Views:	12 
Size:	15.8 KB 
ID:	19029

    Hope someone can see where this rookie has gone wrong, or I may have to formulate a plan B.

    Thanks in advance for any assistance!

    Joe

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Post the SQL statement of the query. Switch to SQL View and copy/paste the statement into post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Your append query is using a SELECT clause, it should use a VALUES clause.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    jewll is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2014
    Posts
    7
    INSERT INTO tblLogInTime ( UserID, LoginTime )
    SELECT GetUserLogin() AS Expr1, Now() AS LogIN
    FROM tblLogInTime;

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Paul was right.

    INSERT INTO tblLogInTime ( UserID, LoginTime ) VALUES(GetUserLogin() AS Expr1, Now() AS LogIN)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    jewll is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2014
    Posts
    7
    Thank you both for your replies!

    Problem solved

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Form not saving multiple records in Table
    By element32d in forum Forms
    Replies: 3
    Last Post: 05-14-2013, 01:32 PM
  2. Replies: 6
    Last Post: 06-09-2012, 08:17 AM
  3. Replies: 5
    Last Post: 12-12-2011, 08:08 AM
  4. Saving Multiple Records at once
    By EvanRosenlieb in forum Access
    Replies: 5
    Last Post: 10-18-2011, 12:39 PM
  5. Saving records in multiple sub forms
    By niak32 in forum Forms
    Replies: 0
    Last Post: 10-13-2008, 04:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums