Recordset using sql statement

[harrisw]

want to return a record set using the statement below.

Set rsSet = dBase.OpenRecordset("SELECT * FROM tblhistory _
WHERE "[recordid] =" & [form]![frmmain.id])

Any Idea

 

[harrisw]

I've tried the above and all I get is "missing )" error.

 

[Jimbob]

try this instead

set rsSet = dBase.OpenRecordset("SELECT * FROM tblHistory WHERE recordid = " & Forms!frmMain.Form!ID & " ")

if ID is a text field then do this:

set rsSet = dBase.OpenRecordset("SELECT * FROM tblHistory WHERE recordid = ' " & Forms!frmMain.Form!ID & " ' ")

*-)

[This message has been edited by Jimbob (edited 04-04-2002).]

 

[harrisw]

Had notification that this has been replied to by jimbob but when I look theres no reply been entered.

Help!

 

[Jimbob]

I'll just have to try again then...

try this instead
set rsSet = dBase.OpenRecordset("SELECT * FROM tblHistory WHERE recordid = " & Forms!frmMain.Form!ID & " ")

if ID is a text field then do this:

set rsSet = dBase.OpenRecordset("SELECT * FROM tblHistory WHERE recordid = ' " & Forms!frmMain.Form!ID & " ' ")

*-)

 

[harrisw]

Tried it using the second one you gave it runs with no errors but I always get 0 in the recordset count when there are matches in the table.

 

[Jimbob]

Have you tried working with the recordset ie populating some text boxes with it's values? I ask this because I have a similar line of code in one of my db's, and although it retrieves records.....the recordcount is always 0, or 1. Odd I know, I'm a bit of a novice so I can't explain this....

I seem to remeber reading that a recordcount of 0 actually means 1....

Have you tried recordset.movefirst then Me!YourField = recordset.YourField to see if there is actually anything there....

 

[harrisw]

Tried it ie movelast and I get no current record so I can presume that the recordset is empty.

Anything else I can try.?

 

[Jimbob]

Hmm, can you post your code here?

ps I'm in Nottingham too

Nice day innit?

 

[harrisw]

Sorted.

When I copied your code it copied an extra space so it would have been looking for space(fieldname) and therefore wouldn't find anything.

Works fine when I remove the space. Thanks for your help.

 

[Jimbob]

No worries

 

[harrisw]

need a bit more help

How do I add another criteria to the end of the SQL statement ie

current statement with "[recordid]="complaint"

cheers

 

[Jimbob]

What kind of field is RecordID? is it a text or number field?

it's already referred to as a criteira in the SQL code...

I'm a bit confused, can you give me alittle more detail?

[This message has been edited by Jimbob (edited 04-08-2002).]

 

[harrisw]

The sql I currently have is ("SELECT count(recordid) FROM tblHistory WHERE tblhistory![recordid] = '" & Forms!frmMain.Form!ID & "'")

As well as recordid = form id I want to say "and tblhistory[history type]="complaint"

 

[Jimbob]

Try this

("SELECT count(recordid), [history type] FROM tblHistory WHERE tblhistory![recordid] = '" & Forms!frmMain.Form!ID & "' AND [history type]= '"Compliant "' ")


Hows that?


[This message has been edited by Jimbob (edited 04-09-2002).]