[Screen].[Activeform]![txtcontrol] in Criteria

[ions]

Dear Access Expert

I wanted to use [Screen].[Activeform]![txtUserID] as a criteria on the query grid but to my dismay this always produced an Empty recordset.. After about 45 minutes of searching (grrrr) I found:

Like "*" & [Screen].[Activeform]![txtUserID] which works when I use it as a criteria in the query grid.

My question is why doesn't [Screen].[Activeform]![txtUserID] work on its own? Why do I have to use the Like "*" Statement?

Also the Above Like statement doesn't exactly do what I want. If my txtUserID control has "bcd" as its input the query can potentially show "Abcd" or "ABCbcd" etc... (DUE TO THE "*") when I only want it to show results "bcd"

I tried Like [Screen].[Activeform]![txtUserID] but again I only get an empty record set.

I am confused by this and would like to hear an explanation.

Thanks so much.

 

[boblarson]

Why are you using [Screen]![Activeform]? Are you trying to use this with multiple forms?

 

[ions]

Yes I have many queries that are identical but they just reference different forms. With this feature I can have one query instead of 4. Also if I need to change the query I just need to change it once and not 4 times.

 

[boblarson]

Actually, you really don't need this. You can have a base query for a form, but open the form filtered by the criteria by using the WHERE Clause in the DoCmd.OpenForm code.

 

[ions]

Hi Bob

Thanks for your response. The queries are actually Make Table Queries. I am making tables because I need temp tables for continous forms which act as selection lists.

For Select Queries, I could use a filter on the form but that is too complicated isn't it? I prefer using the Query Grid.

Isn't there a way to just get the Screen.ActiveForm![TextControl] behavior without using the Like* statement?

 

[boblarson]

Hi Bob

Thanks for your response. The queries are actually Make Table Queries. I am making tables because I need temp tables for continous forms which act as selection lists.

I could use a filter but that is too complicated isn't it? I prefer using the Query Grid.

Isn't there a way to just get the Screen.ActiveForm![TextControl] behavior without using the Like* statement?

Sorry to ask more questions, but why are you using make table queries? You don't need to create tables to use with continuous forms. You can use the Make Table query (turned into a Select Query instead) as the basis for your forms and then you don't even need to make tables (which causes bloat, etc). That would also open the way up that I've mentioned.

Also, I do not know of any way to use Screen.ActiveForm to do what you are trying to do. Your question is the first time I've EVER seen it asked so it is a rare way to do something which can be done other ways.

 

[unclejoe]

Hi,

You cannot use the ActiveForm method to open a QBE query, because the ActiveForm is no longer Active.

You'll have to create a Function on a standard module and pass the Active Form Name and actual control, to loop thru the actual form behind the current active query.

Create a Function like....

Public Function fActiveForm(ctlName As String, frmName As String) As String
Dim frm As Form
Dim ctl As Control

For Each frm In Forms
If frm.Name = frmName Then
    For Each ctl In frm.Controls
    If ctl.Name = ctlName Then
    fActiveForm = ctl.Value
    End If
    Next
End If
Next
Set frm = Nothing
Set ctl = Nothing
End Function

ctlName is the control name in the form and frmName is the 'Active' form.

To use this function, key in the name of the function into the criteria like....

fActiveForm("YourControlName", "YourActiveFormName")

In the function, the "ctl.Value" will return the value the of the "Active Form control".

Dear Access Expert
I wanted to use [Screen].[Activeform]![txtUserID] as a criteria on the query grid but to my dismay this always produced an Empty recordset.. After about 45 minutes of searching (grrrr) I found:
Like "*" & [Screen].[Activeform]![txtUserID] which works when I use it as a criteria in the query grid.
My question is why doesn't [Screen].[Activeform]![txtUserID] work on its own? Why do I have to use the Like "*" Statement?
Also the Above Like statement doesn't exactly do what I want. If my txtUserID control has "bcd" as its input the query can potentially show "Abcd" or "ABCbcd" etc... (DUE TO THE "*") when I only want it to show results "bcd"
I tried Like [Screen].[Activeform]![txtUserID] but again I only get an empty record set.
I am confused by this and would like to hear an explanation.
Thanks so much.

 

[ions]

Hi Bob thanks for your responses.

I use Make Table queries because I have a continous form with an Unbound Checkbox next to each record. However, If I keep the check box unbound when the user selects the check box next to the record all the check boxes become highlighted, hence, I have to make temporary tables. I read somewhere that is considered a limitation of Access.

I discussed using Screen.ActiveForm.controlName with another Access developer and he said he doesn't use it. Instead he writes SQL statements.

I don't like writing SQL statements as they take way too long to debug and would much prefer to use Screen.ActiveForm.controlName.

Thanks

 

[boblarson]

I don't like writing SQL statements as they take way too long to debug and would much prefer to use Screen.ActiveForm.controlName.

Thanks

Given that it has already taken at least 4 days to try to get something to work, maybe you should reconsider the other.

 

[ions]

Hehe OK

thanks Bob ... I really appreciate your help

 

[ions]

Hi uncle Joe you are right, if you call a function using Screen.ActiveForm.ActiveControl you will get an error because he query grid is open. but if you already know the form you want ot use why not just use Forms![frmName]![cntrlName] in the criteria.

 

[unclejoe]

Hi,

Yes, I wanted to suggest using that "Forms![frmName]![cntrlName]" on your queries. But you said that any Form could be the Active Form and the Query needs to refer to that Active Form and if "Forms![frmName]![cntrlName]" was not the Active Form, the Query will not work correctly (meaning refering to the correct Form which Active Form but not that "FormName" (say "Form2" instead of "Form1").

I had to assume that you was using a Command button to open a query and since the Form was still Active, you can refer the form name and control to open the correct criteria with that Function.

Hi uncle Joe you are right, if you call a function using Screen.ActiveForm.ActiveControl you will get an error because he query grid is open. but if you already know the form you want ot use why not just use in the criteria.