Recordset .Find

[-MK-]

I need some help getting this to work.
Basically, I want to search a table for certain items,and then display them
in an unbound text box. (unbound, because i need this info from another table)

heres what i got that works:
rsMyRS.Find "[DIR]=" & strDIR

heres what i need to work(basically find DIR and CATAGORY in the table):
rsMyRS.Find "[DIR]=" & strDIR And "[Catagory]=" & strType

-more of the code-
Dim strType As String
Dim strDIR As String
Dim dbMyDB As New ADODB.Connection
Dim rsMyRS As New ADODB.Recordset
Dim strTablename As String

strType = "Investigation"
txtDir.SetFocus
strDIR = txtDir.Text

dbMyDB.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data
Source=V:\QualMgt\DIRS\DIR.mdb"
strTablename = "tblComments"
rsMyRS.Open strTablename, dbMyDB, adOpenKeyset, adLockOptimistic,
adCmdTable


txtDir.SetFocus
rsMyRS.Find "[DIR]=" & strDIR And "[Catagory]=" & strType
txtCom_In.SetFocus
txtCom_In.Text = txtCom_In.Text & rsMyRS!COMMENT

*If only text boxes could have rowsource, then life would be 1000x easier*
Anyone?
Thanks.
-MK-

 

[-MK-]

Problem solved.

If anyone was wondering how to do this(for future reference)

heres the code:

rsMyRS.MoveFirst
Do Until rsMyRS.EOF

If rsMyRS.Fields!DIR = strDIR And rsMyRS.Fields!Catagory = strType Then
txtCom_In.SetFocus
txtCom_In.Text = txtCom_In.Text & rsMyRS!COMMENT
Exit Do
Else
rsMyRS.MoveNext
End If
Loop

If rsMyRS.EOF Then
MsgBox "No Data Found", vbOKOnly
End If


Cheers!

 

[Ofer]

Try this

Dim dbMyDB As New ADODB.Connection
Dim rsMyRS As New ADODB.Recordset

dbMyDB.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data
Source=V:\QualMgt\DIRS\DIR.mdb"
rsMyRS.Open "Select COMMENT From tblComments Where "[DIR]='" &
txtDir.Text & "' AND [Catagory]='Investigation'", dbMyDB, adOpenKeyset,
adLockOptimistic, adCmdTable

If not rsMyRS.Eof then
me.txtCom_In.Text = txtCom_In.Text & rsMyRS!COMMENT
end if

 

[Ofer]

I put one quote to much


Dim dbMyDB As New ADODB.Connection
Dim rsMyRS As New ADODB.Recordset

dbMyDB.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data
Source=V:\QualMgt\DIRS\DIR.mdb"
rsMyRS.Open "Select COMMENT From tblComments Where [DIR]='" &
txtDir.Text & "' AND [Catagory]='Investigation'", dbMyDB, adOpenKeyset,
adLockOptimistic, adCmdTable

If not rsMyRS.Eof then
me.txtCom_In.Text = txtCom_In.Text & rsMyRS!COMMENT
end if

 

[-MK-]

Hey,

i get an error with that code, trying to figure out what is wrong.

On this line:
rsMyRS.Open "Select Comment From tblComments Where [DIR]='" & txtDir.Text &
"' AND [Catagory]='Investigation'", dbMyDB, adOpenKeyset, adLockOptimistic,
adCmdTable

I get:
Syntax error in FROM clause.


Thanks.
-MK-

I put one quote to much


Dim dbMyDB As New ADODB.Connection
Dim rsMyRS As New ADODB.Recordset

dbMyDB.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data
Source=V:\QualMgt\DIRS\DIR.mdb"
rsMyRS.Open "Select COMMENT From tblComments Where [DIR]='" &
txtDir.Text & "' AND [Catagory]='Investigation'", dbMyDB, adOpenKeyset,
adLockOptimistic, adCmdTable

If not rsMyRS.Eof then
me.txtCom_In.Text = txtCom_In.Text & rsMyRS!COMMENT
end if

--
In God We Trust - Everything Else We Test

I need some help getting this to work.
Basically, I want to search a table for certain items,and then display them
in an unbound text box. (unbound, because i need this info from another table)

heres what i got that works:
rsMyRS.Find "[DIR]=" & strDIR

heres what i need to work(basically find DIR and CATAGORY in the table):
rsMyRS.Find "[DIR]=" & strDIR And "[Catagory]=" & strType

-more of the code-
Dim strType As String
Dim strDIR As String
Dim dbMyDB As New ADODB.Connection
Dim rsMyRS As New ADODB.Recordset
Dim strTablename As String

strType = "Investigation"
txtDir.SetFocus
strDIR = txtDir.Text

dbMyDB.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data
Source=V:\QualMgt\DIRS\DIR.mdb"
strTablename = "tblComments"
rsMyRS.Open strTablename, dbMyDB, adOpenKeyset, adLockOptimistic,
adCmdTable


txtDir.SetFocus
rsMyRS.Find "[DIR]=" & strDIR And "[Catagory]=" & strType
txtCom_In.SetFocus
txtCom_In.Text = txtCom_In.Text & rsMyRS!COMMENT

*If only text boxes could have rowsource, then life would be 1000x easier*
Anyone?
Thanks.
-MK-

 

[Ofer]

Hi
Try and remove the adCmdTable from the open command

Hey,

i get an error with that code, trying to figure out what is wrong.

On this line:
rsMyRS.Open "Select Comment From tblComments Where [DIR]='" & txtDir.Text &
"' AND [Catagory]='Investigation'", dbMyDB, adOpenKeyset, adLockOptimistic,
adCmdTable

I get:
Syntax error in FROM clause.


Thanks.
-MK-

I put one quote to much


Dim dbMyDB As New ADODB.Connection
Dim rsMyRS As New ADODB.Recordset

dbMyDB.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data
Source=V:\QualMgt\DIRS\DIR.mdb"
rsMyRS.Open "Select COMMENT From tblComments Where [DIR]='" &
txtDir.Text & "' AND [Catagory]='Investigation'", dbMyDB, adOpenKeyset,
adLockOptimistic, adCmdTable

If not rsMyRS.Eof then
me.txtCom_In.Text = txtCom_In.Text & rsMyRS!COMMENT
end if

--
In God We Trust - Everything Else We Test

I need some help getting this to work.
Basically, I want to search a table for certain items,and then display them
in an unbound text box. (unbound, because i need this info from another table)

heres what i got that works:
rsMyRS.Find "[DIR]=" & strDIR

heres what i need to work(basically find DIR and CATAGORY in the table):
rsMyRS.Find "[DIR]=" & strDIR And "[Catagory]=" & strType

-more of the code-
Dim strType As String
Dim strDIR As String
Dim dbMyDB As New ADODB.Connection
Dim rsMyRS As New ADODB.Recordset
Dim strTablename As String

strType = "Investigation"
txtDir.SetFocus
strDIR = txtDir.Text

dbMyDB.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data
Source=V:\QualMgt\DIRS\DIR.mdb"
strTablename = "tblComments"
rsMyRS.Open strTablename, dbMyDB, adOpenKeyset, adLockOptimistic,
adCmdTable


txtDir.SetFocus
rsMyRS.Find "[DIR]=" & strDIR And "[Catagory]=" & strType
txtCom_In.SetFocus
txtCom_In.Text = txtCom_In.Text & rsMyRS!COMMENT

*If only text boxes could have rowsource, then life would be 1000x easier*
Anyone?
Thanks.
-MK-