Check if a value already exists for a certain organisation only

[Rhys Davies]

Hi - i posted a question here yesterday asking how to check if a membership
number already exists in the database on exit from the membership no field,
and the following code works fine:

If Not IsNull(DLookup("membershipno", "tbldramaloan", "[membershipno]='" &
Me!membershipno & "'")) Then
MsgBox "You already have a loan issued to this membership number." & vbCrLf
& _
"Please ensure that you allocate repayments to the relevant loan."
End If

However, the loan information in this table can have multiple membership
number rows (the membership number is not unique), which is fine because
multiple rows means that the individual with that particular membership
number has taken out multiple loans. However that loan table is also linked
to an organisation table, which contains the names of organisations who
provide the loans, and membership number 1034 for example could be present in
more than one organisation. The issue is that i want the dlookup to only
look for duplicate membership numbers within the specific organisation and as
far as i know i cant add additional criteria to a dlookup to allow for this.
for every loan that is created the membership number is entered manually and
a companyID is automatically entered that ties it back to the organisation
that is providing the loan. this is done by having
forms!frmcompany!companyID in the default value of the companyID field on the
loan form. I was wondering if there was a way of using a query/query by form
rather than the loan table to restrict the loans to just the relevant
organisation so the dlookup would then lookup the query but i cant seem to
get that to work either.
Any suggestions?

Thanks,

Rhys.

 

[Paolo]

Hi,

if you create a query with the following SQL structure
SELECT tbldramaloan.organisation, tbldramaloan.organisation,
Count(tbldramaloan.membershipno) AS tot_mmbrshpn
FROM tbldramaloan
GROUP BY tbldramaloan.organisation, tbldramaloan.organisation

you'll have in tot_mmbrshpn the number of membershipno per every
organisation so
you can dlookup this query and if tot_mmbrshpn>1 will means that you have a
membershipno with more than one loan from the same organisation.

HTH Paolo

 

[Rhys Davies]

Hi Paolo, i probably havent explained myself very well - its fine to have
more than one loan per membership number.

in tbldramaloan there will be for example the following entries:

Membership number - companyID - loanvalue
834 12 1000
834 12 500
834 25 2000

this shows that membership number 834 that is related to companyID 12 has 2
loans, one for 1000 and one for 500
and membership number 834, which is a different person that is related to
companyID 25 has a loan for 2000 and this is fine

however when i create a new loan, i go into the organisation screen and
click on 'new LOAN' - this then opens up a new loan screen and automatically
enters the companyID from the organisation screen into 'companyID' on the
loan screen so that the loan will be linked to the organisation. i then
enter the membership number, and when i exit the membership number field it
runs the dlookup code that you kindly supplied me with. However that dlookup
references the entire table. therefore it is only searching for the
membership number, it doesnt care which organisation it is linked to. I want
the dlookup to only look for a membership number linked to the organisation
that i am adding the loan to i.e. if i add a new loan using membership number
834 linked to companyID 25 and another one already exists i want the pop up
box to appear telling me this, however if i add a new loan using membership
number 834 linked to companyID 40 and there is no other loan with membership
number 834 linked to companyID 40 but there is one for 834 and companyID 25
then i dont want the message to appear because i only want to know about
loans within the organisation i am currently adding the loan to - ive added
additional code to the dlookup in the 'where' clause below to show what i
logically want to do but im assuming that dlookup cant have multiple where
conditions, therefore is there another way around it?

If Not IsNull(DLookup("membershipno", "qrydramaloan", "[membershipno]='" &
Me!membershipno & "'" & "[companyID]=" & Me!companyID)) Then
MsgBox "You already have a loan issued to this membership number." & vbCrLf
& _
"Please ensure that you allocate repayments to the relevant loan."
End If

Thanks,

Rhys.

Hi,

if you create a query with the following SQL structure
SELECT tbldramaloan.organisation, tbldramaloan.organisation,
Count(tbldramaloan.membershipno) AS tot_mmbrshpn
FROM tbldramaloan
GROUP BY tbldramaloan.organisation, tbldramaloan.organisation

you'll have in tot_mmbrshpn the number of membershipno per every
organisation so
you can dlookup this query and if tot_mmbrshpn>1 will means that you have a
membershipno with more than one loan from the same organisation.

HTH Paolo

Hi - i posted a question here yesterday asking how to check if a membership
number already exists in the database on exit from the membership no field,
and the following code works fine:

If Not IsNull(DLookup("membershipno", "tbldramaloan", "[membershipno]='" &
Me!membershipno & "'")) Then
MsgBox "You already have a loan issued to this membership number." & vbCrLf
& _
"Please ensure that you allocate repayments to the relevant loan."
End If

However, the loan information in this table can have multiple membership
number rows (the membership number is not unique), which is fine because
multiple rows means that the individual with that particular membership
number has taken out multiple loans. However that loan table is also linked
to an organisation table, which contains the names of organisations who
provide the loans, and membership number 1034 for example could be present in
more than one organisation. The issue is that i want the dlookup to only
look for duplicate membership numbers within the specific organisation and as
far as i know i cant add additional criteria to a dlookup to allow for this.
for every loan that is created the membership number is entered manually and
a companyID is automatically entered that ties it back to the organisation
that is providing the loan. this is done by having
forms!frmcompany!companyID in the default value of the companyID field on the
loan form. I was wondering if there was a way of using a query/query by form
rather than the loan table to restrict the loans to just the relevant
organisation so the dlookup would then lookup the query but i cant seem to
get that to work either.
Any suggestions?

Thanks,

Rhys.

 

[Paolo]

Sorry, I misunderstood your question. Sometimes it happens 'cause I'm not a
native english speaker...BTW I hope my english is understandable.

Well you can have 2 conditions in a dlookup. Do that in this way

If Not IsNull(DLookup("membershipno", "qrydramaloan", "[membershipno]='" &
Me!membershipno & "' and [companyID]=" & Me!companyID)) Then
etc.etc.

if companyID is defined as number the above statement'll work well. If is
defined as string put it between quotes as follow:
If Not IsNull(DLookup("membershipno", "qrydramaloan", "[membershipno]='" &
Me!membershipno & "' and [companyID]='" & Me!companyID & "'")) Then
etc.etc.

HAND Paolo

Hi Paolo, i probably havent explained myself very well - its fine to have
more than one loan per membership number.

in tbldramaloan there will be for example the following entries:

Membership number - companyID - loanvalue
834 12 1000
834 12 500
834 25 2000

this shows that membership number 834 that is related to companyID 12 has 2
loans, one for 1000 and one for 500
and membership number 834, which is a different person that is related to
companyID 25 has a loan for 2000 and this is fine

however when i create a new loan, i go into the organisation screen and
click on 'new LOAN' - this then opens up a new loan screen and automatically
enters the companyID from the organisation screen into 'companyID' on the
loan screen so that the loan will be linked to the organisation. i then
enter the membership number, and when i exit the membership number field it
runs the dlookup code that you kindly supplied me with. However that dlookup
references the entire table. therefore it is only searching for the
membership number, it doesnt care which organisation it is linked to. I want
the dlookup to only look for a membership number linked to the organisation
that i am adding the loan to i.e. if i add a new loan using membership number
834 linked to companyID 25 and another one already exists i want the pop up
box to appear telling me this, however if i add a new loan using membership
number 834 linked to companyID 40 and there is no other loan with membership
number 834 linked to companyID 40 but there is one for 834 and companyID 25
then i dont want the message to appear because i only want to know about
loans within the organisation i am currently adding the loan to - ive added
additional code to the dlookup in the 'where' clause below to show what i
logically want to do but im assuming that dlookup cant have multiple where
conditions, therefore is there another way around it?

If Not IsNull(DLookup("membershipno", "qrydramaloan", "[membershipno]='" &
Me!membershipno & "'" & "[companyID]=" & Me!companyID)) Then
MsgBox "You already have a loan issued to this membership number." & vbCrLf
& _
"Please ensure that you allocate repayments to the relevant loan."
End If

Thanks,

Rhys.

Hi,

if you create a query with the following SQL structure
SELECT tbldramaloan.organisation, tbldramaloan.organisation,
Count(tbldramaloan.membershipno) AS tot_mmbrshpn
FROM tbldramaloan
GROUP BY tbldramaloan.organisation, tbldramaloan.organisation

you'll have in tot_mmbrshpn the number of membershipno per every
organisation so
you can dlookup this query and if tot_mmbrshpn>1 will means that you have a
membershipno with more than one loan from the same organisation.

HTH Paolo

Hi - i posted a question here yesterday asking how to check if a membership
number already exists in the database on exit from the membership no field,
and the following code works fine:

If Not IsNull(DLookup("membershipno", "tbldramaloan", "[membershipno]='" &
Me!membershipno & "'")) Then
MsgBox "You already have a loan issued to this membership number." & vbCrLf
& _
"Please ensure that you allocate repayments to the relevant loan."
End If

However, the loan information in this table can have multiple membership
number rows (the membership number is not unique), which is fine because
multiple rows means that the individual with that particular membership
number has taken out multiple loans. However that loan table is also linked
to an organisation table, which contains the names of organisations who
provide the loans, and membership number 1034 for example could be present in
more than one organisation. The issue is that i want the dlookup to only
look for duplicate membership numbers within the specific organisation and as
far as i know i cant add additional criteria to a dlookup to allow for this.
for every loan that is created the membership number is entered manually and
a companyID is automatically entered that ties it back to the organisation
that is providing the loan. this is done by having
forms!frmcompany!companyID in the default value of the companyID field on the
loan form. I was wondering if there was a way of using a query/query by form
rather than the loan table to restrict the loans to just the relevant
organisation so the dlookup would then lookup the query but i cant seem to
get that to work either.
Any suggestions?

Thanks,

Rhys.