Check boxes to become visible when two values match

[Rob_Jones101]

Hi.

I have a sub form for a purchasing system. I need the check box to be invisible until the units ordered field equals the units received fields on a continuous form. I need each check box on each line to be independent of each other.

I'm not sure at all how the code would look for this. I know it would be an if statement but not sure how to make it for each line and not all the check boxes on the form.

Thanks

 

[isladogs]

Its easy enough to do this for a single form
Aircode:

If Me,UnitsOrdered=Me.UnitsReceived Then 
   Me.chkCheckboxName.Visible=True
Else
   Me.chkCheckBoxName.Visible=False
End If

The code could be streamlined further ...

However if you have a continuous form, that type of code will cause all checkboxes to be visible or all hidden

Another approach could be to use conditional formatting to enable/disable the checkbox

 

[Rob_Jones101]

Yeh I know if you use that code all the check boxes appear but I only want one per line to appear.

I've never done conditional formatting before. What would I have to do to make it work.

Thanks.

 

[gemma-the-husky]

As an alternative, could you have two forms (or a filter/group option of some sort) on the form to allow the user to select between open orders and fully supplier orders.

then your filter checks the order quantity against the supplied quantity, and just displays the set you want. A slightly different way of getting a similar result

 

[theDBguy]

Hi. I may have done something similar to this before, but I'll have to check. I'll be back.

 

[theDBguy]

I apologize. My memory didn't serve me well this morning. I looked and this is what I found.

Here it is in Design View:

And here it is in Form View:

As you can see, I was hiding buttons instead of a checkbox. So, it's not quite the same as what you were trying to do. I'll play with it and see if I can do the same with a checkbox. No promises...

 

[Deleted member 147267]

I need the check box to be invisible until the units ordered field equals the units received fields on a continuous form.

Does this mean per record, or over the entire set of subform records? If the former and field1 and field2 are equal then I think all you'd have to do is update the record to True (because the checkbox field would be bound) thus conditional formatting OR vba could then show the checkbox field and AFAIK, would only be for that record. The key to controlling visibility of controls on a continuous form is to make the control bound to something, or make a hidden bound field control the visibility of non-data type controls such as command buttons.

 

[June7]

Conditional Formatting available only for textboxes and comboboxes. CF also does not set Visibility. It can set Enabled/Disabled and BackColor and ForeColor.

What purpose does yes/no field serve?

How many checkboxes on each line?

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[Rob_Jones101]

It is for a purchasing system. When we receive an invoice and the units ordered is equal to units received then the check box will available. The button is to show each item on the order has been received. Sometime we only receive half of the items so it hasn't been fully invoiced and should not appear in the invoiced list. Have you got the code to bind the checkbox to the two fields or how would that be done. Also theDBguy do you have the code for forms you have shown. I would like to have a look at that and see how it works.

Thanks for the help guys.

 

[Minty]

So the checkbox is there to be ticked once the order is completed?

That seems redundant because you can simply calculate that the order is fulfilled, rather than having to update another field?

 

[Rob_Jones101]

Minty

There can be multiple lines on each order so we need to differentiate which items have been delivered and which ones are still outstanding on the order. We can receive 5 out of 10 items and it will be classed as not completed. It will be part invoiced. The check box isn't visible until the items received equal the items ordered.

 

[Minty]

I understand that - I assume you have an ordered qty in your orders table and a received qty in your goods received table that links back to your order line id?

That way you can always calculate which items are outstanding by order by line?

 

[theDBguy]

It is for a purchasing system. When we receive an invoice and the units ordered is equal to units received then the check box will available. The button is to show each item on the order has been received. Sometime we only receive half of the items so it hasn't been fully invoiced and should not appear in the invoiced list. Have you got the code to bind the checkbox to the two fields or how would that be done. Also theDBguy do you have the code for forms you have shown. I would like to have a look at that and see how it works.

Thanks for the help guys.

Hi Rob. Unfortunately, that database is not mine, so I can't really share it here. I made that form for a client for their business. But, if you could post a sample copy of your db with test data, I can try to do the same thing for you.

 

[isladogs]

If you really want to persevere with this, here is a way you can do it:

This works as follows:
In Wingdings2 font, the equivalent of letter "R" looks like a checkbox

The CheckMatch textbox is unbound and uses a Wingdings2 font with control source = IIf([Field1]=[Field2],"R","")

Hope that helps

NOTE:
1. I stole the idea from a similar answer by JoeD in a UA thread where the OP wanted a locked symbol instead of a checkbox
2. Remember Access is NOT case sensitive by default so record 6 is treated as a match. If that isn't desirable there is a fix for that

 

[theDBguy]

Ah, Colin beat me to it. I was thinking of doing it that way; except of course, my IIf() statement might look something more like:


=IIf([Field1]=[Field2] AND [Checked]=True, "R", "£")