Strings and Invalid Qualifier

[dem1an]

I think this is just a simple tweak. I'm trying to create a module in which I can pass keywords and reuse the same code to generate some pricing on the form. I would pass words like Full, Mid, Basic as the SolutionType.

Function MyFunctionName(SolutionType As String)
    Dim sumListPrice As Currency
    Dim FormName As String

    FormName = "Form_embed" & SolutionType
    'results in Form_embedFull when Full is passed to the function"
 
    sumListPrice = Nz(Form_embedFull.List_Price, 0) 
    'the above works
    sumListPrice = Nz(FormName.List_Price, 0)
    'the above is an invalid qualifier
End Function

How do I reference components of a form using a constructed string? Make sense? I tried fishing for sample code and just didn't see how to do this. I'm sure I'm overlooking something simple. Thanks in advance.

 

[jdraw]

Look at the Eval() function. I think it applies to your situation.

As I recall, something like
sumListPrice = Nz(Eval(FormName).List_Price, 0)

**untested***

 

[Uncle Gizmo]

Try:
sumListPrice = Nz(Forms(FormName).List_Price, 0)

Assuming your form is already open of course....

 

[dem1an]

Thanks for the leads.

The forms are actually called "embedFull", "ebedMid", etc. I tried both Form_embedFull and just embedfull as my string.

With the first solution, I got can't find the name 'embedFull' that you entered in the expression. With the second solution, I got cant find the form 'embedFull' that you entered in the expression. Again, I tried embedFull and Form_embedFull. I'll play around more when I get back to the house, but if something comes to mind, please let me know.

 

[boblarson]

Changes required:

Function MyFunctionName(SolutionType As String)
    Dim sumListPrice As Currency
    Dim FormName As String

    FormName = [B][COLOR=red]"embed"[/COLOR][/B] & SolutionType
    'results in Form_embedFull when Full is passed to the function"
 
    sumListPrice = Nz([B][COLOR=red]Forms![/COLOR][/B]embedFull.List_Price, 0) 
    'the above works
    sumListPrice = Nz([B][COLOR=red]Forms(FormName)[/COLOR][/B].List_Price, 0)
    End Function

Best to NOT use the Form_ syntax anyway. It can do strange things that you won't want. So, just use

Forms!YourFormnameHere

or
Forms("YourFormNameHereInQuotes")

 

[dem1an]

Sorry guys! None of these are working. Here is a little bit more of the code in case I'm not sharing something crucial.

Hope you had a Merry Christmas!

Using this code when the embedFull form is opened

Option Compare Database
Option Explicit
 
Private Sub Form_Open(Cancel As Integer)
    'For each embeded form, update this string with each solution type (e.g. Full, Mid, Min, Alt)
    Call UpdateMarginCostPrice("Full")
End Sub

Using this as a module

Option Compare Database
Option Explicit
 
Function UpdateMarginCostPrice(SolutionType As String)
    Dim sumListPrice As Currency
    Dim FormName As String
    FormName = "embed" & SolutionType
  
    'This still works
    sumListPrice = Nz(Form_embedFull.List_Price, 0)
 
    'None of these are working
    sumListPrice = Nz(Forms!FormName.List_Price, 0)
    sumListPrice = Nz(Forms("FormName").List_Price, 0)
    sumListPrice = Nz(Forms(FormName).List_Price, 0)
    sumListPrice = Nz(Forms!embedFull.List_Price, 0)
 
End Function

 

[vbaInet]

Then you're not passing the correct form name or the form is not open.

As Bob adviced, this should have worked:

sumListPrice = Nz(Forms(FormName).List_Price, 0)

From your first post, it seems your form names appear to be this:

sumListPrice = Nz(Form_embedFull.List_Price, 0)

If the form is not open then you simply cannot read values from any of its controls.

What is the error message you're getting for this one Nz(Forms(FormName).List_Price, 0)?

 

[boblarson]

You didn't say you were dealing with subforms. That syntax is completely different.

So, what is the name of your subform control (control on the main form which houses the subform - not the name of the subform itself)? You have to refer to it and then use:

Forms("FormNameHere").Controls("SubformControlNameHere").Form.Value

or if using a variable

Forms(strFormNameHere).Controls(strSubformControlNameHere).Form.Value

or if using the other syntax:

Forms!FormNameHere.SubformControlNameHere.Form.Value

where .Form. is left EXACTLY as shown.

Perhaps you should read my tutorial here and also pay attention to the screenshot there.

 

[dem1an]

Hallelujah!

sumListPrice = Nz(Forms("frmSolutionsMatrix").Controls(FormName).Form.Controls("List Price").Value, 0)

I've struggled with this subform concept a long time. Thanks Bob and gentlemen!

 

[Katie]

This is exactly the information I needed! I have also been struggling with the whole subform concept, and I think I finally understand what I have been doing wrong. Thank you very much for this thread, it was a great find for me.