query or Dlookup 1

[JBad]

hi
for the following, shud i use a SELECT query as recordsource or a Dlookup coded into a cmdButton ?

EmpCode: ___ [Find cmdButton]
EmpName: ________

The user enters the empcode in a txtBox_1 and clicks the Find cmdButton. The snippet shud search the table (EmpMaster) for that EmpNo and display the corresponding EmpName.

Thanks.. JB

 

[Remou]

You could simply set the recordsource of the form to a Select statement with a Where clause. You will need a little code to cover an empty recordset being returned.

 

[JBad]

Hi

As in :

SELECT EMPMASTER.EMPNAME, EMPMASTER.EMPNO WHERE EMPNO = txtboxEMPNO.value ?????

Also, how do i display the corresponding empname of the empcode (if its found). Is there a need to code the cmdButton too??

Thanks... JB

 

[Remou]

Ok. Let's look at a different way.

Dim rs As DAO.Recordset
Set rs=Currentdb.OpenRecordset("tblEmployees")

rs.FindFirst "EmpNo"=txtboxEMPNO 'Numeric empno

If Not rs.NoMatch Then
   Me.txtEmpName=rs!EmpName
Else
   MsgBox "Not found"
End If

However, this is only useful if all you want to do is to look up a name.

My original suggestion was based on the idea that you had a number of fields that could be bound to controls, so that setting the recordsoure would automatically populate these controls:

[tt]Me.RecordSource ="SELECT EmpNo, EmpName, F1, F2 " _
& "FROM tblEmployees WHERE EmpNo=" & Me.txtboxEMPNO 'Numeric EmpNo[/tt]

By far rthe easiest thing to do is to bind the form to a recordset and to use a combobox to find the record you want.

 

[JBad]

Hi
I put in the following code in the AfterUpdate event of the Ecode textbox :

Private Sub txtEcode_AfterUpdate()
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Lab_Master_1")

rs.FindFirst "EmpNo" = txtEcode 'Numeric empno

If Not rs.NoMatch Then
Me.RecordSource = "SELECT EmpName FROM Lab_Master_1 WHERE EmpNo=" & Me.txtEcode 'Numeric EmpNo
Else
MsgBox "Not found"
End If

Set rs = Nothing

End Sub

But, upon running, it gave me an error as:

Operation is not support for this type of Object

, and in the code window, the rs.FindFirst line was highlighted...

Thanks -- JB

 

[TheAceMan1]

How are ya JBad . . .

Try:

[blue]   Dim [purple][b]db[/b][/purple] As DAO.Database, rs As DAO.Recordset
    
   Set [purple][b]db[/b][/purple] = CurrentDb
   Set rs = [purple][b]db[/b][/purple].OpenRecordset("Lab_Master_1")[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Remou]

Oops:

Private Sub txtEcode_AfterUpdate()
Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("Lab_Master_1",dbOpenDynaset)

    rs.FindFirst "EmpNo=" & txtEcode 'Numeric empno

    If Not rs.NoMatch Then
        Me.RecordSource = "SELECT EmpName FROM Lab_Master_1 WHERE EmpNo=" & Me.txtEcode 'Numeric EmpNo
    Else
        MsgBox "Not found"
    End If
    
    Set rs = Nothing

End Sub