Results 1 to 2 of 2
  1. #1
    Gablet is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2015
    Posts
    1

    Append from a textbox to the only blank field in a saved record in a table

    I have designed a database that has two forms as inputs to a table. The first form is a checklist and when it is completed it saves all fields except the ManagerID field. I then use the blank ManagerID, clientID and Date to pull onto a form for the manager to complete. On completion I want the ManagerID to save into the current records so they do not show up in the manager checklist forms and I then have a complete record. I have been searching online and cant seem to see how the best avenue is. I have an append query, see below

    Code:
    INSERT INTO ChecklistResults ( ManagerID )
    SELECT ChecklistResults.ManagerID, ChecklistResults.ClientID, ChecklistResults.DateCompleted
    FROM ChecklistResults
    WHERE (((ChecklistResults.ClientID)=[Forms]![TeamLeader]![ComClientNotFin]) AND ((ChecklistResults.DateCompleted)=[Forms]![TeamLeader]![ComDateSelect]));
    but it doenst work and Ive also tried this in VBA. I tried the below but I am not sure if I am going about this the right way. Any help would be greatly appreciated

    Code:
    Private Sub CmdAppend_Click()
    Dim dbsNorthwind As dao.Database
    Dim rstAmend As dao.Recordset
    Dim qdfAmend As dao.QueryDef
    Dim n As Integer
    Dim strcriteria As String
    Set dbsNorthwind = CurrentDb
    strcriteria = "[ChecklistResults]![DateofChecklist] ='" & [Forms]![TeamLeader]![ComDateSelect] & "' "
    Set qdfAmend = dbsNorthwind.QueryDefs("Get_Questions_NTL")
    qdfAmend.Parameters(0) = [Forms]![TeamLeader]![ComClientNotFin]
    qdfAmend.Parameters(1) = [Forms]![TeamLeader]![ComDateSelect]
    
    Set rstAmend = qdfAmend.OpenRecordset(dbOpenDynaset)
    n = 0
       rstAmend.MoveFirst
       Do Until rstAmend.EOF
          n = n + 1
          rstAmend.Fields("ManagerID") = Form.Controls("SC" & n).Value
          rstAmend.MoveNext
       Loop
    End Sub


  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why does the INSERT action select 3 fields but only 1 field receives data? Why doesn't it save ManagerID - that is the field indicated in the action.

    Date type field needs # delimiter for parameters.

    Don't need to open a recordset and loop records to do edit. General syntax:

    CurrentDb.Execute "UPDATE tablename SET fieldname=something WHERE filter criteria here"
    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.

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

Similar Threads

  1. Replies: 0
    Last Post: 09-03-2014, 02:37 PM
  2. Replies: 5
    Last Post: 12-03-2013, 02:06 PM
  3. Replies: 3
    Last Post: 11-19-2012, 12:27 PM
  4. Replies: 2
    Last Post: 08-09-2012, 04:15 AM
  5. Replies: 4
    Last Post: 05-11-2011, 03:06 AM

Tags for this Thread

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