Executing a string as a command? (1 Viewer)

[dcf1999]

I have no idea if this is possible, but I think i can do a workaround if it's not.

Bear with me as i'm going to try to explain this as best as i can.

My program is growing and, instead of creating a bunch of buttons for each report, I decided to create a table containing the report name (what the user sees), category, access report name (what the actual name in access is), description, and filter criteria (monthly filter, yearly filter, range filter, specific filter).

The user selects a category from a dropdown. After the category is selected, a listbox populates with all the reports under that category. The user then selects the report, types in the filter values, and hits generate. I got most of the reports working except for ones i need to use a dlookup function to find an ID.

Here is the code for the Generate button:

Private Sub CmdGenerateReport_Click()
    On Error GoTo errhandler
    Dim strReportName As String     'AccessReportName
    Dim strPerm As String           'Permissions
    Dim strPreFilter As String      'MonthlyFilter, YearlyFilter, RangeFilter, SpecificIdFilter
    Dim strOpenArgs As String       'Options to bypass filters (montly, yearly, etc...)
    Dim strMonthFilter As String    'String that holds the where conditon
    Dim strYearFilter As String     'String that holds the where conditon
    Dim strFilter As String         'String that holds the where conditon
            
    'Fill string variables
    'If nothing is in the "accessReportName" then that report isn't created yet
    If IsNull(DLookup("[AccessReportName]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)) Then
        MsgBox "This report doesn't exist yet.", vbInformation
        Exit Sub
    Else
        strReportName = DLookup("[AccessReportName]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
    End If
    strPerm = DLookup("[Permissions]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
    'The following if statement bypasses the filter step for general reports like inventory reports where there is not filter needed
    If IsNull(DLookup("[OpenArg]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)) Then GoTo FilterStep
    strOpenArgs = DLookup("[OpenArg]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
    
FilterStep:
    MsgBox "Step 1 complete"    'Put in for testing purposes... take out when done
    'If it's a general report, bypass filter section and just generate the report
    Select Case strOpenArgs
        Case "G"
            GoTo OpenReport
    End Select
    
    'Create filter string
    Select Case Frame8.Value
        Case 1 'monthly
            If IsNull(DLookup("[MonthlyFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)) Then GoTo NextStep
            strMonthFilter = DLookup("[MonthlyFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
            strYearFilter = DLookup("[yearlyFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
            strFilter = strMonthFilter & txtmonth.Value & " AND " & strYearFilter & txtyear.Value
        Case 2 'yearly
            If IsNull(DLookup("[YearlyFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)) Then GoTo NextStep
            strMonthFilter = DLookup("[YearlyFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
            strFilter = strPreFilter & txtyear.Value
        Case 3 'range   'I havn't tested this part yet, but i'm sure i'll have to have 2 string variables and combine them
            If IsNull(DLookup("[RangeFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)) Then GoTo NextStep
            strPreFilter = DLookup("[RangeFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
        Case 4 'Specific ID
            If IsNull(DLookup("[SpecificIdFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)) Then GoTo NextStep
            strPreFilter = DLookup("[SpecificIdFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
            strFilter = strPreFilter & TxtID.Value & "'"")"
        Case Else
            MsgBox "Please select report filter", vbExclamation, "Report"
            Exit Sub
    End Select
       
NextStep:
    MsgBox strFilter
    
    If strFilter = "" Then
        MsgBox "This report isn't available with the filter you selected.", vbInformation
        Exit Sub
    End If

OpenReport:
    'Open Report
    DoCmd.OpenReport strReportName, acViewPreview, , strFilter, acWindowNormal, strOpenArgs
    
ExitProc:
    Exit Sub

errhandler:
    lngErr = Err.Number
    strDesc = Err.Description
    Call LogError(lngErr, strDesc, "Admin - CmdGenerateReport()")
    Resume ExitProc

End Sub

So for one of the reports that uses an specific ID (like getting an airpack inspection history report), the user enters the airpack number (E3-3) in the txtID field on the form. When they click generate it has to look up the ID from the airpack table using E3-3 as the where condition. The expression I have is the table's specific filter is

"Airpack = " & DLookup("[ID]", "[Packs]", "[Dept ID] = "'

So basically the sub gets the above "command line" from the table and uses that as actual vba code.... can this even work?

Here is a picture of my table with the "command line" to give you a better idea of what I mean.

 

[plog]

I'm mostly going by your threads title ("Executing a string as a command?"), since I didn't completely follow your wall of text.


The answer to it is yes. The Eval() function can take code instructions as a string and execute them:

Public Sub test()

x = "MsgBox(""hi"")"
Eval (x)

End Sub

Running the above code will display a message box with "Hi" in it.

 

[dcf1999]

I'm mostly going by your threads title ("Executing a string as a command?"), since I didn't completely follow your wall of text.


The answer to it is yes. The Eval() function can take code instructions as a string and execute them:

Public Sub test()

x = "MsgBox(""hi"")"
Eval (x)

End Sub

Running the above code will display a message box with "Hi" in it.

I guess I could have written the title better. I'm not executing a command per say. I'm trying to open a report with certain WhereConditions. Each report in the table has a different "WhereConditions". Some of the conditions need to look up information from a different table so I need the dlookup command. So basically, in the table's field I have what at I would normally code. Instead, the sub reads and stores into a string variable the table's field contents (Airpack = " & DLookup("[ID]", "[Packs]", "[Dept ID] = "). From there, it tries to put that into the docmd.openreport's WhereCondition. But I have a fealing that the DLookup part of the string isn't actually looking anything up since i'ts stored as a string.

I will look into the eval thing though... maybe there is more to it.

 

[dcf1999]

ok the eval() may actually work so what i did was put the full code to open the report with the correct WhereCondition in the table under a field "SpecificIdField"

The value of the field is:

DoCmd.OpenReport "AirpackHistory", acViewPreview, , "Airpack = " & Dlookup("[ID]", "[Packs]", "[Dept ID] = '" & TxtID.Value & "'"), acWindowNormal

In the code for the generate button, I have:

strFilter = DLookup("[SpecificIdFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
Eval (strFilter)

When I run it, it says it "Can't find the name DoCmd?

 

[MajP]

strFilter = "DLookup('[SpecificIdFilter]', '[ReportName]', '[ID] = " & ListAvailableReports.Value) & "')"
strFilter = replace(strFilter,"'",Chr(34))
Eval (strFilter)

 

[MajP]

should be
& ListAvailableReports.Value &

This worked for me, was not sure if it would

Public Sub testEval()
  Dim strD As String
  strD = " DLookup('LastName','Employees', 'EmployeeID = 1')"
  strD = Replace(strD, "'", Chr(34))
  Debug.Print strD
  Debug.Print Eval(strD)
End Sub

results:

DLookup("LastName","Employees", "EmployeeID = 1")
Davolio

However, I think I would get the dlookup out of the table and have the form resolve it. I would probably just have Airpack = in the table and if necessary another field to tell the form how to do the dlookup.

 

[dcf1999]

should be
& ListAvailableReports.Value &

This worked for me, was not sure if it would

Public Sub testEval()
  Dim strD As String
  strD = " DLookup('LastName','Employees', 'EmployeeID = 1')"
  strD = Replace(strD, "'", Chr(34))
  Debug.Print strD
  Debug.Print Eval(strD)
End Sub

results:

DLookup("LastName","Employees", "EmployeeID = 1")
Davolio

However, I think I would get the dlookup out of the table and have the form resolve it. I would probably just have Airpack = in the table and if necessary another field to tell the form how to do the dlookup.

Yea it didn't work. There has to be a way to do this!

So I did take the dlookup out of the table. What I did was add 4 new fields to the table (dlu, dluexp1, dluexp2, dluexp3). If dlu is true, that means it needs to do a dlookup function. the ..1..2..3 are the parts that go into the dlookup function. So the form code is now:

strFilter = dlookup(dluexp1, dluexp2, dluexp3)

I'm trying to get that going but i'm having issues with doing it that way as well. I'm sure it's all syntax on how I put the "values" into the table.

I'm probably way overthinking this or there is an easier way to do this. I'm sure I'm not the only person who has a table listing different reports... Just how do they call the reports? is each report hard coded as a select case?

 

[dcf1999]

Well I found the easier way to do it... but me using lookup fields finally bit me.... Once I modify my program and get rid of the lookupfields, it will work great.

 

[Gasman]

Please post back with your completed code, as it will likely help someone else.

I have coded similar, but have never used variables in the criteria, merely something along the lines of "[TranDate]>= Date()"

Well I found the easier way to do it... but me using lookup fields finally bit me.... Once I modify my program and get rid of the lookupfields, it will work great.