Solved

Multiple DLookup - Access 2002

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now