Using IIF with checkbox?

First, can you use IIF with a checkbox?

What I want to do is if the checkbox is marked(item is sold), then after
update (checkmark is checked) I want it to append that item to a new table
and open the form that goes with the table and if it is not done then do
nothing (null?).

So do I need to create an append query and then have it open the query?

What it is is if an item is sold at auction, I want to check that the item
is sold and then move that item to a sold item table that has more
information about the sale.

Any help would be great!

On Tue, 7 Jul 2009 09:21:03 -0700, Amelia wrote:

First, can you use IIF with a checkbox?

What I want to do is if the checkbox is marked(item is sold), then after
update (checkmark is checked) I want it to append that item to a new table
and open the form that goes with the table and if it is not done then do
nothing (null?).

So do I need to create an append query and then have it open the query?

What it is is if an item is sold at auction, I want to check that the item
is sold and then move that item to a sold item table that has more
information about the sale.

Any help would be great!

Not a good idea to 'move' records.
Add the [SoldItem] Check Box field to the table, and then also add
that check box field onto your data entry form.
Use a query as record source of your form.
Set the criteria on the [SoldItem] column to:
0

When you open the form, only records that have not been sold will
display on the form. When that item is sold simply check the box. When
you requery or close and re-open the form, that sold item will no
longer appear.

See if you can keep that additional data in the same table.
Probably no need for a different table to add additional information,
but without knowing what the additional information is and what you
intend to do with it, I won't comment more on that.


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

That makes sense. Thanks. As far as having the additional information in a
new table. I would like it to be seperate or have it show only if the
checkbox is marked sold. (not sure how I would do that either, or if it is
possible)

So for instance if this pc was sold, data entry would mark it as sold then
additional fields would pop up that need to be filled in (selling price, sold
date, pick up date, etc...)

Does that help you make a determination in the best way to help?


"fredg" wrote:

On Tue, 7 Jul 2009 09:21:03 -0700, Amelia wrote:

First, can you use IIF with a checkbox?

What I want to do is if the checkbox is marked(item is sold), then after
update (checkmark is checked) I want it to append that item to a new table
and open the form that goes with the table and if it is not done then do
nothing (null?).

So do I need to create an append query and then have it open the query?

What it is is if an item is sold at auction, I want to check that the item
is sold and then move that item to a sold item table that has more
information about the sale.

Any help would be great!

Not a good idea to 'move' records.
Add the [SoldItem] Check Box field to the table, and then also add
that check box field onto your data entry form.
Use a query as record source of your form.
Set the criteria on the [SoldItem] column to:
0

When you open the form, only records that have not been sold will
display on the form. When that item is sold simply check the box. When
you requery or close and re-open the form, that sold item will no
longer appear.

See if you can keep that additional data in the same table.
Probably no need for a different table to add additional information,
but without knowing what the additional information is and what you
intend to do with it, I won't comment more on that.


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Tue, 7 Jul 2009 12:22:01 -0700, Amelia wrote:

That makes sense. Thanks. As far as having the additional information in a
new table. I would like it to be seperate or have it show only if the
checkbox is marked sold. (not sure how I would do that either, or if it is
possible)

So for instance if this pc was sold, data entry would mark it as sold then
additional fields would pop up that need to be filled in (selling price, sold
date, pick up date, etc...)

Does that help you make a determination in the best way to help?

"fredg" wrote:

On Tue, 7 Jul 2009 09:21:03 -0700, Amelia wrote:

First, can you use IIF with a checkbox?

What I want to do is if the checkbox is marked(item is sold), then after
update (checkmark is checked) I want it to append that item to a new table
and open the form that goes with the table and if it is not done then do
nothing (null?).

So do I need to create an append query and then have it open the query?

What it is is if an item is sold at auction, I want to check that the item
is sold and then move that item to a sold item table that has more
information about the sale.

Any help would be great!

Not a good idea to 'move' records.
Add the [SoldItem] Check Box field to the table, and then also add
that check box field onto your data entry form.
Use a query as record source of your form.
Set the criteria on the [SoldItem] column to:
0

When you open the form, only records that have not been sold will
display on the form. When that item is sold simply check the box. When
you requery or close and re-open the form, that sold item will no
longer appear.

See if you can keep that additional data in the same table.
Probably no need for a different table to add additional information,
but without knowing what the additional information is and what you
intend to do with it, I won't comment more on that.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Add those fields to the table.
Add those new fields to the query that is the record source for the
form.
Add those fields to the form.
In the Tag property of each of the controls you wish to normally hide,
write:
Hide

Then code the Form's Current event:

Private Sub Form_Current()
Dim c As Control
For Each c In Controls
If c.Tag = "Hide" Then
If [SoldItem] = -1 Then
c.Visible = True
Else
c.Visible = False
End If
End If
Next
End Sub

Place the same code in the [SortItem] AfterUpdate event.

The controls will become visible only when the SortItem check box has
a check.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Thanks Fred, I am going to give it a whirl. I think this is going to work!

My one question is this, would if I did not use a query to get my form?

"fredg" wrote:

On Tue, 7 Jul 2009 12:22:01 -0700, Amelia wrote:

That makes sense. Thanks. As far as having the additional information in a
new table. I would like it to be seperate or have it show only if the
checkbox is marked sold. (not sure how I would do that either, or if it is
possible)

So for instance if this pc was sold, data entry would mark it as sold then
additional fields would pop up that need to be filled in (selling price, sold
date, pick up date, etc...)

Does that help you make a determination in the best way to help?

"fredg" wrote:

On Tue, 7 Jul 2009 09:21:03 -0700, Amelia wrote:

First, can you use IIF with a checkbox?

What I want to do is if the checkbox is marked(item is sold), then after
update (checkmark is checked) I want it to append that item to a new table
and open the form that goes with the table and if it is not done then do
nothing (null?).

So do I need to create an append query and then have it open the query?

What it is is if an item is sold at auction, I want to check that the item
is sold and then move that item to a sold item table that has more
information about the sale.

Any help would be great!

Not a good idea to 'move' records.
Add the [SoldItem] Check Box field to the table, and then also add
that check box field onto your data entry form.
Use a query as record source of your form.
Set the criteria on the [SoldItem] column to:
0

When you open the form, only records that have not been sold will
display on the form. When that item is sold simply check the box. When
you requery or close and re-open the form, that sold item will no
longer appear.

See if you can keep that additional data in the same table.
Probably no need for a different table to add additional information,
but without knowing what the additional information is and what you
intend to do with it, I won't comment more on that.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Add those fields to the table.
Add those new fields to the query that is the record source for the
form.
Add those fields to the form.
In the Tag property of each of the controls you wish to normally hide,
write:
Hide

Then code the Form's Current event:

Private Sub Form_Current()
Dim c As Control
For Each c In Controls
If c.Tag = "Hide" Then
If [SoldItem] = -1 Then
c.Visible = True
Else
c.Visible = False
End If
End If
Next
End Sub

Place the same code in the [SortItem] AfterUpdate event.

The controls will become visible only when the SortItem check box has
a check.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

It worked perfectly! Thanks!

"fredg" wrote:

On Tue, 7 Jul 2009 12:22:01 -0700, Amelia wrote:

That makes sense. Thanks. As far as having the additional information in a
new table. I would like it to be seperate or have it show only if the
checkbox is marked sold. (not sure how I would do that either, or if it is
possible)

So for instance if this pc was sold, data entry would mark it as sold then
additional fields would pop up that need to be filled in (selling price, sold
date, pick up date, etc...)

Does that help you make a determination in the best way to help?

"fredg" wrote:

On Tue, 7 Jul 2009 09:21:03 -0700, Amelia wrote:

First, can you use IIF with a checkbox?

What I want to do is if the checkbox is marked(item is sold), then after
update (checkmark is checked) I want it to append that item to a new table
and open the form that goes with the table and if it is not done then do
nothing (null?).

So do I need to create an append query and then have it open the query?

What it is is if an item is sold at auction, I want to check that the item
is sold and then move that item to a sold item table that has more
information about the sale.

Any help would be great!

Not a good idea to 'move' records.
Add the [SoldItem] Check Box field to the table, and then also add
that check box field onto your data entry form.
Use a query as record source of your form.
Set the criteria on the [SoldItem] column to:
0

When you open the form, only records that have not been sold will
display on the form. When that item is sold simply check the box. When
you requery or close and re-open the form, that sold item will no
longer appear.

See if you can keep that additional data in the same table.
Probably no need for a different table to add additional information,
but without knowing what the additional information is and what you
intend to do with it, I won't comment more on that.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Add those fields to the table.
Add those new fields to the query that is the record source for the
form.
Add those fields to the form.
In the Tag property of each of the controls you wish to normally hide,
write:
Hide

Then code the Form's Current event:

Private Sub Form_Current()
Dim c As Control
For Each c In Controls
If c.Tag = "Hide" Then
If [SoldItem] = -1 Then
c.Visible = True
Else
c.Visible = False
End If
End If
Next
End Sub

Place the same code in the [SortItem] AfterUpdate event.

The controls will become visible only when the SortItem check box has
a check.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

A separate SoldItems table would in fact be a better design, but you would
not move data from the Items table to it. SoldItems is actually a sub-type
of Items, and creating this table would protect data integrity because it
allows you to do it at table level, rather than merely at form level. The
latter alone does not protect against invalid data being entered by some
other means, so the possibility of an unsold item being given a selling price
etc still exists. Having a single table also means that the columns relating
to sold items will be Null. Null is semantically ambiguous as, being the
absence of a value and not a value per se, has no intrinsic meaning, so can
cause problems of interpretation (as an example think about what a Null
credit rating for a customer would mean – it could mean 'no credit', it could
equally mean 'unlimited credit'; there is no way of knowing).

It does require a little more work to achieve the better model, however, so
I'll leave it to you to decide whether you do want to go that far, but if you
do then this is what's required:

1. In the Items table create a unique index on the table's primary key
column, ItemID say, and the SoldItems column. The index is on both columns
in conjunction, not as individual columns. This might sound odd as the
primary key column is already indexed uniquely of course in its own right by
virtue of being the primary key, but the index on the two columns is
necessary before you can create an enforced relationship with the SoldItems
table as described below.

2. Create the SoldItems table with columns ItemID, SoldItem, SellingPrice,
SoldDate, PickUpDate, etc. But do not include any other columns which
correspond to those in the Items table such as ItemDescription. The SoldItem
column in both tables should be of Boolean (Yes/No) data type.

3. Make the ItemID and the SoldItem columns the composite primary key of
SoldItems table.

4. Set the Defaultvalue property of the SoldItem column in SoldItems to:

True.

and set the ValidationRule property of the SoldItem column in SoldItems to:

=True

This means that the SoldItem column in SoldItems has a value of True inserted
automatically when a new row is inserted and that this cannot be changed to
False.

5. Create a relationship between Items and SoldItems on both the ItemID and
SoldItems columns and enforce referential integrity. You now have a solid
model which means that (a) an item cannot be given sold data such as a price
and date unless its marked as sold in Items and (b) an item cannot be
unmarked as sold in Items until ant matching row in SoldItems is deleted.
Consequently the integrity of the data is protected.

6. For data entry use a form based on Items, and within it a subform based
on SoldItems, linking the form to the subform on ItemID. Hide the subform
until an item is marked sold with:

Me.SoldItemsSubform.Visible = Me.SoldItem

in the form's Current event procedure, and put the same line of code code in
the AfterUpdate event procedure of the SoldItem control in the main parent
form. You might also wish to put some validation code in the BeforeUpdate
event procedure of the SoldItem control in the main parent form:

Const conMESSAGE = _
"You must delete the record from the Sold Items table " & _
"before marking an item unsold."

Dim strCriteria As String

strCriteria = "ItemID = " & Me.ItemID

If Not Me.SoldItem Then
If Not IsNull(DLookup("ItemID ", "SoldItems", strCriteria)) Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
Me.Undo
End If
End If

The above might sound a bit long-winded but its probably taken me longer to
type it than it would have to implement it.

Ken Sheridan
Stafford, England

Amelia wrote:
That makes sense. Thanks. As far as having the additional information in a
new table. I would like it to be seperate or have it show only if the
checkbox is marked sold. (not sure how I would do that either, or if it is
possible)

So for instance if this pc was sold, data entry would mark it as sold then
additional fields would pop up that need to be filled in (selling price, sold
date, pick up date, etc...)

Does that help you make a determination in the best way to help?

First, can you use IIF with a checkbox?

[quoted text clipped - 27 lines]
but without knowing what the additional information is and what you
intend to do with it, I won't comment more on that.

--
Message posted via http://www.accessmonster.com

"Amelia" , iletisinde şunu yazdı,
...
First, can you use IIF with a checkbox?

What I want to do is if the checkbox is marked(item is sold), then after
update (checkmark is checked) I want it to append that item to a new table
and open the form that goes with the table and if it is not done then do
nothing (null?).

So do I need to create an append query and then have it open the query?

What it is is if an item is sold at auction, I want to check that the item
is sold and then move that item to a sold item table that has more
information about the sale.

Any help would be great!