cmd button visibility - dependant upon subform

[calum112]

I'm 'rather' stuck I've had some help to get it working so far but I'm
totally stuck on this:

Each of my records in a form has a cmd button 'expand' next to it, when
pressed, the subform for that record is displayed in a new form. The subform
is matched to the record using field 2 and 3.

Now, how do I make it so that when the page loads up it checks each record in
the form to see if it has a subform (based upon the above fields) and if not
then it hides the button 'expand' for that record.

I've got this so far:

If Not IsNull(DLookup("[Field 2] = '" & Me.[Field 2] & "' And [Field 3] = " &
Me.[Field 3])) Then
Forms![Sub Categories - Do everything]![Expand].Visible = False
Else
Forms![Sub Categories - Do everything]![Expand].Visible = True
End If

And I've tried putting it in the 'On Current' (code builder) part of the form,
however it just tells me the first line (sub expand) is not optional???

Majorly stuck so a way out is much much appreciated, I dont' want to lose my
hair so quickly

Cheers

 

[calum112]

Ok after a bit of reading I've found about using a text box to do this.

I'm now having trouble to get the controlsource requirments working, What do
I need to put in that to get it to work?

Thanks, loads of information on this site

 

[calum112 via AccessMonster.com]

Right, I'm totally confused now. I've tried every different way of writing
the following in the control source field but it is always rejected for some
reason or another??

= If Not IsNull(DLookup("[Field 2] = '" & Me.[Field 2] & "' And [Field 3] = "
& Me.[Field 3])) Then "Expand" else "" End If

You can probably tell coding isn't my strong point.

 

[Bob Hairgrove]

I'm 'rather' stuck I've had some help to get it working so far but I'm
totally stuck on this:

Each of my records in a form has a cmd button 'expand' next to it, when
pressed, the subform for that record is displayed in a new form. The subform
is matched to the record using field 2 and 3.

Now, how do I make it so that when the page loads up it checks each record in
the form to see if it has a subform (based upon the above fields) and ifnot
then it hides the button 'expand' for that record.

I've got this so far:

If Not IsNull(DLookup("[Field 2] = '" & Me.[Field 2] & "' And [Field 3] = " &
Me.[Field 3])) Then
Forms![Sub Categories - Do everything]![Expand].Visible = False
Else
Forms![Sub Categories - Do everything]![Expand].Visible = True
End If

And I've tried putting it in the 'On Current' (code builder) part of theform,
however it just tells me the first line (sub expand) is not optional???

Majorly stuck so a way out is much much appreciated, I dont' want to lose my
hair so quickly

Cheers

It sounds like you are displaying the main form's records in
continuous form view. If you are, then you won't be able to
selectively disable your buttons or make them invisible. As of Access
2000, there were some changes made to what you could do in continuous
form view such as making the background color of a text box a
different color. However, I don't think this has been expanded to
buttons -- maybe someone else can correct me if this is possible?

If each record is displayed singly in form view, then you can add code
to the form's OnCurrent event procedure to do what you want.

However, you will still probably have issues with having columns named
[Field 1], [Field 2], etc. (google for "normalize" or "normal forms").

 

[calum112 via AccessMonster.com]

Spot on; I am using a continuous form.

So to get around it I need to make a text box (of the same background colour
as the form) which only displays text when my conditions are met, then I can
put an OnClick command on the text to make it open my form. That way I have
effectively make a button whose visibility can be set per record.

Now if that's correct then I need to put a condition in the controlsource of
the text where it displays 'open form' if, and only if, a record in the other
form's primary key matches.

So I need something like this:

If Not IsNull(DLookup("[Field 2] = '" & Me.[Field 2] & "' And [Field 3] = "
& Me.[Field 3])) Then "open form" End If

But how do I format that so it works as the controlsource of a text field?

 

[Bob Hairgrove]

Spot on; I am using a continuous form.

So to get around it I need to make a text box (of the same background colour
as the form) which only displays text when my conditions are met, then Ican
put an OnClick command on the text to make it open my form. That way I have
effectively make a button whose visibility can be set per record.

Now if that's correct then I need to put a condition in the controlsource of
the text where it displays 'open form' if, and only if, a record in the other
form's primary key matches.

So I need something like this:

If Not IsNull(DLookup("[Field 2] = '" & Me.[Field 2] & "' And [Field 3] = "
& Me.[Field 3])) Then "open form" End If

But how do I format that so it works as the controlsource of a text field?

If...Then...End If can only be used within a VBA function or sub.
You'll want to check out the help topic for the IIf() function (two
"I"s). Then, your control source would look something like this:

Note how you need to prefix the function name with the assignment
operator (i.e. "=").

=IIf(<condition>,"text1","text2")

As to the evaluation of <condition>, which must evaluate to True or
False, you can use IsNull with the DLookup function. However, your
syntax for DLookup doesn't look right, so look in the help topic for
that as well.

 

[calum112 via AccessMonster.com]

Ahh brilliant, thank you very much; that's the exact reply I was after

I've had a read through those help files, plus a couple of others and have
got it half working. I just still can't the DLookup function to work
correctly with the IsNull expression, it's a tricky bugger.

=IIf([field 2]="G","Confirmed","Not Confirmed")
Works (True)

=IIf(IsNull([field 2]="G"),"Confirmed","Not Confirmed")
Works (False)

=IIf(IsNull(DLookup("[Field 2] = '" & Me.[Field 2])),"Confirmed","Not
Confirmed")
Doesn't work?

p.s. Those aren't my actual field names, I've just been changing them for
ease of reference

Quick refresh: I want to compare the data in the primary keys of two tables
(field 2 & 3 of each) and if a record in both tables has the same date in
those fields then I want the above expression to output either the true or
false statement. The other form is called 'sub categories' or it can use the
table 'Sub Parts'.

Spot on; I am using a continuous form.

[quoted text clipped - 13 lines]

But how do I format that so it works as the controlsource of a text field?

If...Then...End If can only be used within a VBA function or sub.
You'll want to check out the help topic for the IIf() function (two
"I"s). Then, your control source would look something like this:

Note how you need to prefix the function name with the assignment
operator (i.e. "=").

=IIf(<condition>,"text1","text2")

As to the evaluation of <condition>, which must evaluate to True or
False, you can use IsNull with the DLookup function. However, your
syntax for DLookup doesn't look right, so look in the help topic for
that as well.

 

[calum112 via AccessMonster.com]

I've just realised, from my searching, that field 2 is text and field 3 is
numerics. I didnt' realise that was important until just a moment ago.