Loop: Each subform in Form

[vbaInet]

I would still dimension it as a String and not a variant. The Name of a form returns a String.

So you have Set form = Forms!F_labnew ?

 

[mbreu996]

Sorry, I was skipping past the part of the code that I know work. This should make it more clear and simple.

Public Sub test1()

Dim i As Long
Dim c As Long
Dim subformlist(0) As String
Dim cntrl As Control
Dim testarray(1) As String

subformlist(0) = "testtable1_sub"

testarray(0) = "3"
testarray(1) = "5"

For c = 0 To UBound(testarray)
For i = 0 To UBound(subformlist)
Application.DoCmd.OpenForm subformlist(i)
For Each cntrl In Forms(subformlist(i))
If cntrl.Name = testarray(c) Then
cntrl.ColumnHidden = False '****error 438*******
MsgBox "match" & cntrl.Name & ":" & testarray(c)
Else
cntrl.ColumnHidden = True
End If
Next
Next
Next

For i = 0 To UBound(subformlist)
Application.DoCmd.Close acForm, subformlist(i), acSaveYes
Next

End Sub

Runtime error 438 object doesn't support this property or method.

 

[vbaInet]

What type of control is "3"?

 

[mbreu996]

it is a textbox

 

[vbaInet]

Can you post your db? Here's how:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[mbreu996]

Here it is. The sub will be called from a different routine, at this time TESTFORM1 is closed so I have to open it to manipulate the hidden property. You can see how directly referencing a textbox works to hide a column by the buttons I have on TESTFORM1. I was hoping to accomplish the same thing in the loop by referring to each control as "cntrl" rather than Me.TESTTABLE1_sub.Controls("3").ColumnHidden = True.

Thanks!

 

[vbaInet]

I think most of the code is redundant. As long as you know which subform it is, why don't you send the name of the form the sub? No need looping through ALL the controls.

Forms(param_From_Sub).Controls(param_From_Sub).ColumHidden = True

 

[mbreu996]

You make a good point. The subform names are known and will never change, the field names might though. I could use your method if I populated an array with all the field names that are currently on the form (arrayofcurrentfields).

For c = 0 to Ubound(arrayofsubforms)
For i = 0 to Ubound(arrayofcurrentfields)
If arrayofcurrentfields(i) = Public_array Then
Forms(arrayofsubforms(c)).Controls(arrayofcurrentfields(i)).ColumHidden = False
Else
Forms(arrayofsubforms(c)).Controls(arrayofcurrentfields(i)).ColumHidden = True
Next
Next

Thanks!

 

[vbaInet]

What is the big picture? Give me a scenario of when you click a button, what should happen?

 

[mbreu996]

Ok, here is the scoop. This is a database for a water quality lab. The data is stored as 1 result per record, while the lab data entry sheet has many results in the same record. There is a button "SAVE" that activates a bunch of subs that translate the data from the entry form into the tables it is actually stored on.

Before the user enters data for a lab sheet, they define what parameters the samples on a particular hardcopy form were analyzed for. This could be 1 parameter or 30 - it varies. Once the user has the parameters listed on the generator form, they press "MAKE FORM" button which activates a bunch of subs. The parameter list is loaded into an array that is the "recipe" for the entry form to be generated.

My original method was to programmatically create each form - adding controls based on the parameter list array. This was very time consuming and full of bugs. It also took too long to run on the very slow computer one of the main users has - but this might have been because I was using an ActiveX control to rotate some of the labels. The programmatic generation method would get the job done if I could get the code to run fast enough on even very slow computers.

The route I recently tried going was to not programmatically generate the forms, but rather to have a form with all parameters on it, but use code to hide the ones that are not currently applicable for the hardcopy lab form (parameters that were not analyzed for). I strayed away from this originally because I did not like the idea of the columns being "hidden" because I user could potentially muck something up.

As a side note, the entry form has several subforms on it to accommodate all required data.

Normalisation, I know. It is a fact that the workflow is messed up in the lab. The format of the hardcopy forms that have the results on them should not be multiple parameters per record, it should be 1 parameter per record just as the actual table that stores the data is. I am in no position to alter the workflow of the lab. If I could I would. My mission was to make this crap work despite this.

It has not been fun. I have spent 90% of my time on this aspect of the database which normally would be a null issue with proper workflow.

I hope this makes sense.

 

[vbaInet]

Detailed explanation. Phew! lol

I see where your problem is. The ColumnHidden property is only available in Datasheet view, so when you open the form, you have to open it in Datasheet view (acFormDS). In your ELSE statement where you're setting the property to True, you need to check that the control is a text box unless it will throw an error (cntrl.ControlType = acTextbox).

However, I can see that you're closing and saving the changes. The changes will not be saved even if you use acSaveYes. If you want to save the changes then you must open the form in design view and change the VISIBLE property, close & save.

I think you should be perform the hiding of columns for each time the form is opened, that is on the ON LOAD event. So when the user clicks to open the form, the paramters are checked and based on that you hide the columns. No need, opening all the forms and hiding, then closing. Only do it when the form is being called. That way you can create a function that will take as parameters the form name and the control numbers. So something like this to call the function:

Call test1("form_Name", "3;5")

In the function you will split the control names using the semi-colon as the delimeter:

public sub test1(subformName as String, controlString as string)
dim ctlArray() as string

ctlArray = Split(1,controlString, ";")

ctlArray will now contain 3 in ctlArray(0) and 5 in ctlArray(1).

 

[mbreu996]

Thanks a bunch!

 

[vbaInet]

You're welcome.

 

[ajetrumpet]

You're welcome.

As the final last words of Jesus, IT IS FINISHED.

 

[vbaInet]

Jesus' last words did take 3 days for it to be said though

But hey, you never know, the OP might ressurect this thread, just like Jesus ressurected hehe!

 

[ajetrumpet]

hehe!

what's with the HEHE!??? Is that only aimed at young guns like myself?

 

[vbaInet]

Now who said I am in the "old age" bracket?