Using Criteria and Alpha Buttons.

[steve12173]

I have a form that has a query in it. I am using alpha buttons to help
filter the records. I also need to a criteria on the query to only pull
records entered by that employee. So I basically created a form for the
employee to enter their ID and then the form pulls up all their records but,
when you click any of the alpha buttons it asks for the employee id again
(since it is doing a requery). Any way around this?

 

[Marshall Barton]

I have a form that has a query in it. I am using alpha buttons to help
filter the records. I also need to a criteria on the query to only pull
records entered by that employee. So I basically created a form for the
employee to enter their ID and then the form pulls up all their records but,
when you click any of the alpha buttons it asks for the employee id again
(since it is doing a requery). Any way around this?

You should make sure that the query's criteria looks like:
Forms![the form name].[employee id text box]
and all the spelling is accurate.

 

[steve12173]

The criteria is entered as [Forms]![FormName]![FieldName]. So the first time
I run the query, it comes out right. But, when I try to use the Alpha
Buttons to narrow my search by a persons last name, it's asking for the
employee id again. Is there a maco that will grab the employee id from the
current query and reuse it?

I have a form that has a query in it. I am using alpha buttons to help
filter the records. I also need to a criteria on the query to only pull
records entered by that employee. So I basically created a form for the
employee to enter their ID and then the form pulls up all their records but,
when you click any of the alpha buttons it asks for the employee id again
(since it is doing a requery). Any way around this?

You should make sure that the query's criteria looks like:
Forms![the form name].[employee id text box]
and all the spelling is accurate.

 

[Marshall Barton]

The criteria is entered as [Forms]![FormName]![FieldName]. So the first time
I run the query, it comes out right. But, when I try to use the Alpha
Buttons to narrow my search by a persons last name, it's asking for the
employee id again. Is there a maco that will grab the employee id from the
current query and reuse it?

I seriously doubt that the criteria uses "FormName" and
"FieldName". You should use Copy/Paste when posting so we
can see exactly what you are using and everyone can refer to
the real names without ambiguity or concern for typos.

We should also see the code you are using in the buttons.
It sounds like you are filtering the form's recordset (using
the Filter property?), but have some other kind of where
condition.

 

[steve12173]

OK this is the criteria on my query, [Forms]![frmProspectList]![Consultant].
And, yes, I am using filters for the current recordset with the Alpha
Buttons. This is the Where Condition on the filters, [CompanyName] Like
"B*". The Alpha Buttons where added through a macro, I am not real strong
with code, so I felt it was easier for me to do it in a macro.

The criteria is entered as [Forms]![FormName]![FieldName]. So the first time
I run the query, it comes out right. But, when I try to use the Alpha
Buttons to narrow my search by a persons last name, it's asking for the
employee id again. Is there a maco that will grab the employee id from the
current query and reuse it?

I seriously doubt that the criteria uses "FormName" and
"FieldName". You should use Copy/Paste when posting so we
can see exactly what you are using and everyone can refer to
the real names without ambiguity or concern for typos.

We should also see the code you are using in the buttons.
It sounds like you are filtering the form's recordset (using
the Filter property?), but have some other kind of where
condition.

 

[Marshall Barton]

OK this is the criteria on my query, [Forms]![frmProspectList]![Consultant].
And, yes, I am using filters for the current recordset with the Alpha
Buttons. This is the Where Condition on the filters, [CompanyName] Like
"B*". The Alpha Buttons where added through a macro, I am not real strong
with code, so I felt it was easier for me to do it in a macro.

If you are using a form for the EmployeeID criteria, then
the error implies that the form is no longer open.

In general, you should try to keep the criteria text/combo
boxes on the same form (in the Header or Footer section)
that will display the selected records. This means that you
don't have to worry about a separate form and when it can
and can't be closed.

If I am way off base with all that, then try to explain
these forms in more detail.

Note that using a macro may seem easy to create, but it's
not so easy when it doesn't work and you have a very limited
set of debugging aids to help you figure out what went
wrong. The VBA code to set the Filter could be:

Me.Filter = "CompanyName Like 'B*' "
Me,FilterOn = True