search using two field names

[problematic:)]

hi. HAPPY NEW YEAR! :) I'm making a system that uses ms access2003 and vb6. My problem is how to search the database with two field names (namely: Lastname, Firstname) and yet I can still search them one by one.

Ex. the Lastname field = "Smith" while the Firstname field = "Jane". when I input the lastname "Smith" in the textbox the datagrid will display all the Smith, and if I add the word "Jane" in the textbox the datagrid will display all the names that has Smith Jane or Jane Smith.

Did you get my point?

Well here is the code that I made. It has a lot of errors that's why I ask you guys anyway.

Private Sub txtsearch_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 13 Then
Set RS = New Recordset
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= I:\system\Records.mdb;Persist Security Info=False"
Adodc1.RecordSource = "select * from tblrecords where Lastname, Firstname like '" & txtsearch.Text & "'"
Set DataGrid1.DataSource = Adodc1
DataGrid1.Refresh
End If
End If
End Sub

Thank you and I hope you can help me. :)

[abelingaw]

Can you tell what errors you've encountered.?

[problematic:)]

well here it is:
Syntax error (comma)in query expression 'Lastname, Firstname like "Smith Jane"'.

then when I try to change some of the codes, debug comes with this:
Item cannot be found in the requested name or ordinal. (or something like this)

[azharrumani09]

To me I think ur query should be somethink like this
"select * from tblrecords where Lastname + ' ' + Firstname like '" & txtsearch.Text & "'"

[problematic:)]

I tried your code azharrumani09 but there was a message appeared saying:

No value given for one or more required parameters

[azharrumani09]

just try this code and see whether any problem

'-------Place this code on command button on whose click ur grid should get filled with required data
instead of placing in textbox event

Dim Str as string

Dim RsMain as adodb.recordset
Set RsMain=new adodb.recordset

Dim Con_str as string

Con_str = " " '--------- your connection string

Str = " " '--------------whatever is ur query

Set RsMain.ActiveConnection =Con_str
RsMain.CursorLocation = adUseClient
RsMain.CursorType = adOpenStatic
RsMain.LockType = adLockBatchOptimistic

RsMain.Open Str, , , , adCmdText

'-------- DC_MAIN is your adodb control name in this case or whatever u want (just set name property of your adodb control)
Set DC_MAIN.Recordset = RsMain

'----------then assign it to ur grid source

Set DataGrid1.DataSource = DC_MAIN (just whatever is name of ur adodb control)
DataGrid1.Refresh

[problematic:)]

it works... thanks!! :DD but can I ask one more question? I have a datareport where I have to print a some kind of certificate, the problem is, the certificate that should be printed needs to be generated from two fields (like the one that I ask previously)

here is the sql that I used in the datareport in the dataenvironment:

select * from tblrecords where txtsearch.text = Lastname+' '+Firstname;

it doesn't work, so I'm asking you anyway. :)

[azharrumani09]

Have u written code for printing datareport
or just facing problem with query only

[azharrumani09]

just try this query
select * from tblrecords where '" & txtsearch.Text & "' = Lastname+' '+Firstname;

[problematic:)]

it still doesn't work.

'" & txtsearch.Text & "' = Lastname+' '+Firstname; is not a valid datareport sql. :((

[problematic:)]

I'm having trouble with the query. I guess it will not be printed if the query in the sql statement box is incorrect.

[AndreRet]

I would personally use two combo boxes where the user can select the surname, once that is selected, populate the second combobox with the names, then load the datagrid.

'All your data and record source code here

RS.Open "SELECT LastName FROM tblrecords", cn, adOpenStatic, adLockOptimistic

If RS.BOF = True And RS.EOF = True Then
'whatever here
Else
Dim xCount As Integer

For xCount = 0 To RS.RecordCount -1
Combo1.AddItem RS!LastName
RS.MoveNext
Next RS

Once the user selected the surname from the combo1.Click event -

'All your data and record source code here

RS.Open "SELECT FirstName FROM tblrecords", cn, adOpenStatic, adLockOptimistic

If RS.BOF = True And RS.EOF = True Then
'whatever here
Else
Dim xCount As Integer

For xCount = 0 To RS.RecordCount -1
Combo2.AddItem RS!FirstName
RS.MoveNext
Next RS

No load your datagrid...:)

[ChrisPadgham]

If you are only interested in valid combinations then it is more user friendly to restrict the population of the second combo

RS.Open "SELECT UNIQUE FirstName FROM tblrecords WHERE LastName='" & combo1.Text & "'", cn, adOpenStatic, adLockOptimistic

Also using the UNIQUE keyword will return only one instance of each name in the combo box instead of, say, 50 instances of "Chris". Same goes for the SELECT statement for Combo1