Bells & Whistles

[wallymeister]

I have a form with 2 subforms embedded. The first subform is datasheet view
and I use conditional format to highlight a field in that form based on a
condition. The condition is a rare condition so I would like to place a lbl
on the main form that will explain what the highlight means. Basically make
visible everytime this condition is true and hide when this condition is
false. (as I navigate thru the records) Is there a way to do this or am I
dreaming.

Any help is greatly appreciated.
wally

 

[Dale Fye]

If you mean, is there a way to determine whether one of the rows that is
visible in the datasheet meets the condition, and then make the label
visible, probably (but I so rarely use datasheets that I couldn't help you).

However, if you mean, is there a way to display the label if the row or cell
you click on in a datasheet meets the condition, I'd also say probably. I
would try putting some code in the subforms Current event that looks
something like:

Private Sub Form_Current

Dim bLabelVis as boolean

bLabelVis = (Condition1 = true) and (Condition2 = true) and (condition3
= true)
me.parent.lbl_Description.visible = bLabelVis

End Sub
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[wallymeister]

Dale,
Yes I do mean that I would like to unhide the lbl when any row in subform
has a highlighted txtbox. If possible. For my own programming advancement I
sometimes dream stuff up and then try to tackle the task. This is not
something I absolutley have to do, but I want to.

I thought that if I could highlight the txtbox in a subrouotine instead of
using conditional formatting I could easily hide & unhide in that same
routine. I think this would require a loop through all records in subform
from on_current event of that subform but I have never done this before. I
have looped thru cells with code in VB for excel though. Anyway thanks for
your input, and I'll keep at it until I Git'R'Done.

Wallymeister

 

[Dale Fye]

Wally,

If you mean "any row" in the datasheet, not just the ones that are visible,
that is another story entirely.

I think you could put some code similar to the following in the Current
event of your main form.

Private Sub Form_Current

Dim rs as DAO.recordset
Dim bLabelVisible as boolean

bLabelVisible = False
set rs = me.subFormControlName.Form.recordsetclone
do While not rs.eof
if rs("field1") = "some value" AND rs("field2") = 3 Then
bLabelVisible = true
Exit do
endif
rs.movenext
Loop
rs.close
set rs = nothing
me.lblDescription.visible = bLabelVisible

End Sub

Note that you will have to use the name of the subform control, not the name
of the subform as seen in the database window or navigation pane.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[wallymeister]

Dale,
It really doesn't matter if that row is visible or not. I can say that
99.44% of the time all rows in this datasheet will be visible on all records
of the main form. Just as long as if there is an instance of this highlight
in current record then this explaination lbl will become visible. I will try
you suggestion and get back to you. Thanks so much, you are being very
helpful.

Wallymeister

 

[wallymeister]

Ok
Name of Main Form = "WO_Form"
Name of Subform = " Splits_Form"
Name of control on subform = "DateOff"

I tried this code in the On_Current event of the main form...
Dim rs As DAO.Recordset
Dim bLabelVisible As Boolean

bLabelVisible = False
Set rs = Me.DateOff.Form.RecordsetClone
Do While Not rs.EOF
If rs("DateOff.backcolor") = vbRed Then
bLabelVisible = True
Exit Do
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Me.lblHighlightDesc.Visible = bLabelVisible

The above line Dim rs As DAO.Recordset produces complie error "User defined
type not defined"

Not sure whats happening here. Do you see any other potential problems?

Thanks again,
Wallymeister

 

[BruceM]

You may have a references problem. See here for more information:
http://allenbrowne.com/ser-38.html

Specifically, it could be that you are lacking a reference to the Microsoft
DAO 3.x Object Library. The x is because I don't know your version of
Access. In 2007 it is called something other than DAO Object Library. The
link has details.

 

[wallymeister]

Hey Bruce
I'm using Access 2003 at work and I started the project at home on my Access
2000. Could this be the problem. I don't want to convert the database to
2003 because I don't believe I will be able to work on it at home on my 2000
if I do. Need to make some more progress on this project before I convert.
Thanks
Wallymeister

 

[BruceM]

I think they both use the same library reference for DAO. Did you check the
references as described in the link?

You can use Access 2000 file format for Access 2003 files. You can just
leave it that way, or I think you can convert it when the project is done.
Where I work there are still some machines with Access 2000, so I always use
that file format. Because of that I'm not very familiar with converting to
another file format.

 

[Douglas J. Steele]

Yes, Access 2000, 2002 and 2003 all use Microsoft DAO 3.6 Object Library. In
fact, Access 2007 uses that as well if the database is in MDB format.

 

[wallymeister]

Yes I checked the the references and they are as below...

2003 11.0 C:\Program Files\Microsoft Office\Office11\msacc.olb
2002 10.0 C:\Program Files\Microsoft Office\Office10\msacc.olb
2000 9.0 C:\Program Files\Microsoft Office\Office\msacc9.olb

The way I'm understanding this they aren't the same. Point being that 2000
uses msacc9.olb & 2002/2003 uses msacc.olb.

I guess I could make a copy of the program and convert to 2003 and see if
that makes a difference.

Still trying,
Wallymeister

 

[wallymeister]

It didn't make a difference. But I changed line from (Dim rs as
DAO.recordset) to (Dim rs as recordset) and I now get past that compile error
to another compile error on the line that reads Set rs =
Me.DateOff.Form.RecordsetClone METHOD OR DATA MEMBER NOT FOUND.

I'm trying to reference the value of a textbox called DateOff on the subform
from the current event of the main form. Is this the correct way to refence
this control.
Wallymeister

 

[BruceM]

When you click Tools >> References in the VBA editor, what is checked? Not
the file locations, just the basic information.

In Allen Browne's web page he wrote: "The three essentials are VBA, Access,
DAO." You have not mentioned the DAO library reference. If you do not have
it you will get the error you describe no matter the version of Access.

By the way, I think you need only the Office 11 reference, but somebody else
will have to confirm or refute that.

Microsoft Access 2000 and 2002 did not include the DAO library reference by
default. Access 2003 did. You will probably need to add the reference.
The article has all the information you should need, either in the article
or in one of the links. I can't write anything here that does not duplicate
information in the links.

 

[wallymeister]

Ok Bruce,
I now have the DAO ref Lib checked and I'm getting past that compile error.
I now have another compile error on this line, Set rs =
Me.DateOff.Form.RecordsetClone with the DateOff highlighted.

Again,
Name of Main Form = "WO_Form"
Name of Subform = " Splits_Form"
Name of control on subform = "DateOff"

Shouldn't there be a reference of some kind to the Splits_Form, since it is
the subform that holds the DateOff txtbox?

Also, does this code look correct. (***)
Do While Not rs.EOF
*** If rs("DateOff.backcolor") = vbRed Then ***
bLabelVisible = True
Exit Do
End If

I feel it's close!
Wallymeister

 

[BruceM]

What didn't make a difference? You should read what Allen and others have
written about ambiguous or incorrect references, including this:
"Inconsistencies and data corruption can result from referencing the wrong
library for your version of Access."
If that isn't a problem for you, proceed as you are.

The syntax to reference a subform control is:
Forms!MainFormName!SubFormControlName.Form!DateOff

From code in the main form you could have:
Me.SubformControlName.Form.DateOff

 

[BruceM]

Glad to hear the reference is straightened out. Be sure either to remove
any references to ActiveX Data Objects, or be sure the DAO reference is
above them in the References list.

Form another part of this thread:
The syntax to reference a subform control is:
Forms!MainFormName!SubFormControlName.Form!DateOff

From code in the main form (which is what I think you have) you could use:
Me.SubformControlName.Form.DateOff

To reference a property of the subform:
Me.SubformControlName.Form.RecordsetClone

You are correct that there should be a reference to the subform. Actually,
you reference the Form property of the subform control. The subform control
is the "container" for the subform. Just as a text box is the container for
a field, the subform control is the container for a form.

With the names you have listed the code in the main form's Current event
would be:
Me.Splits_Form.Form.RecordsetClone

This is not correct:
If rs("DateOff.backcolor") = vbRed Then

The reason is that rs is a recordset, or a group of records. It does not
have a backcolor property. DateOff is a field in the recordset, which means
it has a value, but no colors or any other physical characteristics. You
need to apply the test you used for conditional formatting (the code that
caused the text box to turn red):

If rs("DateOff") = "something" Then
bLabelVisible = True

You would probably need to set bLabelVisible to False if the test does not
pass. One way to do that is to set it to False before you begin the loop.
That is, at each main form record you start with the assumption that it is
False (no red text boxes in the subform).

bLabelVisible = False
Do While Not rs.EOF
If rs("DateOff") = 'something Then
bLabelVisible = True
Exit Do
End If
etc.

I'll leave you to fill in the rest of the code that Dale provided. Dale, I
didn't mean to hijack the thread. I added what I thought would be a quick
suggestion, and it sort of got carried away.

 

[wallymeister]

OK, I finally got my head around this.
What I ended up doing is keeping the Conditional Formatting, that part was
working great for highlighting the individual cells in the subform datasheet
view.

Pretty much just as Dale wrote hear is the final code I used in the Current
event of the Main Form.

Dim rs As DAO.Recordset
Dim bLabelVisible As Boolean

bLabelVisible = False
Dim DateOffDay As Date

Set rs = Forms!frm_WO!frm_Splits.Form.RecordsetClone

rs.MoveFirst
Do While Not rs.EOF
DateOffDay = rs("DateOff")

If Weekday(DateOffDay) = 1 Or Weekday(DateOffDay) = 6 Or
Weekday(DateOffDay) = 7 Then ' "Fri" "Sat" or "Sun"
bLabelVisible = True

Exit Do
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

Me.lblHighlightDesc.Visible = bLabelVisible
End Sub

Thanks guys for the patience and the great code

 

[Dale Fye]

Wally,

Glad everything worked out.

The thing that most people have a hard time getting their head around is the
reference to the subform "control name", not the actual name of the subform
(as seen in the Source Object). I generally name all of my subforms as
"sub_" followed by the name of the form, so the control name would be
sub_frm_Splits, and the source object would be "Splits_Form" it is easier
for me to keep this straight when I see it that way.

Dale

 

[Dale Fye]

Wally,

Just another quick point. The Weekday( ) function has a second argument
(FirstDayOfWeek), which allows you to change the start day of the week, so
you could make your if statement look like:


IF Weekday(DateOffDay, vbMonday) > 4 Then
bLabelVisible = true
exit Do
endif

Dale

 

[wallymeister]

Thanks Dale,

I was aware of weekday start day argument but this is the way I did it in
conditional formatting but now that you mention it I guess I can change it
there too.

Thanks again, and I'm sure I'm going to need additional help down the road,
so I'll be seeing you guys on and off. I usually can find the answers I need
by just searching the posts. There is so much in here it's awesome.

Wallymeister