• Populate table from SQL (Access 03)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Populate table from SQL (Access 03)

    Author
    Topic
    WSFay Yocum
    AskWoody Lounger
    December 27, 2006 at 5:18 pm #438355

    I have a SQL statement that creates a table and limites the results based on the Manager’s log-on and selection from a listbox. This is done on a button click event. I want a subfrom, datasheet, to be populated with three of the five fields from that table. I tried the refresh mode but that didn’t work. What event should I be looking at instead. Thank you.
    Fay

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • WSJezza
      AskWoody Lounger
      December 27, 2006 at 5:23 pm #1044927

      Shoot me if I have gone down the obvious duck

      Have you tried

      Me.Requery
      Me.Refresh

      in your code?

      Reply | Quote
      • WSFay Yocum
        AskWoody Lounger
        December 27, 2006 at 7:15 pm #1044938

        Nope I forgot the requery. Now I am getting a message saying that the tblProductivity is currently in use. The subform it tied directly to the tblProductivity table. Here is my code. Thank you. Fay

        Private Sub cmdProdRoster_Click()
        On Error GoTo Err_cmdProdRoster_Click

        Dim stDocName As String
        Dim strSQL As String
        Dim strWhere As String

        If Forms!Main!lstDeptP.ItemsSelected.Count = 0 Then
        MsgBox “No departments are selected!!”, vbInformation
        [Forms]![Main].SetFocus
        Exit Sub
        End If

        For Each varItm In Forms!Main!lstDeptP.ItemsSelected
        strWhere = strWhere & “, ” & Chr(34) & Forms!Main!lstDeptP.ItemData(varItm) & Chr(34)
        Next varItm
        strWhere = “PerDiem2Unit In (” & Mid(strWhere, 3) & “)”

        DoCmd.SetWarnings False

        ‘Creates the base table that Productivity will be moved over to Excel
        strSQL = ” SELECT tblLearners.LastName, tblLearners.Nickname, tblLearners.Credential, ” & _
        “tblLearnerDepartments.PerDiem2Unit, tblLearners.Inactive INTO tblProductivity ” & _
        “FROM qryLimitDepartments INNER JOIN (tblLearners INNER JOIN tblLearnerDepartments ON ” & _
        “tblLearners.LearnerID = tblLearnerDepartments.LearnerID) ON ” & _
        “qryLimitDepartments.Department = tblLearnerDepartments.PerDiem2Unit ” & _
        “WHERE (((tblLearners.Inactive) = 0) AND ” & strWhere & “) ORDER BY tblLearners.LastName, tblLearners.Nickname ”

        Me.Requery
        Me.Refresh

        Exit_cmdProdRoster_Click:
        Exit Sub

        Err_cmdProdRoster_Click:
        MsgBox Err.Description
        Resume Exit_cmdProdRoster_Click

        End Sub

        Reply | Quote
        • WSJezza
          AskWoody Lounger
          December 27, 2006 at 7:31 pm #1044940

          I have a feeling that as your subform is currently using data from your tblProductivity it is confliscting with the SQL request. For some reason a little part of my brain is calling to make a temporary table of the data in the form of:

          CREATE TABLE #tblBears (
          BearID int, BearName char(30) )

          and populate it with the data but I may go be going down the wrong avenue for Access as this is what I would do in a stored procedure in SQL..sorry

          Reply | Quote
        • WSHansV
          AskWoody Lounger
          December 28, 2006 at 1:40 pm #1044989

          As it is now, you only assemble an SQL statement strSQL but never execute it, so I don’t understand where the warning that the table is in use comes from.
          Also, you are requerying the main form, not the subform (assuming that cmdProdRoster is on the main form).

          Instead of executing a make-table query, which will cause conflicts if a (sub)form is bound to the target table, I would execute a delete query to remove all existing records, followed by an append query to add the new records:

          ‘ Delete existing records
          strSQL = “DELETE * FROM Productivity”
          DoCmd.RunSQL strSQL
          ‘ Append new records
          strSQL = “INSERT INTO Productivity ( LastName, NickName, Credential, PerDiem2Unit, Inactive ) ” & _
          “SELECT tblLearners.LastName, tblLearners.Nickname, tblLearners.Credential, ” & _
          “tblLearnerDepartments.PerDiem2Unit, tblLearners.Inactive ” & _
          “FROM qryLimitDepartments INNER JOIN (tblLearners INNER JOIN tblLearnerDepartments ON ” & _
          “tblLearners.LearnerID = tblLearnerDepartments.LearnerID) ON ” & _
          “qryLimitDepartments.Department = tblLearnerDepartments.PerDiem2Unit ” & _
          “WHERE tblLearners.Inactive = 0 AND ” & strWhere & ” ORDER BY tblLearners.LastName, tblLearners.Nickname”
          DoCmd.RunSQL strSQL
          ‘ Requery subform
          Me.Subformname.Requery

          You must replace Subformname with the name of the subform as a control on the main form. This is not necessarily the same as the name of the subform in the database window. If the name contains spaces or punctuation, put square brackets [ ] around it.

          Reply | Quote
    Viewing 0 reply threads
    Reply To: Populate table from SQL (Access 03)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    April 2025
    S M T W T F S
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.