Counting records

[Elise_L]

Can anyone see where I'm going wrong? I'm trying to click on a button, which goes to a form called 'RelatedMaterials', this works fine. I want this form to have the check boxes on which are ticked according to the Query. This was working fine, exect ot gets to the end and produces an error message. So all I want to do now, is count the records obtained from the Query, something on the lines of:

ContribID is the ID from the original form
myID is the ID of Contributer on the new form
Get the materials associated with the ID selected, and count them.


-ContribID and myID come out at different values, myID is correct though, (I think)
-mySQL just prints out the select statement

I want to just use this number to do a loop so it only goes round x times, and therefore not produce the error message the end. I'm sure I'm making it over complicated...
(I've left in the text I've commented out, as I don't know what's right or wrong!)

Public Sub ShowValue(myID As Long)
Dim dbsComo As Database
Dim rstContMaterials As Recordset
Dim strMessage As String
Dim mySQL As String

'Let ContribID = myID

Debug.Print ContribID
Debug.Print myID

Let mySQL = "SELECT * FROM Query_Contributer/Material_Link WHERE ContribID=" & myID & ";"
'Let mySQL = "SELECT myfield FROM mytable"

Debug.Print mySQL

Set dbsComo = CurrentDb
With dbsComo
'Set rstContMaterials = .OpenRecordset("mySQL")
'Set rstContMaterials = dbsCompenRecordset(mySQL)


' Show the RecordCount property after populating the Recordset.
rstContMaterials.MoveLast

Debug.Print " RecordCount = " & rstContMaterials.RecordCount
rstContMaterials.Close

End With

End Sub

Please help,I'm totally lost!!!Thank you!!!
Elise
(email: TimFa@cogapp.com)

 

[Travis]

Here is a better way to do this. (The count is not as important as limiting to just looping through the returned records).

Public Sub ShowValue(myID As Long)
Dim rstContMaterials As Recordset
Dim strMessage As String
Dim mySQL As String

mySQL = "SELECT * FROM Query_Contributer/Material_Link WHERE ContribID=" & myID & ";"

Set rstContMaterials = Currentdb.OpenRecordset("mySQL")
rstContMaterials.Movefirst

Do While not rstContMaterials.EOF
'Loop through the recordset
rstContMaterials.MoveNext
loop

rstContMaterials.Close
set rstContMaterials = Nothing

End Sub

 

[Elise_L]

Thankyou Travis for getting back to me, I had a few problems adapting the code, I ended up using CreateQueryDef.

Working fine now, except I can't delete teh query that I create each time!!! I've put at the end:


rstContMaterials.Close
Set rstContMaterials = Nothing
'FindMaterials.Delete
Set qdf = Nothing
End Sub

It's The FindMaterials query that I've just create and want to delete.

HELP!!!??!!

[This message has been edited by Elise_L (edited 07-28-2000).]