Use text from record to paste into an Access form

问题:

Based on a user's job ID number, I create a recordset of an ID with its different unit types (think pipe sizes) and unit (think footage of pipe). Each unit type record already has the name of the form textbox where the total footage goes in a different column. What I want to do is go through each recordset and plugin the footage for each unit type for that job ID number (that the user puts in a form).

Dim rst_UnitEntryCounts As Recordset   
Set rst_UnitEntryCounts = CurrentDb.OpenRecordset("SELECT tbl_UnitTypes.UnitTypes_WeeklyTextBoxUserEntryForm, " _
                                                        & "             tbl_PMEntry.PMEntry_Week_Ending, " _
                                                        & "             tbl_UnitTypes.UnitTypes_CumalativeTextBoxUserEntryForm, " _
                                                        & "             Sum(tbl_UnitEntry.UnitEntry_Unit_Count) AS SumOfUnitEntry_Unit_Count " _
                                                        & "FROM tbl_UnitTypes " _
                                                        & "         INNER JOIN tbl_UnitEntry ON tbl_UnitTypes.UnitTypes_ID = tbl_UnitEntry.UnitEntry_UnitTypes_ID) " _
                                                        & "         INNER JOIN (tbl_PMHeader " _
                                                        & "         INNER JOIN tbl_PMEntry ON tbl_PMHeader.PMHeader_ID = tbl_PMEntry.PMEntry_PMHeader_ID) ON tbl_UnitEntry.UnitEntry_PMEntry_ID = tbl_PMEntry.PMEntry_PMHeader_ID " _
                                                        & "WHERE tbl_PMHeader.PMHeader_ID = " & num_PM _
                                                        & "GROUP BY tbl_UnitTypes.UnitTypes_WeeklyTextBoxUserEntryForm, " _
                                                        & "             tbl_PMEntry.PMEntry_Week_Ending, " _
                                                        & "             tbl_UnitTypes.UnitTypes_CumalativeTextBoxUserEntryForm ")
            rst_UnitEntryCounts.MoveFirst
            Do Until rst_UnitEntryCounts.EOF = True
                [rst_UnitEntryCounts.UnitTypes_WeeklyTextBoxUserEntryForm] = SumOfUnitEntry_Unit_Count
            rst_UnitEntryCounts.MoveNext
            Loop
    Exit Sub

image of what my query table looks like Also, Im getting an error 3131 Syntax error in FROM clause as well. Thanks in advance!

回答1:

Finally figured out how to "place" a value (sum of units) in a textbox that varies based on criteria (unit type) in a form, where the name of the textbox is inside a record itself within my recordset. In case someone else has a similar question, here is how I did it:

Dim cntl As String
 Dim frm As String
 Dim rst_UnitEntry As Recordset
 frm = "frm_UserEntry"

Set rst_UnitEntryCounts = CurrentDb.OpenRecordset("SELECT...)     
If rst_UnitEntryCounts.RecordCount <> 0 Then
        rst_UnitEntryCounts.MoveFirst
        Do Until rst_UnitEntryCounts.EOF = True
        If rst_UnitEntryCounts![UnitTypes_CumalativeTextBoxUserEntryForm] <> "" Then
            cntl = rst_UnitEntryCounts![UnitTypes_CumalativeTextBoxUserEntryForm]
            Forms(frm).Controls(cntl) = rst_UnitEntryCounts![SumOfUnitEntry_Unit_Count]
        End If
        rst_UnitEntryCounts.MoveNext
        Loop