OpenRecordset problem (1 Viewer)

[micks55]

Hi all, I do try to resolve my own problems but this one is a mystery to me as I'm sure that similar has been fine in the past.

I am trying to open a sub set of table "PriceList" which has 900 records and includes the fields ListID(Lng) and Valid(Y/N) but it's not going well...

Dim DB As DAO.Database, rs As DAO.Recordset
Dim strSQL
strSQL = "SELECT * FROM PriceList WHERE PriceList.ListID = " & Me![List33] & " And PriceList.[Valid] = True"
Set DB = CurrentDb
Set rs = DB.OpenRecordset(strSQL)

ListID = Me![List33] is good but I'm getting extra records because it's finding Valid = True and Valid = False.

I've tried various ways of defining Valid = True but clearly I haven't yet found the right one.

Any guidance will be gratefully appreciated. Thanks.

 

[CBrighton]

If you do use Debug.Print or MsgBox to view strSQL does it look correct?

 

[vbaInet]

Valid = -1
Valid = Yes

 

[micks55]

CBrighton. Good idea but have tried looping through to EOF with a simple MsgBox and that's where I'm finding too many records.

vbaInet. I have tried -1 and will try =Yes. Thanks

 

[CBrighton]

A msgbox displaying strSQL will allow you to check the string that is being used to open the recordset exactly as it is. It would let you check the end result is what you expected.

If you use Debug.Print you could then easily copy it and paste it into the SQL view of a new query to see if the problem persists when it's done directly in Access rather than via VBA.

 

[vbaInet]

Run that sql statement in a query to see the results. Obviously type in the criteria just for testing purposes.

 

[boblarson]

I've had issue before with trying to use YES, so I've gone to a way that seems to work slightly better. Use

PriceList.[Valid] <> 0

Anything other than zero is yes and I don't know why it fails to see yes/true sometimes but I have experienced that. So, using <> 0 seems to work every time.

 

[micks55]

That's what's weird. Pasted into a query (and setting criteria as ListID 6, Valid True), it works fine but with the DB.OpenRecordset(strSQL) it doen't! I've also tried copying the SQL from the query to the module.
I'd be happy to open a recordset based on a saved query but I don't know if that can be done.

 

[vbaInet]

Hmm... I've never had problems with that Bob.

@micks55: That fied's data type is definitely YES/NO? It's not Text which has Yes/No in it?

 

[micks55]

Once again boblarson comes up with a different approach and I'll certainly be trying that later. Have to sign off as it's time to join the rush hour traffic so Many Many Thanks to all and I'll post results later.

 

[boblarson]

Hmm... I've never had problems with that Bob.

Yeah, I hadn't either but with a SQL Server 2005 backend, it does appear to be a problem sometimes. But for consistency, I now use it even with my linked Access backends too.

 

[vbaInet]

Yeah with SQL backend the bools are treated differently. I wonder how it will interpret Nulls if the field was wrapped in Nz().

 

[micks55]

Back on the case...
Sorry to report that this doesn't work
strSQL = "SELECT * FROM PriceList WHERE PriceList.ListID = " & Me![List33] & " And PriceList.[Valid] <> 0"

Also this doesn't work
Set rs = DB.OpenRecordset("query1")
even though query1 does work!

Seems that my only option is to use DoCmd.OpenQuery to run a saved delete query that removes all where Valid = False and then open the recordset. Not the best result as I would have preferred to keep the out of date prices for historical reasons.
Thanks anyway guys and if I do find a way, I'll post it.

 

[vbaInet]

Thanks anyway guys and if I do find a way, I'll post it.

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[micks55]

Tried it all to no avail. Gonna give up and run a delete query. Shame, I'd rather keep historical records.
Thanks all

ps. always have probs logging in to the forum. after two ot three log ons, I usually get in. this time i get in but when i go to mods & vba, i appear to be not logged in. ho hum, lets see if this reply gets there.

 

[vbaInet]

My last post contains a thread that shows you how to upload your database.

 

[CBrighton]

Also this doesn't work
Set rs = DB.OpenRecordset("query1")
even though query1 does work!

Queries are fine to use as recordset sources, but queries with references to controls aren't.

If valid = true was your only criteria then query1 would have worked fine.

 

[boblarson]

Queries are fine to use as recordset sources, but queries with references to controls aren't.

That is an inaccurate statement. It is fine to use them if they have references to controls. The form has to be open and you may have to include the parameters in the query (in 2003 it is QUERY > PARAMETERS) but you CAN use queries with references to controls in control sources.

 

[CBrighton]

That is an inaccurate statement. It is fine to use them if they have references to controls. The form has to be open and you may have to include the parameters in the query (in 2003 it is QUERY > PARAMETERS) but you CAN use queries with references to controls in control sources.

In that case I stand corrected.

I have never used a parameter in a query for a form reference.

I've used parameters for things like start date / end date rather than adding extra controls to the form which the user launches the report from but didn't know it was even possible to set a reference to a control as a parameter. And obviously I've used control references in queries which are not used as VBA recordsets.

I know that a query with criteria set to a control reference but no parameters set can't be used as a recordset source, I just get around it by basically using the querie's SQL instead of it's name.

 

[boblarson]

I know that a query with criteria set to a control reference but no parameters set can't be used as a recordset source,

Sorry to burst your bubble again. You can use a query which has criteria set to a control reference and without specifying the parameters. In fact, I use that a lot of the time. What probably has happened is you tried something in the past which didn't work and assumed (incorrectly) that it couldn't be used that way. There are times where you have to specify the parameters but many times you don't need to, even if the criteria is referencing a control on a form.