do while loop problem

[mikarsen]

Hi I'm creating an access program that will automatically generate random samples.

Samples are generated by using an SQL query and the resulting query is appended to a table.

The sample generations works fine by setting criteria thru a textbox inside a form. However, I'm thinking if i could also automate the criterias inputed in the sql query.

Old method:
users input paramater and generate samples per branch on at a time...

My plan:
generate all samples for all offices base on pre define samples with just a click of a button.

Ex... if there are 30 branches... and required sample is 5 per branch...the total rows for the resulting table would be 150.

here's what i have in mind, I pasted the code below to the button...however the program doesnt seem to follow the criterias...

Thanks in advance for those who could help me.... or could have any suggestions.
Code:
'three branches with 5 samples each

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSql As String
Dim strTopVal As String

Dim strcrit As String
Dim count As Integer

Set db = CurrentDb
'strTopVal = InputBox("Enter number of samples:")

count = 0

strSql = "SELECT TOP 5 BRANCH, CR, SIN, MEDIUM, ST, RECPTDATE, LSTACT, RESDTE, TYPE" & _
" FROM BC " & strcrit & " " & _
" ORDER BY RandomNumber([SIN]);"

Do Until count = 2
DoCmd.SetWarnings False
db.QueryDefs.Delete "MyTopQuery"
if count=0 then strcrit=" branch='branchA'"
if count=1 then strcrit=" branch='branchB'"
if count=2 then strcrit=" branch='branchC'"
Set qdf = db.CreateQueryDef("MyTopQuery", strSql)
DoCmd.OpenQuery "Query7"
DoCmd.SetWarnings True
count = count + 1
Loop
End Sub

'query7 appends the resulting samples (5 per branch) to the main table per each loop

 

[ajetrumpet]

My plan:
generate all samples for all offices base on pre define samples with just a click of a button.

Ex... if there are 30 branches... and required sample is 5 per branch...the total rows for the resulting table would be 150.

here's what i have in mind, I pasted the code below to the button...however the program doesnt seem to follow the criterias...

I would help if you give an example of "how" the program is not following the criterias. Getting error messages? Debugging problems? Got an error number to give?

(Change the declared string called count to another word. That's probably a reserved word by the program.

 

[mikarsen]

Hi i updated the code...based on the other's recommendation..

however, the results are not consistent
i need to have 10 samples per branch...however, sometimes
the program gives me... seven branches with 10 samples each and one branch with 20 samples

the total samples (90) are always correct...however the distribution doesnt seem to be consistent.

thanks in advance for those who can help me with this project. =)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSql As String
Dim strTopVal As String

Dim strcrit As String
Dim strcrit2 As String
Dim intcount As Integer

Set db = CurrentDb
'strTopVal = InputBox("Enter number of samples:")

intcount = 0
'strcrit2 = Forms![Mainform]![cbocustom]

Do Until intcount = 9
DoCmd.SetWarnings False
db.QueryDefs.Delete "MyTopQuery"
strcrit2 = Forms![Mainform]![cbocustom]
If intcount = 0 Then strcrit = " where branch='BALAGTAS' "
If intcount = 1 Then strcrit = " where branch='BALINTAWAK' "
If intcount = 2 Then strcrit = " where branch='BALIWAG' "
If intcount = 3 Then strcrit = " where branch='COMMONWEALTH' "
If intcount = 4 Then strcrit = " where branch='MALABON' "
If intcount = 5 Then strcrit = " where branch='MALOLOS' "
If intcount = 6 Then strcrit = " where branch='NOVALICHES' "
If intcount = 7 Then strcrit = " where branch='STAMARIA' "
If intcount = 8 Then strcrit = " where branch='VALENZUELA' "
strSql = "SELECT TOP 10 BRANCH, CR, SIN, MEDIUM, ST, RECPTDATE, LSTACT, RESDTE, TYPE, MARK " & _
" FROM BC " & strcrit & strcrit2 & " " & _
" ORDER BY RandomNumber([SIN]);"
Set qdf = db.CreateQueryDef("MyTopQuery", strSql)
DoCmd.OpenQuery "Query7"
DoCmd.SetWarnings True
intcount = intcount + 1
Loop

Me.test1_subform.Requery
End Sub