Calling "Me" in a SQL expression.

[eTom]

In an effort to make my code portable when I copy a form to change minor details, is there any way I can add "Me" to the SQL expressions that populate my combo boxes in certain locations?

There are a lot of them that inherit and filter selections based on other selections, but they all refer to "Forms![FormName]![FieldName], and that means a lot of time is spent going and editing every SQL line to change the FormName when I create a copy of a form.

Thanks!

eTom

 

[boblarson]

Me is only good in VBA. It isn't good in SQL. You have to use the actual form reference in SQL.

 

[eTom]

Nuts, that is what I was afraid of.

Is there some way I can have a form ask a user for input when opening the form? Sort of like a query if you leave a ? in there.

 

[gemma-the-husky]

instead of using the form control name in your control, store the value in a global variable

then dereference the global variable in your source query - you have to do this with a function


so in a module

public myglobalvar as long

function readglobalvar as long
readglobalvar = nz(myglobalvar,0)
end function

in your form, set myglobalvar as required

and in your query just use
=readglobalvar() instead of the forms control reference


if you want to be really anal, then dim the variable as dim, rather than public, and set the value via another function/sub - which means you dont necessarily have to keep track of loads of global variables

 

[boblarson]

Sure, you can have a form open for input and then base the other form's recordset on a query that looks at that input form.

 

[datAdrenaline]

Option 1:

If your combo is on the form you wish to reference, then create and name a control that is bound to the field you wish to be the filtering value, then you can simply reference that control in your SQL statement.

Control Name: txtMyFilteringField
Control Source: FieldName

Combo's RowSource:
SELECT * FROM SomeTable WHERE SomeField = [txtMyFilterField]

If the value you wish to use is already visible, then I still suggest creating a hidden control for the SQL to get its value from, just to ensure the SQL is getting the value from the appropriate control (ie: two forms may be opened with the same control name).

Option 2:
If you REALLY want the fully qualified name and wish to avoid the creation of hidden controls, you can then use the Eval() function to your advantage by setting the control source of the combo box to something like this:

SELECT * FROM SomeTable WHERE SomeField = Eval("Forms('" & [Form].[Name] & "').Controls('SomeFieldOrControl')")


Option 3:
Modify all your RowSource properties dynamically using the forms OnOpen event ...

Private Sub Form_Open()
    Me.SomeCombo.RowSource = "SELECT * FROM SomeTable WHERE SomeField = [Forms]![" & Me.Name & "]![SomeFieldOrControl]"
End Sub

Hope all this helps!

 

[eTom]

Hmmmm... well, originally I was going to create a copy of the entry form for each type of search: customer, date, batch, etc...

Now that I know it's possible to open the form based on query that is based on a form for user search parameters, I can have just one form that will be used to display search results. This means I won't have to replace [FormName] in multiple SQL statements across a pile of tables! Yay!

Now it's just matter of muddling my way through a query that loads user input from a form.

Thanks for the great suggestions!

 

[datAdrenaline]

Be sure to read the edits to my previous post, I added a couple more options ...

 

[eTom]

Be sure to read the edits to my previous post, I added a couple more options ...

Many options that are... above my head I think.

Let's start simple. If I have a form "SearchForm" and a text box called "LotNumber"

Can I have a button that does a simple OpenForm for "SearchResultsForm". Then in the SQL statement for "SearchResultsForm" it includes a WHERE statement:

WHERE ((tblIncidents.LotNumber)=[Forms]![SearchForm]!LotNumber)

As long as the SearchForm is opened first and a LotNumber is entered, it should show only results that meet that criteria, yes?

 

[datAdrenaline]

>> As long as the SearchForm is opened first and a LotNumber is entered, it should show only results that meet that criteria, yes? <<

Yep! ...

 

[eTom]

>> As long as the SearchForm is opened first and a LotNumber is entered, it should show only results that meet that criteria, yes? <<

Yep! ...

Here's an interesting question. I've got it working for a single field now... You type in a date and click a button and the form opens with the records filtered by that date.

Next I'll add "Yes/No" check boxes so that you can search by LotNumber, Date, or both. That seems easy enough using IF and WHERE all nested and such...

The only issue with LotNumber is that the LotNumber field is actually part of a sub-form that is nested in this form. (The main form holds the contact info, then the subform is where products are entered, so as to allow multiple products on a single entry. Pretty standard stuff, I'm told.) I'm sure there's a way around this, and I'll muddle my way through that one when I get there, but...

What about if I want to search by name, but only know a portion of it? ie: All customers who's ContactName field contains "Bill"?

I was looking at a SQL tutorial at the CONTAINS operator for the WHERE statement, but from what I can gather this seems to be for ranking results rather than filtering records. Am I correct in that, or do I just not understand it?

 

[eTom]

I'm trying to implement a Check Box system so that I can nest a bunch of criteria to narrow down results based on more than one field. So far it's just one field, NameInput, and one check box (NameCheck).

It works all well and fine until I put the NameCheck clause in there. I'm not sure exactly where I'm going wrong, but I'm sure it's a silly syntax issue. Any thoughts?

SELECT tblIncidents.*
FROM tblIncidents
WHERE ((([Forms]![SearchParameters]![NameCheck])=("Yes")) AND ((tblIncidents.ContactName)=([Forms]![SearchParameters]![NameInput])));

Thanks!

eTom

 

[boblarson]

WHERE ([Forms]![SearchParameters]![NameCheck])= -1 )

 

[eTom]

Ah brilliant. I'd tried 1, but hadn't though to try -1!

If I want to add on a second line with a second record filter check, can I nest them in this manner:

WHERE (IF CheckBox1 (Condition 1)) AND (IF CheckBox2 (Condition2)) etc...

 

[LPurvis]

Just to pop in to mention (for hopefully even greater simplicity)...
The occasional addition of hidden controls as parameters is to disambiguate the controls from field names in the query.
For example a control on the form will often be named the same as the field to which it's bound in it's controlsource. That field may also feature by name in the query which is being filtered.
(That's what Brent is protecting you against with the distinctly named control).

You can also coerce the field name itself into an expression that will be resolved as required by pushing the parameter to the local Form object.
i.e. with
Control Name: FieldName
Control Source: FieldName

Combo's RowSource:
SELECT * FROM SomeTable WHERE SomeField = Form!FieldName

and even

SELECT * FROM SomeTable WHERE FieldName = Form!FieldName

This can, of course, even be a saved query used in any number of forms. It will be non-evaluatable until that query is used as a form's source. But on any form with a control named as expected, the parameter will be evaulated.

Cheers.

 

[LPurvis]

Can I just check to ask the purpose of the criteria

WHERE [Forms]![SearchParameters]![NameCheck]=-1 AND tblIncidents.ContactName=[Forms]![SearchParameters]![NameInput]

You're wanting the checkbox NameCheck to act as a switch for the criteria?
So that when it's not checked what happens?
It's not technically acting as making the ContactName criterion optional, I'm just wondering how requirement it is satisfying. It effectively switches off the entire query? You'll get no results unless it is active and the correct name is supplied?

I'm all for limiting query results. Searches which pull over all records when nothing's supplied are certainly not my preference.

Knowing your intention though will help provide the solution you seek to your subsequent question.

Cheers.

 

[eTom]

Ideally it's so I can type a name into the field, check off the box, and then it will open my form with records who's ContactName match what I typed in the box.

The check box was there so that when I add other criteria (ie: lot number, date, etc.) I can use the check box to decide which criteria I'm goin to search for.

Ideally I'd like to eventually write it so that I could put a name in, check it off, and maybe a date too. I'm still struggling with the logic of that one, though.

I can have two fields and check boxes, but can only use one at a time, currently.

 

[Rich]

Ideally it's so I can type a name into the field, check off the box, and then it will open my form with records who's ContactName match what I typed in the box.

The check box was there so that when I add other criteria (ie: lot number, date, etc.) I can use the check box to decide which criteria I'm goin to search for.

Ideally I'd like to eventually write it so that I could put a name in, check it off, and maybe a date too. I'm still struggling with the logic of that one, though.

I can have two fields and check boxes, but can only use one at a time, currently.

I do have a form that does something similar with a lot of code to make it work, it's too big to post here though. If you pm me your E.Mail I'll send it to you, it is in 97 format though

 

[LPurvis]

Is the goal to have a single textbox entry for criteria and a series of checkboxes to represent the fields within which you want to compare that entered text?
(If so then are you intending to "OR" condition the criteria as to require it appear in each field seems unlikely).

Or do you plan for a checkbox per textbox (in which case it's more normal just accepting any value entered into a textbox to represent the intention to search for that as criteria for that particular field).

The code building method is going to be the most effective and efficient.
You can add fields and perform comparative expressions to negate fields in a single SQL statement, but you can do so at the cost of query efficiency.
Consider the standard optional parameters such as
WHERE FieldName = Forms!SearchParameters!NameInput OR Forms!SearchParameters!NameInput Is Null
This negates the engine from performing index optimisation.

If we build a statement such as yours, for example
WHERE ([NameCheck] = True AND ContactName Like [NameInput])
OR ([OtherCheck] = True AND ContactOther Like [NameInput])
OR ([WhateverCheck] = True AND ContactWhatever Like [NameInput])
the literal constant values should similarly scupper index use - even if all fields are indexed (which is a general requirement for OR conditions).

Almost any example search form can be adapted to build a query request as you require. (IIRC there's a simple example search form in the examples page linked to in my sig below).
You just choose fields to add based on your checkbox rather than values entered in different textboxes.
Or wait and see Rich's specific example.

Cheers.

 

[eTom]

Alright, here's the scoop. The SQL route didn't work out as well as I had hoped.

It is sure finicky, so I think I'm going to go the code route. The big problem I had going with VBA was how/where I was supposed to interact with the forms. How does this sound, logically (though I'm sure it's not exactly the *best* way to handle it:

2 tables: tblSearchParamaters where the user inputs their parameters
tblSearchResults that displays them. I went with two forms because the records contain a lot of data that I want to keep formatted in the same manner as the form I use to enter data. I suppose the subform route would have worked nicely too.

Anyway, when clicking on the "Search Button" it opens tblSearchResults. On Open event (VBA). That VBA script pulls the data from the tblSearchParameters and through IF statements, compiling a complete SQL statement and puts it into tblSearchResuls.RecordSource.

It might be sloppy, but it seems like an easy way to check mulitple check boxes and choose which ones need to be put into the SQL statement.