HELP Changing ADO Recordset?!

[VBAmateur]

Hello I will be so thankful for help on this: I am trying to use one form that will change according to who accesses it. I am using an ADO and a datagrid on the form. So I am trying to change the recordset for the ADO at runtime. Everything I have found says to use the following code but it will not REFRESH. Any help or suggestions on how you changed your ADO recordset at runtime. Thanks Much.



Private Sub Form_Load()
Dim strquery As String

With frmSkipGAFL.Adodc3
.Mode = adModeReadWrite
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=S:\DBAPPS\Repo\RepoInv.mdb"
.CommandType = adcommandtext
End With

strquery = "SELECT * FROM RepoInventory WHERE RepoStatusID = 12"
Adodc3.RecordSource = strquery
Adodc3.Refresh

End Sub

[VBAmateur]

Actually it is telling me that there is a data type mismatch in the criteria expression. Help

[Nias]

I have an upcoming enhancement I will be making to one of my programs which will perform a similar function. I know that on the mainframe you can't change the query on the fly like that (Where clause). You have to close and open again with the new query. I was starting to wonder if I would have to do that in VB too. Perhaps you have to do something similar to get it to work. Let me know what you come up with!

[VBAmateur]

I found the problem was in my syntax, the sql statement needed to be:

"SELECT * FROM RepoInventory WHERE repoinventory.RepoStatusID = " & "'" & 11 & "'"

and it works beautifully!

[Serge]

If you use a hard coded value, then you don't have to concatenate your SQL statement. You can do it like this:

strquery = "SELECT * FROM RepoInventory WHERE RepoStatusID = '12'"