Check boxes to become visible when two values match (1 Viewer)

Rob_Jones101

Member
Local time
Today, 08:14
Joined
Aug 8, 2019
Messages
41
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

MVP / VIP
Local time
Today, 08:14
Joined
Jan 14, 2017
Messages
18,186
Its easy enough to do this for a single form
Aircode:
Code:
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

Member
Local time
Today, 08:14
Joined
Aug 8, 2019
Messages
41
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

Super Moderator
Staff member
Local time
Today, 08:14
Joined
Sep 12, 2006
Messages
15,614
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

I’m here to help
Staff member
Local time
Today, 01:14
Joined
Oct 29, 2018
Messages
21,358
Hi. I may have done something similar to this before, but I'll have to check. I'll be back.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:14
Joined
Oct 29, 2018
Messages
21,358
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...
 

Attachments

  • design.png
    design.png
    20.7 KB · Views: 265
  • form.png
    form.png
    44.7 KB · Views: 264

Micron

AWF VIP
Local time
Today, 04:14
Joined
Oct 20, 2018
Messages
3,476
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

AWF VIP
Local time
Today, 00:14
Joined
Mar 9, 2014
Messages
5,424
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.
 
Last edited:

Rob_Jones101

Member
Local time
Today, 08:14
Joined
Aug 8, 2019
Messages
41
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

AWF VIP
Local time
Today, 08:14
Joined
Jul 26, 2013
Messages
10,355
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

Member
Local time
Today, 08:14
Joined
Aug 8, 2019
Messages
41
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

AWF VIP
Local time
Today, 08:14
Joined
Jul 26, 2013
Messages
10,355
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

I’m here to help
Staff member
Local time
Today, 01:14
Joined
Oct 29, 2018
Messages
21,358
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

MVP / VIP
Local time
Today, 08:14
Joined
Jan 14, 2017
Messages
18,186
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
 

Attachments

  • TwoValuesMatch.zip
    23.2 KB · Views: 101
  • Capture.PNG
    Capture.PNG
    11.8 KB · Views: 239

theDBguy

I’m here to help
Staff member
Local time
Today, 01:14
Joined
Oct 29, 2018
Messages
21,358
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", "£")
 

Users who are viewing this thread

Top Bottom