dbSeeChanges error

[spinkung]

Hi all,

i'm trying to retrieve some info from a SQL table but i keep getting an error saying i need to add the dbSeeChanges option. i have found some bits on this forum about it but i am doing something slightly different and it doesn't work when i add it.

Here's what i've got....

Dim st As String
Dim res As New Recordset
Dim db As Database
Set db = CurrentDb

ReqNo = Me.lab3.Caption
stkCod = RTrim(Me.lab4.Caption)

st = "SELECT dbo_tbl_RetRANrequest.UNID " & _
      "FROM dbo_tbl_RetRANrequest " & _
      "WHERE dbo_tbl_RetRANrequest.ReqNo = '" & ReqNo & "' " & _
      "AND dbo_tbl_RetRANrequest.stockCode = '" & stkCod & "'"


Set res = db.OpenRecordset([COLOR="Red"]st[/COLOR], dbOpenDynaset, [dbSeeChanges])

The red part is my SQL query result. In the other example the red text has speech marks ("st") aroung it but mine is coming from a variable and i get a type mismatch error.

Any ideas?

Cheers,
Spinkung.

 

[RoyVidar]

My guess is that one or both of your parameters are not text, but numeric (RegNo?). I'd suggest removing the single quotes surrounding that/those parameters. Also, dbSeeChanges is a constant, so there's no need for the [brackets] around it.

 

[spinkung]

thanks for the reply,

reqNo & stkCode are both text and numeric. (R12345 & S12345).

when i use....

Set rs = db.OpenRecordset(st, dbOpenDynaset, dbSeeChanges)

i get a type mismatch error.

I really can't figure it out???

 

[RoyVidar]

The important part about the datatype, is what they are defined as in the table.

Is the type mismatch on the OpenRecordset line?

If so, try opening without where condition - if that's OK, add one of the fields... to check what triggers the error.

Edit: Oh, and do a

debug.print st

and pick up the resulting SQL in the immediate pane (ctrl+g). That SQL should be runnable from the SQL view of the query designer.

 

[spinkung]

OK,


when i do the SQL in a query with & without the WHERE clause i get results.

when i try to directly link to the linked table ....
Set rs = db.OpenRecordset("linkedTable", dbOpenDynaset, dbSeeChanges)
...i still get a type mismatch error.

And yes it is on this line?????

Spinkung.

 

[RoyVidar]

Ah, I should probably have seen it - it seems you're working with an ADO recordset

replace

Dim res As New Recordset

with

Dim res As DAO.Recordset

 

[spinkung]

Brilliant. I owe you a beer. Thanks.

cheers,
Spinkung

 

[spinkung]

Not sure if i should post this hear or start a new thread but how then do i get the result into my text box.

i.e.
Me.unid1.Value = rs.???

Cheers,
Spinkung

 

[RoyVidar]

For something returning only one field, I could sometimes use ordinal position

Me.unid1.Value = rs.fields(0).value

but would prefer field name

Me.unid1.Value = rs.fields("UNID").value

 

[spinkung]

Thanks for your help.