OpenRecordset Problem 1

[TimGoff]

Hi

Would anybody be able to tell me why this SQL string won't work with the OpenRecordset command - I get a "Syntax Error In From Cause 3131"? It works in a normal query window fine though.

SELECT DISTINCT tblMusicOutstanding.[Programme Title],
Right([tblMusicOutstanding].[costing no],8),
tblExp_Reports.[House No]
FROM tblMusicOutstanding, tblExp_Reports
WHERE (((Right([tblMusicOutstanding].[costing no],8))=[tblexp_reports].[house no]));

Thanks in advance
Tim

 

[Steve101]

When you open the recordset, is it based on a query in which this sql is stored, or a string with the above sql assigned to it. If its the latter, are you sure that you have appropriate spaces between the various keywords. Perhaps you can publish snippents of the code to provide more detail.
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[TimGoff]

Hi Steve,

It's the latter option, as this:

Set rst = dbs.OpenRecordset("SELECT DISTINCT tblMusicOutstanding.[Programme Title], Right([tblMusicOutstanding].[costing no],8), tblExp_Reports.[House No]" _
& "FROM tblMusicOutstanding, tblexp_reports" _
& "WHERE (((Right([tblMusicOutstanding].[costing no],8))=[tblexp_reports].[house no]))&quot

Thanks
Tim

 

[Steve101]

Tim, its as I suspected. When the bits of the string are concatenacted (ie. joined), there is no space character before the FROM word.

Its nearly always a good idea to leave a space before the closing quote and continuation chartacter of each line of code before the continuation line; ie. your corrected code would look like this:

Set rst = dbs.OpenRecordset("SELECT DISTINCT tblMusicOutstanding.[Programme Title], Right([tblMusicOutstanding].[costing no],8), tblExp_Reports.[House No] " _
& "FROM tblMusicOutstanding, tblexp_reports " _
& "WHERE (((Right([tblMusicOutstanding].[costing no],8))=[tblexp_reports].[house]))&quot

Note that I have had to make this correction in two places (ie. to the left of the WHERE word as well.) Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[TimGoff]

Hi Steve,

Thanks very much, that sorted the problem out.

Do you think you could point me in the right direction after this please?

Once I've opened the recordset I need to extract each row from it, and set it to a variable, eg row 1:

Field 1 Field 2 Field 3
107 Acquisitions 300

Using the example, I would the variable to be set to 300, based on the criteria 107 & Acquisitions...would you know how to do this?

Thanks
Tim

 

[Steve101]

Tim,

Once you've opened a recordset, you can loop through it and programatically do anything with the fields associated with the recordset. For example, as a continuation of your example:

dim DB as database
dim rst as Recordset
set DB = CurrentDB
sq = "SELECT DISTINCT MO.[Programme Title], " & _
" Right(MO.[costing no],8) AS CostNo, " & _
" ER.[House No] " & _
"FROM tblMusicOutstanding AS MO, " & _
" tblexp_reports AS ER " & _
"WHERE Right(MO.[costing no],8)= ER.[house no] "
Set rst = dbs.OpenRecordset(sq)

While not rst.eof
'its in this loop that you can access the current
'record's fields, and assign them to variables.

varFirst = rst("Programme Title&quot
varSecond = rst("CostNo&quot
'..... and so on
'
'now move to the next record
rst.movenext
Wend

rst.close
DB.close


Note the following:

(a) Ive used Alias's in the SQL to make it a little shorter and easier to read. Also played with the layout a little to make it more legible.

(b) Ive also 'aliased' the expression Right(MO.[costing no],8) to give it a name; otherwise Access will call it something like "expression1" in the recordset; not terribly meaningful.

(c) Scan around for examples in online help, or on the net regarding DAO or ADO recordset examples. It may be a little confusing at first, but programatic control of data combined with queries is where the real data manipulation power of access is.

Hope that this helps,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[TimGoff]

This is great, cheers

Thanks for your help over the last couple of days, I've learnt alot and these examples have really helped

Thanks again
Tim

 

[Steve101]

My pleasure. Good luck.
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au