Populate table from SQL (Access 03)

Topic

New Reply

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

Replies

Shoot me if I have gone down the obvious

Have you tried

Me.Requery
Me.Refresh

in your code?

Reply | Quote

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

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

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