Driving me nuts (yes more access)

General Computing Coding and Design
1

I’m trying to pull a single field from a Query, so I can make it into a string to be used as an email ‘to’ can I bloomin get it to hook into the query, can I heck as like.

the second bit of code I have, I just need to get the query open to be accessed!

please help me

it only gets as far as the open record set line

Dim rs As Recordset
Dim sObject

sObject = ""
strsql = "SELECT * FROM [det_q]"
rs.OpenRecordset strsql

If Not rs.EOF Then
    Do Until rs.EOF
        If sObject = "" Then
            sObject = Trim(Service_No)
        Else
            sObject = ";" & Trim(Service_No)
        End If
        rs.MoveNext
    Loop
End If

Lunchtime, I’m outta here!

2

Dim rs As Recordset
Dim sObject as String

sObject = “”
strsql = “SELECT * FROM [det_q]”
rs.OpenRecordset strsql

While rs.eof = False
If sObject = “” Then
sObject = Trim(Service_No)
Else
sObject = “;” & Trim(Service_No)
End If
rs.MoveNext
Wend

I changed the process some…the object should be a string if an email address
Also the While / Wend is faster

3

Comming back with a runtime error of 91, “Object Variable or With block variable not set”

Which is what I think I was getting before, must be something I have set up odd here

off to google

4

Maybe something more like this:

Dim rs As Recordset
Set db = CurrentDb
Dim sObject,strsql as String

strsql = “SELECT * FROM [det_q]”

Set rs = db.OpenRecordset(strsql, dbOpenForwardOnly)

While rs.eof = False
If rs.recordcount < 1 Then
sObject = Trim(Service_No)
Else
sObject = “;” & Trim(Service_No)
End If
rs.MoveNext
Wend

5

grr annoyed now,

Set rs = db.OpenRecordset(strsql, dbOpenForwardOnly)

stops with “too few peramiters, expected, 1”

Don’t stress about it Greg, I’ll leave it on the back boiler and if I get time at the back and of the project I’ll go back to it, it was just a nicety for the DB users, not necessary for function.

Thanks for all your help.

6

Error is that it isn’t pulling any records from the query. Is the DATBASE pointed also ?

db = CurrentDb

7

ended up with instead of selecting the records from a query result having to run the query direct, now it works, go figure.