Non existing information 1

[TriniGal]

Hello,

I have the following code attached to one of my text box so that when the user enters a tech_ID in the L_TECH # text box the tech's name automatically poplulates in the L_TECH text box. What I didn't think about is if the person doesn't have a tech_ID. Does anyone know how I would minipulate my code?

I want to change it so if the person doesn't have a tech_ID, that text box can be left blank and the user then has to enter the non-tech person's name. Can this be done?

Private Sub L_TECH___AfterUpdate()

    Dim rst As DAO.Recordset
    
    Set rst = CurrentDb.OpenRecordset( _
            "SELECT NAME From TECH " & _
            "WHERE TECH_ID= " & Me.L_TECH__)

    
    If Not (rst.EOF And rst.BOF) Then
    
     '/now populate the textboxes
        Me.L_TECH = rst("NAME")
        
    End If

    Set rst = Nothing
    
End Sub

I have to confess one of my coworkers helped me with this code and he is on vacation as of yesterday. I thank you in advance for your help with this matter.

 

[PHV]

Replace this:
End If
with this:
Else
Me.L_TECH = ""
Me.L_TECH.SetFocus
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TriniGal]

Thank you PH for responding so fast.

Unfortunately, that didn't work. I'm getting the following error

Run-time error '3075':

Syntax (missing operator) in query expression 'TECH_ID='.

When I hit debug, this is what I see

[highlight]

Set rst = CurrentDb.OpenRecordset( _
            "SELECT NAME From TECH " & _
            "WHERE TECH_ID= " & Me.L_TECH__)

[/highlight]

Can you figure this out?

 

[PHV]

You have to test that L_TECH__ id is not Null nor ZeroLength.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TriniGal]

Do you have any idea how I would do that?

 

[PHV]

Private Sub L_TECH___AfterUpdate()
Dim rst As DAO.Recordset
If Trim(Me.L_TECH__ & "") = "" Then
Me.L_TECH = ""
Me.L_TECH.SetFocus
Else
Set rst = CurrentDb.OpenRecordset( _
"SELECT NAME From TECH " & _
"WHERE TECH_ID= " & Me.L_TECH__)
If Not (rst.EOF And rst.BOF) Then
Me.L_TECH = rst("NAME") 'populate the textboxes
Else
Me.L_TECH = ""
Me.L_TECH.SetFocus
End If
Set rst = Nothing
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TriniGal]

Its quitting time right now for me. I will try it on Monday. Thank you so much for all your help.

 

[TriniGal]

PHV,

THANK YOU, THANK YOU, THANK YOU.

That worked PERFECTLY.

Thanks again and have a GREAT WEEKEND.

YOU GET A STAR!