How to use "Me" syntax in a Form? 1

[ggslack]

Using the "Me" syntax within VBA works fine. However, I have tried unsuccessfully a dozen variations of syntax to achieve the same thing in forms. For example: Within a form field that uses the dLookup function which uses values from the form. The only way I've gotten it to work is to explicitly using the name of the form.

i.e. [linenumber] = forms![formName]![fieldName]

Can't get Me.fieldName to work (or other variations...

I want to be able to generically refer to the form using "Me".

Any ideas?

 

[TheAceMan1]

How are ya ggslack . . . . .

[linenumber] = forms![formName]![fieldName]
[linenumber] = [purple]Me[/purple]![fieldName]

[blue]The [purple]Me[/purple] keyword behaves like an [blue]implicitly declared variable[/blue]. It is automatically available to every procedure in a class module. When a class can have more than one instance, Me provides a way to refer to the specific instance of the class where the code is executing. Using Me is particularly useful for passing information about the currently executing instance of a class to a procedure in another module. For example, suppose you have the following procedure in a module:

Sub ChangeFormColor(FormName As Form)
FormName.BackColor = RGB(Rnd * 256, Rnd * 256, Rnd * 256)
End Sub

You can call this procedure and pass the current instance of the Form class as an argument using the following statement:

ChangeFormColor Me[/blue]

See Ya! . . . . . .

 

[RoyVidar]

For referring to controls in controlsources within the current form, use only the control name. The Me keyword is only good when using VBA. So in your example above, assigning the value of the control "fieldname" to the control "linenumber" when both controls are on the same form, use the following controlsource in the "linenumber" control:

[tt]=[fieldname]
' where "fieldname" is a control on the current form[/tt]

When using the Domain Aggregate functions in controlsources referring to this control:

[tt]=DLookup("somefield","sometable","somefield = " & [fieldname])[/tt]

Referring "the whole way" thru the form, is only needed when referring to controls on other forms.

So - there are some differences between coding and controlsources (have to throw in queries too - where you have to use fully qualified referencing), and the Me keyword is reserved for referencing the current form in VBA.

Then there's referencing subform/subreport controls How to Refer to a Control on a Subform or Subreport

Roy-Vidar

 

[ggslack]

Upon review, I don't think I stated my problem as well as I could have. Let me try again.
A sample of the code in a form variable that is problematic, is as follows:

=DLookup("[ADescription]","2004SpendAuth","[FiscalYr]=forms![2004CombinedAuth]![FiscalYr] and [Office]=forms![2004CombinedAuth]![Office]and [BLI]=forms![2004CombinedAuth]![BLI]and [SubBLI]=forms![2004CombinedAuth]![LineNo]")

As things change between years, I have to modify the code, and it the form name changes (sometimes out of my control) the code has to change.

What I want to say is something like:

=DLookup("[ADescription]","2004SpendAuth","[FiscalYr]=forms!me![FiscalYr] and [Office]=forms!me![Office]and [BLI]=forms!me![BLI]and [SubBLI]=forms!me![LineNo]")

I have tried a dozen variations of the above code but nothing will work (I am also experiencing similiar problems on reports also).

 

[LittleSmudge]

Oh No !

If you have TABLE names that are changing from year to year then you have some SERIOUS and FUNDIMENTAL design problems that are not going to get fixed by fiddling about with Me.


If the code in the post above is a direct copy - then the problem is not with Me! ( although it shouldn't be there ) it is with missing spaces between operators and operands.

And the total lack of delimiters makes me suspicious.
Are ALL of those values really numbers ?






G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[LittleSmudge]

You also need to extract the variables out of your literal string.

Eg

= DLookup("ADescription","2004SpendAuth","FiscalYr = " & FiscalYr & " AND Office = '" & Office & "' AND BLI = '" & BLI & "' AND SubBLI = " & LineNo)


( I've guessed at the delimiters in there as per my earlier comment - but you get the gist I hope.


'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[TheAceMan1]

Ok ggslack . . . . .

When assigning a function to the [blue]Control Source[/blue] of a control on the form, you have to use the the full reference:
[/b]forms![2004CombinedAuth]![FiscalYr][/blue][/code]
This is your problem, as you say, the form name may change. But, if you set [blue]Control Source[/blue] to a function that returns DLookup, you can use Me to solve your problem.

In the [blue]Form Module[/blue] add the following code(you supply [purple]DesiredDataType[/purple]):

[blue]Public Function RtnVal() As [purple][b]DesiredDataType[/b][/purple]
   Dim Criteria As String
   
   Criteria = "([FiscalYr] = " & Me![FiscalYr] & " and " & _
              "[Office] = " & Me![Office] & " and " & _
              "[BLI] = " & Me![BLI] & " and " & _
              "[SubBLI]=" & Me![LineNo] & ")"
   
   RtnVal = DLookup("[ADescription]", "2004SpendAuth", Criteria)

End Function[/blue]

Then just set the [blue]Control Source[/blue] of a control to:
[blue]=RtnVal()[/blue]

You'll have modify as necessary for any literal values.

See Ya! . . . . . .

 

[RoyVidar]

Heh heh, you where fast LittleSmudge, and I agree with you, of course, but it also seems some of the processes are outside the control of ggslack. But I'd also advice on trying to amend the design.

If that's not appliccable, then one trick could be to use a query as recordsource of the form. Then one could use this kind of design (shudder), and only have to change the query each year, and LittleSmudges example should probably work (just replace the year prefixed tablename with the name of the query).

Roy-Vidar