Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-06-09
4
Medium Priority
?
337 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

721 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