Check data in a field from a form

[Pam]

I have a fairly simple Access db. I have created a form to enter information.
One of the controls is a list where the user can choose one of several
options (outsourced, scrap, rework or none). At the end of the form I have a
control for "open, on hold, or closed". I don't want the user to be able to
choose "closed" UNLESS one of the options in the other control is checked. If
they try to choose "closed" in this instance, it should look to that control,
and if nothing is checked, I'd like to warn the user that they must choose
one of those options before they can close the job.

It's not working and I think I know why, but I can't figure out how to fix
it. This is my code:

Private Sub Option379_MouseUp(Button As Integer, Shift As Integer, X As
Single, Y As Single)
If Me.ECO_ACTION_TAKEN = "" Then
MsgBox "You must choose a value"
Me.Option379.SetFocus
End If
End Sub

I am getting the following error:

"Runtime Error 438 Object doesn't support this property or method."

The "Me.ECO_ACTION_TAKEN" is a field in my ERF/ECO table. So, when the user
clicks on the "close" button, I want the form ... this code ... to look at
the table and find the value in that field. If it is blank, then show the
MsgBox. How do I reference the TABLE and that field in this code rather than
the form I'm on. In using the "Me.otherfield", isn't that what I am doing,
referencing the form I'm on? How do I reference the table itself?

I have some knowledge of VB, but am pretty rusty. Would also be appreciative
of any recommendations for a good book(s) on Access DB programming for
instances like this as I am getting more involved in doing this type of
thing. Thanks!

 

[Marshall Barton]

I have a fairly simple Access db. I have created a form to enter information.
One of the controls is a list where the user can choose one of several
options (outsourced, scrap, rework or none). At the end of the form I have a
control for "open, on hold, or closed". I don't want the user to be able to
choose "closed" UNLESS one of the options in the other control is checked. If
they try to choose "closed" in this instance, it should look to that control,
and if nothing is checked, I'd like to warn the user that they must choose
one of those options before they can close the job.

It's not working and I think I know why, but I can't figure out how to fix
it. This is my code:

Private Sub Option379_MouseUp(Button As Integer, Shift As Integer, X As
Single, Y As Single)
If Me.ECO_ACTION_TAKEN = "" Then
MsgBox "You must choose a value"
Me.Option379.SetFocus
End If
End Sub

I am getting the following error:

"Runtime Error 438 Object doesn't support this property or method."

The "Me.ECO_ACTION_TAKEN" is a field in my ERF/ECO table. So, when the user
clicks on the "close" button, I want the form ... this code ... to look at
the table and find the value in that field. If it is blank, then show the
MsgBox. How do I reference the TABLE and that field in this code rather than
the form I'm on. In using the "Me.otherfield", isn't that what I am doing,
referencing the form I'm on? How do I reference the table itself?

That error probably indicates that ECO_ACTION_TAKEN is not a
field in the form's record source table/query.

Until you can explain what you have going on that makes you
think you have to check the field in the table, I have to
say that that does not make sense. If the form is bound to
a table query, then the record with both the status and
ECO_ACTION_TAKEN fields should be bound to controls on the
form. Think about what you are asking when a user is
creating a new record and the record has not been saved to
its table yet.

If the ECO_ACTION_TAKEN field is bound to a control (combo
box?) on the form, then you code should work. I would
probably undo the closed status before moving the fos back
to the ECO_ACTION_TAKEN control.

 

[Pam]

I didn't want the user to choose a "closed" status on the form until some
other information had been entered.

I ended up putting a reference to the field right on the form and making it
invisible. Then using DoCmd.GoToControl to put the focus back on that field
that the user needed to complete.

Private Sub Option379_MouseUp(Button As Integer, Shift As Integer, X As
Single, Y As Single)

If Me.Text409 = "0" Then
MsgBox "Before you can close this ECO, must choose an ECO ACTION TAKEN"
DoCmd.GoToControl "Frame363"
End If

End Sub

 

[Marshall Barton]

I didn't want the user to choose a "closed" status on the form until some
other information had been entered.

I ended up putting a reference to the field right on the form and making it
invisible. Then using DoCmd.GoToControl to put the focus back on that field
that the user needed to complete.

Private Sub Option379_MouseUp(Button As Integer, Shift As Integer, X As
Single, Y As Single)

If Me.Text409 = "0" Then
MsgBox "Before you can close this ECO, must choose an ECO ACTION TAKEN"
DoCmd.GoToControl "Frame363"
End If

End Sub

That's about what I was suggesting, although I would
probably disable the frame instead of making it invisible.

Is there a good reason to use Option379's MouseUp event
instead of its AfterUpdate event?

Side note, control names like Option379 and Frame363 are a
poor practice. It's better to name your controls with
something more meaningful. A common naming convention would
use optStatus and frmECO so you can tell what type of
control it is and what it is used for next year when you
can't remember what purpose Frame363 serves.

 

[Pam]

Marsh,

Thanks for the additional information. I did as you suggested and changed
it from MouseUp to AfterUpdate. I also tried making the other frame disabled
instead of invisible. However, when I did that, the frame is still visible
on the form, although it is greyed out.

Thanks for the tip on naming convention. I've already found it way more
useful and meaningful to do as you've suggested.