Lookup if record exists and retrieve another field from record

[Rich1234]

Hi

I have two tables:
Vehicles (PK: VRM)
Awards (PK: AwdID. Other fields: AwardString, VRM)

Tables are joined in one to many using VRM (ie Vehicles is on "one" side.)
I have a main form, Vehicles, with a continuous subform, Awards.
When adding new records to the Awards subform (ie entering into the
"AwardString" field), VRM is of course assigned automatically to be the same
as the record showing in the main form. I want to alert the user if the
AwardString has already been entered but still permit them to enter it if
they wish. For this reason I do not want to index the AwardString field and
set allow duplicates to no.

Here's my question for you:

How can I lookup to see if the awardstring has already been entered, and
then display one of two messages depending on whether the award has already
been assigned to this VRM (ie the vehicle record showing in the main form) or
a different VRM?
I know how to check to see if the awardstring exists by putting the
following code in the AfterUpdate event of the field:

If Not IsNull(DLookup("[AwardString]", "[Awards]", "[AwardString] = '" &
Me![AwardString] & "'")) Then
MsgBox "This awardstring already exists in the database."
How do I also include code to check which VRM the awardstring was entered
for, and then display a message saying either, "This vehicle already has this
awardstring assigned," or ,"Another vehicle " & [VRM] &" already has this
awardstring assigned"?

TIA
rich

 

[Klatuu]

What you look up and what you return don't have to be the same. If you want
to know what vehicle has the award string, return the VRM instead of the
AwardString:

Dim varVRM as Variant
varVRM = (DLookup("[VRM]", "[Awards]", "[AwardString] = '" _
& Me![AwardString] & "'"))
If Not IsNull(varVRM) Then
MsgBox "Award String " & Me.AwardString & " is already assigned to " _
& varVRM
End If

 

[Rich1234]

Great answer Klatuu.
Got it working and have adapted it to selectively display different messages.

Thanks
rich

What you look up and what you return don't have to be the same. If you want
to know what vehicle has the award string, return the VRM instead of the
AwardString:

Dim varVRM as Variant
varVRM = (DLookup("[VRM]", "[Awards]", "[AwardString] = '" _
& Me![AwardString] & "'"))
If Not IsNull(varVRM) Then
MsgBox "Award String " & Me.AwardString & " is already assigned to " _
& varVRM
End If

Hi

I have two tables:
Vehicles (PK: VRM)
Awards (PK: AwdID. Other fields: AwardString, VRM)

Tables are joined in one to many using VRM (ie Vehicles is on "one" side.)
I have a main form, Vehicles, with a continuous subform, Awards.
When adding new records to the Awards subform (ie entering into the
"AwardString" field), VRM is of course assigned automatically to be the same
as the record showing in the main form. I want to alert the user if the
AwardString has already been entered but still permit them to enter it if
they wish. For this reason I do not want to index the AwardString field and
set allow duplicates to no.

Here's my question for you:

How can I lookup to see if the awardstring has already been entered, and
then display one of two messages depending on whether the award has already
been assigned to this VRM (ie the vehicle record showing in the main form) or
a different VRM?
I know how to check to see if the awardstring exists by putting the
following code in the AfterUpdate event of the field:

If Not IsNull(DLookup("[AwardString]", "[Awards]", "[AwardString] = '" &
Me![AwardString] & "'")) Then
MsgBox "This awardstring already exists in the database."
How do I also include code to check which VRM the awardstring was entered
for, and then display a message saying either, "This vehicle already has this
awardstring assigned," or ,"Another vehicle " & [VRM] &" already has this
awardstring assigned"?

TIA
rich

 

[Rich1234]

One more thought....
is it possible to show all vehicles that may have that award? This just
returns one vehicle, even if many vehicles have the same award assigned.
thanks again
rich

What you look up and what you return don't have to be the same. If you want
to know what vehicle has the award string, return the VRM instead of the
AwardString:

Dim varVRM as Variant
varVRM = (DLookup("[VRM]", "[Awards]", "[AwardString] = '" _
& Me![AwardString] & "'"))
If Not IsNull(varVRM) Then
MsgBox "Award String " & Me.AwardString & " is already assigned to " _
& varVRM
End If

Hi

I have two tables:
Vehicles (PK: VRM)
Awards (PK: AwdID. Other fields: AwardString, VRM)

Tables are joined in one to many using VRM (ie Vehicles is on "one" side.)
I have a main form, Vehicles, with a continuous subform, Awards.
When adding new records to the Awards subform (ie entering into the
"AwardString" field), VRM is of course assigned automatically to be the same
as the record showing in the main form. I want to alert the user if the
AwardString has already been entered but still permit them to enter it if
they wish. For this reason I do not want to index the AwardString field and
set allow duplicates to no.

Here's my question for you:

How can I lookup to see if the awardstring has already been entered, and
then display one of two messages depending on whether the award has already
been assigned to this VRM (ie the vehicle record showing in the main form) or
a different VRM?
I know how to check to see if the awardstring exists by putting the
following code in the AfterUpdate event of the field:

If Not IsNull(DLookup("[AwardString]", "[Awards]", "[AwardString] = '" &
Me![AwardString] & "'")) Then
MsgBox "This awardstring already exists in the database."
How do I also include code to check which VRM the awardstring was entered
for, and then display a message saying either, "This vehicle already has this
awardstring assigned," or ,"Another vehicle " & [VRM] &" already has this
awardstring assigned"?

TIA
rich

 

[Klatuu]

Sure, you could do that.
My first thought would be to create a Select query that would return all the
vehicles that have a specific award string, then create a form set to
Datasheet mode with the query as the record source. I would also set it up
not to allow any edits, adds, or deletes. I would also do the same test you
are using now and if you get a hit, then open this form.

One more thought....
is it possible to show all vehicles that may have that award? This just
returns one vehicle, even if many vehicles have the same award assigned.
thanks again
rich

What you look up and what you return don't have to be the same. If you want
to know what vehicle has the award string, return the VRM instead of the
AwardString:

Dim varVRM as Variant
varVRM = (DLookup("[VRM]", "[Awards]", "[AwardString] = '" _
& Me![AwardString] & "'"))
If Not IsNull(varVRM) Then
MsgBox "Award String " & Me.AwardString & " is already assigned to " _
& varVRM
End If

Hi

I have two tables:
Vehicles (PK: VRM)
Awards (PK: AwdID. Other fields: AwardString, VRM)

Tables are joined in one to many using VRM (ie Vehicles is on "one" side.)
I have a main form, Vehicles, with a continuous subform, Awards.
When adding new records to the Awards subform (ie entering into the
"AwardString" field), VRM is of course assigned automatically to be the same
as the record showing in the main form. I want to alert the user if the
AwardString has already been entered but still permit them to enter it if
they wish. For this reason I do not want to index the AwardString field and
set allow duplicates to no.

Here's my question for you:

How can I lookup to see if the awardstring has already been entered, and
then display one of two messages depending on whether the award has already
been assigned to this VRM (ie the vehicle record showing in the main form) or
a different VRM?
I know how to check to see if the awardstring exists by putting the
following code in the AfterUpdate event of the field:

If Not IsNull(DLookup("[AwardString]", "[Awards]", "[AwardString] = '" &
Me![AwardString] & "'")) Then
MsgBox "This awardstring already exists in the database."
How do I also include code to check which VRM the awardstring was entered
for, and then display a message saying either, "This vehicle already has this
awardstring assigned," or ,"Another vehicle " & [VRM] &" already has this
awardstring assigned"?

TIA
rich