Random selection of EmpNames

[christrawick]

Joe - I'm not being very successful at figuring out how to get the results of the code you suggested, into a form/report that can be viewed/printed. How do I direct the output of the Text1.(LostFocus) event to a form/report that I can view/print? Learning VB is a heck of a lot more difficult that anyone says.

Thanks

 

[lovell811]

that's our problem then what value are you putting in the text box??

----------------
Joe

 

[christrawick]

I'm selecting an EmployerName from the ComboBox and then typing 50 in the Text1 box (to represent 50%) I have 6 records in the Access db under that specific EmployerName, so it should return a random 3 records from within it.

 

[lovell811]

put a program stop on the formula for percent then push F8 to step past it and see what the value is. ----------------
Joe

 

[christrawick]

Both are 0 still. ?

 

[lovell811]

try this:

percent = Val(text1.text)
percent = percent / 100

lets see what that does.
----------------
Joe

 

[christrawick]

Made the change - Percent and Count still equalling 0. They should have subcontracted this project out!

 

[lovell811]

I'm not sure what's going on then. Try this:

Dim percent as LONG - I'm changing it from an integer

before the loop put this:

Set mcnn = New ADODB.Connection
mcnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\autochooser.mdb ;Persist Security Info=False"
mcnn.Open

Set dbs = New ADODB.Recordset
dbs.CursorType = adOpenKeyset
dbs.LockType = adLockOptimistic
dbs.Source = "SELECT * FROM tblEmployers" & _
"WHERE EmployerName = '"& EmplName & ""
Set dbs.ActiveConnection = mcnn
dbs.Open
percent = text1.text / 100
percent = dbs.recordcount * pecent
dbs.close
mcnn.close

or you can leave mcnn open and take that out of the loop just this you can take out---:
Set mcnn = New ADODB.Connection
mcnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\autochooser.mdb ;Persist Security Info=False"
mcnn.Open


but only if you don't close it!! this should do the trick
----------------
Joe

 

[christrawick]

Last post Joe, I swear. I'm about to throw this *($)# out the 2nd floor window. Here is the entire code, did I place it where you are telling me -

-------------------------------------
Public mcnn As ADODB.Connection
Private dbs As ADODB.Recordset




Private Sub Command1_Click()
Dim EmplName As String
Dim percent As Long
'Dim Percent As Integer
Dim count As Integer
Dim random As Integer

count = 0
'Commented out 2:13pm >>Percent = (Text1.Text / 100)
percent = Val(Text1.Text)
percent = percent / 100
EmplName = Combo1.Text


Set mcnn = New ADODB.Connection
mcnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\autochooser.mdb ;Persist Security Info=False"
mcnn.Open

Set dbs = New ADODB.Recordset
dbs.CursorType = adOpenKeyset
dbs.LockType = adLockOptimistic
dbs.Source = "SELECT * FROM tblEmployees" & _
"WHERE EmployerName = '" & EmplName & ""
Set dbs.ActiveConnection = mcnn
dbs.Open
percent = Text1.Text / 100
percent = dbs.RecordCount * percent
dbs.Close
mcnn.Close



'Commented out 11:53 >>While Not count = Percent
Do While percent <> count

Randomize
random = (Rnd * 100)

Set mcnn = New ADODB.Connection
mcnn.ConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & App.Path & &quot;\autochooser.mdb ;Persist Security Info=False&quot;
mcnn.Open

Set dbs = New ADODB.Recordset
dbs.CursorType = adOpenKeyset
dbs.LockType = adLockOptimistic
dbs.Source = &quot;SELECT * FROM tblEmployees&quot; & _
&quot;WHERE EmployerName = '&quot; & EmplName & &quot;' and fieldauto = &quot; & random & &quot;&quot;
Set dbs.ActiveConnection = mcnn
dbs.Open
If dbs.RecordCount > 0 Then
count = count + 1
'Display all your info here
End If
dbs.Close
mcnn.Close
'Commented out 11:53 >>Wend
Loop

End Sub

Private Sub Command2_Click()
Form4.Hide
Form3.Show
End Sub

Private Sub Form_Load()
'The following sections populate a ComboBox with results from an Access query
Dim Conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

With Conn
.CursorLocation = adUseClient
.ConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & App.Path & &quot;\autochooser.mdb ;Persist Security Info=False&quot;
.Open
End With

With cmd
Set .ActiveConnection = Conn
.CommandText = &quot;qryEmployerNames&quot; 'Name of the access query
.CommandType = adCmdStoredProc
'Add a parameter if needed
'.Parameters.Append .CreateParameter(&quot;Param&quot;, adInteger, adParamInput, 6)
'.Parameters(&quot;Param&quot.Value = variable
End With
rs.Open cmd, , adOpenDynamic, adLockOptimistic
rs.MoveFirst

Do
Form4.Combo1.AddItem rs![EmployerName]
rs.MoveNext
Loop Until rs.EOF
End Sub

 

[lovell811]

try that and see what happens, then go ahead and post a few more times, I know what you mean about throwing it out the window, I've thought about that too, on a lot of occasions. But what I hadn't thought about earlier was the data type integer rounds down so that is why it was zero, so I'm doing a little research looking for the right data type. But also we needed to know how many records were there. so just give it a little while longer! ----------------
Joe

 

[lovell811]

instead of long try: currency or decimal
----------------
Joe

 

[lovell811]

I'm pretty sure that is it change it to a DECIMAL!!! ----------------
Joe

 

[christrawick]

Syntax error in &quot;From clause&quot;

dbs.Open before the Loop.

On the Do While, the Percent is being populated now with .5 (50%), so it seems that we have made progress..

 

[lovell811]

Replace this:
&quot;WHERE EmployerName = '&quot; & EmplName & &quot;&quot;

With this:
&quot;WHERE EmployerName = '&quot; & EmplName & &quot;'&quot;

forgot the double quote!
----------------
Joe

 

[lovell811]

Replace this:
&quot;WHERE EmployerName = '&quot; & EmplName & &quot;&quot;

With this:
&quot;WHERE EmployerName = '&quot; & EmplName & &quot;'&quot;

forgot the single quote!
----------------
Joe

 

[christrawick]

It's STILL hanging up on the dbs.Open just before the Do While!! AHHHHHHHHHHGGGGGGGGGGGGGGGG!!!!!!!!!!

 

[christrawick]

Joe - I appreciate your tremendous help with this app. It's obviously not meant to work, huh?

-Chris

 

[CCLINT]

I only took a very shoert look at the SELECT statement and don't mean to get into this further at this point. I noticed an error in the statement:
&quot;SELECT * FROM tblEmployees&quot; & _
&quot;WHERE EmployerName .....

Add a space after tblEmployees so it reads:
&quot;SELECT * FROM tblEmployees &quot; & _
&quot;WHERE EmployerName .....

There is an easier way of getting random records under ADO but I do not know if that is desired here, and haven't seen the original question (which thread is it under?).
Don't respond to me if I should stay out....

 

[lovell811]

CCLINT,

thread709-315954

and yeah I there are some problems with the code, I wrote it in a hurry, I haven't had a lot of time to do this. I've been trying to help him in my free time during work. I couldn't even spare the time to write a small program for this. your input would be a lot of help, as I can't dedicate all my time to this. Thanks! ----------------
Joe