Solved

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

Posted on 2013-06-09
4
305 Views
Last Modified: 2013-06-09
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
Comment
Question by:wlwebb
  • 2
  • 2
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39233348
<<
            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
 

Author Comment

by:wlwebb
ID: 39233371
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
 

Author Closing Comment

by:wlwebb
ID: 39233405
THANK YOU THANK YOU!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39233435
Glad to help :)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now