[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Multiple DLookup - Access 2002

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

656 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