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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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]"...
Click to expand...

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]
 
Back
Top