MsgBox for null "matching" field

[MikeF]

The following code opens a form that contains detail info on only *some* of
the records in the master form.

Would like to adjust so when I click on the button, if there's no matching
AdvID in the detail form, a MsgBox appears and states as such, instead of
opening the detail form at a new blank record.

Any assistance is greatly appreciated.
Regards,
- Mike


Private Sub btnFrmAdvSummary_Click()
On Error GoTo Err_btnFrmAdvSummary_Click

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "frmAdvSummary"

strLinkCriteria = "[AdvID] = """ & Me![AdvID] & """"


DoCmd.OpenForm strDocName, _
WhereCondition:=strLinkCriteria, _
WindowMode:=acDialog

Exit_btnFrmAdvSummary_Click:
Exit Sub

Err_btnFrmAdvSummary_Click:
MsgBox Err.Description
Resume Exit_btnFrmAdvSummary_Click



End Sub

 

[Jack Leach]

If you need this before the form opens, I think you'll have to pull an SQL
and check for a recordcount.

If you don't mind having the form open, followed by the message box, you
could do something like this:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.Recordcount = 0 Then
MsgBox "There are no records"
Cancel = True
End If
End Sub


Maybe you could modifiy this a bit and open the form as Hidden, and if
there's records, show the form? That way the user wouldn't see the form and
msgbox before the form closes again.

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[MikeF]

Jack, thanx.
On a semi-related subject, please see the following post from earlier today...
Subject: Change drive letter in linked table manager
... Any ideas?

- Mike

If you need this before the form opens, I think you'll have to pull an SQL
and check for a recordcount.

If you don't mind having the form open, followed by the message box, you
could do something like this:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.Recordcount = 0 Then
MsgBox "There are no records"
Cancel = True
End If
End Sub


Maybe you could modifiy this a bit and open the form as Hidden, and if
there's records, show the form? That way the user wouldn't see the form and
msgbox before the form closes again.

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

The following code opens a form that contains detail info on only *some* of
the records in the master form.

Would like to adjust so when I click on the button, if there's no matching
AdvID in the detail form, a MsgBox appears and states as such, instead of
opening the detail form at a new blank record.

Any assistance is greatly appreciated.
Regards,
- Mike


Private Sub btnFrmAdvSummary_Click()
On Error GoTo Err_btnFrmAdvSummary_Click

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "frmAdvSummary"

strLinkCriteria = "[AdvID] = """ & Me![AdvID] & """"


DoCmd.OpenForm strDocName, _
WhereCondition:=strLinkCriteria, _
WindowMode:=acDialog

Exit_btnFrmAdvSummary_Click:
Exit Sub

Err_btnFrmAdvSummary_Click:
MsgBox Err.Description
Resume Exit_btnFrmAdvSummary_Click



End Sub

 

[Jack Leach]

Subject: Change drive letter in linked table manager

No ideas other than what's already been said (I use a slightly modified
version of Dev's code, and have never had any issues with it... throw it in
it's own module and you're good to go). I suspect that by the time you get
what you are looking for you might have code almost as complex as Dev's to
make sure everything goes as planned. I would just use his.


Anyway, as far as the actual subject at hand, I think Jim's idea of using
Dlookup is way better. You'll get the first record it finds, or nothing, and
can base your msgbox from there without having to open the form

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Jack, thanx.
On a semi-related subject, please see the following post from earlier today...
Subject: Change drive letter in linked table manager
.. Any ideas?

- Mike

If you need this before the form opens, I think you'll have to pull an SQL
and check for a recordcount.

If you don't mind having the form open, followed by the message box, you
could do something like this:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.Recordcount = 0 Then
MsgBox "There are no records"
Cancel = True
End If
End Sub


Maybe you could modifiy this a bit and open the form as Hidden, and if
there's records, show the form? That way the user wouldn't see the form and
msgbox before the form closes again.

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

The following code opens a form that contains detail info on only *some* of
the records in the master form.

Would like to adjust so when I click on the button, if there's no matching
AdvID in the detail form, a MsgBox appears and states as such, instead of
opening the detail form at a new blank record.

Any assistance is greatly appreciated.
Regards,
- Mike


Private Sub btnFrmAdvSummary_Click()
On Error GoTo Err_btnFrmAdvSummary_Click

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "frmAdvSummary"

strLinkCriteria = "[AdvID] = """ & Me![AdvID] & """"


DoCmd.OpenForm strDocName, _
WhereCondition:=strLinkCriteria, _
WindowMode:=acDialog

Exit_btnFrmAdvSummary_Click:
Exit Sub

Err_btnFrmAdvSummary_Click:
MsgBox Err.Description
Resume Exit_btnFrmAdvSummary_Click



End Sub

 

[MikeF]

That works great, thank you!!
Mike

Assuming that frmAdvSummary is bound to a table which has a field called
AdvID, just do a DLookup before you open the form:

If IsNull(DLookup("*","YourTableNameHere","AdvID = " & AdvID)) then
msgbox "There are no detail records (or whatver you want the message to
be)"
Else
DoCmd.OpenForm...
End If

replace 'YourTableNameHere' with the table name the form is bound to. This
also assumes that AdvID is a numeric field - ifnot, then you would have to
surround it with quotes in the DLookup:

If IsNull(DLookup("*","YourTableNameHere","AdvID = """ & AdvID & """"))
then

The following code opens a form that contains detail info on only *some* of
the records in the master form.

Would like to adjust so when I click on the button, if there's no matching
AdvID in the detail form, a MsgBox appears and states as such, instead of
opening the detail form at a new blank record.

Any assistance is greatly appreciated.
Regards,
- Mike

Private Sub btnFrmAdvSummary_Click()
On Error GoTo Err_btnFrmAdvSummary_Click

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "frmAdvSummary"

strLinkCriteria = "[AdvID] = """ & Me![AdvID] & """"

DoCmd.OpenForm strDocName, _
WhereCondition:=strLinkCriteria, _
WindowMode:=acDialog

Exit_btnFrmAdvSummary_Click:
Exit Sub

Err_btnFrmAdvSummary_Click:
MsgBox Err.Description
Resume Exit_btnFrmAdvSummary_Click


End Sub

 

[MikeF]

Thanx Jack, will give it a try.
But --- some of my tables are linked to different directories.
Am concerned about those. ???

- Mike

Subject: Change drive letter in linked table manager

No ideas other than what's already been said (I use a slightly modified
version of Dev's code, and have never had any issues with it... throw it in
it's own module and you're good to go). I suspect that by the time you get
what you are looking for you might have code almost as complex as Dev's to
make sure everything goes as planned. I would just use his.


Anyway, as far as the actual subject at hand, I think Jim's idea of using
Dlookup is way better. You'll get the first record it finds, or nothing, and
can base your msgbox from there without having to open the form

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Jack, thanx.
On a semi-related subject, please see the following post from earlier today...
Subject: Change drive letter in linked table manager
.. Any ideas?

- Mike

If you need this before the form opens, I think you'll have to pull an SQL
and check for a recordcount.

If you don't mind having the form open, followed by the message box, you
could do something like this:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.Recordcount = 0 Then
MsgBox "There are no records"
Cancel = True
End If
End Sub


Maybe you could modifiy this a bit and open the form as Hidden, and if
there's records, show the form? That way the user wouldn't see the form and
msgbox before the form closes again.

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



:

The following code opens a form that contains detail info on only *some* of
the records in the master form.

Would like to adjust so when I click on the button, if there's no matching
AdvID in the detail form, a MsgBox appears and states as such, instead of
opening the detail form at a new blank record.

Any assistance is greatly appreciated.
Regards,
- Mike


Private Sub btnFrmAdvSummary_Click()
On Error GoTo Err_btnFrmAdvSummary_Click

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "frmAdvSummary"

strLinkCriteria = "[AdvID] = """ & Me![AdvID] & """"


DoCmd.OpenForm strDocName, _
WhereCondition:=strLinkCriteria, _
WindowMode:=acDialog

Exit_btnFrmAdvSummary_Click:
Exit Sub

Err_btnFrmAdvSummary_Click:
MsgBox Err.Description
Resume Exit_btnFrmAdvSummary_Click



End Sub

 

[MikeF]

Jim, have an idea.
Is there any way to make the button smart and "grey it out" -- ie disable
the button if there are no detail records?
That way there's no need for even clicking it!!
- Mike

Assuming that frmAdvSummary is bound to a table which has a field called
AdvID, just do a DLookup before you open the form:

If IsNull(DLookup("*","YourTableNameHere","AdvID = " & AdvID)) then
msgbox "There are no detail records (or whatver you want the message to
be)"
Else
DoCmd.OpenForm...
End If

replace 'YourTableNameHere' with the table name the form is bound to. This
also assumes that AdvID is a numeric field - ifnot, then you would have to
surround it with quotes in the DLookup:

If IsNull(DLookup("*","YourTableNameHere","AdvID = """ & AdvID & """"))
then

The following code opens a form that contains detail info on only *some* of
the records in the master form.

Would like to adjust so when I click on the button, if there's no matching
AdvID in the detail form, a MsgBox appears and states as such, instead of
opening the detail form at a new blank record.

Any assistance is greatly appreciated.
Regards,
- Mike

Private Sub btnFrmAdvSummary_Click()
On Error GoTo Err_btnFrmAdvSummary_Click

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "frmAdvSummary"

strLinkCriteria = "[AdvID] = """ & Me![AdvID] & """"

DoCmd.OpenForm strDocName, _
WhereCondition:=strLinkCriteria, _
WindowMode:=acDialog

Exit_btnFrmAdvSummary_Click:
Exit Sub

Err_btnFrmAdvSummary_Click:
MsgBox Err.Description
Resume Exit_btnFrmAdvSummary_Click


End Sub

 

[Jack Leach]

Dev's code will link to various different backends, and request the BE from
the user if the tabledef db is not found. I think it should work fine.

The modification that I have for his code removes any user interface code so
it runs on startup without the user knowing.


I frequently switch drives on my development copies... K:\ if I'm at my
house, F:\ if I'm doing development at work, "\\Server\...." for the actual
working db BE path.

I define these as constants and refer all of my functions to these constants.

Public Const DSSERVER = _
"K:\DSServer\"
'"\\Server-pesc440\DSServer\"

Public Const DSLOCAL = _
"K:\DSLocal\"


When I change locations, I SHIFT+Open the db, change these two values (I
comment out the server location until the dev copy if done), restart the app
and RelinkTables pulls the paths from these constants. Not 100% automated,
but takes about 20secs each time I cnahge location, which isn't bad.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Thanx Jack, will give it a try.
But --- some of my tables are linked to different directories.
Am concerned about those. ???

- Mike

Subject: Change drive letter in linked table manager

No ideas other than what's already been said (I use a slightly modified
version of Dev's code, and have never had any issues with it... throw it in
it's own module and you're good to go). I suspect that by the time you get
what you are looking for you might have code almost as complex as Dev's to
make sure everything goes as planned. I would just use his.


Anyway, as far as the actual subject at hand, I think Jim's idea of using
Dlookup is way better. You'll get the first record it finds, or nothing, and
can base your msgbox from there without having to open the form

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Jack, thanx.
On a semi-related subject, please see the following post from earlier today...
Subject: Change drive letter in linked table manager
.. Any ideas?

- Mike

:

If you need this before the form opens, I think you'll have to pull an SQL
and check for a recordcount.

If you don't mind having the form open, followed by the message box, you
could do something like this:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.Recordcount = 0 Then
MsgBox "There are no records"
Cancel = True
End If
End Sub


Maybe you could modifiy this a bit and open the form as Hidden, and if
there's records, show the form? That way the user wouldn't see the form and
msgbox before the form closes again.

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



:

The following code opens a form that contains detail info on only *some* of
the records in the master form.

Would like to adjust so when I click on the button, if there's no matching
AdvID in the detail form, a MsgBox appears and states as such, instead of
opening the detail form at a new blank record.

Any assistance is greatly appreciated.
Regards,
- Mike


Private Sub btnFrmAdvSummary_Click()
On Error GoTo Err_btnFrmAdvSummary_Click

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "frmAdvSummary"

strLinkCriteria = "[AdvID] = """ & Me![AdvID] & """"


DoCmd.OpenForm strDocName, _
WhereCondition:=strLinkCriteria, _
WindowMode:=acDialog

Exit_btnFrmAdvSummary_Click:
Exit Sub

Err_btnFrmAdvSummary_Click:
MsgBox Err.Description
Resume Exit_btnFrmAdvSummary_Click



End Sub

 

[Jack Leach]

Check the DLookup value on the Current event, and if a record is found, edit
the buttons visible property...

Me.ButtonName.Visible = Iif(IsNull(DLookup(...)), False, True)



--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Jim, have an idea.
Is there any way to make the button smart and "grey it out" -- ie disable
the button if there are no detail records?
That way there's no need for even clicking it!!
- Mike

Assuming that frmAdvSummary is bound to a table which has a field called
AdvID, just do a DLookup before you open the form:

If IsNull(DLookup("*","YourTableNameHere","AdvID = " & AdvID)) then
msgbox "There are no detail records (or whatver you want the message to
be)"
Else
DoCmd.OpenForm...
End If

replace 'YourTableNameHere' with the table name the form is bound to. This
also assumes that AdvID is a numeric field - ifnot, then you would have to
surround it with quotes in the DLookup:

If IsNull(DLookup("*","YourTableNameHere","AdvID = """ & AdvID & """"))
then

The following code opens a form that contains detail info on only *some* of
the records in the master form.

Would like to adjust so when I click on the button, if there's no matching
AdvID in the detail form, a MsgBox appears and states as such, instead of
opening the detail form at a new blank record.

Any assistance is greatly appreciated.
Regards,
- Mike

Private Sub btnFrmAdvSummary_Click()
On Error GoTo Err_btnFrmAdvSummary_Click

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "frmAdvSummary"

strLinkCriteria = "[AdvID] = """ & Me![AdvID] & """"

DoCmd.OpenForm strDocName, _
WhereCondition:=strLinkCriteria, _
WindowMode:=acDialog

Exit_btnFrmAdvSummary_Click:
Exit Sub

Err_btnFrmAdvSummary_Click:
MsgBox Err.Description
Resume Exit_btnFrmAdvSummary_Click


End Sub

 

[Jack Leach]

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

you could use the .Enabled property instead of visible... don't know why I
was thinking about the visible property there

 

[MikeF]

That's great.
The .Enabled was the trick!!
Thanx Jack.

- Mike

 

[MikeF]

Jack,
After working with this, realize that if the button is disabled there's no
way to add new records to the form.
Perhaps either the font, or the button itself, could be a different color -
say, red - if there are no matching records.
Have messed around with this, but can't come up with the right syntax.

Suggestions?

Thanx,
- Mike

 

[Jack Leach]

I'm pretty sure you cant change the color of a button. The text, however,
should be editiable by referring to the ForeColor property of the button.
You'll have to get the numeric value for red (or whatever other color), I
forget what it is off the top of my head. You can manually change it and
then check it in the properties dialog to see what the number you need is.
So it would look like:

Me.btnWhatever.ForeColor = 457869547

or something...

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[MikeF]

Thanx for the reply.
This is one of the dozen or so attempts that didn't work.

Me.btnFrmAdvDocuments.ForeColor = 903C39 IIf(IsNull(DLookup("EventID",
"tblAdvDocuments", "EventID = """ & EventID & """")), False, True)

- Mike

 

[Jack Leach]

This is one of the dozen or so attempts that didn't work.

Me.btnFrmAdvDocuments.ForeColor = 903C39 IIf(IsNull(DLookup("EventID",
"tblAdvDocuments", "EventID = """ & EventID & """")), False, True)

The compiler is expecting 903C39 as the end of the statement. An Iif()
always has to follow an equal sign. Henceforth:

Me.btnFrmAdvDocuments.ForeColor = _
Iif(IsNull(DLookup("EventID", _
"tblAdvDocuments", _
"EventID = """ & Me![EventID] & """" _
)), _
255, -2147483630)


I added the line breaks _
(obviously) and replaced 903C39 with 255 (red) because I couldn't get it to
compile running the alphanumeric.


If your EventID field happens to be a number, you'll have to get rid of the
quotes... would look like this

"EventID = " & Me![EventID] _


If its easier to read the whole thing in one line instead of broken:

Me.btnFrmAdvDocuments.ForeColor = Iif(IsNull(DLookup("EventID",
"tblAdvDocuments", "EventID = """ & Me![EventID] & """" )), 255, -2147483630)



hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[MikeF]

Thanx Jack.
Where did you get the non-alphanumeric color number?

- Mike

This is one of the dozen or so attempts that didn't work.

Me.btnFrmAdvDocuments.ForeColor = 903C39 IIf(IsNull(DLookup("EventID",
"tblAdvDocuments", "EventID = """ & EventID & """")), False, True)

The compiler is expecting 903C39 as the end of the statement. An Iif()
always has to follow an equal sign. Henceforth:

Me.btnFrmAdvDocuments.ForeColor = _
Iif(IsNull(DLookup("EventID", _
"tblAdvDocuments", _
"EventID = """ & Me![EventID] & """" _
)), _
255, -2147483630)


I added the line breaks _
(obviously) and replaced 903C39 with 255 (red) because I couldn't get it to
compile running the alphanumeric.


If your EventID field happens to be a number, you'll have to get rid of the
quotes... would look like this

"EventID = " & Me![EventID] _


If its easier to read the whole thing in one line instead of broken:

Me.btnFrmAdvDocuments.ForeColor = Iif(IsNull(DLookup("EventID",
"tblAdvDocuments", "EventID = """ & Me![EventID] & """" )), 255, -2147483630)



hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Thanx for the reply.
This is one of the dozen or so attempts that didn't work.

Me.btnFrmAdvDocuments.ForeColor = 903C39 IIf(IsNull(DLookup("EventID",
"tblAdvDocuments", "EventID = """ & EventID & """")), False, True)

- Mike

 

[Jack Leach]

(2000-2003) Right click on the control and select Properties, under the
Format tab (all the way to the left), scroll somewhere around 3/4 of the way
down and find:

Fore Color............ -240142838

Click in the number field and open the palette dialog via the ... button
that pops up on the side (you can double click the field to open it, i
believe).

All of the standard colors have numeric values, I'm not sure about custom
colors off the top of my head. Anyway select the color you want and close
the palette and the pertaining number should now be in the Fore Color field
in the under the Format tab of the Properties dialog.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Thanx Jack.
Where did you get the non-alphanumeric color number?

- Mike

This is one of the dozen or so attempts that didn't work.

Me.btnFrmAdvDocuments.ForeColor = 903C39 IIf(IsNull(DLookup("EventID",
"tblAdvDocuments", "EventID = """ & EventID & """")), False, True)

The compiler is expecting 903C39 as the end of the statement. An Iif()
always has to follow an equal sign. Henceforth:

Me.btnFrmAdvDocuments.ForeColor = _
Iif(IsNull(DLookup("EventID", _
"tblAdvDocuments", _
"EventID = """ & Me![EventID] & """" _
)), _
255, -2147483630)


I added the line breaks _
(obviously) and replaced 903C39 with 255 (red) because I couldn't get it to
compile running the alphanumeric.


If your EventID field happens to be a number, you'll have to get rid of the
quotes... would look like this

"EventID = " & Me![EventID] _


If its easier to read the whole thing in one line instead of broken:

Me.btnFrmAdvDocuments.ForeColor = Iif(IsNull(DLookup("EventID",
"tblAdvDocuments", "EventID = """ & Me![EventID] & """" )), 255, -2147483630)



hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Thanx for the reply.
This is one of the dozen or so attempts that didn't work.

Me.btnFrmAdvDocuments.ForeColor = 903C39 IIf(IsNull(DLookup("EventID",
"tblAdvDocuments", "EventID = """ & EventID & """")), False, True)

- Mike

:

I'm pretty sure you cant change the color of a button. The text, however,
should be editiable by referring to the ForeColor property of the button.
You'll have to get the numeric value for red (or whatever other color), I
forget what it is off the top of my head. You can manually change it and
then check it in the properties dialog to see what the number you need is.
So it would look like:

Me.btnWhatever.ForeColor = 457869547

or something...

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



:


Jack,
After working with this, realize that if the button is disabled there's no
way to add new records to the form.
Perhaps either the font, or the button itself, could be a different color -
say, red - if there are no matching records.
Have messed around with this, but can't come up with the right syntax.

Suggestions?

Thanx,
- Mike



:


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

you could use the .Enabled property instead of visible... don't know why I
was thinking about the visible property there

:


Check the DLookup value on the Current event, and if a record is found, edit
the buttons visible property...

Me.ButtonName.Visible = Iif(IsNull(DLookup(...)), False, True)

 

[MikeF]

That's exactly what I did [in 2007] prior to asking you.
Only the alphanumeric shows up ....

(2000-2003) Right click on the control and select Properties, under the
Format tab (all the way to the left), scroll somewhere around 3/4 of the way
down and find:

Fore Color............ -240142838

Click in the number field and open the palette dialog via the ... button
that pops up on the side (you can double click the field to open it, i
believe).

All of the standard colors have numeric values, I'm not sure about custom
colors off the top of my head. Anyway select the color you want and close
the palette and the pertaining number should now be in the Fore Color field
in the under the Format tab of the Properties dialog.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Thanx Jack.
Where did you get the non-alphanumeric color number?

- Mike

This is one of the dozen or so attempts that didn't work.

Me.btnFrmAdvDocuments.ForeColor = 903C39 IIf(IsNull(DLookup("EventID",
"tblAdvDocuments", "EventID = """ & EventID & """")), False, True)

The compiler is expecting 903C39 as the end of the statement. An Iif()
always has to follow an equal sign. Henceforth:

Me.btnFrmAdvDocuments.ForeColor = _
Iif(IsNull(DLookup("EventID", _
"tblAdvDocuments", _
"EventID = """ & Me![EventID] & """" _
)), _
255, -2147483630)


I added the line breaks _
(obviously) and replaced 903C39 with 255 (red) because I couldn't get it to
compile running the alphanumeric.


If your EventID field happens to be a number, you'll have to get rid of the
quotes... would look like this

"EventID = " & Me![EventID] _


If its easier to read the whole thing in one line instead of broken:

Me.btnFrmAdvDocuments.ForeColor = Iif(IsNull(DLookup("EventID",
"tblAdvDocuments", "EventID = """ & Me![EventID] & """" )), 255, -2147483630)



hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



:


Thanx for the reply.
This is one of the dozen or so attempts that didn't work.

Me.btnFrmAdvDocuments.ForeColor = 903C39 IIf(IsNull(DLookup("EventID",
"tblAdvDocuments", "EventID = """ & EventID & """")), False, True)

- Mike

:

I'm pretty sure you cant change the color of a button. The text, however,
should be editiable by referring to the ForeColor property of the button.
You'll have to get the numeric value for red (or whatever other color), I
forget what it is off the top of my head. You can manually change it and
then check it in the properties dialog to see what the number you need is.
So it would look like:

Me.btnWhatever.ForeColor = 457869547

or something...

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



:


Jack,
After working with this, realize that if the button is disabled there's no
way to add new records to the form.
Perhaps either the font, or the button itself, could be a different color -
say, red - if there are no matching records.
Have messed around with this, but can't come up with the right syntax.

Suggestions?

Thanx,
- Mike



:


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

you could use the .Enabled property instead of visible... don't know why I
was thinking about the visible property there

:


Check the DLookup value on the Current event, and if a record is found, edit
the buttons visible property...

Me.ButtonName.Visible = Iif(IsNull(DLookup(...)), False, True)

 

[Jack Leach]

I've never used 07 and am not sure how to get around that, but a google of
"access color conversions" pulled this up as the first result, which may
prove helpful.

http://www.endprod.com/colors/



Or, just try putting a # in front of it so access reads it as a hex? I'm
not sure if vba will accept #90C3whatever, but it's worth a shot I guess.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

That's exactly what I did [in 2007] prior to asking you.
Only the alphanumeric shows up ....

(2000-2003) Right click on the control and select Properties, under the
Format tab (all the way to the left), scroll somewhere around 3/4 of the way
down and find:

Fore Color............ -240142838

Click in the number field and open the palette dialog via the ... button
that pops up on the side (you can double click the field to open it, i
believe).

All of the standard colors have numeric values, I'm not sure about custom
colors off the top of my head. Anyway select the color you want and close
the palette and the pertaining number should now be in the Fore Color field
in the under the Format tab of the Properties dialog.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Thanx Jack.
Where did you get the non-alphanumeric color number?

- Mike

:

This is one of the dozen or so attempts that didn't work.

Me.btnFrmAdvDocuments.ForeColor = 903C39 IIf(IsNull(DLookup("EventID",
"tblAdvDocuments", "EventID = """ & EventID & """")), False, True)

The compiler is expecting 903C39 as the end of the statement. An Iif()
always has to follow an equal sign. Henceforth:

Me.btnFrmAdvDocuments.ForeColor = _
Iif(IsNull(DLookup("EventID", _
"tblAdvDocuments", _
"EventID = """ & Me![EventID] & """" _
)), _
255, -2147483630)


I added the line breaks _
(obviously) and replaced 903C39 with 255 (red) because I couldn't get it to
compile running the alphanumeric.


If your EventID field happens to be a number, you'll have to get rid of the
quotes... would look like this

"EventID = " & Me![EventID] _


If its easier to read the whole thing in one line instead of broken:

Me.btnFrmAdvDocuments.ForeColor = Iif(IsNull(DLookup("EventID",
"tblAdvDocuments", "EventID = """ & Me![EventID] & """" )), 255, -2147483630)



hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



:


Thanx for the reply.
This is one of the dozen or so attempts that didn't work.

Me.btnFrmAdvDocuments.ForeColor = 903C39 IIf(IsNull(DLookup("EventID",
"tblAdvDocuments", "EventID = """ & EventID & """")), False, True)

- Mike

:

I'm pretty sure you cant change the color of a button. The text, however,
should be editiable by referring to the ForeColor property of the button.
You'll have to get the numeric value for red (or whatever other color), I
forget what it is off the top of my head. You can manually change it and
then check it in the properties dialog to see what the number you need is.
So it would look like:

Me.btnWhatever.ForeColor = 457869547

or something...

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



:


Jack,
After working with this, realize that if the button is disabled there's no
way to add new records to the form.
Perhaps either the font, or the button itself, could be a different color -
say, red - if there are no matching records.
Have messed around with this, but can't come up with the right syntax.

Suggestions?

Thanx,
- Mike



:


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

you could use the .Enabled property instead of visible... don't know why I
was thinking about the visible property there

:


Check the DLookup value on the Current event, and if a record is found, edit
the buttons visible property...

Me.ButtonName.Visible = Iif(IsNull(DLookup(...)), False, True)