Contingent formatting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dmieluk - 21 Dec 2005 02:24 GMT

Problem:

When moving between records, I need to control which combo/text boxes
are displayed on the current form, dependent upon data in the current
record.

More:

I building my first access database.

Record entry is by a single form, and the form contains many cases of
contingent combo boxes, text boxes, etc.

When building the form, I was testing only the form by itself for a
single record, not moving between records. Everything works fine so
long as you do not move between records.

However, when I move between records, the formatting (ie. which
combo/text boxes are visible/enabled) remains as it was on the most
recent manually entered record.

A simple way to address this would be to have an 'OnChangeRecord' type
event which detects presses on the MS Access default record controls
which appear at the bottom left of the screen. Is this possible? If so,
how?

For my form formatting, I have used the _Change event for combo boxes.
Where should this formatting go, and where does it need to be to update
correctly whenever a user moves between records?

I have experimented with the Form_AfterUpdate event without success.
Any help greatly appreciated.

Reply to this Message

Allen Browne - 21 Dec 2005 02:53 GMT

The Current event of the form fires whenever the user moves record.

The Change event of a combo fires for every keystroke, so use its
AfterUpdate event to respond to a change in value.

If you have lots of controls that need to be hidden/shown, you probably need
a different data structure, where some of the attributes you are saving
become values in a related table rather than fields of the one big table.

You cannot conditionally hide columns if your form is in Continuous or
Datasheet view.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Problem:
>
[quoted text clipped - 28 lines]
> I have experimented with the Form_AfterUpdate event without success.
> Any help greatly appreciated.

Reply to this Message

dmieluk - 21 Dec 2005 03:15 GMT

Thank you for your quick reply.

>> The Current event of the form fires whenever the user moves record.

In form view, Access provides default record selection buttons. Is the
Current event supposed to fire when the user changes record using these
buttons? I added this code to my form:

Private Sub Form_Current()

Beep

End Sub

I do not hear the beep when I change records using the provided
buttons.

By contrast, I do hear the beep when I add the beep command to one of
my existing combo box Change events (when I change that combo box).

Reply to this Message

Allen Browne - 21 Dec 2005 03:24 GMT

Yes: Form_Current fires with the built-in nav. buttons.

Replace
beep
with
Debug.Print "Changed record at " & Now()

Open the Immediate Window (Ctrl+G) and watch the output.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thank you for your quick reply.
>
[quoted text clipped - 15 lines]
> By contrast, I do hear the beep when I add the beep command to one of
> my existing combo box Change events (when I change that combo box).

Reply to this Message

dmieluk - 21 Dec 2005 03:48 GMT

Hmmm.

I'll be as explicit as I can because this is starting to get strange.

I altered the code as said, and opened the Immediate window.

I saved the code, opened the form and went to form view.

I used the default Nav buttons to move between records, and also edited
a few records, and created a new record.

There was no entry in the immediate window.

Then, I added the Debug code(Debug.Print "Changed record at " & Now())
into one of my combo box change events. I returned to form view, used
the combo box, and the immediate window subsequently had an entry
(Changed record at 21/12/2005 2:44:42 PM).

I am using Windows XP with MS Access 2000.

The form code is exactly:

Private Sub Form_Current()

Debug.Print "Changed record at " & Now()

End Sub

and this procedure is in the same place where all the form events are
placed by default (Indeed, the event was created automatically using
the drop down menus).

Perplexed.

Reply to this Message

Allen Browne - 21 Dec 2005 03:57 GMT

Presumably this is a bound form, because the built-in nav buttons would not
do anything in an unbound form.

If Form_Current is not firing, the database is partially corrupt. Follow
this sequence until you start seeing the debug.print working.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, and reference ambiguities are
resolved.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hmmm.
>
[quoted text clipped - 29 lines]
>
> Perplexed.

Reply to this Message

dmieluk - 21 Dec 2005 04:20 GMT

Thanks for your help Allen.

I follow through this carefully and see if I can get it going.

Reply to this Message

Wayne Gillespie - 21 Dec 2005 04:30 GMT

I would suggest that this more likely due to the [Event Procedure] not being set in the form's properties, rather than
corruption. The OP indicated that the Form_Current procedure was created directly in the VBE, which will not
automatically set the property.

>Presumably this is a bound form, because the built-in nav buttons would not
>do anything in an unbound form.
[quoted text clipped - 38 lines]
>at:
> http://allenbrowne.com/ser-47.html

Wayne Gillespie
Gosford NSW Australia

Reply to this Message

Allen Browne - 21 Dec 2005 05:06 GMT

Good thought. Thanks Wayne and Tom. Very likely.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I would suggest that this more likely due to the [Event Procedure] not
> being set in the form's properties, rather than
> corruption. The OP indicated that the Form_Current procedure was created
> directly in the VBE, which will not
> automatically set the property.

Reply to this Message

dmieluk - 21 Dec 2005 05:52 GMT

Ok, thank you very much guys, for all your help.

I did not have the event procedure option selected.

I have made this alteration and it has solved the problem.

Kudos to the support gurus who help out at this forum.

Reply to this Message

Tom van Stiphout - 21 Dec 2005 04:21 GMT

This could be a case of "event procedure not connected to event".
Proceed as follows:
Bring form in design view
Show form-level properties
Select Events, and click the ... button next to the Current event.
Observe that the code window opens, with your Beep code or Debug.Print
code.
Close all and test again.
If it works now, then the above has reconnected the event procedure to
the event. No corruption was ever there.

-Tom.

>Hmmm.
>
[quoted text clipped - 29 lines]
>
>Perplexed.

Reply to this Message

Wayne Gillespie - 21 Dec 2005 04:24 GMT

>and this procedure is in the same place where all the form events are
>placed by default (Indeed, the event was created automatically using
>the drop down menus).
>
>Perplexed.

Open the Property Sheet for the form and click the Event tab.
In the text box next to On Current make sure that it has [Event Procedure] entered.

Code against each event will not fire unless this property is set. It sounds like your form may not have this property
set.

If you create a procedure in the VBE window directly (by using the drop down menus), this property is not set
automatically and you must enter [Event Procedure] in the property sheet against the related event before the code will
fire.

To set the property automatically you must initiate the code creation from the property sheet.
In the property sheet, click in the Event you wish to create. An elipse? button (...) will appear to the extreme left of
the property. Click this button and select Event Procedure. The VBE window will open with procedure header and footer
created, and [Event Procedure] will be entered in the property sheet automatically.

Wayne Gillespie
Gosford NSW Australia

Reply to this Message

Pachydermitis - 21 Dec 2005 03:55 GMT

Allen already answered most everything, and so here are my 2cents as
well. :)
To show or hide specific controls based on the record you are showing I
usually do it this way.
In the properties for the controls you will see one called Tag. For
all the controls you want to disable, put in a word that signifies
something to do with why you are disabling these controls - for this
example, we'll use the word Employed. I don't know what you are
looking for that will signal to the form to hide records, but just for
the sake of explanation, lets say you have a field called Employed and
it's either true or false.
Now in your foms OnCurrent event
dim ctlDisable as control
for each ctlDisable in me.controls
If ctlDisable.tag="Employed" then ctlDisabled.enabled=me!Employed
next
Anyway I hope that helps you

Reply to this Message