Help -SQL

[emmzou]

I amnew in VB and I would appreciate any help
Can anyone tell me why this argument does not work?

__________________________
Dim newID
Private RS As New ADODB.Recordset

Set RS = Adodc1.Recordset
RS.Bookmark = DataGrid1.Bookmark
newID = RS.Fields("RefID")

' Adodc1, RS and Adodc2 work fine
' then here is the problem :
Adodc2.RecordSource = "SELECT Reference.*,_
Reference.RefID From Reference_
WHERE (((Reference.RefID)=newID));"
Adodc2.Refresh
_________________________________

When instead of newID I put a number everything is OK. What is the problem with newID?

Thanks in advance

emmzou

[Jimbob]

you will need to add newID to the sql string, like this

Dim newID 
Private RS As New ADODB.Recordset 

Set RS = Adodc1.Recordset 
RS.Bookmark = DataGrid1.Bookmark 
newID = RS.Fields("RefID") 

' Adodc1, RS and Adodc2 work fine 
' then here is the problem : 
Adodc2.RecordSource = "SELECT Reference.*,_ 
Reference.RefID From Reference_ 
WHERE (((Reference.RefID)= "& newID &"));" 
Adodc2.Refresh

bear in mind, you may have to put something surrounding newID, eg if it contains text: -

WHERE (((Reference.RefID)= '"& newID & "'));"

hope this is of some help to you...

[emmzou]

I think that my question was rather incomplete. The RefID is the primary key (Autonumber) in an Access database thus it corresponds to a number and not text. As I mentioned when I use the newID in the SQL query this does not work but it runs fine when I put the corresponding number, for example

Adodc2.RecordSource = "SELECT Reference.*,_
Reference.RefID From Reference_
WHERE (((Reference.RefID)= 105));"

runs fine.

This is why I suspect that there is a problem with newID.
Any other idea?

THANKS for your help

[emmzou]

Jimbob

I gave a second try using:

"& newID &"

and now it's OK.

THANKS a lot