Case Select on subform (1 Viewer)

[fibayne]

Hi

I had hoped to be able to choose from a combo on a continuous subform and by the selection make certain controls visible or invisible, it does do this however the selection in each new line on the form changes what is visible and invisible on all previous lines, is it possble to, by the selection on a combo, on a continuous subform, have certain controls show, per line, on the form ??? I hope it is as always thanks for any help given

cheers

Fi

Private Sub Form_Current()
Call ShowPOrderControls
End Sub

Private Sub ShowPOrderControls()
Me.cboProduct.Visible = False
Me.cboSupplier.Visible = False
Me.CP.Visible = False
Me.RRP.Visible = False
Me.Quantity.Visible = False
Me.CCP.Visible = False
Me.Duty.Visible = False
Me.DutyDue.Visible = False
Me.DateofJob.Visible = False
Me.Start.Visible = False
Me.Finish.Visible = False
Me.TimeDescription.Visible = False
Me.TC.Visible = False
Me.TimeSpent.Visible = False
Me.TimeCost.Visible = False

Select Case Me.ProductCode

Case 1, 2, 3, 6
Me.cboProduct.Visible = True
Me.cboSupplier.Visible = True
Me.CP.Visible = True
Me.RRP.Visible = True
Me.Quantity.Visible = True
Me.CCP.Visible = True
Me.Duty.Visible = True
Me.DutyDue.Visible = True
Case 5
Me.DateofJob.Visible = True
Me.Start.Visible = True
Me.Finish.Visible = True
Me.TimeDescription.Visible = True
Me.TC.Visible = True
Me.TimeSpent.Visible = True
Me.TimeCost.Visible = True

Case Else

End Select
End Sub

Private Sub ProductCode_AfterUpdate()
ShowPOrderControls
End Sub

 

[Galaxiom]

On a continuous form the controls are the same ones repeated for each record. Consequently I would not expect they could be individually configurable.

 

[Kafrin]

No, I'm afraid you can't do this on a continuous form. Things like the Visible property of a control apply to the control in general. You must remember that whilst the control shows different data for each record, it is still the same control on every record.

Sorry, hope you can work around this!

 

[fibayne]

Hi galaxio and kafrin...thanks for your replies, not the answer I wanted to hear though. ..would have a suggestion to get round this ???...cheers Fi

 

[Kafrin]

It really depends what you're hoping to see...

Could you make the form be a single-record-view form instead? Then you can set the visible properites in the OnCurrent event.

Could you move the controls that change onto a second (single-record) form which is accessed from a button on your continuous form? It could be a pop-up form and the visible properties could be set in the OnOpen event.

 

[joh024]

Hi galaxio and kafrin...thanks for your replies, not the answer I wanted to hear though. ..would have a suggestion to get round this ???...cheers Fi

For something like this it get's a little bit complex. I have done things similar though so it is not impossible. What I would do is remove the binding from the form so that it points to no table. Then use a module to insert a single record into the form each time a new record is needed. when the new record is inserted into the form you can decide at that time what does and does not need to be visible similar to what you were already doing.

I think that there is a docmd function that allows you to goto a specific record when a table is opened.

Or if you used something like a auto-Id field you can keep a static count within your form and use a query within a module to reference that specific record.

good luck!
-TJ

 

[Kafrin]

Also, the controls you are showing/hiding, do the need to be editable? I not, could you leave them visible, but change your recordsource so that in certain cases they show "N/A" instead of the field value?

 

[fibayne]

Hi Joh024..thanks for your reply, afraid your suggestion is a bit beyond me at the moment...I am thinking about have 2 subforms that are either visible or invisible depending on the button clicked on the main form, but am having probs as I have the data for both subforms stored in the same table, i can get the one I want to be visible but it shows all the lines related to the main form for that record (they are blank as those specific controls had not been filled)....thanks again
Kafrin looking at your suggestion ...thanks

 

[Kafrin]

For your two subforms, you need to limit the recordsource for each, so for one you would have something like:
"SELECT * FROM YourTable WHERE ProductCode = 5;"
and for the other you'd amend the 'where' clause, probably to "WHERE ProductCode <>5".

 

[fibayne]

Would this work do you think when creating new records ? essentially I want to be able to enter new records into the Main table that both subforms are based on from either of the subforms ??

 

[Kafrin]

I *think* you'd be OK with this, but to be honest you're in a better position to test it than me!

Alternatively could you use just your one original subform, but apply a filter to it depending on what was chosen in the main form? Then I'm sure you'll have no problems with adding records.

 

[fibayne]

thanks Kafrin...will try out a few things tonight, and hopefully come up withsomething...will post back..cheers Fi