combo box value based on if - then statement

[Guest]

I have a unbound control on a form that calculates the Qty remaining on a PO
release. In the subform I have an bound combobox (cboReleaseStatus) with
its rowsource based on an OrderStatus table and displays PO status options
(open, closed, or canceled - with a hidden statusID column).

I would like cboReleaseStatus to update based on the value in the
QtyRemainingOnRelease control.

I have this code in place, but it isn’t working….where have I gone wrong?

Private Sub ReleaseStatus_AfterUpdate()
If [subF_ReceivedGoods].Form![QtyRemainingOnRelease] <= 0 Then
Me.ReleaseStatus = 2
Else
Me.ReleaseStatus = 1
End If

Me.Requery
End Sub


Thanks
Diane

 

[Guest]

Since the control you are trying to reference is on another form that is the
sourc object of the subform control on your form, you have to include the
name of the subform control and the source object (Form). It would be like
this:

Me.SubformControlName.Form!ReleaseStatus = 2

 

[Guest]

Thanks for the quick reply!

I am still struggling with what control name goes where in the code...
here is what I want to happen....

If [QtyRemainingOnRelease] on form [EnterPO] is <= 0
Then (bound combo) [ReleaseStatus] should display "Closed".

The [ReleaseStatus] combo box is bount to table PODetails and has a
rowsource of SELECT OrderStatus.OrderStatusID, OrderStatus.OrderStatus FROM
OrderStatus;
the OrderStatusID for closed is 2, open is 1.

Would you mind walking me through what control names go where in the If Then
statement?

I apologize for being so needy, but I have just started to attempt to teach
myself VBA.

Thanks again,
Diane

Since the control you are trying to reference is on another form that is the
sourc object of the subform control on your form, you have to include the
name of the subform control and the source object (Form). It would be like
this:

Me.SubformControlName.Form!ReleaseStatus = 2
--
Dave Hargis, Microsoft Access MVP

I have a unbound control on a form that calculates the Qty remaining on a PO
release. In the subform I have an bound combobox (cboReleaseStatus) with
its rowsource based on an OrderStatus table and displays PO status options
(open, closed, or canceled - with a hidden statusID column).

I would like cboReleaseStatus to update based on the value in the
QtyRemainingOnRelease control.

I have this code in place, but it isn’t working….where have I gone wrong?

Private Sub ReleaseStatus_AfterUpdate()
If [subF_ReceivedGoods].Form![QtyRemainingOnRelease] <= 0 Then
Me.ReleaseStatus = 2
Else
Me.ReleaseStatus = 1
End If

Me.Requery
End Sub


Thanks
Diane

 

[Guest]

Your combo has two columns. Column 1 should be the bound column. This is in
the properties dialog for the control. The first column is the ID and the
second is the Description. In the ColumnWidths property, you will want to
set the first column width to 0 so it will not be visible. it would be
something like 0";0.5"
That would make the first column invisible and the second column 1/2" wide.

The info provided is a little scetchy in spots, but if I understand
correctly, a control named [QtyRemainingOnRelease] on the main form is what
you are testing on and the combo [ReleaseStatus] is on the subform.

When you want to reference a control on a subform, you have to reference the
subform control by name and the object Form and the control name:

The parts are:
Forms!MainFormName!SubFormControlName.Form!ControlName

If the code is in the main form, you can use Me just like you do for
referencing controls on the form. In reality this is what you are doing.
The SubformControlName is the name of the subform control on the main form.
You have to use the word Form to tell access you want to refer to the form
object of the subform control. Then you need to reference the control on the
form that is the subform for the main form.

I know this is all very confusing, but this is the way it is.

Also, you will not use the literal description, because that is not what is
being used in your table. It should be the ID.

If Me.QtyRemainingOnRelease <= 0 Then
Me.Subform.Form![ReleaseStatus] = 2
End If
--
Dave Hargis, Microsoft Access MVP

Thanks for the quick reply!

I am still struggling with what control name goes where in the code...
here is what I want to happen....

If [QtyRemainingOnRelease] on form [EnterPO] is <= 0
Then (bound combo) [ReleaseStatus] should display "Closed".

The [ReleaseStatus] combo box is bount to table PODetails and has a
rowsource of SELECT OrderStatus.OrderStatusID, OrderStatus.OrderStatus FROM
OrderStatus;
the OrderStatusID for closed is 2, open is 1.

Would you mind walking me through what control names go where in the If Then
statement?

I apologize for being so needy, but I have just started to attempt to teach
myself VBA.

Thanks again,
Diane

Since the control you are trying to reference is on another form that is the
sourc object of the subform control on your form, you have to include the
name of the subform control and the source object (Form). It would be like
this:

Me.SubformControlName.Form!ReleaseStatus = 2
--
Dave Hargis, Microsoft Access MVP

I have a unbound control on a form that calculates the Qty remaining on a PO
release. In the subform I have an bound combobox (cboReleaseStatus) with
its rowsource based on an OrderStatus table and displays PO status options
(open, closed, or canceled - with a hidden statusID column).

I would like cboReleaseStatus to update based on the value in the
QtyRemainingOnRelease control.

I have this code in place, but it isn’t working….where have I gone wrong?

Private Sub ReleaseStatus_AfterUpdate()
If [subF_ReceivedGoods].Form![QtyRemainingOnRelease] <= 0 Then
Me.ReleaseStatus = 2
Else
Me.ReleaseStatus = 1
End If

Me.Requery
End Sub


Thanks
Diane

 

[Guest]

Thank you so much for your help!