?
Solved

Do Lookup - if exists, do this, if does not exist, do that...

Posted on 2004-11-17
14
Medium Priority
?
209 Views
Last Modified: 2012-05-05
I have a form called frmMain.

I have a table called Engineers.

On my form, if a user enters an engineer number, I'd like the after_update event to check a couple of things - there are 3 fields, one called 'Engineer number', andother called 'End date' and another called 'mobile'.  If 'End date' is null, and if 'Engineer number' exists, then I'd like the 'mobile' number inserted into a field called 'Engtel' on my form.

If the 'Engineer number' does not exist, I'd like a msgbox "does not exist".

If the 'End date' is not null, then a msgbox "Engineer terminated on " & [End date].

Easy enough?  I'm just getting to grips with Dlookups, but there are a few variables in my criteria that I can't seem to place correctly.  Can anyone assist?

Thanks,
Lapchien
0
Comment
Question by:Lapchien
  • 6
  • 5
  • 3
14 Comments
 
LVL 14

Expert Comment

by:bluelizard
ID: 12612366
to look up if the engineer number x exist, you can try:

dim e as variant

e = dlookup("[Engineer number]", "Engineers", "[Engineer number] = " & x )

if not ( isnull ( e ) ) then
  ' engineer number x exists
else
  ' engineer number x does not exist
  msgbox("does not exist")
end if


--bluelizard
0
 
LVL 14

Expert Comment

by:bluelizard
ID: 12612382
similar for the end date:

e = dlookup("[End date]", "Engineers", "[Engineer number] = " & x )

if not ( isnull ( e ) ) then
  ' engineer number x exists and there is a non-null end date
  msgbox("Engineer terminated on " & format(e, "yyyy-mm-dd"))
else
  ' engineer number x does not exist or end date is null
end if


--bluelizard
0
 

Author Comment

by:Lapchien
ID: 12612419
first one - i get missing operator in query expression '[Engineer number] ='

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12612521
If Nz([engineer number],"")<>"" And Nz([[End date],"")<>"" Then
   Me!Engtel=Mobile
ElseIf Nz([engineer number],"")="" Then
  MsgBox "does not exist"
ElseIf Nz([[End date],"")<>"" Then
  MsgBox "Engineer terminated on " & [End date].
End If
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12612554
Correction..., Extra [

If Nz([engineer number],"")<>"" And Nz([End date],"")<>"" Then
   Me!Engtel=Mobile
ElseIf Nz([engineer number],"")="" Then
  MsgBox "does not exist"
ElseIf Nz([[End date],"")<>"" Then
  MsgBox "Engineer terminated on " & [End date].
End If

If you have to use DLookup, try:

If Nz(DLookup("[Engineer number]", "Engineers", "[SomeID] = " & Me!txtID),"")<>"" And Nz(DLookup("[End date]", "Engineers", "[SomeID] = " & Me!txtID),"")<>"" Then
   Me!Engtel=Mobile
ElseIf Nz(DLookup("[Engineer number]", "Engineers", "[SomeID] = " & Me!txtID),"")="" Then
  MsgBox "does not exist"
ElseIf Nz(DLookup("[End date]", "Engineers", "[SomeID] = " & Me!txtID),"")<>"" Then
  MsgBox "Engineer terminated on " & [End date].
End If

Note, if you criteria is text enclose it with two 's like:

DLookup("[End date]", "Engineers", "[SomeName] = '" & Me!txtName & "'")

mike
0
 
LVL 14

Expert Comment

by:bluelizard
ID: 12612565
can you post the exact code line that causes the error?
0
 

Author Comment

by:Lapchien
ID: 12612704
Ok, this is what I'm using:

Private Sub Engineer_AfterUpdate()

If Nz(DLookup("[Engineer number]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer), "") <> "" And Nz(DLookup("[End date]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer), "") <> "" Then

'do this code...
 
 Else
 
If Nz(DLookup("[Engineer number]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer), "") = "" Then
  MsgBox "Engineer number you entered does not exist"
    Cancel = True
    Me.Engineer.SetFocus
   
Else

If Nz(DLookup("[End date]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer), "") <> "" Then
  MsgBox "Engineer terminated on " & "[End date]"
End If
End If
End If


couple of things - if the engineer number does not exist, i'd like the focus to remain on, or go back to, Engineer text box on the form, for the user to enter a correct engineer number.

If End date on the table is not null, then i need a msgbiox "engineer terminated".  currently the code above for thsi does not work!

0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12612751
To do this use similar code in before update event where you can use

Cancel = True

after sending focus to a desired txtbox or event enabling or desabling some key txt boxes.  Make sure not to disable a control that has focus.  Send focus to another control before disabling it.

Mike
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12612756
use this:

If Nz(DLookup("[Engineer number]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer), "") <> "" And Nz(DLookup("[End date]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer), "") <> "" Then

 
 ElseIf Nz(DLookup("[Engineer number]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer), "") = "" Then
  MsgBox "Engineer number you entered does not exist"
    Cancel = True
    Me.Engineer.SetFocus
   
Elseif Nz(DLookup("[End date]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer), "") <> "" Then
  MsgBox "Engineer terminated on " & "[End date]"
End If
0
 

Author Comment

by:Lapchien
ID: 12612810
Ok, that works fine for the - If engineer number does not exist, msg "does not exist".

But this part of the code:

Elseif Nz(DLookup("[End date]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer), "") <> "" Then
  MsgBox "Engineer terminated on " & "[End date]"

just doesn't work!

0
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 2000 total points
ID: 12612854
correction...

Elseif Nz(DLookup("[End date]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer), "") = "" Then
  MsgBox "Engineer terminated on " & "[End date]"
0
 

Author Comment

by:Lapchien
ID: 12613191
Using this:

Dim dEnddate As Date

dEnddate = DLookup("[End date]", "dbo_Engineers")

If Nz(DLookup("[End date]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer), "") <> "" Then
MsgBox "Engineer terminated on " & dEnddate & ". Select valid engineer number.", vbCritical, "Engineer Terminated!"
Cancel = True
End If

Just returns 'End date' as the first end date in the table.  How can I return the correct end date for the engineer, based on 'Engineer number'?
0
 

Author Comment

by:Lapchien
ID: 12613313
Seem to have gotten over that - however, with this almost completed code:

Dim dEnddate As Date

dEnddate = DLookup("[End date]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer)

If Nz(DLookup("[End date]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer), "") <> "" Then
MsgBox "Engineer terminated on " & dEnddate & ". Select valid engineer number.", vbCritical, "Engineer Terminated!"

ElseIf Nz(DLookup("[Engineer number]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer), "") <> "" Then

Me.Line63.Visible = False
Me.Label62.Visible = False
Me.Label64.Visible = False

Me.Text35 = Null

Me.Check66 = False

    If Me.Rate3 > 0 Then
        ratetoengineer = Rate3
    ElseIf Me.Rate2 > 0 Then
        ratetoengineer = Rate2
    ElseIf Me.Rate1 > 0 Then
        ratetoengineer = Rate1
    Else
        ratetoengineer = Null
    End If


 If Me.Check48.Value = True Then
 Me.Text50 = "P/HH"
 ElseIf Me.Check48.Value = False Then
 Me.Text50 = "P/H"
 End If
 
    ElseIf Nz(DLookup("[Engineer number]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer), "") = "" Then
    MsgBox "Engineer number you entered does not exist.  Please check and try again.", vbExclamation, "Allocations"
    Cancel = True
End If


I get 'Invalid use of Null' runtime 94, with

dEnddate = DLookup("[End date]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer)

highlighted, if I enter an enineer number that does not exist!
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12613315
Why you have this:

Dim dEnddate As Date
dEnddate = DLookup("[End date]", "dbo_Engineers")   '<-- this  doesn't have any criteria and will return first record
-------------

Make sure field "[Engineer number]" is not string if it is use:

"[Engineer number] = '" & Me!Engineer & "'"),
---------------

It is 3 am PST, will log out shortly.

mike
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Implementing simple internal controls in the Microsoft Access application.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question