Random Sample by Person automation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am getting back into Access development and programming and of course I'm
in over my head with no one to turn to.

I'm creating a database in Access2000 to track an audting function. The
data will be imported from an outside database on a weekly basis. Then the
data must be randomly sampled per Approval Name ie 10% of each person's
claims per week must be reviewed. I've managed to write the queries to find
the sample per person and to flag them for auditing, but it does each person
one at a time. I have a form that does that quite nicely. It takes
information from the RawData table, groups based on fields, assigns random
numbers and selects the 10% and makes a table Xtmp. The second query takes
the information in the Xtmp table and updates the corresponding record in
the RawData table.

BUT I've been struggling with writing something that will do it for all
Specialists at once. I've tried writing an Event Procedure that will use the
MpApprvdBy name in the Specialist table to run the queries as SQL until all
people have been updated, but it gives me the generic error of "object
variable or with block variable not set". I've searched and searched and
don't know what silly stupid simple thing I'm doing wrong... or perhaps my
entire logic is flawed. I've hard coded some variables trying to get it to
work and that didn't make a difference.

Table

Below is my code! I appreciate any help!

Thanks

KJGinNC

Option Compare Database
Option Explicit

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String, strSQL2 As String, SpecName As String

Set cnn = CurrentProject.Connection

strSQL = "SELECT TOP 10 PERCENT RawData.ID, RawData.MpApprvdBy,
RawData.MpSrNum, RawData.Week" _
& "INTO xTmp FROM RawData" _
& "GROUP BY RawData.ID, RawData.MpApprvdBy, RawData.MpSrNum,
RawData.Week, RawData.ToAudit" _
& "HAVING (((RawData.MpApprvdBy) = [SpecName]) And ((RawData.Week) =
'22') And ((RawData.ToAudit) Is Null))" _
& "ORDER BY Rnd([ID])"
strSQL2 = "UPDATE xTmp INNER JOIN RawData ON xTmp.ID = RawData.ID SET
RawData.ToAudit = '1'"

rst.Open "Specialist", cnn, adOpenDynamic, adLockPessimistic
rst.MoveFirst
Do
SpecName = (rst![MpApprvdBy])
cnn.Execute strSQL
cnn.Execute strSQL2
rst.MoveNext

Loop Until rst.EOF


Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub
 
First you should use the randomize statement before using rnd() function.
Second, your sqlstr where you are putting in variables that change must be
outside the quotation marks like [specname]. it shoud be & me![specname].
Also you should be sure to use ' for strings and # for date as follows ...='"
& [SpecName]) & "' And ... Nothing needed for numbers. Also why is specname
assigned after you use the sqlstr? if you are going to use the variable in
the sqlstr then u must assign it a value before you assign the sqlstr a sql
string.

HTH
Martin J

KJGinNC said:
Hi,

I am getting back into Access development and programming and of course I'm
in over my head with no one to turn to.

I'm creating a database in Access2000 to track an audting function. The
data will be imported from an outside database on a weekly basis. Then the
data must be randomly sampled per Approval Name ie 10% of each person's
claims per week must be reviewed. I've managed to write the queries to find
the sample per person and to flag them for auditing, but it does each person
one at a time. I have a form that does that quite nicely. It takes
information from the RawData table, groups based on fields, assigns random
numbers and selects the 10% and makes a table Xtmp. The second query takes
the information in the Xtmp table and updates the corresponding record in
the RawData table.

BUT I've been struggling with writing something that will do it for all
Specialists at once. I've tried writing an Event Procedure that will use the
MpApprvdBy name in the Specialist table to run the queries as SQL until all
people have been updated, but it gives me the generic error of "object
variable or with block variable not set". I've searched and searched and
don't know what silly stupid simple thing I'm doing wrong... or perhaps my
entire logic is flawed. I've hard coded some variables trying to get it to
work and that didn't make a difference.

Table

Below is my code! I appreciate any help!

Thanks

KJGinNC

Option Compare Database
Option Explicit

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String, strSQL2 As String, SpecName As String

Set cnn = CurrentProject.Connection

strSQL = "SELECT TOP 10 PERCENT RawData.ID, RawData.MpApprvdBy,
RawData.MpSrNum, RawData.Week" _
& "INTO xTmp FROM RawData" _
& "GROUP BY RawData.ID, RawData.MpApprvdBy, RawData.MpSrNum,
RawData.Week, RawData.ToAudit" _
& "HAVING (((RawData.MpApprvdBy) = [SpecName]) And ((RawData.Week) =
'22') And ((RawData.ToAudit) Is Null))" _
& "ORDER BY Rnd([ID])"
strSQL2 = "UPDATE xTmp INNER JOIN RawData ON xTmp.ID = RawData.ID SET
RawData.ToAudit = '1'"

rst.Open "Specialist", cnn, adOpenDynamic, adLockPessimistic
rst.MoveFirst
Do
SpecName = (rst![MpApprvdBy])
cnn.Execute strSQL
cnn.Execute strSQL2
rst.MoveNext

Loop Until rst.EOF


Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub
Click to expand...
 
Martin,

I'm not sure what you mean by "use the randomize statement before using the
rnd() function." I just took the sql from the query that works and pasted
it into the procedure. I then broke it up into readable pieces. I thought
the rnd() was the randomize statement?

I'll work on getting the variables outside of the string. Maybe I'll just
reference the entire sql statement in the loop?

As for the specname being assigned after I used the sqlstr, I'm not sure. I
thought I was getting the first specname, then entering the loop, executing
the sqlstr's and then getting the next specname, and then repeating the loop.
But since it's not working I'm obviously doing something (many things) wrong.

Thanks for the first response!

KJGinNC

Martin J said:
First you should use the randomize statement before using rnd() function.
Second, your sqlstr where you are putting in variables that change must be
outside the quotation marks like [specname]. it shoud be & me![specname].
Also you should be sure to use ' for strings and # for date as follows ...='"
& [SpecName]) & "' And ... Nothing needed for numbers. Also why is specname
assigned after you use the sqlstr? if you are going to use the variable in
the sqlstr then u must assign it a value before you assign the sqlstr a sql
string.

HTH
Martin J

KJGinNC said:
Hi,

I am getting back into Access development and programming and of course I'm
in over my head with no one to turn to.

I'm creating a database in Access2000 to track an audting function. The
data will be imported from an outside database on a weekly basis. Then the
data must be randomly sampled per Approval Name ie 10% of each person's
claims per week must be reviewed. I've managed to write the queries to find
the sample per person and to flag them for auditing, but it does each person
one at a time. I have a form that does that quite nicely. It takes
information from the RawData table, groups based on fields, assigns random
numbers and selects the 10% and makes a table Xtmp. The second query takes
the information in the Xtmp table and updates the corresponding record in
the RawData table.

BUT I've been struggling with writing something that will do it for all
Specialists at once. I've tried writing an Event Procedure that will use the
MpApprvdBy name in the Specialist table to run the queries as SQL until all
people have been updated, but it gives me the generic error of "object
variable or with block variable not set". I've searched and searched and
don't know what silly stupid simple thing I'm doing wrong... or perhaps my
entire logic is flawed. I've hard coded some variables trying to get it to
work and that didn't make a difference.

Table

Below is my code! I appreciate any help!

Thanks

KJGinNC

Option Compare Database
Option Explicit

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String, strSQL2 As String, SpecName As String

Set cnn = CurrentProject.Connection

strSQL = "SELECT TOP 10 PERCENT RawData.ID, RawData.MpApprvdBy,
RawData.MpSrNum, RawData.Week" _
& "INTO xTmp FROM RawData" _
& "GROUP BY RawData.ID, RawData.MpApprvdBy, RawData.MpSrNum,
RawData.Week, RawData.ToAudit" _
& "HAVING (((RawData.MpApprvdBy) = [SpecName]) And ((RawData.Week) =
'22') And ((RawData.ToAudit) Is Null))" _
& "ORDER BY Rnd([ID])"
strSQL2 = "UPDATE xTmp INNER JOIN RawData ON xTmp.ID = RawData.ID SET
RawData.ToAudit = '1'"

rst.Open "Specialist", cnn, adOpenDynamic, adLockPessimistic
rst.MoveFirst
Do
SpecName = (rst![MpApprvdBy])
cnn.Execute strSQL
cnn.Execute strSQL2
rst.MoveNext

Loop Until rst.EOF


Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub
Click to expand...
Click to expand...
 
Look at help for rnd It will explain it for you.

This doesn't work ...
SpecName = (rst![MpApprvdBy])
cnn.Execute strSQL
....
Strsql hasn't changed at all. Specname has but strsql needs to be reassigned
with the sql string including the new specname.

HTH
Martin J

KJGinNC said:
Martin,

I'm not sure what you mean by "use the randomize statement before using the
rnd() function." I just took the sql from the query that works and pasted
it into the procedure. I then broke it up into readable pieces. I thought
the rnd() was the randomize statement?

I'll work on getting the variables outside of the string. Maybe I'll just
reference the entire sql statement in the loop?

As for the specname being assigned after I used the sqlstr, I'm not sure. I
thought I was getting the first specname, then entering the loop, executing
the sqlstr's and then getting the next specname, and then repeating the loop.
But since it's not working I'm obviously doing something (many things) wrong.

Thanks for the first response!

KJGinNC

Martin J said:
First you should use the randomize statement before using rnd() function.
Second, your sqlstr where you are putting in variables that change must be
outside the quotation marks like [specname]. it shoud be & me![specname].
Also you should be sure to use ' for strings and # for date as follows ...='"
& [SpecName]) & "' And ... Nothing needed for numbers. Also why is specname
assigned after you use the sqlstr? if you are going to use the variable in
the sqlstr then u must assign it a value before you assign the sqlstr a sql
string.

HTH
Martin J

KJGinNC said:
Hi,

I am getting back into Access development and programming and of course I'm
in over my head with no one to turn to.

I'm creating a database in Access2000 to track an audting function. The
data will be imported from an outside database on a weekly basis. Then the
data must be randomly sampled per Approval Name ie 10% of each person's
claims per week must be reviewed. I've managed to write the queries to find
the sample per person and to flag them for auditing, but it does each person
one at a time. I have a form that does that quite nicely. It takes
information from the RawData table, groups based on fields, assigns random
numbers and selects the 10% and makes a table Xtmp. The second query takes
the information in the Xtmp table and updates the corresponding record in
the RawData table.

BUT I've been struggling with writing something that will do it for all
Specialists at once. I've tried writing an Event Procedure that will use the
MpApprvdBy name in the Specialist table to run the queries as SQL until all
people have been updated, but it gives me the generic error of "object
variable or with block variable not set". I've searched and searched and
don't know what silly stupid simple thing I'm doing wrong... or perhaps my
entire logic is flawed. I've hard coded some variables trying to get it to
work and that didn't make a difference.

Table

Below is my code! I appreciate any help!

Thanks

KJGinNC

Option Compare Database
Option Explicit

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String, strSQL2 As String, SpecName As String

Set cnn = CurrentProject.Connection

strSQL = "SELECT TOP 10 PERCENT RawData.ID, RawData.MpApprvdBy,
RawData.MpSrNum, RawData.Week" _
& "INTO xTmp FROM RawData" _
& "GROUP BY RawData.ID, RawData.MpApprvdBy, RawData.MpSrNum,
RawData.Week, RawData.ToAudit" _
& "HAVING (((RawData.MpApprvdBy) = [SpecName]) And ((RawData.Week) =
'22') And ((RawData.ToAudit) Is Null))" _
& "ORDER BY Rnd([ID])"
strSQL2 = "UPDATE xTmp INNER JOIN RawData ON xTmp.ID = RawData.ID SET
RawData.ToAudit = '1'"

rst.Open "Specialist", cnn, adOpenDynamic, adLockPessimistic
rst.MoveFirst
Do
SpecName = (rst![MpApprvdBy])
cnn.Execute strSQL
cnn.Execute strSQL2
rst.MoveNext

Loop Until rst.EOF


Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the help! I complete forgot that the rnd() from the SQL query
might conflict with the rnd() in VB. I'm going to have to completely rethink
this.

Thanks!

KJGinNC

Martin J said:
Look at help for rnd It will explain it for you.

This doesn't work ...
SpecName = (rst![MpApprvdBy])
cnn.Execute strSQL
...
Strsql hasn't changed at all. Specname has but strsql needs to be reassigned
with the sql string including the new specname.

HTH
Martin J

KJGinNC said:
Martin,

I'm not sure what you mean by "use the randomize statement before using the
rnd() function." I just took the sql from the query that works and pasted
it into the procedure. I then broke it up into readable pieces. I thought
the rnd() was the randomize statement?

I'll work on getting the variables outside of the string. Maybe I'll just
reference the entire sql statement in the loop?

As for the specname being assigned after I used the sqlstr, I'm not sure. I
thought I was getting the first specname, then entering the loop, executing
the sqlstr's and then getting the next specname, and then repeating the loop.
But since it's not working I'm obviously doing something (many things) wrong.

Thanks for the first response!

KJGinNC

Martin J said:
First you should use the randomize statement before using rnd() function.
Second, your sqlstr where you are putting in variables that change must be
outside the quotation marks like [specname]. it shoud be & me![specname].
Also you should be sure to use ' for strings and # for date as follows ...='"
& [SpecName]) & "' And ... Nothing needed for numbers. Also why is specname
assigned after you use the sqlstr? if you are going to use the variable in
the sqlstr then u must assign it a value before you assign the sqlstr a sql
string.

HTH
Martin J

:

Hi,

I am getting back into Access development and programming and of course I'm
in over my head with no one to turn to.

I'm creating a database in Access2000 to track an audting function. The
data will be imported from an outside database on a weekly basis. Then the
data must be randomly sampled per Approval Name ie 10% of each person's
claims per week must be reviewed. I've managed to write the queries to find
the sample per person and to flag them for auditing, but it does each person
one at a time. I have a form that does that quite nicely. It takes
information from the RawData table, groups based on fields, assigns random
numbers and selects the 10% and makes a table Xtmp. The second query takes
the information in the Xtmp table and updates the corresponding record in
the RawData table.

BUT I've been struggling with writing something that will do it for all
Specialists at once. I've tried writing an Event Procedure that will use the
MpApprvdBy name in the Specialist table to run the queries as SQL until all
people have been updated, but it gives me the generic error of "object
variable or with block variable not set". I've searched and searched and
don't know what silly stupid simple thing I'm doing wrong... or perhaps my
entire logic is flawed. I've hard coded some variables trying to get it to
work and that didn't make a difference.

Table

Below is my code! I appreciate any help!

Thanks

KJGinNC

Option Compare Database
Option Explicit

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String, strSQL2 As String, SpecName As String

Set cnn = CurrentProject.Connection

strSQL = "SELECT TOP 10 PERCENT RawData.ID, RawData.MpApprvdBy,
RawData.MpSrNum, RawData.Week" _
& "INTO xTmp FROM RawData" _
& "GROUP BY RawData.ID, RawData.MpApprvdBy, RawData.MpSrNum,
RawData.Week, RawData.ToAudit" _
& "HAVING (((RawData.MpApprvdBy) = [SpecName]) And ((RawData.Week) =
'22') And ((RawData.ToAudit) Is Null))" _
& "ORDER BY Rnd([ID])"
strSQL2 = "UPDATE xTmp INNER JOIN RawData ON xTmp.ID = RawData.ID SET
RawData.ToAudit = '1'"

rst.Open "Specialist", cnn, adOpenDynamic, adLockPessimistic
rst.MoveFirst
Do
SpecName = (rst![MpApprvdBy])
cnn.Execute strSQL
cnn.Execute strSQL2
rst.MoveNext

Loop Until rst.EOF


Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top