permanently store a Criteria in query using a combo box

[Silvio]

This is what I have:

1) A tblLocation with 3 numerical records called OfficeID (1,2,3)
2) A query called qryData based on the tblData. The Criteria in the query is
set to 1 so I can retrieve only data for office 1
3) I have a form with a Combo Box on it called ComboOffice based on data
from tblLocation.

What I am trying to do is to use the combo box to ***permanently*** change
the value in my qryData criteria from 1 to 2 or 3. In few words even if I
close the form the query need to retain the new Criteria. (I know I can place
in the query criteria something like [Forms]![MyForm]![ComboOffice] but this
require that my form is open and once I close the form the value selected is
lost - I don't want the query to permanently depend on the form to run, I
want it to depend on the set criteria in it only)

Can anyone help?

 

[kingston via AccessMonster.com]

Create a textbox with a default value of 1, 2, or 3. Have your query
reference that textbox. With your combobox, change the default value of the
textbox and save the form. You can hide the textbox if you want.

This is what I have:

1) A tblLocation with 3 numerical records called OfficeID (1,2,3)
2) A query called qryData based on the tblData. The Criteria in the query is
set to 1 so I can retrieve only data for office 1
3) I have a form with a Combo Box on it called ComboOffice based on data
from tblLocation.

What I am trying to do is to use the combo box to ***permanently*** change
the value in my qryData criteria from 1 to 2 or 3. In few words even if I
close the form the query need to retain the new Criteria. (I know I can place
in the query criteria something like [Forms]![MyForm]![ComboOffice] but this
require that my form is open and once I close the form the value selected is
lost - I don't want the query to permanently depend on the form to run, I
want it to depend on the set criteria in it only)

Can anyone help?

 

[Silvio]

Kingston, I did try your why, however it works only as long the form is open
and this is what the problem is with my query and that's why I need a static
not dynamic change in criteria value. :-(

Create a textbox with a default value of 1, 2, or 3. Have your query
reference that textbox. With your combobox, change the default value of the
textbox and save the form. You can hide the textbox if you want.

This is what I have:

1) A tblLocation with 3 numerical records called OfficeID (1,2,3)
2) A query called qryData based on the tblData. The Criteria in the query is
set to 1 so I can retrieve only data for office 1
3) I have a form with a Combo Box on it called ComboOffice based on data
from tblLocation.

What I am trying to do is to use the combo box to ***permanently*** change
the value in my qryData criteria from 1 to 2 or 3. In few words even if I
close the form the query need to retain the new Criteria. (I know I can place
in the query criteria something like [Forms]![MyForm]![ComboOffice] but this
require that my form is open and once I close the form the value selected is
lost - I don't want the query to permanently depend on the form to run, I
want it to depend on the set criteria in it only)

Can anyone help?

 

[kingston via AccessMonster.com]

You have to change the property Default Value and save the form in your code.

Kingston, I did try your why, however it works only as long the form is open
and this is what the problem is with my query and that's why I need a static
not dynamic change in criteria value. :-(

Create a textbox with a default value of 1, 2, or 3. Have your query
reference that textbox. With your combobox, change the default value of the

[quoted text clipped - 17 lines]

 

[Silvio]

.... mmmhhh this is what I have in AfterUpdate event in my combo box:

Me.TxOffice.DefaultValue = Me.CmboOffice

what I am doing wrong? Please be patient and pecific.

Thank you,
Silvio

You have to change the property Default Value and save the form in your code.

Kingston, I did try your why, however it works only as long the form is open
and this is what the problem is with my query and that's why I need a static
not dynamic change in criteria value. :-(

Create a textbox with a default value of 1, 2, or 3. Have your query
reference that textbox. With your combobox, change the default value of the

[quoted text clipped - 17 lines]

Can anyone help?

 

[kingston via AccessMonster.com]

I'm sorry. It doesn't work even after I use a save command. I know that
this will work:
Create a table for the criteria value (1 field, 1 record).
Create a (hidden) control for this value in your form and change its value
via the combo box.
Again, I apologize for what I thought was a simple way around having to
create another table.

... mmmhhh this is what I have in AfterUpdate event in my combo box:

Me.TxOffice.DefaultValue = Me.CmboOffice

what I am doing wrong? Please be patient and pecific.

Thank you,
Silvio

You have to change the property Default Value and save the form in your code.

[quoted text clipped - 7 lines]

 

[Silvio]

Not a problem! However, this is what I did and it doe not work still. In my
query, I entered the following syntax in the Criteria line:
[Table1]![myOffice]. Table1 has only one field called myOffice and I have
entered only one numerical record, 1. I tried to run the query to make sure
it grabs the data from the table but the query still prompts me to enter a
value in a message box. What I am doing wrong now?

I'm sorry. It doesn't work even after I use a save command. I know that
this will work:
Create a table for the criteria value (1 field, 1 record).
Create a (hidden) control for this value in your form and change its value
via the combo box.
Again, I apologize for what I thought was a simple way around having to
create another table.

... mmmhhh this is what I have in AfterUpdate event in my combo box:

Me.TxOffice.DefaultValue = Me.CmboOffice

what I am doing wrong? Please be patient and pecific.

Thank you,
Silvio

You have to change the property Default Value and save the form in your code.

[quoted text clipped - 7 lines]

Can anyone help?

 

[kingston via AccessMonster.com]

You need a control (a textbox) that is bound to the value in the extra table.
Then your query and your code can refer to the value in the control via Forms!
myform!mycontrol. I'm assuming that your form is bound to the query and not
a table. Let me know if this is not the case (it gets more complicated and
another method might be easier to implement via code).

As far as your query goes, think about your results as a product of two
tables rather than one table and one criteria. In other words, add the extra
table to the design grid and link the corresponding fields from both tables.
Then your results will automatically and permanently be driven by the value
in the extra table.

Not a problem! However, this is what I did and it doe not work still. In my
query, I entered the following syntax in the Criteria line:
[Table1]![myOffice]. Table1 has only one field called myOffice and I have
entered only one numerical record, 1. I tried to run the query to make sure
it grabs the data from the table but the query still prompts me to enter a
value in a message box. What I am doing wrong now?

I'm sorry. It doesn't work even after I use a save command. I know that
this will work:

[quoted text clipped - 18 lines]

 

[kingston via AccessMonster.com]

Clarification:

Then your query and your code can refer to the value in the control via ...
should have been
Then your code can refer to the value in the control via...

You need a control (a textbox) that is bound to the value in the extra table.
Then your query and your code can refer to the value in the control via Forms!
myform!mycontrol. I'm assuming that your form is bound to the query and not
a table. Let me know if this is not the case (it gets more complicated and
another method might be easier to implement via code).

As far as your query goes, think about your results as a product of two
tables rather than one table and one criteria. In other words, add the extra
table to the design grid and link the corresponding fields from both tables.
Then your results will automatically and permanently be driven by the value
in the extra table.

Not a problem! However, this is what I did and it doe not work still. In my
query, I entered the following syntax in the Criteria line:

[quoted text clipped - 8 lines]

 

[Hamid]

Have you tried a 'Global Variable' x declared in any module ...
then when the parameter is changed in the form (afterupdate event) set the
variable x to the new value ... and base the query on that variable [x] ...

Hamid

 

[Silvio]

Hamid, I am not a pro ...yet. I have no idea what a Global variable is and
how to set one up and how to link it to the query. Can you be more specific
please?

Have you tried a 'Global Variable' x declared in any module ...
then when the parameter is changed in the form (afterupdate event) set the
variable x to the new value ... and base the query on that variable [x] ...

Hamid

This is what I have:

1) A tblLocation with 3 numerical records called OfficeID (1,2,3)
2) A query called qryData based on the tblData. The Criteria in the query is
set to 1 so I can retrieve only data for office 1
3) I have a form with a Combo Box on it called ComboOffice based on data
from tblLocation.

What I am trying to do is to use the combo box to ***permanently*** change
the value in my qryData criteria from 1 to 2 or 3. In few words even if I
close the form the query need to retain the new Criteria. (I know I can place
in the query criteria something like [Forms]![MyForm]![ComboOffice] but this
require that my form is open and once I close the form the value selected is
lost - I don't want the query to permanently depend on the form to run, I
want it to depend on the set criteria in it only)

Can anyone help?

 

[Hamid]

Silvio:

1. Simply open any existing module (or create a new one)
Immediately after the 'Option Compare Database' line, paste the following
code:

Global xVarName As String

Public Function fParam()
fParam = xVarName
End Function

2. In your form, for control 'ComboOffice' add the following code to the
'After_Update' event :
'xVarname = me.comboOffice.value'

3. In your Query, replace '[Forms]![MyForm]![ComboOffice]' with 'fparam()'.

Hamid

Hamid, I am not a pro ...yet. I have no idea what a Global variable is and
how to set one up and how to link it to the query. Can you be more specific
please?

Have you tried a 'Global Variable' x declared in any module ...
then when the parameter is changed in the form (afterupdate event) set the
variable x to the new value ... and base the query on that variable [x] ...

Hamid

This is what I have:

1) A tblLocation with 3 numerical records called OfficeID (1,2,3)
2) A query called qryData based on the tblData. The Criteria in the query is
set to 1 so I can retrieve only data for office 1
3) I have a form with a Combo Box on it called ComboOffice based on data
from tblLocation.

What I am trying to do is to use the combo box to ***permanently*** change
the value in my qryData criteria from 1 to 2 or 3. In few words even if I
close the form the query need to retain the new Criteria. (I know I can place
in the query criteria something like [Forms]![MyForm]![ComboOffice] but this
require that my form is open and once I close the form the value selected is
lost - I don't want the query to permanently depend on the form to run, I
want it to depend on the set criteria in it only)

Can anyone help?

 

[Hamid]

Silvio the type of variable should be whatever your combox contains (if 1,2,3
then it should be As Integer (not String)...

Also add after 'Public Function fParam()', add 'As Integer' (in your case)
to make sure the declare function & variable are of the same type.

Hamid

Silvio:

1. Simply open any existing module (or create a new one)
Immediately after the 'Option Compare Database' line, paste the following
code:

Global xVarName As String

Public Function fParam()
fParam = xVarName
End Function

2. In your form, for control 'ComboOffice' add the following code to the
'After_Update' event :
'xVarname = me.comboOffice.value'

3. In your Query, replace '[Forms]![MyForm]![ComboOffice]' with 'fparam()'.

Hamid

Hamid, I am not a pro ...yet. I have no idea what a Global variable is and
how to set one up and how to link it to the query. Can you be more specific
please?

Have you tried a 'Global Variable' x declared in any module ...
then when the parameter is changed in the form (afterupdate event) set the
variable x to the new value ... and base the query on that variable [x] ...

Hamid

:

This is what I have:

1) A tblLocation with 3 numerical records called OfficeID (1,2,3)
2) A query called qryData based on the tblData. The Criteria in the query is
set to 1 so I can retrieve only data for office 1
3) I have a form with a Combo Box on it called ComboOffice based on data
from tblLocation.

What I am trying to do is to use the combo box to ***permanently*** change
the value in my qryData criteria from 1 to 2 or 3. In few words even if I
close the form the query need to retain the new Criteria. (I know I can place
in the query criteria something like [Forms]![MyForm]![ComboOffice] but this
require that my form is open and once I close the form the value selected is
lost - I don't want the query to permanently depend on the form to run, I
want it to depend on the set criteria in it only)

Can anyone help?

 

[Silvio]

it is not working! This is what I have done:

create a module as follow:

Option Compare Database

Global xvarname As Integer

Public Function fParam(xvarname As Integer)

fParam = xvarname

End Function


In my control (combobox) is entered the following syntax in After Update:

xvarname = Me.ComOffice.Value

and I changed the criteria in my query to read:

fparam()

As result the query is giving me the 'wrong number of arguments ...' error
message

the form after making a selection from my combobox I get '


When I select something from the combo box I get the following '...can't
find the macro name xvarname.me'

What I am doing wrong here? Aslo, can I still run the query even after the
form is close? Because this is the idea of this project.

Silvio the type of variable should be whatever your combox contains (if 1,2,3
then it should be As Integer (not String)...

Also add after 'Public Function fParam()', add 'As Integer' (in your case)
to make sure the declare function & variable are of the same type.

Hamid

Silvio:

1. Simply open any existing module (or create a new one)
Immediately after the 'Option Compare Database' line, paste the following
code:

Global xVarName As String

Public Function fParam()
fParam = xVarName
End Function

2. In your form, for control 'ComboOffice' add the following code to the
'After_Update' event :
'xVarname = me.comboOffice.value'

3. In your Query, replace '[Forms]![MyForm]![ComboOffice]' with 'fparam()'.

Hamid

Hamid, I am not a pro ...yet. I have no idea what a Global variable is and
how to set one up and how to link it to the query. Can you be more specific
please?



:

Have you tried a 'Global Variable' x declared in any module ...
then when the parameter is changed in the form (afterupdate event) set the
variable x to the new value ... and base the query on that variable [x] ...

Hamid

:

This is what I have:

1) A tblLocation with 3 numerical records called OfficeID (1,2,3)
2) A query called qryData based on the tblData. The Criteria in the query is
set to 1 so I can retrieve only data for office 1
3) I have a form with a Combo Box on it called ComboOffice based on data
from tblLocation.

What I am trying to do is to use the combo box to ***permanently*** change
the value in my qryData criteria from 1 to 2 or 3. In few words even if I
close the form the query need to retain the new Criteria. (I know I can place
in the query criteria something like [Forms]![MyForm]![ComboOffice] but this
require that my form is open and once I close the form the value selected is
lost - I don't want the query to permanently depend on the form to run, I
want it to depend on the set criteria in it only)

Can anyone help?

 

[Hamid]

Silvio:

There is an error( Parenthesis closed after xvarname)

Public Function fParam(xvarname As Integer)

should be

Public Function fParam(xvarname) As Integer

Hamid

it is not working! This is what I have done:

create a module as follow:

Option Compare Database

Global xvarname As Integer

Public Function fParam(xvarname As Integer)

fParam = xvarname

End Function


In my control (combobox) is entered the following syntax in After Update:

xvarname = Me.ComOffice.Value

and I changed the criteria in my query to read:

fparam()

As result the query is giving me the 'wrong number of arguments ...' error
message

the form after making a selection from my combobox I get '


When I select something from the combo box I get the following '...can't
find the macro name xvarname.me'

What I am doing wrong here? Aslo, can I still run the query even after the
form is close? Because this is the idea of this project.

Silvio the type of variable should be whatever your combox contains (if 1,2,3
then it should be As Integer (not String)...

Also add after 'Public Function fParam()', add 'As Integer' (in your case)
to make sure the declare function & variable are of the same type.

Hamid

Silvio:

1. Simply open any existing module (or create a new one)
Immediately after the 'Option Compare Database' line, paste the following
code:

Global xVarName As String

Public Function fParam()
fParam = xVarName
End Function

2. In your form, for control 'ComboOffice' add the following code to the
'After_Update' event :
'xVarname = me.comboOffice.value'

3. In your Query, replace '[Forms]![MyForm]![ComboOffice]' with 'fparam()'.

Hamid

:

Hamid, I am not a pro ...yet. I have no idea what a Global variable is and
how to set one up and how to link it to the query. Can you be more specific
please?



:

Have you tried a 'Global Variable' x declared in any module ...
then when the parameter is changed in the form (afterupdate event) set the
variable x to the new value ... and base the query on that variable [x] ...

Hamid

:

This is what I have:

1) A tblLocation with 3 numerical records called OfficeID (1,2,3)
2) A query called qryData based on the tblData. The Criteria in the query is
set to 1 so I can retrieve only data for office 1
3) I have a form with a Combo Box on it called ComboOffice based on data
from tblLocation.

What I am trying to do is to use the combo box to ***permanently*** change
the value in my qryData criteria from 1 to 2 or 3. In few words even if I
close the form the query need to retain the new Criteria. (I know I can place
in the query criteria something like [Forms]![MyForm]![ComboOffice] but this
require that my form is open and once I close the form the value selected is
lost - I don't want the query to permanently depend on the form to run, I
want it to depend on the set criteria in it only)

Can anyone help?

 

[Hamid]

Silvio:

Also, check your syntax & make sure the combobox NAME (XXX) is correct:

the error '...can't find the macro name xvarname.me' suggest that the syntax
in the
after_Update event of the combox is NOT correct ...

Private Sub XXX_AfterUpdate()
xvarname = Me.XXX.Value
End Sub


If these corrections do not work ... then copy & paste ALL the code you have
inserted into a Word document ... and send it to me

I have thoroughly tested this ... selcted a value from combobox, closed the
form, then ran the query ... IT WORKS!

Hamid

it is not working! This is what I have done:

create a module as follow:

Option Compare Database

Global xvarname As Integer

Public Function fParam(xvarname As Integer)

fParam = xvarname

End Function


In my control (combobox) is entered the following syntax in After Update:

xvarname = Me.ComOffice.Value

and I changed the criteria in my query to read:

fparam()

As result the query is giving me the 'wrong number of arguments ...' error
message

the form after making a selection from my combobox I get '


When I select something from the combo box I get the following '...can't
find the macro name xvarname.me'

What I am doing wrong here? Aslo, can I still run the query even after the
form is close? Because this is the idea of this project.

Silvio the type of variable should be whatever your combox contains (if 1,2,3
then it should be As Integer (not String)...

Also add after 'Public Function fParam()', add 'As Integer' (in your case)
to make sure the declare function & variable are of the same type.

Hamid

Silvio:

1. Simply open any existing module (or create a new one)
Immediately after the 'Option Compare Database' line, paste the following
code:

Global xVarName As String

Public Function fParam()
fParam = xVarName
End Function

2. In your form, for control 'ComboOffice' add the following code to the
'After_Update' event :
'xVarname = me.comboOffice.value'

3. In your Query, replace '[Forms]![MyForm]![ComboOffice]' with 'fparam()'.

Hamid

:

Hamid, I am not a pro ...yet. I have no idea what a Global variable is and
how to set one up and how to link it to the query. Can you be more specific
please?



:

Have you tried a 'Global Variable' x declared in any module ...
then when the parameter is changed in the form (afterupdate event) set the
variable x to the new value ... and base the query on that variable [x] ...

Hamid

:

This is what I have:

1) A tblLocation with 3 numerical records called OfficeID (1,2,3)
2) A query called qryData based on the tblData. The Criteria in the query is
set to 1 so I can retrieve only data for office 1
3) I have a form with a Combo Box on it called ComboOffice based on data
from tblLocation.

What I am trying to do is to use the combo box to ***permanently*** change
the value in my qryData criteria from 1 to 2 or 3. In few words even if I
close the form the query need to retain the new Criteria. (I know I can place
in the query criteria something like [Forms]![MyForm]![ComboOffice] but this
require that my form is open and once I close the form the value selected is
lost - I don't want the query to permanently depend on the form to run, I
want it to depend on the set criteria in it only)

Can anyone help?

 

[Silvio]

Hamid, the only error meggage I am getting now is when I try to run the query
is:

'Wrong number of arguments used with function in query
wxpression'(((tbl.data.Office)=fparam()))'.

The SQL view of the query is a follow:

SELECT tblData.[No], tblData.Office, tblData.Street
FROM tblData
WHERE (((tblData.Office)=fparam()));

What I am doing wrong now?

Silvio:

Also, check your syntax & make sure the combobox NAME (XXX) is correct:

the error '...can't find the macro name xvarname.me' suggest that the syntax
in the
after_Update event of the combox is NOT correct ...

Private Sub XXX_AfterUpdate()
xvarname = Me.XXX.Value
End Sub


If these corrections do not work ... then copy & paste ALL the code you have
inserted into a Word document ... and send it to me

I have thoroughly tested this ... selcted a value from combobox, closed the
form, then ran the query ... IT WORKS!

Hamid

it is not working! This is what I have done:

create a module as follow:

Option Compare Database

Global xvarname As Integer

Public Function fParam(xvarname As Integer)

fParam = xvarname

End Function


In my control (combobox) is entered the following syntax in After Update:

xvarname = Me.ComOffice.Value

and I changed the criteria in my query to read:

fparam()

As result the query is giving me the 'wrong number of arguments ...' error
message

the form after making a selection from my combobox I get '


When I select something from the combo box I get the following '...can't
find the macro name xvarname.me'

What I am doing wrong here? Aslo, can I still run the query even after the
form is close? Because this is the idea of this project.

Silvio the type of variable should be whatever your combox contains (if 1,2,3
then it should be As Integer (not String)...

Also add after 'Public Function fParam()', add 'As Integer' (in your case)
to make sure the declare function & variable are of the same type.

Hamid


:

Silvio:

1. Simply open any existing module (or create a new one)
Immediately after the 'Option Compare Database' line, paste the following
code:

Global xVarName As String

Public Function fParam()
fParam = xVarName
End Function

2. In your form, for control 'ComboOffice' add the following code to the
'After_Update' event :
'xVarname = me.comboOffice.value'

3. In your Query, replace '[Forms]![MyForm]![ComboOffice]' with 'fparam()'.

Hamid

:

Hamid, I am not a pro ...yet. I have no idea what a Global variable is and
how to set one up and how to link it to the query. Can you be more specific
please?



:

Have you tried a 'Global Variable' x declared in any module ...
then when the parameter is changed in the form (afterupdate event) set the
variable x to the new value ... and base the query on that variable [x] ...

Hamid

:

This is what I have:

1) A tblLocation with 3 numerical records called OfficeID (1,2,3)
2) A query called qryData based on the tblData. The Criteria in the query is
set to 1 so I can retrieve only data for office 1
3) I have a form with a Combo Box on it called ComboOffice based on data
from tblLocation.

What I am trying to do is to use the combo box to ***permanently*** change
the value in my qryData criteria from 1 to 2 or 3. In few words even if I
close the form the query need to retain the new Criteria. (I know I can place
in the query criteria something like [Forms]![MyForm]![ComboOffice] but this
require that my form is open and once I close the form the value selected is
lost - I don't want the query to permanently depend on the form to run, I
want it to depend on the set criteria in it only)

Can anyone help?

 

[Silvio]

PS. I tried to e-mail you but I did not see your e-mail any where :-(

Silvio:

Also, check your syntax & make sure the combobox NAME (XXX) is correct:

the error '...can't find the macro name xvarname.me' suggest that the syntax
in the
after_Update event of the combox is NOT correct ...

Private Sub XXX_AfterUpdate()
xvarname = Me.XXX.Value
End Sub


If these corrections do not work ... then copy & paste ALL the code you have
inserted into a Word document ... and send it to me

I have thoroughly tested this ... selcted a value from combobox, closed the
form, then ran the query ... IT WORKS!

Hamid

it is not working! This is what I have done:

create a module as follow:

Option Compare Database

Global xvarname As Integer

Public Function fParam(xvarname As Integer)

fParam = xvarname

End Function


In my control (combobox) is entered the following syntax in After Update:

xvarname = Me.ComOffice.Value

and I changed the criteria in my query to read:

fparam()

As result the query is giving me the 'wrong number of arguments ...' error
message

the form after making a selection from my combobox I get '


When I select something from the combo box I get the following '...can't
find the macro name xvarname.me'

What I am doing wrong here? Aslo, can I still run the query even after the
form is close? Because this is the idea of this project.

Silvio the type of variable should be whatever your combox contains (if 1,2,3
then it should be As Integer (not String)...

Also add after 'Public Function fParam()', add 'As Integer' (in your case)
to make sure the declare function & variable are of the same type.

Hamid


:

Silvio:

1. Simply open any existing module (or create a new one)
Immediately after the 'Option Compare Database' line, paste the following
code:

Global xVarName As String

Public Function fParam()
fParam = xVarName
End Function

2. In your form, for control 'ComboOffice' add the following code to the
'After_Update' event :
'xVarname = me.comboOffice.value'

3. In your Query, replace '[Forms]![MyForm]![ComboOffice]' with 'fparam()'.

Hamid

:

Hamid, I am not a pro ...yet. I have no idea what a Global variable is and
how to set one up and how to link it to the query. Can you be more specific
please?



:

Have you tried a 'Global Variable' x declared in any module ...
then when the parameter is changed in the form (afterupdate event) set the
variable x to the new value ... and base the query on that variable [x] ...

Hamid

:

This is what I have:

1) A tblLocation with 3 numerical records called OfficeID (1,2,3)
2) A query called qryData based on the tblData. The Criteria in the query is
set to 1 so I can retrieve only data for office 1
3) I have a form with a Combo Box on it called ComboOffice based on data
from tblLocation.

What I am trying to do is to use the combo box to ***permanently*** change
the value in my qryData criteria from 1 to 2 or 3. In few words even if I
close the form the query need to retain the new Criteria. (I know I can place
in the query criteria something like [Forms]![MyForm]![ComboOffice] but this
require that my form is open and once I close the form the value selected is
lost - I don't want the query to permanently depend on the form to run, I
want it to depend on the set criteria in it only)

Can anyone help?

 

[Silvio]

Hamid if you can, you can e-mail the database example you have so I can See
how you did it. (e-mail address removed)

Thank you,
Silvio

Silvio:

Also, check your syntax & make sure the combobox NAME (XXX) is correct:

the error '...can't find the macro name xvarname.me' suggest that the syntax
in the
after_Update event of the combox is NOT correct ...

Private Sub XXX_AfterUpdate()
xvarname = Me.XXX.Value
End Sub


If these corrections do not work ... then copy & paste ALL the code you have
inserted into a Word document ... and send it to me

I have thoroughly tested this ... selcted a value from combobox, closed the
form, then ran the query ... IT WORKS!

Hamid

it is not working! This is what I have done:

create a module as follow:

Option Compare Database

Global xvarname As Integer

Public Function fParam(xvarname As Integer)

fParam = xvarname

End Function


In my control (combobox) is entered the following syntax in After Update:

xvarname = Me.ComOffice.Value

and I changed the criteria in my query to read:

fparam()

As result the query is giving me the 'wrong number of arguments ...' error
message

the form after making a selection from my combobox I get '


When I select something from the combo box I get the following '...can't
find the macro name xvarname.me'

What I am doing wrong here? Aslo, can I still run the query even after the
form is close? Because this is the idea of this project.

Silvio the type of variable should be whatever your combox contains (if 1,2,3
then it should be As Integer (not String)...

Also add after 'Public Function fParam()', add 'As Integer' (in your case)
to make sure the declare function & variable are of the same type.

Hamid


:

Silvio:

1. Simply open any existing module (or create a new one)
Immediately after the 'Option Compare Database' line, paste the following
code:

Global xVarName As String

Public Function fParam()
fParam = xVarName
End Function

2. In your form, for control 'ComboOffice' add the following code to the
'After_Update' event :
'xVarname = me.comboOffice.value'

3. In your Query, replace '[Forms]![MyForm]![ComboOffice]' with 'fparam()'.

Hamid

:

Hamid, I am not a pro ...yet. I have no idea what a Global variable is and
how to set one up and how to link it to the query. Can you be more specific
please?



:

Have you tried a 'Global Variable' x declared in any module ...
then when the parameter is changed in the form (afterupdate event) set the
variable x to the new value ... and base the query on that variable [x] ...

Hamid

:

This is what I have:

1) A tblLocation with 3 numerical records called OfficeID (1,2,3)
2) A query called qryData based on the tblData. The Criteria in the query is
set to 1 so I can retrieve only data for office 1
3) I have a form with a Combo Box on it called ComboOffice based on data
from tblLocation.

What I am trying to do is to use the combo box to ***permanently*** change
the value in my qryData criteria from 1 to 2 or 3. In few words even if I
close the form the query need to retain the new Criteria. (I know I can place
in the query criteria something like [Forms]![MyForm]![ComboOffice] but this
require that my form is open and once I close the form the value selected is
lost - I don't want the query to permanently depend on the form to run, I
want it to depend on the set criteria in it only)

Can anyone help?

 

[Hamid]

Silvio::

Sorry, I was away & did not check the site....

Here is my e-mail: (e-mail address removed)

I will sent you my sample db... after some cleanup.

Hamid

Hamid if you can, you can e-mail the database example you have so I can See
how you did it. (e-mail address removed)

Thank you,
Silvio

Silvio:

Also, check your syntax & make sure the combobox NAME (XXX) is correct:

the error '...can't find the macro name xvarname.me' suggest that the syntax
in the
after_Update event of the combox is NOT correct ...

Private Sub XXX_AfterUpdate()
xvarname = Me.XXX.Value
End Sub


If these corrections do not work ... then copy & paste ALL the code you have
inserted into a Word document ... and send it to me

I have thoroughly tested this ... selcted a value from combobox, closed the
form, then ran the query ... IT WORKS!

Hamid

it is not working! This is what I have done:

create a module as follow:

Option Compare Database

Global xvarname As Integer

Public Function fParam(xvarname As Integer)

fParam = xvarname

End Function


In my control (combobox) is entered the following syntax in After Update:

xvarname = Me.ComOffice.Value

and I changed the criteria in my query to read:

fparam()

As result the query is giving me the 'wrong number of arguments ...' error
message

the form after making a selection from my combobox I get '


When I select something from the combo box I get the following '...can't
find the macro name xvarname.me'

What I am doing wrong here? Aslo, can I still run the query even after the
form is close? Because this is the idea of this project.

:

Silvio the type of variable should be whatever your combox contains (if 1,2,3
then it should be As Integer (not String)...

Also add after 'Public Function fParam()', add 'As Integer' (in your case)
to make sure the declare function & variable are of the same type.

Hamid


:

Silvio:

1. Simply open any existing module (or create a new one)
Immediately after the 'Option Compare Database' line, paste the following
code:

Global xVarName As String

Public Function fParam()
fParam = xVarName
End Function

2. In your form, for control 'ComboOffice' add the following code to the
'After_Update' event :
'xVarname = me.comboOffice.value'

3. In your Query, replace '[Forms]![MyForm]![ComboOffice]' with 'fparam()'.

Hamid

:

Hamid, I am not a pro ...yet. I have no idea what a Global variable is and
how to set one up and how to link it to the query. Can you be more specific
please?



:

Have you tried a 'Global Variable' x declared in any module ...
then when the parameter is changed in the form (afterupdate event) set the
variable x to the new value ... and base the query on that variable [x] ...

Hamid

:

This is what I have:

1) A tblLocation with 3 numerical records called OfficeID (1,2,3)
2) A query called qryData based on the tblData. The Criteria in the query is
set to 1 so I can retrieve only data for office 1
3) I have a form with a Combo Box on it called ComboOffice based on data
from tblLocation.

What I am trying to do is to use the combo box to ***permanently*** change
the value in my qryData criteria from 1 to 2 or 3. In few words even if I
close the form the query need to retain the new Criteria. (I know I can place
in the query criteria something like [Forms]![MyForm]![ComboOffice] but this
require that my form is open and once I close the form the value selected is
lost - I don't want the query to permanently depend on the form to run, I
want it to depend on the set criteria in it only)

Can anyone help?