Can't give value from a recordset to a variable.

[DaiDreamer]

Hi!

I can't give value from a record set to a variable. I tried Rst.fields("???&quot (Error 3265-Item found in this collection) and I tried Rst!??? (same error as rst,fields)and I tried AS in my sql statement( which gave me NOTHING). I'm using Access 97.

Help!

Here's my code:

Option Compare Database
Option Explicit

Public TotalMineArray() As String
Public TotalMines As Integer
Public Function MineCount(TheType As String, Info As String)

Dim MineArray() As String
Dim MineID As String
Dim Mines As Integer
Dim Yes As Integer
Dim yes2 As Integer
Dim HELP As Integer
Dim Counter As Integer
Dim Counter2 As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
Dim intCnt As Integer


MsgBox (TheType & Info)
Set db = CurrentDb
strSql = "SELECT MineName AND " & TheType & " FROM qryInspections WHERE " & TheType & "= '" & Info & "'"
Set rst = db.OpenRecordset(strSql, dbOpenSnapshot, dbForwardOnly)


'Error here
MineID = rst.Fields("MineName&quot
'MineID = rst!MineName


Mines = 0
MsgBox ("Nothing?" & MineID)
ReDim MineArray(Mines)
MineArray(Mines) = MineID
Do While MineID <> &quot;&quot; Or HELP = 25
Yes = 0
'Counters are only running thought the array once for each group. Problem with the way the counters are set up
HELP = HELP + 1
For Counter = 1 To Mines + 1
If MineID = MineArray(Counter - 1) Then
Yes = Yes + 1
MsgBox (&quot;MineID:&quot; & MineID)
End If
MsgBox (CStr(Mines) & CStr(Yes))
If Yes = 0 And Counter = Mines - 1 Then
Mines = Mines + 1
ReDim Preserve MineArray(Mines)
MineArray(Mines) = MineID
MsgBox (&quot;2nd ifthen&quot
For Counter2 = 1 To TotalMines + 1
If MineArray(Mines) <> TotalMineArray(Counter2 - 1) Then
yes2 = yes2 + 1
End If
If yes2 = 1 And Counter2 = TotalMines Then
TotalMines = TotalMines + 1
ReDim Preserve TotalMineArray(TotalMines)
TotalMineArray(TotalMines) = MineID
End If
Next Counter2
End If
Next Counter

MineCount = Mines + 1
rst.MoveNext
MineID = rst.Fields(&quot;MineName&quot
'MineID= rst!MineName
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Function

 

[Philly44]

I think if you add the .Value at the end it might do it. I have some code that sorta works along the same lines only I'm comparing the value of a variable to the field.

MineID = rst.Fields(&quot;MineName&quot.Value

I hope this helps

 

[Beren1h]

You might try setting up a QueryDef and using its recordset.

Dim qryI as QueryDef

Set qryI = db.QueryDefs(&quot;qryInspections&quot
Set rst = qryI.OpenRecordset

then do

MineID = rst(&quot;MineName&quot

 

[VogonPoet]

I think your query should read:

strSql = &quot;SELECT MineName, &quot; & TheType & &quot; FROM qryInspections WHERE &quot; & TheType & &quot;= '&quot; & Info & &quot;'&quot;

instead of:

strSql = &quot;SELECT MineName AND &quot; & TheType & &quot; FROM qryInspections WHERE &quot; & TheType & &quot;= '&quot; & Info & &quot;'&quot;


I'm not certain about this, but I think this may be your problem.

Michael