Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

Access Using a variable item at beginning of a VBA code line

Hello All

Trying to rewrite some of my stuff with the DAO to get rid of some comboboxes etc on my form.  

I have a form with 7 unbound textboxes that I want populated with some data ONLY when the user has clicked on a command button and then confirmed that action.

Where I am having trouble is line 32 & 33 because I'm trying to make that an automatically changing code line that draws the txtbox name from the control table that I set up.

Is there a way I can change that line of code to vary?
Private Sub LastAllowData()
Dim lngEmpID As Long, lngLastFilingID As Long, i As Integer, k As Integer, strItem As String, lngYr As Long
Dim strTxtbox As String
Dim rst As DAO.Recordset, rst2 As DAO.Recordset

lngEmpID = 1 'GetMyEmpID
lngLastFilingID = Forms![tfrm_MultiTab].Form![cboStatus]
lngYr = Me.cboYr

Set rst = CurrentDb.OpenRecordset("qrydta_WksheetLine", dbOpenDynaset)
Set rst2 = CurrentDb.OpenRecordset("sysctl_WkSheetsLines", dbOpenDynaset)

rst.FindLast "EmpID=" & lngEmpID
rst2.FindLast "WkSheetLineID" & " AND YrID=" & lngYr

If rst.BOF Or rst.EOF Then
    Debug.Print "Not Found"
Else
    Debug.Print "Found"; rst!WksheetID; rst!EmpID
    k = rst.RecordCount

rst2.FindFirst "WkSheetLineID" & " AND YrID= " & lngYr
    
    If lngEmpID > 0 Then
        If k > 0 Then
            rst.FindFirst "EmpID=" & lngEmpID & " AND EmpStatusID=" & lngLastFilingID
            For i = 1 To k
            strItem = rst![WkSheetLineID]
            rst2.FindNext "WkSheetLineID= " & strItem
            strTxtbox = rst2![WkSheettxtboxName]
            
            sqlStr = "Forms![tfrm_MultiTab]![tsfrm_Pg1_Allow].Form! " & strTxtbox
            sqlStr = rst!WkSheetLineAmt
            
            If i <> k Then
            rst2.FindNext "WkSheetLineID" & " AND YrID" & lngYr
            End If
            
            Next
        End If
    End If
End If

rst.Close
rst2.Close

End Sub

Open in new window

0
wlwebb
Asked:
wlwebb
  • 2
  • 2
1 Solution
 
mbizupCommented:
<<
            strTxtbox = rst2![WkSheettxtboxName]
           
            sqlStr = "Forms![tfrm_MultiTab]![tsfrm_Pg1_Allow].Form! " & strTxtbox
            sqlStr = rst!WkSheetLineAmt
>>

try replacing these lines with:

            strTxtbox = rst2![WkSheettxtboxName]
            
            Forms![tfrm_MultiTab]![tsfrm_Pg1_Allow].Form.Controls(strTextBox) =  rst!WkSheetLineAmt

Open in new window

0
 
wlwebbAuthor Commented:
I get Run-time error '438'
Object doesn't support this property or method

Note:
the variable strTxtBox has the right string when I hover over it in debug

the line the error is on is the
       Forms![tfrm_MultiTab]![tsfrm_W4Pg1_Allow].Form.Controls(strTextBox) = rst!WkSheetLineAmt
**************************

NEVER MIND -  IT WORKED!!!!!!!!  not sure what I changed from the first time I ran it but it worked!!!!  Thanks mbiz
0
 
wlwebbAuthor Commented:
THANK YOU THANK YOU!
0
 
mbizupCommented:
Glad to help :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now