Solved

Multiple DLookup - Access 2002

Posted on 2004-09-07
5
274 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Convert VBA UDF to SQl SERVER UDF 4 51
Clear Current Value from Combobox 2 26
Access Runtime Error '3070' 5 9
unknown computers in my LAN 12 35
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.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 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