DLOOKUP with Multiple Keys

  • Thread starter Thread starter bw
  • Start date Start date
B

bw

My Table tblModules has...
an AddrID field where duplicates are ok.
a MonicsID field where duplicates are ok.

The main form (frmModules) links to the sub form (frmModulesSubForm1) where Child
and Master Fields are AddrID.

All of the following displays tell me all references in them are correct.

MsgBox "Parent Name is " & Me.Parent.Name
MsgBox "SubForm Name is " & Form.Name
MsgBox "Current AddrID is " & Me.Parent.AddrID
MsgBox "Current MonicsID is " & Me.Parent.frmModulesSubForm1.Form.Mnemonic
MsgBox "tblModules MonicsID is " & DLookup("MonicsID", "tblModules", "[Mnemonic]
='ADS'")

So my question is, how do I use DLOOKUP to determine if there are duplicate records
in tblModules, when for each AddrID, there can be many MonicsID values, some of
which may be duplicates? Since we have to "look" at both the AdddrID field, and the
MonicsID field I don't know how to modify code as below, to accomplish this.

X = DLookup("[MonicsID]", "tblModules", "[Mnemonic]='ADS'")
is what I want, but it doesn't take into account the value of AddrID.

Thanks for whatever help you can provide.
Bernie
 
Don't use DLOOKUP, since it is a function that should be
used to return a single value, which in your case would
not be helpful.
If you are actually interested in the duplicate records
themselves, you need to have a query that figures that
out. Access has a wizard that will make that for you if
that is indeed what you want.
It isn't quite clear to me exactly what you are doing.
Can you elaborate a bit more on what you need the
MonicsID value for.
-----Original Message-----
My Table tblModules has...
an AddrID field where duplicates are ok.
a MonicsID field where duplicates are ok.

The main form (frmModules) links to the sub form
Click to expand...
(frmModulesSubForm1) where Child
and Master Fields are AddrID.

All of the following displays tell me all references in them are correct.

MsgBox "Parent Name is " & Me.Parent.Name
MsgBox "SubForm Name is " & Form.Name
MsgBox "Current AddrID is " & Me.Parent.AddrID
MsgBox "Current MonicsID is " & Me.Parent.frmModulesSubForm1.Form.Mnemonic
MsgBox "tblModules MonicsID is " & DLookup
Click to expand...
("MonicsID", "tblModules", "[Mnemonic]
='ADS'")

So my question is, how do I use DLOOKUP to determine if there are duplicate records
in tblModules, when for each AddrID, there can be many MonicsID values, some of
which may be duplicates? Since we have to "look" at
Click to expand...
both the AdddrID field, and the
MonicsID field I don't know how to modify code as below, to accomplish this.

X = DLookup("[MonicsID]", "tblModules", "[Mnemonic] ='ADS'")
is what I want, but it doesn't take into account the value of AddrID.

Thanks for whatever help you can provide.
Bernie






.
Click to expand...
 
In my subform I am allowing duplicates, but there is a single Mnemonic = "ADS" which I
DO NOT want to be entered more than once . DLOOKUP returns a single value, which
is exactly why I am trying to us it.

How do you suggest I do it?

Thanks,
Bernie


Don't use DLOOKUP, since it is a function that should be
used to return a single value, which in your case would
not be helpful.
If you are actually interested in the duplicate records
themselves, you need to have a query that figures that
out. Access has a wizard that will make that for you if
that is indeed what you want.
It isn't quite clear to me exactly what you are doing.
Can you elaborate a bit more on what you need the
MonicsID value for.
-----Original Message-----
My Table tblModules has...
an AddrID field where duplicates are ok.
a MonicsID field where duplicates are ok.

The main form (frmModules) links to the sub form
Click to expand...
(frmModulesSubForm1) where Child
and Master Fields are AddrID.

All of the following displays tell me all references in them are correct.

MsgBox "Parent Name is " & Me.Parent.Name
MsgBox "SubForm Name is " & Form.Name
MsgBox "Current AddrID is " & Me.Parent.AddrID
MsgBox "Current MonicsID is " & Me.Parent.frmModulesSubForm1.Form.Mnemonic
MsgBox "tblModules MonicsID is " & DLookup
Click to expand...
("MonicsID", "tblModules", "[Mnemonic]
='ADS'")

So my question is, how do I use DLOOKUP to determine if there are duplicate records
in tblModules, when for each AddrID, there can be many MonicsID values, some of
which may be duplicates? Since we have to "look" at
Click to expand...
both the AdddrID field, and the
MonicsID field I don't know how to modify code as below, to accomplish this.

X = DLookup("[MonicsID]", "tblModules", "[Mnemonic] ='ADS'")
is what I want, but it doesn't take into account the value of AddrID.

Thanks for whatever help you can provide.
Bernie






.
Click to expand...
Click to expand...
 
X = DLookup("[MonicsID]", "tblModules", "[Mnemonic]='ADS'")

DLookup("[MonicsID]", "tblModules", "([Mnemonic]='ADS') and ([AddrID]=" &
Me.Parent.AddrID)
 
Back
Top