Looping a query

  • Thread starter Thread starter osmondb
  • Start date Start date
O

osmondb

Hi all,

I was just wondering if it is possible to 'loop' a query
that I've created. Basically, the query is designed to let
the user input a record's "Unique ID" (set up with
AutoNumber) and the query will automatically change the
True/False field in that record to 'True'. The problem is,
if I have several records that I know the Unique ID for
that I want to change to true, I have to run the query once
for each record I want to update. Normally it would be
easier to just go to the table, find the records and click
the box etc...but the table has MANY records and I would
like to be able to run the query, and it keeps asking for
Unique ID's until the user closes or cancels the query.
Any and all help is appreciated!

osmondb
 
You'd need to do this via VBA code. I would use the SQL of the query in the
code for running the query, instead of "calling" the query.
Something like this, perhaps:

Public Sub RunQueryUntilDone()
Dim strSQL As String
Dim dbs As DAO.Database
Dim strID As String
Set dbs = CurrentDb
strID = "starting"
Do
strID = InputBox("Enter ID or enter nothing to stop:")
If strID <> "" Then
strSQL = "UPDATE TableName SET TrueFalseField = True " & _
"WHERE [IDField]=" & CLng(strID) & ";"
dbs.Execute strSQL, dbFailOnError
Else
MsgBox "No value entered. Query ending now."
Exit Do
End If
Loop
dbs.Close
Set dbs = Nothing
End Sub
 
I entered this code below and I changed it around to suit
my table and query (only had to change field names in the
SQL part), but when I try and run it I'm getting an error
saying that the User-Defined Type is not defined on this line

Dim dbs As DAO.Database

I have absolutely NO knowledge of VB, but I am quite
familiar with C++ and it looks to me like either the dbs
type isn't a valid type, or the DAO struct (or class object
whatever it may be) isn't defined. Can anyone help me out
with this error? I've tried looking in help files etc...but
it doesn't make much sense to me as this is the first time
I've ever used VB.
-----Original Message-----
You'd need to do this via VBA code. I would use the SQL of the query in the
code for running the query, instead of "calling" the query.
Something like this, perhaps:

Public Sub RunQueryUntilDone()
Dim strSQL As String
Dim dbs As DAO.Database
Dim strID As String
Set dbs = CurrentDb
strID = "starting"
Do
strID = InputBox("Enter ID or enter nothing to stop:")
If strID <> "" Then
strSQL = "UPDATE TableName SET TrueFalseField = True " & _
"WHERE [IDField]=" & CLng(strID) & ";"
dbs.Execute strSQL, dbFailOnError
Else
MsgBox "No value entered. Query ending now."
Exit Do
End If
Loop
dbs.Close
Set dbs = Nothing
End Sub

--

Ken Snell
<MS ACCESS MVP>


Hi all,

I was just wondering if it is possible to 'loop' a query
that I've created. Basically, the query is designed to let
the user input a record's "Unique ID" (set up with
AutoNumber) and the query will automatically change the
True/False field in that record to 'True'. The problem is,
if I have several records that I know the Unique ID for
that I want to change to true, I have to run the query once
for each record I want to update. Normally it would be
easier to just go to the table, find the records and click
the box etc...but the table has MANY records and I would
like to be able to run the query, and it keeps asking for
Unique ID's until the user closes or cancels the query.
Any and all help is appreciated!

osmondb
Click to expand...


.
Click to expand...
 
You need to set a refernce to the DAO library. Open VBE, click Tools |
References. Find the Microsoft DAO 3.x library and check it. Close
References window.

--

Ken Snell
<MS ACCESS MVP>

osmondb said:
I entered this code below and I changed it around to suit
my table and query (only had to change field names in the
SQL part), but when I try and run it I'm getting an error
saying that the User-Defined Type is not defined on this line

Dim dbs As DAO.Database

I have absolutely NO knowledge of VB, but I am quite
familiar with C++ and it looks to me like either the dbs
type isn't a valid type, or the DAO struct (or class object
whatever it may be) isn't defined. Can anyone help me out
with this error? I've tried looking in help files etc...but
it doesn't make much sense to me as this is the first time
I've ever used VB.
-----Original Message-----
You'd need to do this via VBA code. I would use the SQL of the query in the
code for running the query, instead of "calling" the query.
Something like this, perhaps:

Public Sub RunQueryUntilDone()
Dim strSQL As String
Dim dbs As DAO.Database
Dim strID As String
Set dbs = CurrentDb
strID = "starting"
Do
strID = InputBox("Enter ID or enter nothing to stop:")
If strID <> "" Then
strSQL = "UPDATE TableName SET TrueFalseField = True " & _
"WHERE [IDField]=" & CLng(strID) & ";"
dbs.Execute strSQL, dbFailOnError
Else
MsgBox "No value entered. Query ending now."
Exit Do
End If
Loop
dbs.Close
Set dbs = Nothing
End Sub

--

Ken Snell
<MS ACCESS MVP>


Hi all,

I was just wondering if it is possible to 'loop' a query
that I've created. Basically, the query is designed to let
the user input a record's "Unique ID" (set up with
AutoNumber) and the query will automatically change the
True/False field in that record to 'True'. The problem is,
if I have several records that I know the Unique ID for
that I want to change to true, I have to run the query once
for each record I want to update. Normally it would be
easier to just go to the table, find the records and click
the box etc...but the table has MANY records and I would
like to be able to run the query, and it keeps asking for
Unique ID's until the user closes or cancels the query.
Any and all help is appreciated!

osmondb
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top