?
Solved

Multiple DLookup - Access 2002

Posted on 2004-09-07
5
Medium Priority
?
284 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 450 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…
Suggested Courses

764 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