Can I load code on demand for a Report ON_Open? Access 97

Access 97:
I have a report that has some dynamic fields.

I want to load different On_OPEN code based on some other logic.

Is there a way to do this? Maybe store the code in a table and call it for execution?

Maybe another way to do it? I am trying to avoid Select...Case code. Ideally, I hold the code in a table that I can edit and then when the report opens, it looks up the code in a table and executes it.

Make sense? High points for a speedy solution!
SOTAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
<I have a report that has some dynamic fields.>
Care to elaborate on this?  Is this a Crosstab Report?

What exactly are you trying to do...?

Not sure why you need to avoid Select-Case?  (is If-Then-Else, ...also to be avoided...?)

You can always set a Variable (or use the OpenArg porperty), ...then do something based on this value, for example:

Private Sub Report_Open(Cancel As Integer)
    If YourVariable=SomeValue Then
        Do something
    End if
End Sub



But again, you need to be more specific
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Is there a way to do this? Maybe store the code in a table and call it for execution?>>

 Bad Idea.  Will put your app in a decompiled state, which means everything will run slower.

 As Jeff stated, I'm sure there's a better way to do what you want to do and I'll leave you with him.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
< Maybe store the code in a table and call it for execution?>>>
Oh!
Thanks Jim, I missed that...
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SOTAAuthor Commented:
Sorry, I will be away for severals hours. Will respond later. Thanks everyone!
0
Jeffrey CoachmanMIS LiasonCommented:
SOTA,

Yes, I am confused by your requirement as well...
I mean, it sound doable, ...but also can be a pain to maintain and execute...

Awaiting clarification....
0
SOTAAuthor Commented:
OK, I am back.
Here's the situation:

We have 5 different shipping options on our Invoice module. For each shipping method, I need to have different code on the report. I could certainly use Select...Case and then code it in "hard-code". However, it would be very cool to have the code held in a table of some sort, whereby the user can configure the code himself.

Othwerwise, each time we add or change a shipping method I have to re-write the code behind the scenes.

Maybe there is no elegant way of doing this.
0
Jeffrey CoachmanMIS LiasonCommented:
I am not sure what that means regarding your Report "On Open" requirement...?

In any event, I think you are using the term "Hard-Code" incorrectly.

Let's say you have a shipping methods table:

tblShippingMethod
smID
smName

For example:
smID,smName
1,USPS
2,FedEx
3,UPS

Now you use a combobox to select the shipping method for an order.
The combobox stores the smID in the order table, ...but presents the smName to the user in the order form.

Now with just the smId in the order table, you also need the smName for reports and so forth.
So you can pull in the smName in a query with the Orders table.

SELECT OrderID, CustomerID, OrderDate, smID,smName
FROM tblShippingMethod
INNER JOIN Orders
ON tblShippingMethod.smID = tblOrders.smID;

Or you can "Lookup" the smName:
SELECT OrderID, CustomerID, OrderDate, smID, Dlookup("smName","tblShippingMethod","smID=" & smID) AS ShippingMethod
FROM tblOrders

So here there is no need to hard-code anything.
This is the standard way of doing this in Access


JeffCoachman
0
SOTAAuthor Commented:
Thanks Jeff,
I do understand that part. The issue was having labels change their contants based on what shipping method was chosen. Here is the code in question. It puts in barcodes for each of the label fields. I would like to be able to do this dynamically by possibly storing these Caption = "xxx" strings in a table and then using code to pull the correct string.

Sorry if I did not explain this more clearly!!

'BarCodes
If Forms![InvoiceOrderEntry]![SameShipTo] = False Then 'use Shipping Addresses
    If Not IsNull(Frm![ShipLastName]) Then
        Me.BarCode_Name.Caption = PrecisionID_C128AUTO(Frm![ShipFirstName] & " " & Frm![ShipLastName] & Chr(9) & Chr(9) & Chr(9))
    Else:
        Me.BarCode_Name.Visible = False
    End If
    If IsNull(Frm![ShipAddress2]) Then
        Me.BarCode_Address1.Caption = PrecisionID_C128AUTO(Frm![ShipAddress1] & Chr(9) & Chr(9))
        Me.BarCode_Address2.Visible = False
    Else:
        Me.BarCode_Address1.Caption = PrecisionID_C128AUTO(Frm![ShipAddress1] & Chr(9))
        Me.BarCode_Address2.Caption = PrecisionID_C128AUTO(Frm![ShipAddress2] & Chr(9))
    End If
    If Not IsNull(Frm![ShipCity]) Then
        Me.BarCode_City.Caption = PrecisionID_C128AUTO(Frm![ShipCity] & Chr(9) & Chr(9))
    Else:
        Me.BarCode_City.Visible = False
    End If
    If Not IsNull(Frm![ShipPostalZip]) Then
        Me.BarCode_Postal_Zip.Caption = PrecisionID_C128AUTO(Chr(9) & Frm![ShipPostalZip] & Chr(9))
    Else:
        Me.BarCode_Postal_Zip.Visible = False
    End If
    If Not IsNull(Frm![ShipMainPhone]) Then
        Me.BarCode_Phone.Caption = PrecisionID_C128AUTO(Frm![ShipMainPhone] & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9))
    Else:
       Me.BarCode_Phone.Visible = False
    End If
Else:
    If Not IsNull(Frm![LastName]) Then
        Me.BarCode_Name.Caption = PrecisionID_C128AUTO(Frm![FirstName] & " " & Frm![LastName] & Chr(9) & Chr(9) & Chr(9))
    Else:
        Me.BarCode_Name.Visible = False
    End If
    If IsNull(Frm![Address2]) Then
        Me.BarCode_Address1.Caption = PrecisionID_C128AUTO(Frm![Address1] & Chr(9) & Chr(9))
        Me.BarCode_Address2.Visible = False
    Else:
        Me.BarCode_Address1.Caption = PrecisionID_C128AUTO(Frm![Address1] & Chr(9))
        Me.BarCode_Address2.Caption = PrecisionID_C128AUTO(Frm![Address2] & Chr(9))
    End If
    If Not IsNull(Frm![City]) Then
        Me.BarCode_City.Caption = PrecisionID_C128AUTO(Frm![City] & Chr(9) & Chr(9))
    Else:
        Me.BarCode_City.Visible = False
    End If
    If Not IsNull(Frm![PostalZip]) Then
        Me.BarCode_Postal_Zip.Caption = PrecisionID_C128AUTO(Chr(9) & Frm![PostalZip] & Chr(9))
    Else:
        Me.BarCode_Postal_Zip.Visible = False
    End If
    If Not IsNull(Frm![MainPhone]) Then
        Me.BarCode_Phone.Caption = PrecisionID_C128AUTO(Frm![MainPhone] & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9))
    Else:
       Me.BarCode_Phone.Visible = False
    End If
End If
0
SOTAAuthor Commented:
Thanks. I will re-look at my approach to this.
0
Jeffrey CoachmanMIS LiasonCommented:
It looks like all of that info could be pulled from the reports recordsource, ...or Looked up
For example if this was a report for Customer "Jim"
Then you would base the report on Jim's data, then all of Jim's data would be in the "Fields" already, ...so no need to use any code whatsoever to change the info in the report "Dynamically" with code...
0
SOTAAuthor Commented:
Yes, thanks so much!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.