VBA

[Sarah]

Hi,

Need a little help with VBA. I have written the below and what i want it to
do, is from the main form if i pick a cylinder (Drop down box) then it goes
to a different form in "Add Mode", but if i pick a Tank then i want it to go
to a different form in "add mode".

But the below is not working? Could someone please help.

Thanks
Sarah

Private Sub Multiif()

If Job_Installable.[Column](1) = "Cylinder" Then
Forms!OrderForm.ctlSubForm.Form!Controls.NewData
Forms!
[tblJob_Consumables SubForm]![Job Consumable Consumable ID].ControlSource =
"qryConsumables"
ElseIf Job_Installable.Column(1) = "Tank" Then
[tblJob_Consumables SubForm]![Job_Consumable Consumable ID].ControlSource =
"qryConsumableTank"
End If
Me.tbl_Job_Consumable_SubForm.Requery

End Sub

 

[Philip Herlihy]

There are some really smart folk here who could probably tell you right off
what's wrong, but I'm too lazy to figure it all out without using the tools
provided. I do wonder what the line ending .NewData is supposed to do, and
why one line setting the ControlSource begins with Forms! and one doesn't,
but I could figure all that out using the tools.

That's the main thing I think you're getting wrong - you're not taking the
built-in help available!

Firstly, I always set "Option Explicit" at the top of a module, so that any
object which isn't declared throws a compile error. Secondly, I never (not
even for the most trivial Sub or Function) omit error handling code. See
this: http://allenbrowne.com/ser-23a.html - for most procedures it's enough
to use the nine numbered lines.

Then set a breakpoint in your code (bring up the VBA Editor using Alt-F11
and click in the margin). When the execution point hits that line it'll
raise the VBA editor and you can step through line by line. You'll see at
which point execution jumps to the error-handler and see a message saying
what the runtime thinks is wrong. This is surprisingly easy and
informative - you can hover over a variable and view its value, and there
are all sorts of other neat stunts you can pull.

Also, it's worth mentioning the "Compile" option under the Debug menu in the
VBA editor. Before running the code, check it compiles!

Finally, I have a nagging doubt that changing the ControlSource at run-time
is going to lead to the best solution. Perhaps you could use (and
hide/show) different subforms? But that's a different question...

Hope that helps!

Phil, London

 

[J_Goddard via AccessMonster.com]

Hi -

What do you mean by "not working"? Do you get an error message?
When you say "go to a different form", what do you mean? Do you want to open
one of two different forms on top of you main form, or do you want to select
which form is displayed in a sub-form control on the main form?

Your code does neither - the ControlSource property does not indicate which
form is to be used as the subform. To do that, look at the SourceObject
property of the subform control.

HTH

John

Hi,

Need a little help with VBA. I have written the below and what i want it to
do, is from the main form if i pick a cylinder (Drop down box) then it goes
to a different form in "Add Mode", but if i pick a Tank then i want it to go
to a different form in "add mode".

But the below is not working? Could someone please help.

Thanks
Sarah

Private Sub Multiif()

If Job_Installable.[Column](1) = "Cylinder" Then
Forms!OrderForm.ctlSubForm.Form!Controls.NewData
Forms!
[tblJob_Consumables SubForm]![Job Consumable Consumable ID].ControlSource =
"qryConsumables"
ElseIf Job_Installable.Column(1) = "Tank" Then
[tblJob_Consumables SubForm]![Job_Consumable Consumable ID].ControlSource =
"qryConsumableTank"
End If
Me.tbl_Job_Consumable_SubForm.Requery

End Sub

 

[Sarah]

Hi, I am very new at writing code etc and know what i want the system to do
but cannot figure out how to do it. Does anyone know any good websites for
VBA code learning etc??? I had help writing this code with a trainer that we
paid for but she couldnt make it work either so figure that something is
wrong with it but cannot for the life of me get it to work. It is meant to
open a subform which when you pick cylinder or tank would oepn that sub form.
Does this make sense???

Thanks
Sarah

Hi -

What do you mean by "not working"? Do you get an error message?
When you say "go to a different form", what do you mean? Do you want to open
one of two different forms on top of you main form, or do you want to select
which form is displayed in a sub-form control on the main form?

Your code does neither - the ControlSource property does not indicate which
form is to be used as the subform. To do that, look at the SourceObject
property of the subform control.

HTH

John

Hi,

Need a little help with VBA. I have written the below and what i want it to
do, is from the main form if i pick a cylinder (Drop down box) then it goes
to a different form in "Add Mode", but if i pick a Tank then i want it to go
to a different form in "add mode".

But the below is not working? Could someone please help.

Thanks
Sarah

Private Sub Multiif()

If Job_Installable.[Column](1) = "Cylinder" Then
Forms!OrderForm.ctlSubForm.Form!Controls.NewData
Forms!
[tblJob_Consumables SubForm]![Job Consumable Consumable ID].ControlSource =
"qryConsumables"
ElseIf Job_Installable.Column(1) = "Tank" Then
[tblJob_Consumables SubForm]![Job_Consumable Consumable ID].ControlSource =
"qryConsumableTank"
End If
Me.tbl_Job_Consumable_SubForm.Requery

End Sub

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca




.