Form control value as input to a query

[CanuckBuck]

Is it possible to use Me.ControlName.Value (or Me.Name) as input to a query that supplies values for a combo box?

I need to populate a combo box's list of values with values which are appropriate to the form, based on the form's name. I need to create a number (eleven) of these forms (They're all similar in function but contain more or fewer controls based on their purpose).

I'm hoping to be able to do this by using Me. notation so that I didn't have to go into the query for the combo box on each of the eleven forms and change the form name.

Is this possible?

Here's the code of my combo box's RowSource query.

SELECT DISTINCT
  R_Application_Group.Name, 
  R_Application_Group.DB_Key
FROM (R_Application_Group
INNER JOIN Application_Class ON R_Application_Group.DB_Key = Application_Class.Group_DB_Key) 
INNER JOIN AppTracker_Form ON Application_Class.AppTracker_Form_DB_Key = AppTracker_Form.DB_Key
WHERE
  R_Application_Group.Effective_Date <= Date()
  AND (R_Application_Group.Obsolete_Date Is Null Or R_Application_Group.Obsolete_Date >= Date()) 
  AND AppTracker_Form.Name = [Forms]![Main Form - PNoA Entries - Edit]![AppTracker_Form_Name];

I'd like to replace

[Forms]![Main Form - PNoA Entries - Edit]![AppTracker_Form_Name]

with something equivalent to

Me.[AppTracker_Form_Name]

 

[pbaldy]

In a word, no. "Me" is only valid in VBA code, it would cause a parameter prompt in a query like that.

 

[pbaldy]

I should add a couple of alternatives. You could copy the value to a hidden textbox on a form that remains open all the time, and have your query look there. You could use a single form instead of 11, and hide/show controls as needed by the situation.

 

[theDBguy]

I should add a couple of alternatives. You could copy the value to a hidden textbox on a form that remains open all the time, and have your query look there. You could use a single form instead of 11, and hide/show controls as needed by the situation.

Or how about a TempVar?

 

[pbaldy]

Or how about a TempVar?

That would certainly work. I never got in the habit of using them so don't think about them.

 

[theDBguy]

That would certainly work. I never got in the habit of using them so don't think about them.

It’s very handy and doesn’t require one to create another object that then adds to the file size as well.

 

[pbaldy]

I typically have a "main menu" form that stays open all the time anyway.

 

[CanuckBuck]

After posting I've kept hammering away at this and the approach I'm scraping at now is to set the RowSource in the form_Load event handler. I think this will work.

I'm not familiar with the TempVar technique mentioned by theDBguy. Can someone point me in the right direction?

 

[theDBguy]

I'm not familiar with the TempVar technique mentioned by theDBguy. Can someone point me in the right direction?

Here’s just one of the many articles discussing TempVars. Hope it helps...

 

[Deleted member 147267]

If I may, bookmark this page http://allenbrowne.com/AppIssueBadWord.html and refer to it often. Name is a reserved word, and if Me.Name didn't error out for some reason, it would return the name of your form or report - certainly not what you'd want. As for your names, I think I would soon get tired of typing all those underscores! Plus, you're showing an example with special characters (-) which is ill advised. I have a couple of links about how to name things if you want to take a look.

Also, I'd advise to not use ! in front of control names as you show.
Try this: in some code that would be harmless to play with (or create new) mis-spell a control name using Me. and compile. Because it doesn't exist and you've use Me. , Access will error on compile. Then change to Me!(still mis-spelled) and compile again. You will find there's no complaint; until your code tries to make use of the mis-spelled control name at run time.

 

[CanuckBuck]

theDBguy;

Thanks so much for the link to information about TempVar. It is the perfect solution for my use case.

Micron;

Thanks for the link to the list of reserved words. I confess it makes me feel like I should just use a random character generator for column and variable names since that list makes it seem like anything meaningful to real people is a reserved word.

With regard to "Name" - in the context in which I'm using it, I'm simply able to enclose it in square brackets. I come from the SQL Server world and the use of square brackets has been my practice for pretty much everything, whether it's required or not, for many years now. I find it makes visual identification very quick.

As for the underscores in my table and column names that was a database architecture choice made in our enterprise more than 20 years ago and over which I had no influence. I've made peace with it. It's actually so automatic to me now I don't even think about it. There's a certain quick visual clarity to it.

The choice of using hyphens in my form names is entirely mine and may indeed be ill advised but again, is mitigated through the use of square brackets.

 

[isladogs]

I need to populate a combo box's list of values with values which are appropriate to the form, based on the form's name. I need to create a number (eleven) of these forms (They're all similar in function but contain more or fewer controls based on their purpose).

This seems to be an unnecessary load of extra work.
If the forms are very similar, why not just use one form and the open args property to determine which controls should be visible/hidden

 

[theDBguy]

theDBguy;

Thanks so much for the link to information about TempVar. It is the perfect solution for my use case.

Hi. You’re welcome. We’re all happy to assist. Good luck with your project.

 

[Deleted member 147267]

it makes me feel like I should just use a random character generator for column and variable names

Well, to each their own, I guess. I find that a proper naming convention precludes the possibility of using a reserved word. It also makes it easier to read someone else's code when they do, which definitely helps to solve their problem.

 

[CanuckBuck]

isladogs;

...why not just use one form...

Thanks for the suggestion.

I'd thought about that but I felt that the logistics of moving controls about on the form so they're in the right place and visible seemed more complicated than it was worth.

What I've wound up doing is building a single "template" form with all of the controls common to all forms and then built the required UI functionality (cascading drop-downs, date order validation, mandatory field validation etc.). I've moved all of the code of the form into a common module so that it doesn't get duplicated with each copy of the form. That way if there's a defect I can change it in one place. I make a copy for a specific function and place the unique controls for that function on the form.

Now that the form code is centralized and the template is functionally complete I can stamp out a new form in about 10 minutes.

 

[isladogs]

Sounds like you are part of the way towards combining the forms so why not go 'all the way'. I have many multi purpose forms containing a number of common components but which can be adapted for different purposes including showing/hiding different groups of controls, changing source data etc.

As well as opening in different ways using open args, I often use the Tag property to show/hide, enable/disable controls. See this example app for more info http://www.mendipdatasystems.co.uk/set-controls/4594398114

I think you will significantly reduce your development time using a single multipurpose form

 

[gemma-the-husky]

one way is to edit a query. Have a master query with a term such as Me.[placeholderform]

When you want to use this query, first copy the master query to the query name you want.

I suggest the master query idea, because it's easy to know exactly what term to search and replace.

Then simply use replace(currentdb.querydefs("actionquery").sql,"[placeholderform]","[the form you want to use]")