Prevent Overbooking Problem

[PhillipsTCasey]

I am working on a Kennel Database and I am currently working on the Grooming Appointment section. I am having problems giving the user a warning when they are about to overbook for the day.

When you are making an appointment you see sub-form that contains a preview of the day the appointment is for.

I currently have each appointment assigned a block value. This ranges from 1 - 3 depending on the type of dog being groomed. When the sub-form has data is sums the block values and I can determine if it is over.

The only problem is if there are no appointments for that day the sum returns a #Error value. I have tried http://allenbrowne.com/RecordCountError.html that tip but it did not work. I still get the error if there is no data.

Is there anyway around this #Error to make it show as 0?

I have thought about doing it all in the VBA Editor but I do not know how to detect if a subform has records and I also do not know how to sum a column in subform in code. I feel using code would be a more efficient method but I simply do not know how to do it.

 

[vbaInet]

What you need is the Nz() function. Look that up.

And welcome to AWF!

 

[pbaldy]

Nz() will not work in this instance. Try this:

http://www.mvps.org/access/forms/frm0022.htm

 

[PhillipsTCasey]

I am still getting an error

Dim overbook As String

overbook = Nz(Me.txtTotalBlock, 0)


MsgBox (overbook)

that is what I have when the user clicks the save button. I am just having it show in a msg to test.

I get a run-time error.

Run-time error '-2147352567 (800020009)':

You entered an expression that has no value.

Right now I have the sum of the column in a textbox in the footer of the subform. Then on the main form I have another textbox that gets the value of the textbox in the footer.

 

[vbaInet]

Did you see pbaldy's post?

Is txtTotalBlock an unbound text box?

 

[PhillipsTCasey]

Nz() will not work in this instance. Try this:

http://www.mvps.org/access/forms/frm0022.htm

That worked perfectly.

That website was a little vague though. For others with the same problem,

Place the code from the website above in a module.

Then in the controlsource of the textbox wrap the code in the nnz() tag.

Example of control source:

=nnz([txtTotal])

Thanks for all the help

 

[pbaldy]

No problemo, glad it worked for you.

 

[gemma-the-husky]

paul

that was useful snippet

1) Is there a general mechanism that works for all field types (eg strings etc) as the example specifically tested numeric.

2) does this only apply to testing a form/control where the fields are undefined. ie
a form with no records, where additions are not permitted (or the rst is non-updateable). Are there applications in other scenarios?