field value present in two tables

[kimangel]

Hi experts, kimangel is here again, doubling my time. Thanks alot for helping.

I have problem with two tables, when I click cmdSearch button searching for Employees_IdNo error occur "The specified field 'Employees_IdNo' could refer to more one table in the FROM clause in your SQL statement.

below is my code:

Private Sub cmdSearch_Click()

Dim sTrID As String

sTrID = txtSearch.Text

Set rs = New ADODB.Recordset
rs.Open "Select * from Time_In, Time_Out where Employees_IdNo like '" & sTrID & "'", cn, adOpenKeyset, adLockPessimistic

If Not rs.EOF Then

Ado.RecordSource = "Select * from Time_In, Time_Out where Employees_IdNo Like '" & sTrID & "'"
Ado.Refresh


rs.Close
Set rs = Nothing
Else
MsgBox "No records found! ", vbExclamation, "DailyRecords"
End If

End Sub

Kimangel

[AndreRet]

That is because you have used LIKE in your sql statement and not = . What it does is when you search using like, it will search ALL records where there is a match. Say strID = kim1234 it will return all records containing k, i, m, 1, 2, 3, 4 ..... Not good coding practise.

rs.Open "Select * from Time_In, Time_Out where Employees_IdNo = '" & sTrID & "'", cn, adOpenKeyset, adLockPessimistic

Use the above. You know what the employee_id is - search for just that record.

[kimangel]

thank you sir. same error when i change like to =. the scenario is this Ihave a datagrid view which have entries from two tables, Time_In and Time_Out both have fields Employees_IdNo.

[rishif2]

try this

rs.Open "Select * from Time_In where Employees_IdNo = '" & strid & "'" & " union Select * from Time_Out where Employees_IdNo = '" & strid & "'", cn, adOpenKeyset, adLockPessimistic

make sure that Employees_IdNo is string or not .
now i hope that you will handle that issue .

hope this helps you . . .

[AndreRet]

Oops, I missed that one - the call to 2 tables. Use the following sql statement...

"SELECT * FROM Time_In WHERE Employees_IdNo = '" & sTrID & "' AND Employees_IdNo IN (SELECT Employees_IdNo FROM Time_Out WHERE Employees_IdNo = '" & sTrID & "'), cn, adOpenStatic, adLockOptimistic

[kimangel]

Hi sir both code suggested still have error. the scenario is this (sorry for my english, am not good on it (:o)
I have datagrid view with fields, DateIn, TimeIn for Time_In Table and DateOut, TimeOut for Time_Out table. and when i search for certain employees log time in and time out. I used thier Employees_IdNo for search but this two table has it that is why i did not include Employees_IdNo (Though I want to show it) in datagrid view because both Employees_IdNo will display. How will code it. Here's my current code; pls help me re-code. Thanks.

Private Sub cmdSearch_Click()

Dim sTrID As String

sTrID = txtSearch.Text

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Time_In WHERE Employees_IdNo = '" & sTrID & "' AND Employees_IdNo IN (SELECT Employees_IdNo FROM Time_Out WHERE Employees_IdNo = '" & sTrID & "')", cn, adOpenStatic, adLockOptimistic


Ado.RecordSource = "Select * from Time_In where Employees_IdNo = '" & sTrID & "'" & "union select * from Time_Out where Employees_IdNo = '" & sTrID & "'"
Ado.Refresh

rs.Close
Set rs = Nothing
MsgBox "No records found! ", vbExclamation, "DailyRecords"

End Sub

with this code, when I search Employees_IdNo the log timeout fields will move/transfer to TimeIn fields.

By the way, these TimeIn and TimeOut data these will used as thier daily time records, that will be computed for the number of days work and thier total time.

Thanks!

[AndreRet]

See my previous remark on your times in and out. Use only 1 table. easier, more convenient AND much faster with less errors...