Using a checkbox to control textboxes?

[laxster]

Hi,

It's been quite awhile since I've had to post in here; I think I have the general hang of Access down.

However, there's one thing I am struggling to figure out right now. I have a form in which people enter information that runs a query and spits the results out in text boxes on a report.

Two of those text boes I would like to have completely blank and empty if their respective check box on the form is blank. How would I trigger this behavior?

 

[pbaldy]

In the after update event of the checkbox (and perhaps the current event), test the value of the checkbox and clear the textbox accordingly.

 

[laxster]

Well, it should not display that textbox, which normally gets populated from that query. At the bottom of the form is "Generate" button, so clicking on the check box shouldn't trigger the report to generate or anything else until "Generate" is clicked.

I'm not quite sure how to get this to not display a textbox which normally gets filled by the report's generation query.

 

[pbaldy]

I'm sorry, you mean on the report? In the detail format event, check the value of the checkbox field and display/hide the textbox appropriately.

 

[laxster]

I'm sorry to sound ignorant, but where do I find this? When I go into properties for the form's checkbox I want to control, I don't see a detail format event. Am I looking in the wrong place?

 

[pbaldy]

Sorry, that refers to the detail section of the report. It's in the properties of the section.

 

[laxster]

Okay, I found it. Would I need to compile code for this? I can do some coding when it comes to queries, but I'm not sure what I'd need to code to pull this off.

I still have a lot to learn, and I appreciate your help.

 

[pbaldy]

Along the lines of:

If Me.CheckBox = True Then
  Me.Textbox.Visible = True
Else
  Me.Textbox.Visible = False
End If

Which can be shortened using Boolean logic to:

Me.Textbox.Visible = Me.CheckBox

 

[laxster]

Awesome! So I put that in the report code to execute, correct?

 

[pbaldy]

That is VBA code, and would go in the detail section's format event. If you need it:

http://www.baldyweb.com/FirstVBA.htm

 

[laxster]

I keep getting run-time errors. I try to change the "CheckBox" to "Check21" and "Textbox" to "Field1" so it matches what's currently setup, so I'm not sure why it's spitting run-time errors back. Am I inserting the wrong values into these code formulas?

 

[laxster]

So after some playing around, I found the code was for if there was a checkbox on the Report itself. So, the good news is this code works. However, the issue I'm running against is that checkbox is on a form, not the report. The form records to a table, and the field that the checkbox records to will have a 0 or a -1 (this is called "Display Pallet Qty")

The table is part of a query which generates the report. Sowhat I'm trying to get Access to do is if "Display Pallet Qty" is equal to -1 in the query, the textbox on the report displays. If it's 0 the textbox on the report does not display.

Does that make sense?

 

[pbaldy]

It should work with the field name if the field is in the report's source.

 

[laxster]

Well, that's what I've tried, and I keep getting run-time errors, so I'm not sure what I'm doing incorrectly. The run-time error I get is '2465' and says that Microsoft Office Access cannot find the field 'l' referred to in your expression.

Here's the modified code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.[Display Pallet Qty] = True Then
  Me.Field1.Visible = True
Else
  Me.Field1.Visible = False
End If
End Sub

I hope I'm not being frustrating here and appreciate your patience! I'm just a bit clueless as to what I'm doing wrong.

 

[pbaldy]

Can you post the db, or a sample of it?

 

[laxster]

It is slightly too large to post here. Could I send it?

Is there something I should be looking for here that I'm missing? I've found I learn a lot simply by doing.

 

[pbaldy]

Well, I'm surprised that it isn't recognizing a field in the record source, but the fix is easy enough. Put a textbox on the report bound to that field, call it "txtHiddenDisplayField" or whatever you want. Hide it, then change your code to:

If Me.txtHiddenDisplayField = True Then

 

[laxster]

Awesome, everything is working perfectly now!

 

[pbaldy]

Yowza! Glad we got it sorted out.