Creating expression that changes a checkbox

[zipmaster07]

I'm pretty inept at using VBA and I seem to be having some trouble creating a proper expression. I've got a table called goals and an int column in it called idea (goals.idea). This column actually links back to an ideas table but that's probably not really relevant here. I have a form called "Goal Details" that uses the goals table as a record source. In the form I put a checkbox control and called it "Idea". I want the checkbox to be checked if goals.idea is not null and to not be checked if it is null. I tried doing this by writing the following expression in the control source of the checkbox:

IIF(IsNull([goals]![idea]=1),[Me].[Idea].[Value]=0,[Me].[Idea].[Value]=1)

However this always makes the checkbox look like this not matter what is in the goals.idea column:



any help would be appreciated.

[June7]

In Access 0 = False and -1 = True.

Can't use Me. in ControlSource expression. Me is used in VBA as an alias for form name.

You have a field called Idea in the form's RecordSource? What type of field is it? Why do you also need a checkbox with a calculated value?

[zipmaster07]

You have a field called Idea in the form's RecordSource? What type of field is it? Why do you also need a checkbox with a calculated value?

The record source of the "Goal Details" form is simply pulling the goals table and that's it. The goals table has a column called idea which is of type number. There is also a control on the Goal Details form that is called Idea and is of type check box. goals.idea links to a table called ideas (idea_id is the primary key)... goals.idea --> ideas.idea_id.

I store a lot of business proposals under the ideas table simply to keep track of them. The goals tables is more formal and has (or at least eventually will have) a lot more details associated to it. So ideas can become goals, but it doesn't have to be that way, a goal doesn't have to be an idea first. In this situation (where a goal was originally not an idea) the goals.idea field would be null and I would like the Idea checkbox to be unchecked, indicated that the goal never originated from an idea. However, if it did originate from an idea then the goals.idea column will not be null and it will link back to the ideas table. In this case I want the Ideas checkbox to be checked. As far was what the original idea actually was, I could care less. If someone wants to know that info they can go and manually find that information. I guess a better name for the checkbox is actually "WasIdea" or "IsIdea" instead of just "Idea".

So I changed the expression to: IIf(IsNull([goals]![idea]),[Idea].[Value]=0,[Idea].[Value]=-1) and it seems to not make any difference. Maybe this isn't the best way to accomplish my above goal (no pun intended) and I should be asking what the best practice is for the above situation. Hopefully this makes sense... thanks

[June7]

I suppose user could just look at the Idea field and conclude that if the field is blank the goal is an original and not from Idea table.

I have never tried to calculate a checkbox value by expression in ControlSource. Try this one:

=Not IsNull([idea])

[zipmaster07]

Hmm... i guess so. I'll keep poking around, thanks for help.

[June7]

I just tested my suggested expression in checkbox ControlSource and it works.