"PopUp Message" notification? warning?

[Rufus]

Can anyone help? I am really not sure exactly how to best approach this
problem. So here goes ..

I have a TABLE called "TableA" of property numbers where the field name &
unique key is called {"APN"} it is a text format. I have another TABLE
called "TableB" and in this table I have a unique key also called {"APN"} and
another field called {"MORITORIUM"} and this field can have several [TYPES].
For example: it can be a "Logging Moratorium", "Water Moratorium", "Special
Moratorium", etc. just like saying it can be different colors : blue, green,
red, etc.

I need a way to search the form/table so that if the {"APN"} does indeed
have a "moratorium" in the table as a record, that a MsgBox will notify me or
the user, of the possible "TYPE" of moratorium, or perhaps merely just a
Msgbox?

Ideas?

 

[Carl Rapson]

It's not completely clear what you're trying to do and how you're setting it
up. If you want this to run from a form, and you want to select a specific
APN and have a message box pop up if that APN has a MORITORIUM record, then
you could place a combo box (named cboBox, for example) on your form with
its RowSource property set to something like

SELECT DISTINCT APN FROM TableA ORDER BY APN;

Then in the AfterUpdate event of the combo box, put code something like

If DCount("*", "TableB", "APN='" & cboBox.Value & "'") > 0 Then
MsgBox "APN '" & cboBox.Value & "' has the Moritorium value of '" & _
DLookUp("MORITORIUM", "TableB", "APN='" & cboBox.Value & "'") & "'"
Else
MsgBox "APN '" & cboBox.Value & "' has no Moritorium"
End If

This may not be the most efficient way to do it, but it's the quickest I
could think of. This assumes that each APN only has a single entry in
TableB. Is this what you had in mind?

HTH,

Carl Rapson

 

[Rufus]

Carl, oooh...I am soooo' close. I get an debugger on the first line?

If DCount("*", "TableB", "APN='" & cboBox.Value & "'") > 0 Then

The message box opens and I type in the APN okay - then I get the debugger.
I halt it and in my combo box drop down there is the APN number I just typed
like in memory ?? i guess thats how you could explain it? Its like it
remembered my last APN number maybe? But when I select it it brings up the
debugger again.

It's not completely clear what you're trying to do and how you're setting it
up. If you want this to run from a form, and you want to select a specific
APN and have a message box pop up if that APN has a MORITORIUM record, then
you could place a combo box (named cboBox, for example) on your form with
its RowSource property set to something like

SELECT DISTINCT APN FROM TableA ORDER BY APN;

Then in the AfterUpdate event of the combo box, put code something like

If DCount("*", "TableB", "APN='" & cboBox.Value & "'") > 0 Then
MsgBox "APN '" & cboBox.Value & "' has the Moritorium value of '" & _
DLookUp("MORITORIUM", "TableB", "APN='" & cboBox.Value & "'") & "'"
Else
MsgBox "APN '" & cboBox.Value & "' has no Moritorium"
End If

This may not be the most efficient way to do it, but it's the quickest I
could think of. This assumes that each APN only has a single entry in
TableB. Is this what you had in mind?

HTH,

Carl Rapson

Can anyone help? I am really not sure exactly how to best approach this
problem. So here goes ..

I have a TABLE called "TableA" of property numbers where the field name &
unique key is called {"APN"} it is a text format. I have another TABLE
called "TableB" and in this table I have a unique key also called {"APN"}
and
another field called {"MORITORIUM"} and this field can have several
[TYPES].
For example: it can be a "Logging Moratorium", "Water Moratorium",
"Special
Moratorium", etc. just like saying it can be different colors : blue,
green,
red, etc.

I need a way to search the form/table so that if the {"APN"} does indeed
have a "moratorium" in the table as a record, that a MsgBox will notify me
or
the user, of the possible "TYPE" of moratorium, or perhaps merely just a
Msgbox?

Ideas?

 

[Carl Rapson]

Rufus,

I guess I don't understand how you are approaching this. What do you mean,
the message box opens and you type in the APN? As far as I know, you can't
enter anything into a message box. Are you using a pop-up form? Where are
you typing the APN number?

When you say 'you get the debugger', I assume you mean Access enters debug
mode and the code appears with that line highlighted. Usually, that is
preceded by an error message box giving you the option to End or Debug. What
error message are you getting? What is the name of your combo box?

Also, if you halt the debugger (rather than correcting the problem and
continuing) then I expect that Access is in an indeterminate state and you
probably can't continue without further problems. Whenever I halt the
debugger, I find I have to close and re-open Access to continue
successfully.

Carl Rapson

Carl, oooh...I am soooo' close. I get an debugger on the first line?

If DCount("*", "TableB", "APN='" & cboBox.Value & "'") > 0 Then

The message box opens and I type in the APN okay - then I get the
debugger.
I halt it and in my combo box drop down there is the APN number I just
typed
like in memory ?? i guess thats how you could explain it? Its like it
remembered my last APN number maybe? But when I select it it brings up
the
debugger again.

It's not completely clear what you're trying to do and how you're setting
it
up. If you want this to run from a form, and you want to select a
specific
APN and have a message box pop up if that APN has a MORITORIUM record,
then
you could place a combo box (named cboBox, for example) on your form with
its RowSource property set to something like

SELECT DISTINCT APN FROM TableA ORDER BY APN;

Then in the AfterUpdate event of the combo box, put code something like

If DCount("*", "TableB", "APN='" & cboBox.Value & "'") > 0 Then
MsgBox "APN '" & cboBox.Value & "' has the Moritorium value of '" & _
DLookUp("MORITORIUM", "TableB", "APN='" & cboBox.Value & "'") &
"'"
Else
MsgBox "APN '" & cboBox.Value & "' has no Moritorium"
End If

This may not be the most efficient way to do it, but it's the quickest I
could think of. This assumes that each APN only has a single entry in
TableB. Is this what you had in mind?

HTH,

Carl Rapson

Can anyone help? I am really not sure exactly how to best approach this
problem. So here goes ..

I have a TABLE called "TableA" of property numbers where the field name
&
unique key is called {"APN"} it is a text format. I have another
TABLE
called "TableB" and in this table I have a unique key also called
{"APN"}
and
another field called {"MORITORIUM"} and this field can have several
[TYPES].
For example: it can be a "Logging Moratorium", "Water Moratorium",
"Special
Moratorium", etc. just like saying it can be different colors : blue,
green,
red, etc.

I need a way to search the form/table so that if the {"APN"} does
indeed
have a "moratorium" in the table as a record, that a MsgBox will notify
me
or
the user, of the possible "TYPE" of moratorium, or perhaps merely just
a
Msgbox?

Ideas?