Simple q: setting the value of one field based on a form selection

[Guest]

Hey all,

Newbie to VBA who is struggling along, but I've run into a minor (but
simple) problem. I have a form that lists a number of columns from a table.
One of the columns is uses a lookup function. I want to be able to change
the value of another column based on the first column. Example:

Table:
Col 1 - Shape
Col 2 - Color (lookups to: red, green, blue)
Col 3 - Has color (bool)

Form lists all tables.

User goes to row 1 ("Circle") and changes the blank 'color' field to "Red".
Access then sets 'has color' to "Yes."
**or**
User goes to row 1 ("Circle") and changes the 'color' field from "Red" to
blank.
Access then sets 'has color' to "No."

I've been trying to put (ugly) code in pretty much every event handler field
that seems relevant, but I can't get it going. Any help - even just
guildelines saying "use AfterUpdate and look up help on [codenamehere]"...

Thanks!
Dave

 

[John Vinson]

Hey all,

Newbie to VBA who is struggling along, but I've run into a minor (but
simple) problem. I have a form that lists a number of columns from a table.
One of the columns is uses a lookup function. I want to be able to change
the value of another column based on the first column. Example:

Table:
Col 1 - Shape
Col 2 - Color (lookups to: red, green, blue)
Col 3 - Has color (bool)

Form lists all tables.

User goes to row 1 ("Circle") and changes the blank 'color' field to "Red".
Access then sets 'has color' to "Yes."
**or**
User goes to row 1 ("Circle") and changes the 'color' field from "Red" to
blank.
Access then sets 'has color' to "No."

I've been trying to put (ugly) code in pretty much every event handler field
that seems relevant, but I can't get it going. Any help - even just
guildelines saying "use AfterUpdate and look up help on [codenamehere]"...

I would strongly suggest that the "Has Color" boolean field should
simply not exist in your table. IT's redundant.

You can instead create a Query (or set the control source of a
checkbox or textbox) to

=Not IsNull([Color])

This value will be TRUE if the Color field is not NULL, False if it
is.

If you're stuck with this redundant, risky field (somebody could go
outside your form and change the value of either the [Color] or [Has
Color] fields, making your table WRONG), then use code in the combo
box's AfterUpdate event:

Private Sub Color_AfterUpdate()
Me![Has Color] = (Me!Color & "" <> "")
End Sub

John W. Vinson[MVP]

John W. Vinson[MVP]