Continuous Forms - Work with one record

[sandyshoes]

Have several command buttons on continuous subform. Need some to display, some to be invisible on each record depending on the contents of a field on the subform. ie., on record 1 only buttons 1, 3, 5, 6 should be visible while on record 2 buttons 1,2,4,6,8,9 should be visible.

Thanks.

 

[tunsarod]

Firstly the place to put any code to hide/expose the button is in the OnCurrent event for the subform.

Secondly what code you could use will depend on the field in the subform that you want to use as the controlling value. If for instance the field can ONLY contain say two possible values, say True or False, then I would use a line like this for each button in the OnCurrent event procedure:

If subfrmField = 0 then cmdbtn1.visible = True Else cmdBtn1.visible = False

You would need to establish what values to use here.


If on the other hand your subform's field can contain a large variety of values then a more complicated solution may be required.

Rod

 

[BoxHead]

Sandy,

What version of Access are you using?

Access 2000 has a feature called conditional formatting that may solve this for you. I use Access 97 and know that on continuous forms, the property settings of the current controls will apply to all visible records.

If you're using '97, consider placing the command buttons on the header or footer if you can. Rod's If...Then description should work for you there.


HTH John

Use what you have,
Learn what you can,
Create what you need.

 

[tunsarod]

Yes, thanks John. I assumed the buttons would not be in the subform's detail section and forgot to mention it.

Rod

 

[sandyshoes]

Thanks for your replies.

I am using Acc2k and cannot put the buttons in the header as I need the option of the continuous display. I have been playing with conditional formatting but have not got it to do what I want. The buttons need to be visible or not visible based on the true/false entries of fields in the table for each record.

I will mess with it some more. Thanks again!

 

[sandyshoes]

Follow up:

Yup, putting that line of code in the current event of the subform makes all the identified buttons on all line of the subform visible or not. Does not limit to just one line.

Rats!

 

[tunsarod]

Sandy,

I do not understand your comment about not being able to use the header. I have just created a tabular (continuous) form which has a row of fields called Bar1 to Bar9. The values are either 1 or 3. In the header immediately above the column title labels I have placed 9 buttons , cmd1 to cmd9. The followinmg code is in the OnCurrent event for the form and it does exactly what you havce described as your objective. i.e. to hide or display each button depending on the contents of the corresponding text box in the currently selected row.


Private Sub Form_Current()

Cmd1.Visible = IIf(Bar1 = 1, True, False)
Cmd2.Visible = IIf(Bar2 = 1, True, False)
Cmd3.Visible = IIf(Bar3 = 1, True, False)
Cmd4.Visible = IIf(Bar4 = 1, True, False)
Cmd5.Visible = IIf(Bar5 = 1, True, False)
Cmd6.Visible = IIf(Bar6 = 1, True, False)
Cmd7.Visible = IIf(Bar7 = 1, True, False)
Cmd8.Visible = IIf(Bar8 = 1, True, False)
Cmd9.Visible = IIf(Bar9 = 1, True, False)

End Sub
[/color)

Rod.

 

[tunsarod]

Has the penny dropped?

It has just occured to me (bit slow here perhaps) that you really are looking to present a series of records in a tabular format and with a totally separate button associated with each field such that (for example) if you had 10 rows of records, each with 10 fields, then you would have 100 individually programmable buttons on screen, each one of which may be visible or invisible depending on the content of it's associated textbox. Is that a correct interpretation of your reuirements?

If the answer is yes then I am pretty sure that you cannot use a continuous form in the normal sense. Assuming the above 10x10 matrix, you will have to construct a bespoke form to simulate a continuous form - with a single row of fields showing the current record, plus a further nine rows of ten independent textboxes to display the next nine records.

Each textbox in the additional nine rows would need to be filled based on a Dlookup function using an offset to locate data from the appropriate successive record.

eg: the first box in the first row would need a statement something like:
=Dlookup("Field1","tbleSourceData","[RecNum] = '" & Me.Form!RecNum + 1 & "'&quot

the second box in the first row would need a statement something like:
=Dlookup("Field2","tbleSourceData","[RecNum] = '" & Me.Form!RecNum + 1 & "'&quot

the first box in the second row would need a statement something like:
=Dlookup("Field1","tbleSourceData","[RecNum] = '" & Me.Form!RecNum + 2 & "'&quot

The second box in the second row would need a statement something like:
=Dlookup("Field2","tbleSourceData","[RecNum] = '" & Me.Form!RecNum + 2 & "'&quot

etcetera, etcetera...

Now you can populate 100 individual buttons between the rows and use code, similar to the code I posted previously, in the form's OnCurrent event to hide/unhide each button according to the contents of it's associated textbox.

You have two options here: 1. place the Dlookup function in the Control Source for each textbox, or 2. place the Dlookup instruction for each textbox in the forms OnCurrent event.

At least with this latter approach you don't need to worry about refreshing the form as the OnCurrent event will do it for you.

This approach will certainly give the appearance of being a continuous form and you will have the ability to control each of the form's buttons independently of each other.

I guess the biggest problem will be the refresh rate for the lookup fields when the current record is advanced or retarded. It might take an unacceptablly long time but perhaps you could switch focus temporarily to an image of a nice painting or an MPEG of rabbits running around a field, just while the data is refreshing.

Regards
Rod

 

[sandyshoes]

Rod,

Thanks so much for thinking this through! You are correct in your assessment of what I had wanted this form to do. I had come to a similar conclusion about the inability of this form type to do what I want, at least without a lot of coding and testing and looking for some kind of picture for the user to look at while the form did its thing....

So, on to another solution!

Sandy