Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2013-06-09
4
Medium Priority
?
346 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 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

564 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