Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populate table from SQL (Access 03)

    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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Populate table from SQL (Access 03)

    Shoot me if I have gone down the obvious <img src=/S/duck.gif border=0 alt=duck width=23 height=23>

    Have you tried

    Me.Requery
    Me.Refresh


    in your code?
    Jerry

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate table from SQL (Access 03)

    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

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Populate table from SQL (Access 03)

    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
    Jerry

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populate table from SQL (Access 03)

    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.

Posting Permissions

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