Solved

Multiple DLookup - Access 2002

Posted on 2004-09-07
5
281 Views
Last Modified: 2008-03-06
g'day,

I have check box "HasSecondaryAddress" in form frmContacts which serves to flag those contacts that are located at a branch of the company. What I want to do is as follows:

1) If true, dlookup tblBranchAddress and check for CompanyID and ContactID
         if a match if found display msgbox "name already there"
         exit sub
         if there is no match append record

2) If false, dlookup tblBranchAddress and check for CompanyID and ContactID
         if a match is found display msgbox "do you want to delete?"
              if yes delete record
              if no abort
          if a match is not found exit sub

I have the following code on the AfterUpdate event of the ckbox (messages are for testing purposes only and will be replaced with proper code later):

Private Sub HasSecondaryAddress_AfterUpdate()

Select Case "HasSecondaryAddress"

    Case True

        If (DLookup("CompanyID", "tblBranchAddress", "CompanyID" = Me![CompanyID] And _
                                                     "ContactID" = Me![ContactID])) Then
   
        MsgBox "contact there"
       
        Else
   
        MsgBox "contact not there"
       
        End If

    Case False

        If (DLookup("CompanyID", "tblBranchAddress", "CompanyID" = Me![CompanyID] And _
                                                     "ContactID" = Me![ContactID])) Then

        MsgBox "Do you want to delete this record?"

        Else
       
        MsgBox "aborting procedure"
        Exit Sub
       
        End If

    End Select

End Sub

Nothing happens. There are no message or error messages displayed. Any ideas?

thanks

Dave
0
Comment
Question by:dcobau
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 34

Accepted Solution

by:
flavo earned 150 total points
ID: 12002327
g'mate!

Private Sub HasSecondaryAddress_AfterUpdate()

Select Case Me.HasSecondaryAddress

    Case True

        If Not IsNull(DLookup("CompanyID", "tblBranchAddress", "CompanyID" = Me![CompanyID] And _
                                                     "ContactID" = Me![ContactID])) Then
            MsgBox "contact there"
        Else
            MsgBox "contact not there"
        End If

    Case False

        If Not IsNull(DLookup("CompanyID", "tblBranchAddress", "CompanyID" = Me![CompanyID] And _
                                                     "ContactID" = Me![ContactID])) Then
              MsgBox "Do you want to delete this record?"
        Else
            MsgBox "aborting procedure"
        End If

    End Select

End Sub




Tell me how it goes!

Dave :-)
0
 
LVL 34

Expert Comment

by:flavo
ID: 12002332
>> g'mate!

g'day mate!
0
 
LVL 34

Expert Comment

by:flavo
ID: 12002358
why the "B"??? - Dont really care but interested why...???
0
 

Author Comment

by:dcobau
ID: 12002375
What "B"??

When I accepted your answer I thought I also sent a message but it does not appear in here so I repeate it:

Easiest points I ever given away")))) thanks pal

Dave
0
 
LVL 34

Expert Comment

by:flavo
ID: 12002388
agghh...

When selecting a Q as the answer, you have the opertunity to give the 'expert' (me) a grade, A, B or C..

It must default to B.
see here:

http://www.experts-exchange.com/Databases/MS_Access/help.jsp#hs4

Cheers mate!

Dave
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

707 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