Using IIf + default value for a combo box

[Opal]

I am trying to make a form almost complete itself
because it seems that many of my multiple users
can't change a combo box when needed.... :-(

A user enters details into a form regarding an
issue that they have investigated. When they
enter the details, the issue could be solved, and
therefore the "StatusID" combo box should be
changed to "Closed" or, they may still be working
on the issue and therefore the status should
be "Open". The default value is "Open" or 1.

When they enter a Closed Date, the value in
the StatusID should now be "Closed" or 2.
However, when they enter the closed date, they neglect
to change the status and that skews my data.

I thought if I used the following in the default
value of the StatusID combo box, it might work:

=IIf(IsNull([ClosedDate]),1,2)

But, it doesn't. My research tells me that the Default
Value is assigned at the point where a new record is started,
so Access doesn't know that the ClosedDate field will no
longer be null. How can I accomplish this in VBA when
the value in the ClosedDate text box changes from Null to
notNull?

 

[Maverick]

You would write the following code on the BeforeUpdate event for ClosedDate:

If IsDate(Me.ClosedDate) Then
Me.StatusID = 2

Else
Me.StatusID = 1

End If

 

[Opal]

You would write the following code on the BeforeUpdate event for ClosedDate:

If IsDate(Me.ClosedDate) Then
   Me.StatusID = 2

Else
   Me.StatusID = 1

End If

I am trying to make a form almost complete itself
because it seems that many of my multiple users
can't change a combo box when needed.... :-(

A user enters details into a form regarding an
issue that they have investigated.  When they
enter the details, the issue could be solved, and
therefore the "StatusID" combo box should be
changed to "Closed" or, they may still be working
on the issue and therefore the status should
be "Open".  The default value is "Open" or 1.

When they enter a Closed Date, the value in
the StatusID should now be "Closed" or 2.
However, when they enter the closed date, they neglect
to change the status and that skews my data.

I thought if I used the following in the default
value of the StatusID combo box, it might work:

=IIf(IsNull([ClosedDate]),1,2)

But, it doesn't.  My research tells me that the Default
Value is assigned at the point where a new record is started,
so Access doesn't know that the ClosedDate field will no
longer be null.  How can I accomplish this in VBA when
the value in the ClosedDate text box changes from Null to
notNull?- Hide quoted text -

- Show quoted text -

Beautiful, thank you!

 

[Maverick]

HTH

By the way, please click on yes to "Was this post helpful to you?" on the
post that answered your question. This lets others know that there was a
solution to your post. Thanks.

You would write the following code on the BeforeUpdate event for ClosedDate:

If IsDate(Me.ClosedDate) Then
Me.StatusID = 2

Else
Me.StatusID = 1

End If

I am trying to make a form almost complete itself
because it seems that many of my multiple users
can't change a combo box when needed.... :-(

A user enters details into a form regarding an
issue that they have investigated. When they
enter the details, the issue could be solved, and
therefore the "StatusID" combo box should be
changed to "Closed" or, they may still be working
on the issue and therefore the status should
be "Open". The default value is "Open" or 1.

When they enter a Closed Date, the value in
the StatusID should now be "Closed" or 2.
However, when they enter the closed date, they neglect
to change the status and that skews my data.

I thought if I used the following in the default
value of the StatusID combo box, it might work:

=IIf(IsNull([ClosedDate]),1,2)

But, it doesn't. My research tells me that the Default
Value is assigned at the point where a new record is started,
so Access doesn't know that the ClosedDate field will no
longer be null. How can I accomplish this in VBA when
the value in the ClosedDate text box changes from Null to
notNull?- Hide quoted text -

- Show quoted text -

Beautiful, thank you!

 

[Opal]

HTH

By the way, please click on yes to "Was this post helpful to you?" on the
post that answered your question. This lets others know that there was a
solution to your post. Thanks.

You would write the following code on the BeforeUpdate event for ClosedDate:
If IsDate(Me.ClosedDate) Then
   Me.StatusID = 2
Else
   Me.StatusID = 1
End If
:
I am trying to make a form almost complete itself
because it seems that many of my multiple users
can't change a combo box when needed.... :-(
A user enters details into a form regarding an
issue that they have investigated.  When they
enter the details, the issue could be solved, and
therefore the "StatusID" combo box should be
changed to "Closed" or, they may still be working
on the issue and therefore the status should
be "Open".  The default value is "Open" or 1.
When they enter a Closed Date, the value in
the StatusID should now be "Closed" or 2.
However, when they enter the closed date, they neglect
to change the status and that skews my data.
I thought if I used the following in the default
value of the StatusID combo box, it might work:
=IIf(IsNull([ClosedDate]),1,2)
But, it doesn't.  My research tells me that the Default
Value is assigned at the point where a new record is started,
so Access doesn't know that the ClosedDate field will no
longer be null.  How can I accomplish this in VBA when
the value in the ClosedDate text box changes from Null to
notNull?- Hide quoted text -
- Show quoted text -

Beautiful, thank you!- Hide quoted text -

- Show quoted text -

Maverick, I don't see that option in this google group....?