need help fast running out of time

G

gizmogeek

Registered User.
Local time
Yesterday, 21:41
Joined
Oct 3, 2007
Messages
95
Hi All -

I need some direction. I don't know to much about programing or adding code to MS Access.

I need to create a report containing a specific number of randomized records from each catagory. However I do not know which way to go with this.

I have tried vb coding within the report but it doesn't run. I tried top records in queries.

Some direction is needed.
When I go to run the report I get a runtime error 424.

Here is my code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'initialize ASP RND() function
Randomize (5)
intRandomNumber = Int(1000 * Rnd) + 1

'Return 15 random records
strSQL = _
"SELECT TOP 5 INVENTORY_1,CHEESE, RND(" & -1 * (intRandomNumber) & "*Catagory)" & _
"FROM INVENTORY_1 " & _
"CATAGORY BY 5"

Set objRS = objConn.Execute(strSQL)


End Sub


It stops on Set objRS = objConn.Execute(strSQL)
 
I'm not sure where you intended to go with this code, but to clarify, the .Execute member of the ADODB Connection object is for the execution of an Action query, not for the return of data to a RecordSet.

Assuming that INVENTORY_1.CATAGORY is the field with the numeric key field from which you are attempting to make the randomized selection, the following code demonstrates how a randomized set of 15 records may be pulled from your source database and returned to the ADODB RecordSet object:
Code: 
Dim vRnd(1 To 15) As String
Dim X As Long, Y As Long

Randomize -Now

For Y = 1 To 15
    vRnd(Y) = CStr(Int(1000 * Rnd) + 1)
    For X = 1 To Y - 1
        If vRnd(X) = vRnd(Y) Then Y = Y - 1: Exit For
    Next X
Next Y

objRS.Open "SELECT INVENTORY_1.CHEESE " _
    & "FROM INVENTORY_1 " _
    & "WHERE INVENTORY_1.CATAGORY IN (" & Join(vRnd, ",") & ")", _
    objConn

Please note: The above code assumes that objConn has been instantiated as a New ADODB.Connection, with the connection opened to your DataSource, and that objRS has been instantiated as a New ADODB.RecordSet.
 
Thank you for your reply.

When I go to compile the code that you have provided (which I really appreciate) it comes up with error on:
Randomize -Now

I have added this as an event on my catagory header. Should I be doing it some other way?

Thanks!!
 
There are no compile errors when I do it with Access 2000. What version of Microsoft Access are you running? Also, what is the specific error message that is returned when you attempt to compile the code?
 
Thank you Bytemyzer!

Okay. I corrected MY mistake but now I am back to the original runtime error '424' object required.

Please help?
 
You apparently did not read my earlier note:
ByteMyzer said:
Please note: The above code assumes that objConn has been instantiated as a New ADODB.Connection, with the connection opened to your DataSource, and that objRS has been instantiated as a New ADODB.RecordSet.
Click to expand...

So, since you apparently did NOT do this in any other part of your database, I recommend adding the following statements to the beginning of the code:
Code: 
Dim objConn As ADODB.Connection
Dim objRS As ADODB.RecordSet

Set objConn = New ADODB.Connection
Set objRS = New ADODB.RecordSet

This still leaves two open questions:
* What version of Microsoft Access are you running?
* What is your intention once you have opened the recordset?
 
I am so missing something...

I added the
Dim objConn As ADODB.Connection
Dim objRS As ADODB.RecordSet

Set objConn = New ADODB.Connection
Set objRS = New ADODB.RecordSet

compiled it and ran it and now I get runtime erro 3709-
The connection cannot be used to perform this operation. It is either closed or invalid in this context.

The debug stops at:
objRS.Open "SELECT INVENTORY_1.CHEESE " _
& "FROM INVENTORY_1 " _
& "WHERE INVENTORY_1.CATAGORY IN (" & Join(vRnd, ",") & ")", _
objConn

Here is the full code:
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset
Dim vRnd(1 To 15) As String
Dim X As Long, Y As Long

Randomize -Now

For Y = 1 To 15
vRnd(Y) = CStr(Int(1000 * Rnd) + 1)
For X = 1 To Y - 1
If vRnd(X) = vRnd(Y) Then Y = Y - 1: Exit For
Next X
Next Y

objRS.Open "SELECT INVENTORY_1.CHEESE " _
& "FROM INVENTORY_1 " _
& "WHERE INVENTORY_1.CATAGORY IN (" & Join(vRnd, ",") & ")", _
objConn

End Sub
 
Nowhere have you set the connection -

before you USE objConn you have to tell it what the connection is (after creating it with Set objConn = New ADODB.Connection. If you are using the current database you can simply use:

objConn = CurrentProject.Connection
 
Not sure I understand then.

Isn't this setting the connection and if not how can I do it?

Dim objConn As ADODB.Connection
Dim objRS As ADODB.RecordSet

Set objConn = New ADODB.Connection
Set objRS = New ADODB.RecordSet
 
gizmogeek said:
Not sure I understand then.

Isn't this setting the connection and if not how can I do it?

Dim objConn As ADODB.Connection
Dim objRS As ADODB.RecordSet

Set objConn = New ADODB.Connection
Set objRS = New ADODB.RecordSet
Click to expand...

No, that isn't setting the connection. It is instantiating the objects. Add it here:
Code: 
Dim objConn As ADODB.Connection
Dim objRS As ADODB.RecordSet

Set objConn = New ADODB.Connection
Set objRS = New ADODB.RecordSet
[b]objConn = CurrentProject.Connection[/b]
 
Thank you but I've added it and still come up with:
compiled it and ran it and now I get runtime erro 3709-
The connection cannot be used to perform this operation. It is either closed or invalid in this context.

What am I doing wrong?
Here's the code once again. I don't have to add a datasource within the os right?

The debug stops at:
objRS.Open "SELECT INVENTORY_1.CHEESE " _
& "FROM INVENTORY_1 " _
& "WHERE INVENTORY_1.CATAGORY IN (" & Join(vRnd, ",") & ")", _
objConn


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)


Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset
objConn = CurrentProject.Connection

Dim vRnd(1 To 15) As String
Dim X As Long, Y As Long

Randomize -Now

For Y = 1 To 15
vRnd(Y) = CStr(Int(1000 * Rnd) + 1)
For X = 1 To Y - 1
If vRnd(X) = vRnd(Y) Then Y = Y - 1: Exit For
Next X
Next Y

objRS.Open "SELECT INVENTORY_1.CHEESE " _
& "FROM INVENTORY_1 " _
& "WHERE INVENTORY_1.CATAGORY IN (" & Join(vRnd, ",") & ")", _
objConn

End Sub
 
I think you might need to specify the type and lock:
Code: 
objRS.Open "SELECT INVENTORY_1.CHEESE FROM INVENTORY_1 " & _
"WHERE INVENTORY_1.CATAGORY IN (" & Join(vRnd, ",") & ")", objConn, [color=red]adOpenDynamic, adLockOptimistic[/color]
 
No dice still same runtime error.

Please advise????

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)


Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset
objConn = CurrentProject.Connection

Dim vRnd(1 To 15) As String
Dim X As Long, Y As Long

Randomize -Now
objRS.Open "SELECT INVENTORY_1.CHEESE FROM INVENTORY_1 " & _
"WHERE INVENTORY_1.CATAGORY IN (" & Join(vRnd, ",") & ")", objConn, adOpenDynamic, adLockOptimistic
For Y = 1 To 15
vRnd(Y) = CStr(Int(1000 * Rnd) + 1)
For X = 1 To Y - 1
If vRnd(X) = vRnd(Y) Then Y = Y - 1: Exit For
Next X
Next Y

objRS.Open "SELECT INVENTORY_1.CHEESE " _
& "FROM INVENTORY_1 " _
& "WHERE INVENTORY_1.CATAGORY IN (" & Join(vRnd, ",") & ")", _
objConn

End Sub
 
Not sure, as I think I'm getting disoriented too, but you have the same code in as before but you put the extra in, so change to:

Code: 
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)


Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset
objConn = CurrentProject.Connection

Dim vRnd(1 To 15) As String
Dim X As Long, Y As Long

Randomize -Now
   For Y = 1 To 15
      vRnd(Y) = CStr(Int(1000 * Rnd) + 1)
         For X = 1 To Y - 1
            If vRnd(X) = vRnd(Y) Then Y = Y - 1: Exit For
         Next X
   Next Y

[color=red]objRS.Open "SELECT INVENTORY_1.CHEESE FROM INVENTORY_1 " & _
"WHERE INVENTORY_1.CATAGORY IN (" & Join(vRnd, ",") & ")", objConn, adOpenDynamic, adLockOptimistic
[/color]
End Sub
 
first of all thank you for your help!

Secondly, it didn't work once again same error 3709

If I wasn't worried about my job I've paid someone else to do it at this point.

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)


Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset
objConn = CurrentProject.Connection

Dim vRnd(1 To 15) As String
Dim X As Long, Y As Long

Randomize -Now
For Y = 1 To 15
vRnd(Y) = CStr(Int(1000 * Rnd) + 1)
For X = 1 To Y - 1
If vRnd(X) = vRnd(Y) Then Y = Y - 1: Exit For
Next X
Next Y

objRS.Open "SELECT INVENTORY_1.CHEESE FROM INVENTORY_1 " & _
"WHERE INVENTORY_1.CATAGORY IN (" & Join(vRnd, ",") & ")", objConn, adOpenDynamic, adLockOptimistic

End Sub
 
I've added the code to the detail section of my report and no errors. However, it does not select a random number of records????

All records come up?
 
any way you can either post the db or, if too large, email it.
 
I have attached the file.

This is what I was trying to accomplish.

1. 15 random items chosen from the Category CHEESE and DOUGH

2. 15 random item chosen from the Category SAUCE MEAT TOPPINGS and FZPIZ

3. 5 random from BAKERY, COND, ITAL, CP, MISC and POTATO

4. 5 random from DRINK, EQUIP, FRUIT, HARDWA, PAPER and ACCES



The report that is generated should have a list of 40 items in total in a checklist format… with Item Number and Description populated.


DATE:


Item Number Description #on hand #in system
 

Attachments

Well, the problem has to do with the randomizing function but I am at a loss as to how to make it do the things you want.
 
Bob thank you so much for your input.

I couldn't figure a way either and I've been working on this one project for 14 plus hours straight.

I guess I have to give in but how to do it gracefully and save face.

Thanks again!!
 
You must log in or register to reply here.

Similar threads

MajP
Randomize Not Working
2
Replies
25
Views
1,254
Pat Hartman
P
R
Query is fast to run, but very slow to export results
Replies
14
Views
1,184
Rudolph583
R
essaytee
Why messagebox appears then closes on successful run of DoCmd.OutputTo....pdf
Replies
5
Views
397
Mark_
M
zelarra821
Solved Loop through records in a table that gives an error
Replies
4
Views
435
zelarra821
zelarra821
K
Solved strSql - Object Required
Replies
11
Views
688
Minty
Minty

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom