running sql on a recordset

[Earth]

Hi,

I've got this code:

Dim dbMidbase As Database
Dim rsMidbase As Recordset
Dim sSql As String

sSql = "SELECT [Documents]![DRegistrationNumber], [Jobs]![Job No], [Jobs]![Department], [Jobs]![Job Name] FROM Documents INNER JOIN Jobs ON [Documents]![DOurJob] = [Jobs]![Job No]WHERE ((([Documents]![DRegistrationNumber])=[Forms]![Transmissions]![TDRegCombo]))"

Set dbMidbase = OpenDatabase("\\location\midbdata97.mdb&quot
Set rsMidbase = dbMidbase.OpenRecordset(sSql) '***

The function has error trapping: it get's caught on the line ***. I don't know if this is because I have a problem in my SQL statement, or if there is something incorrect in my dealing with a recordset. I have very little idea on the correct use of recordsets: the recordset that I am trying to create here is a temporary combination of tables (as delivered by the SQL statement - if I put it into an Access query and run it, it works exactly as I want it to), from which the information can be used (to put into a word document). I don't need to make any changes/additions to the data - simply get the information out of it and then 'throw' the recordset away.

I've been stuck on this for days, so any help would be greatly appreciated. I'm sure I'm just doing doing something small and incredibly stupid, but I can't for the life of me figure out what it is.

Thanks muchly.

 

[kctaylordotcodotuk]

Try adding some quotes around the criteria i.e.: -
sSql = "SELECT [Documents]![DRegistrationNumber], [Jobs]![Job No], [Jobs]![Department], [Jobs]![Job Name] FROM Documents INNER JOIN Jobs ON [Documents]![DOurJob] = [Jobs]![Job No]WHERE ((([Documents]![DRegistrationNumber])= '" & [Forms]![Transmissions]![TDRegCombo] & "'))"

Note that is a single quote then a double quote after the = and a double quote followed by a single quote before the ))

If that doesn't work, let me know the error message returned when the openrecordset fails (Err & Err.Description please)

 

[christheprogrammer]

I think your OpenRecordset is missing something, try this:
Set rsMidbase = dbMidbase.OpenRecordset(sSql, dbOpenDynaset)

Cheers
Chris [sig][/sig]

 

[Earth]

It's still not working.

I tried your suggestion, Keith, to no avail.

I also tried your suggestion Chris.

The error I am getting is:
Run-time error '3296'
Join expression not supported.

Access help files say:

* Your SQL statement contains multiple joins in which the results of the query can differ, depending on the order in which the joins are performed. You may want to create a separate query to perform the first join, and then include that query in your SQL statement.

* The ON statement in your JOIN operation is incomplete or contains too many tables. You may want to put your ON expression in a WHERE clause.

I don't understand how this applies to my situation. I gather it's some kind of limitation in executing SQL in VB? Considering the statement works perfectly when executed as a query??

Any suggestions, and thanks Chris and Keith for you help?

[sig][/sig]

 

[Earth]

In an attempt to 'fix' the last problem, I changed my SQL statement to be:

sSql = "SELECT [Documents]![DRegistrationNumber], [Jobs]![Job No], [Jobs]![Department], [Jobs]![Job Name]
FROM Documents, Jobs
WHERE (([Documents]![DOurJob] = [Jobs]![Job No])
AND ([Documents]![RegistrationNumber]= '" & [Forms]![Transmissions]![TDRegCombo] & "'))"

The new error message is:
Run-time error '3061'
Too few parameters. Expected 1.

I can only assume that to be referring to the value being used from the calling form, ie. [Forms]![Transmissions]![TDRegCombo]. Any suggestions on how to fix this???

thanks. [sig][/sig]

 

[kctaylordotcodotuk]

When the error trapping halts the program can you press Ctrl-G (to open the debug window) then type ? sSQL

This will display the query that is trying to be run, so you can copy and paste it into another message.

Sorry I'm sure I know the answer to this, as I've had it in the past, but the old brain just isn't recalling as it should this week.

[sig]<p>Keith C Taylor<br><a href=mailto:TekTips@kctaylor.co.uk>TekTips@kctaylor.co.uk</a><br><a href=

http://www.kctaylor.co.uk>The

Information Gardener</a><br>The C stands for Computer![/sig]

 

[tombirch]

I find it much easier to build my sql sentenses for code by simply creating a new query from the database window. This makes it much easier to check if the sql is okey.

After you have designed and tested the query, goto to menu:view - SQL then select and copy the sql statement by pressing [ctrl]+[C].
Now go to the code and past in the sql. Access will wrap the inserted sql i the code view, but this can easily be fixed.
You can also be done the other way by pasting the sql from code into a query.

I hope this helps you
Tom
[sig][/sig]

 

[Earth]

Tom: I agree with you completely about that being the easiest way to structure queries - so much in fact that that is what I always do, and in fact, did in this case as well. Which is why it was so perplexing.

I ended up getting it to work. The way I did it was to remove the Inner Join bit completely. The code I ended up with was:

sSql = &quot;SELECT [Documents]![DRegistrationNumber], [Jobs]![Job No] AS JobNo, [Jobs]![Department], [Jobs]![Job Name] AS Project
FROM Documents, Jobs
WHERE (([Documents]![DOurJob] = [Jobs]![Job No])
AND ([Documents]![DRegistrationNumber]= '&quot; & [Forms]![Transmissions]![TDRegCombo] & &quot;'))&quot;

thus the inner join, and Run-time error '3296' were gone.

Access. Pffft.

Thanks everyone for your help. [sig][/sig]

 

[vanleurth]

Hi everyone, I was searching for the same problem posted here and gave it a try.
The first SQL is the original one that gives me the following error;

- Run-time error '3296'
- Join expression not supported.

Then I changed it to do the joins under where (second SQL command), but now I get this other error;

- Syntax error in join operation

Nut I don't have any other join operations anymore ???
Any help will be appretiated.

Thanks,

V.

Start Code -----------------------------------

' Define the db object to point to current database
Set db = CurrentDb

' FIRST SQL - Define the SQL command to get all trainees
'sqlstring = &quot;SELECT TrainingID, [Last Name], Status &quot; _
'& &quot;FROM ([tbl training] INNER JOIN [tbl training group sub] ON TrainingID = TrainingID) &quot; _
'& &quot;INNER JOIN ([tbl employees] &quot; _
'& &quot;INNER JOIN [tbl employees groups] ON EmployeeID = EmployeeID) ON GroupID = GroupID &quot; _
'& &quot;WHERE (((Status)='full' Or (Status)='part'))&quot;

' SECOND SQL :.
sqlstring = &quot;SELECT [tbl training].TrainingID, [tbl employees].[Last Name], [tbl employees].Status &quot; _
& &quot;FROM ([tbl training], [tbl training group sub], [tbl employees], [tbl employees groups])&quot; _
& &quot;WHERE (([tbl training].TrainingID = [tbl training group sub].TrainingID) and &quot; _
& &quot;([tbl employees].EmployeeID = [tbl employees groups].EmployeeID) and &quot; _
& &quot;([tbl training group sub].GroupID = [tbl employees groups].GroupID) and &quot; _
& &quot;(([tbl employees].Status='full') Or ([tbl employees].Status='part')))&quot;


' Run query and create recordset
Set rstsql = db.OpenRecordset(sqlstring)