Tick box problem

[ansentry]

I have a continuous form that has two fields’ txtCusNameAndAddress and txtIncludeInMerge.

txtIncludeInMerge is a tick box default -1

The user can “un-tick” the tick box next to a customers name and when the “Continue” button is clicked, the record is saved and the “un-ticked” customers are not included in the merge.

Now the problem; Lets say there are 10 Customers listed on the form and the user un-ticks ALL the tick boxes, clicks Continue and because only customers with a -1 are included in the merge, there are not records to merge and I get an error in my code that creates the merge.

What I would like is a code sample that will check the number of ticks, if ALL un-ticked then I will use a msgbox “All records de-selected” etc, Else if at least 1 is ticked proceed as normal.

Any help will be appreciated.


Regards,

 

[RichO]

How about doing a DSum of the check box fields in your table?

Is the record source for your form a table or a query that selects only certain records to the form? If the form is based directly on a table you can simply DSum the IncludeInMerge field in the table. If it's anything but zero you can proceed with your code.

 

[ansentry]

RichO,

While I was waiting for a reply I tried the "Count" functions, this is in an Unbound Text Box (txtCountTicks) on the form and it return the number of ticks fine.=Count([conIncludeInMerge])

After you reply I tried yours,
=DSum("[conIncludeInMerge]","qryMergeElseTemp") that works also but if there are say 17 ticked records then it returns -17.

Now this is the problem, I have the following code behind a command button (just to test for errors). It works fine for any value that is 1 or greater but if all the ticks are removed then it give me an error. "Runtime error 2427" You entered an expression that has no value.

In lieu of <=0 I Null, I have tried If isNull (me.txtCountTicks) then etc but nothing works.

This is what is highlighted in my code SumTicks = txtcountTicks
What is the value of a Count Function is it is empty?

Private Sub cmdTest_Click()
Dim SumTicks As Integer
SumTicks = txtcountTicks

If SumTick <= 0 Then

      MsgBox "The count is " & SumTicks

ElseIf SumTick >= 0 Then


      MsgBox "The count is " & SumTicks

End If

End Sub

Thank you for your reply, any further help would be appreciated.

Regards,

 

[Bat17]

you could try

Private Sub cmdTest_Click()
Dim SumTicks As Integer
SumTicks = nz(txtcountTicks)

If SumTick <= 0 Then

      MsgBox "The count is " & SumTicks

ElseIf SumTick >= 0 Then


      MsgBox "The count is " & SumTicks

End If

End Sub

HTH

Peter

 

[Mile-O]

Other info:

txt is a prefix used to denote a textbox. The widely used prefix for a chekbox is chk. You should refer to it on the current form with the Me. method.

i.e. Me.chkMyCheckBox

if there are say 17 ticked records then it returns -17.

That's because True is represented by -1. 17 multiplied by -1 is -17.

Two ways to return it to a positive integer:

=DSum("[conIncludeInMerge]","qryMergeElseTemp") * -1

=Abs(DSum("[conIncludeInMerge]","qryMergeElseTemp"))

If there are no records the the DSum will create an Invalid Use of Null error which can be remedied with the Nz() function arounf it.

 

[ansentry]

SJ McAbney,

Thank you this worked,

=NZ(Abs(DSum("[chkConIncludeInMerge]","qryMergeElseTemp")))

Bat17,

Will give your answer a try as well, thank you



Regards,