Querydef

[Guest]

I am trying to modify an existing query by using VB. I need to randomly
select an X number of records without having the user change the top values
property of the query each time it is run. I have written the following code
and placed it in a new module of the database. My problem is that when I
close the module, the existing query is not being modified.
Any suggestions?

Public Sub NewQueryDef()
'Create a new query definition that changes the SQL of existing query
Dim dbsCurrent As Database
Dim myqs As QueryDef

Set dbsCurrent = CurrentDb
Set myqs = dbsCurrent.QueryDefs("Random Families")
myqs.SQL = "SELECT Top " & X & " Families.* From Families ORDER BY
Rnd([ID]);"

End Sub

 

[Duane Hookom]

Try set the SQL to something like
SELECT Families.* From Families;
Then run your query making sure the query is not open. Check the SQL.

We can't see where X is coming from.

 

[Guest]

Thanks. I've changed my code to create a function so that it now reads:

Public Function NewQueryDef(X As Integer)
'Create a new query definition that changes the SQL of existing query
Dim dbsCurrent As Database
Dim myqs As QueryDef

Set dbsCurrent = CurrentDb
Set myqs = dbsCurrent.QueryDefs("Random Families")
myqs.SQL = "SELECT Top " & X & " Families.* From Families ORDER BY
Rnd([ID]);"

End Function

I'm new to VB and am not sure how to run the query from code. I tried typing:
docmd.OpenQuery([Random Families],,)
from the immediate window, but I get a compile error - expected expression.
Also, can I use the X to represent a number of records in the SELECT Top
line of the code? Ultimately, I need to use this in a form so a user can
enter a number of records to be randomly selected. This number will vary. TIA.
--
Deb H

Try set the SQL to something like
SELECT Families.* From Families;
Then run your query making sure the query is not open. Check the SQL.

We can't see where X is coming from.

--
Duane Hookom
MS Access MVP
--

I am trying to modify an existing query by using VB. I need to randomly
select an X number of records without having the user change the top
values
property of the query each time it is run. I have written the following
code
and placed it in a new module of the database. My problem is that when I
close the module, the existing query is not being modified.
Any suggestions?

Public Sub NewQueryDef()
'Create a new query definition that changes the SQL of existing query
Dim dbsCurrent As Database
Dim myqs As QueryDef

Set dbsCurrent = CurrentDb
Set myqs = dbsCurrent.QueryDefs("Random Families")
myqs.SQL = "SELECT Top " & X & " Families.* From Families ORDER BY
Rnd([ID]);"

End Sub

 

[Duane Hookom]

Again, the query must be closed when the code is run. To open the query from
code, use:

docmd.OpenQuery "Random Families"

Normally you would open a form or report based on the query.

--
Duane Hookom
MS Access MVP
--

Thanks. I've changed my code to create a function so that it now reads:

Public Function NewQueryDef(X As Integer)
'Create a new query definition that changes the SQL of existing query
Dim dbsCurrent As Database
Dim myqs As QueryDef

Set dbsCurrent = CurrentDb
Set myqs = dbsCurrent.QueryDefs("Random Families")
myqs.SQL = "SELECT Top " & X & " Families.* From Families ORDER BY
Rnd([ID]);"

End Function

I'm new to VB and am not sure how to run the query from code. I tried
typing:
docmd.OpenQuery([Random Families],,)
from the immediate window, but I get a compile error - expected
expression.
Also, can I use the X to represent a number of records in the SELECT Top
line of the code? Ultimately, I need to use this in a form so a user can
enter a number of records to be randomly selected. This number will vary.
TIA.
--
Deb H

Try set the SQL to something like
SELECT Families.* From Families;
Then run your query making sure the query is not open. Check the SQL.

We can't see where X is coming from.

--
Duane Hookom
MS Access MVP
--

I am trying to modify an existing query by using VB. I need to randomly
select an X number of records without having the user change the top
values
property of the query each time it is run. I have written the following
code
and placed it in a new module of the database. My problem is that when
I
close the module, the existing query is not being modified.
Any suggestions?

Public Sub NewQueryDef()
'Create a new query definition that changes the SQL of existing query
Dim dbsCurrent As Database
Dim myqs As QueryDef

Set dbsCurrent = CurrentDb
Set myqs = dbsCurrent.QueryDefs("Random Families")
myqs.SQL = "SELECT Top " & X & " Families.* From Families ORDER BY
Rnd([ID]);"

End Sub

 

[Guest]

Still having a few problems:
1. It doesn't seem like the function I've written is changing my query
because when I view the SQL of the original query it is unchanged. Shouldn't
I be seeing a revised select statement?
2. Also, I've created a form for the user to input the desired number of
records to be randomly selected. In an unbound text box, I've set its control
source to:
=NewQueryDef(" x ")
When I display the form in form view, I get an error in the text box. Any
help is greatly appreciated.
--
Deb H

Again, the query must be closed when the code is run. To open the query from
code, use:

docmd.OpenQuery "Random Families"

Normally you would open a form or report based on the query.

--
Duane Hookom
MS Access MVP
--

Thanks. I've changed my code to create a function so that it now reads:

Public Function NewQueryDef(X As Integer)
'Create a new query definition that changes the SQL of existing query
Dim dbsCurrent As Database
Dim myqs As QueryDef

Set dbsCurrent = CurrentDb
Set myqs = dbsCurrent.QueryDefs("Random Families")
myqs.SQL = "SELECT Top " & X & " Families.* From Families ORDER BY
Rnd([ID]);"

End Function

I'm new to VB and am not sure how to run the query from code. I tried
typing:
docmd.OpenQuery([Random Families],,)
from the immediate window, but I get a compile error - expected
expression.
Also, can I use the X to represent a number of records in the SELECT Top
line of the code? Ultimately, I need to use this in a form so a user can
enter a number of records to be randomly selected. This number will vary.
TIA.
--
Deb H

Try set the SQL to something like
SELECT Families.* From Families;
Then run your query making sure the query is not open. Check the SQL.

We can't see where X is coming from.

--
Duane Hookom
MS Access MVP
--

I am trying to modify an existing query by using VB. I need to randomly
select an X number of records without having the user change the top
values
property of the query each time it is run. I have written the following
code
and placed it in a new module of the database. My problem is that when
I
close the module, the existing query is not being modified.
Any suggestions?

Public Sub NewQueryDef()
'Create a new query definition that changes the SQL of existing query
Dim dbsCurrent As Database
Dim myqs As QueryDef

Set dbsCurrent = CurrentDb
Set myqs = dbsCurrent.QueryDefs("Random Families")
myqs.SQL = "SELECT Top " & X & " Families.* From Families ORDER BY
Rnd([ID]);"

End Sub

 

[Duane Hookom]

How, where are you calling the function? Have you tried placing a breakpoint
in the code?
Your control source must return a value. Don't place a function like this in
a control source.

I have mentioned twice that the query (and anything using it) must be closed
when you want to change the SQL property but you have never acknowledged
this.

--
Duane Hookom
MS Access MVP
--

Still having a few problems:
1. It doesn't seem like the function I've written is changing my query
because when I view the SQL of the original query it is unchanged.
Shouldn't
I be seeing a revised select statement?
2. Also, I've created a form for the user to input the desired number of
records to be randomly selected. In an unbound text box, I've set its
control
source to:
=NewQueryDef(" x ")
When I display the form in form view, I get an error in the text box. Any
help is greatly appreciated.
--
Deb H

Again, the query must be closed when the code is run. To open the query
from
code, use:

docmd.OpenQuery "Random Families"

Normally you would open a form or report based on the query.

--
Duane Hookom
MS Access MVP
--

Thanks. I've changed my code to create a function so that it now reads:

Public Function NewQueryDef(X As Integer)
'Create a new query definition that changes the SQL of existing query
Dim dbsCurrent As Database
Dim myqs As QueryDef

Set dbsCurrent = CurrentDb
Set myqs = dbsCurrent.QueryDefs("Random Families")
myqs.SQL = "SELECT Top " & X & " Families.* From Families ORDER BY
Rnd([ID]);"

End Function

I'm new to VB and am not sure how to run the query from code. I tried
typing:
docmd.OpenQuery([Random Families],,)
from the immediate window, but I get a compile error - expected
expression.
Also, can I use the X to represent a number of records in the SELECT
Top
line of the code? Ultimately, I need to use this in a form so a user
can
enter a number of records to be randomly selected. This number will
vary.
TIA.
--
Deb H


:

Try set the SQL to something like
SELECT Families.* From Families;
Then run your query making sure the query is not open. Check the SQL.

We can't see where X is coming from.

--
Duane Hookom
MS Access MVP
--

I am trying to modify an existing query by using VB. I need to
randomly
select an X number of records without having the user change the top
values
property of the query each time it is run. I have written the
following
code
and placed it in a new module of the database. My problem is that
when
I
close the module, the existing query is not being modified.
Any suggestions?

Public Sub NewQueryDef()
'Create a new query definition that changes the SQL of existing
query
Dim dbsCurrent As Database
Dim myqs As QueryDef

Set dbsCurrent = CurrentDb
Set myqs = dbsCurrent.QueryDefs("Random Families")
myqs.SQL = "SELECT Top " & X & " Families.* From Families ORDER
BY
Rnd([ID]);"

End Sub