Query recordset 2

[draegar]

I am using access 2000.

I have a form that displays equipment data from an equipment data table. On the form is a command button that will display any open work orders (from a separate table) existing for a particular piece of equipment.

I want to be able to disable the command button if no work orders exist but am having trouble figuring out how to implement it.

I have tried to open a recordset based on an existing query;

dim db as dao.database
dim rs as dao.recordset
set db = Currentdb()
set rs = OpenRecordset("QueryName")
msgbox, rs

I get a "Too few parameters, expected 1" error message. I've tried adding other parameters to the openrecordset command but it still doesn't work.


I have also tried using ADO but am getting an SQL error.

Is there an easier way to do this?

Thanks,

 

[Steve101]

The error message is actually referring to the SQL of the query that you are referring to, and not the OpenRecordset() command. You'll need to review the actual query itself to resolve this problem. If you publish the SQL, it will make it easier for someone to assist more.


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[glalsop]

Not sure if it is a typo or not, but the OpenRecordset method is a member of the database object.

set rs = db.OpenRecordset("QueryName")


-Gary

 

[draegar]

Here is the SQL from the query and the code I am trying to use:

SELECT tblWorkOrders.WorkOrder_ID, tblWorkOrders.Equip_ID, tblWorkOrders.ClosedBy
FROM tblEquipData INNER JOIN tblWorkOrders ON tblEquipData.Equip_ID = tblWorkOrders.Equip_ID
WHERE (((tblWorkOrders.WorkOrder_ID)=True) AND ((tblWorkOrders.Equip_ID)=[Forms]![frmEquipData]![Equip_ID]) AND ((tblWorkOrders.ClosedBy) Is Null));


Private Sub Form_Current()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("qryFromEquipDataForWOs")

If rs.RecordCount = 0 Then
cmdOpenWOs.Enabled = False
Else
cmdOpenWOs.Enabled = True
End If

End Sub

Thanks,

Bill Hughes

 

[Steve101]

Bill,

You are getting that error because the openrecordset method does not recognise that [Forms]![frmEquipData]![Equip_Id] as a control on an open form. Unfortunately, it simply regards the expression as an unresolved parameter (even if the form is open!); hense the error.

What you need to do is to evaluate the query before passing the SQL through to the openrecordset command. There are a number of ways to do this; here's one way, via some sample code. I'll assume that the referenced form is open, and the conrol is appropriately populated with a numeric value:

dim DB as DAO.Database
dim RS as DAO.Recordset
set DB = CurrentDB()

[green]'----------------------------------------------------------
'Retrieve queries SQL, and resolve Equip_Id value from form
'----------------------------------------------------------[/green]
sql = DB.QueryDefs("qryFromEquipDataForWOs").SQL
thisEquip_Id = [Forms]![frmEquipData]![Equip_ID]
sql = replace(sql,"[Forms]![frmEquipData]![Equip_ID]",thisEquip_Id)

[green]'----------------------------------------------------------
'Open recordset based on modified SQL
'----------------------------------------------------------[/green]
msgbox sql
Set RS = OpenRecordset(sql)
While not RS.eof
      'do your record based processing here
      RS.movenext
wend
RS.close
DB.close

As stated, there are other ways to do this, but this should suffice in the meantime. Hope its clear,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[draegar]

Steve,

Thanks so much for your help. I think I understand what this code is doing but I may have screwed up...

>>the conrol is populated with a numeric value:

Actually, I am using a text field because the Equipment ID's are alpha-numeric. Could this be what is causing the code to crash for me. Here are the SQL's before and after the REPLACE statement:


SELECT [tblWorkOrders].[Equip_ID], [tblWorkOrders].[ClosedBy]
FROM tblWorkOrders
WHERE ((([tblWorkOrders].[Equip_ID])=[Forms]![frmEquipData]![Equip_ID]) And (([tblWorkOrders].[ClosedBy]) Is Null));


SELECT [tblWorkOrders].[Equip_ID], [tblWorkOrders].[ClosedBy]
FROM tblWorkOrders
WHERE ((([tblWorkOrders].[Equip_ID])=11001) And (([tblWorkOrders].[ClosedBy]) Is Null));

And here the code:
Private Sub Form_Current()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCount As Integer


Set db = CurrentDb()

SQL = db.QueryDefs("qryWOsForEquip").SQL
MsgBox SQL

ThisEquip_ID = [Forms]![frmEquipData]![Equip_ID]

SQL = Replace(SQL, "[Forms]![frmEquipData]![Equip_ID]", ThisEquip_ID)
MsgBox SQL

Set rs = db.OpenRecordset(SQL)


intCount = rs.RecordCount
MsgBox intCount

End Sub

It's giving a runtime error "Data type mismatch in criteria selection" on the 'Set rs = db.OpenRecordset(SQL)' statement.

When I run the query by itself (with the equip data form open) I get the proper recordset.

Bill

 

[PHV]

Instead of this:
sql = replace(sql,"[Forms]![frmEquipData]![Equip_ID]",thisEquip_Id)
try this:
sql = replace(sql,"[Forms]![frmEquipData]![Equip_ID]",[blue]"'" & [/blue]thisEquip_Id[blue] & "'"[/blue])

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Steve101]

Yea; that should do it. For text strings, you need to surround the value with quotes.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[draegar]

Thanks PHV and Steve,

This has cleared up the error and I am getting records returned now. However the:

"And (([tblWorkOrders].[ClosedBy]) Is Null));" part does not seem to be working.

It's returning all the records instead of just the ones where [ClosedBy] Is Null.

This query does work normally when I run it by itself.

 

[Steve101]

Bill, try putting a space after the closing single quote on the line as provided by PHV; ie:

sql = replace(sql,"[Forms]![frmEquipData]![Equip_ID]","'" & thisEquip_Id & "[red]' [/red]")

suspect that the AND clause is being malformed without this space.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[draegar]

Steve,

It still ignores the AND clause. I tried reversing the order of the test also (placing [CompBy] before the [Equip_ID] but it still did the same thing. It's returning all 8 records as opposed to running the Query by itself which returns 5 records.

I'm sorry to be pestering you so much with this one problem.

Thanks,
Bill

 

[Steve101]

Suggest you put a breakpoint (ie. stop statement) in your code after the sql string has been defined and had the form value applied.

then debug. print the value of sql in the immediate area. then copy the sql string into the copy buffer, and past this into the sql of a new query and run it. If it performs properly, then this is a mystery; if not, then you'll need to construct the way in which the sql is being constructed. Nothing else sticks out as being a problem.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[draegar]

Thanks for all your help, Steve. Looks like it will have to be a mystery. I copied the SQL from the immediate window as you suggested and it performed properly.

Bill

 

[draegar]

Steve,

You have my permission to kick me!!!

I was trying to use:

intCount = rs.RecordCount
Then testing for a recordcount of 0

That was coming up with all the records.

I went back and re-read one of your earlier posts and changed this to:

intCount = 0
While Not rs.EOF
rs.MoveNext
intCount = intCount + 1
Wend

Now I am getting the right number of records.

Thank you so much.. Just wish I would have paid more attention earlier.

Just for my curiosity, any idea why it doesn't work the way I tried it (intCount = rs.RecordCount)?

Bill

 

[Steve101]

Bill, to get an accurate rs.recordcount reading, its recommended that you first do a movefirst, movelast; ie.:

rs.movefirst
rs.movelast
recCount = rs.recordcount

you have achieved the same thing with the while loop

glad you solved it,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[halamazoo]

I just checked out this post because I was having the same problem, and I never would have thought of the problem residing in the query like it did. It's good to know that somebody out there knows what they're doing.

 

[mmusick]

All,

I was having the same problem too, and this post was a great find! Thanks. One related question. My initial query was so large that I created another query to test with that included only the expressions with parameters. Can someone explain why in my original query the replace will work with this:
strSQL = Replace(strSQL, "[Forms]![APP FRM]![SPN]", "'" & strSPN & "'")
but using my test query, I needed
strSQL = Replace(strSQL, "Forms![APP FRM]!SPN", "'" & strSPN & "'")
when the test query was just copied from the original.
I would hate to include both within the logic.

Thanks.

 

[Zygor]

Here is a follow up question. I am having a similar problem as described above, however, my query is based on another query which has the parameter in it. When I do a replace it sees only the reference to the other query, not the sql of the query so no replace occurs. Any Thoughts?