Works - now it doesnt

[JohnD]

What would trigger an error to occur if there has been no changes to a DB.

My error # is 2427 (You entered an expression that has no value).

This error occurs when I click on a command button to open a report. When I debug, it sends me to an IF statement that I have loaded in the On Format of the report.

This worked perfectly fine before - the If statement is simple, if a value is true, then it changes a box to bold and if the value is false, the box in the report remains the same weight.

I am not understanding why it is saying that I have entered an expression with no value when the IF statement reads both the true and the false of a chkbox and adjusts the box accordingly.

Can someone explain why this is occuring? Im litteraly stuck.

 

[ChrisO]

Could the chkbox be Null?

Can you post the code you are using or better still a small demo of the problem?

 

[Rich]

Use the Nz function

 

[JohnD]

This is my command key to preview the report:

Private Sub cmdOpenReport_Click()
    If Me.Filter = "" Then
        msgbox "This preview is record specific, apply a filter before proceeding"
    Else
        DoCmd.OpenReport "Newptd", A_PREVIEW, , Me.Filter
        DoCmd.RunCommand acCmdFitToWindow
    End If
End Sub

This is the code for the report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [completioncheck] = True Then

    completionbox.BorderWidth = 3
    compbox.BorderWidth = 2
        
Else

    completionbox.BorderWidth = 1
    compbox.BorderWidth = 1
    
End If
If [empcheck] = True Then

    Employedbox.BorderWidth = 3
    empbox.BorderWidth = 2
    
Else

    Employedbox.BorderWidth = 1
    empbox.BorderWidth = 1
    
End If
If [educheck] = True Then

    conteducationbox.BorderWidth = 3
    edubox.BorderWidth = 2
        
Else

    conteducationbox.BorderWidth = 1
    edubox.BorderWidth = 1

End If

End Sub

When the reader is viewing a printed report - the boxes width grabs the focus of the reader to a specific location on the report.

Rich, I will have to do a little research on the Nz function. I have not used it before (like many, im too still learning)

 

[JohnD]

I did some research on the Nz function. In simple terms: It lets you return a value when a variant is null.

Okay, now my check box displays either a 0 or a -1.

0 being false and -1 being true.

This means that my check box is NOT null....correct?

(I thought a check box could never be null considering it always holds a value - true or false)

..and correct me if im wrong - NULL is essentially the term for something not having data correct? For example, if I have two fields, Phone # and Alt Phone # - if there is no data in Alt Phone #, then that would be considered Null?

 

[Bat17]

A check box can be set to Triple State, which would retun a greyed out checkbox.

I would add a debug line and the check the imediate window to see wht value was passed from the form.

debug.print me.completioncheck
if [completioncheck] = True Then

HTH

Peter

 

[JohnD]

Peter, I tried the line of code and it gave me the same error and highlighted the line of code

debug.print me.completioncheck

So I added this line of code before the If statement

Detail_Format_Err:
    msgbox Err.Number & " - " & Err.Description

Im not sure if I did this correct but this is what was returned -

0

Problem with this is when I check the check box to make it true (-1) the code still pulls up 0

I have added a small example of the DB (I know there is no normalization, but this needs to get me by for a little while). Unfortunetely its A97.

when prompted to enter an Annual Report, enter in 2005. From there, there is a command button on the bottom to 'Preview PTD'. Click on that and that is where the error occurs. For some reason, my 2006 & 2007 work out fine, but the 05 does not.

Instead of entering 2005, enter in 2006 or 2007 to see HOW its supposed to work

 

[JohnD]

I also checked if it was set to 'triple state' and it is set to no.

Im so confused

any more suggestions is greatly appreciated

 

[JohnD]

I got it to work. Instead of referencing the checkbox itself on the report to format a box, I referenced the checkbox on the form. So the new line of code is the following:

If Forms!frmfilterform!UnavailableforCompletion.Value = True Then

It still gave me errors on every IF statement so I had to replace each statement with the above code referencing my form instead of the report.

Im still puzzled on why it did not work before, but at least its working now. Thank you for the suggestions - I learned a little more by trial and error

John D

 

[Freak81]

This done doesn't work for me either.

I still get the error "You entered an expression that has no value" (run-time errror 2427) on this line:

If Forms!INPUT_LIGGING!ParkeerCheck.Value = True Then

I don't know what's going wrong, but he doesn't seem to know my checkbox.

Can anyone give other suggestions on how to overcome this problem?

Thnx in advance.

 

[jmurphy]

is it possible the form you're referncing isn't open?

 

[Freak81]

What do you mean by that? I can change other fields in my form...

 

[Freak81]

Now that you say it, another strange thing about this checkbox is that I can't uncheck it after I checked it (initially it's unchecked). Could this have anything to do with it?

 

[The_Doc_Man]

First, a clarification. If you don't specify a default value for a checkbox, it can have three states regardless of the triple-state settng. If you have a "virgin" record and no default value, your underlying yes/no field will be neither Yes nor No. Instead it will be NULL (not the character ASCII NULL but the state of non-existence). Because, you see, the Access paradigm for field storage is to store NOTHING AT ALL until you have a storing reference to the field. This was designed into fields as a debugging tool originally. If you tried to use an uninitialized field, Access barfed because of that NULL. Stated another way, Access storage has some overhead because it uses a "Sparse Storage" algorithm. It has POINTERS to its data - but the pointers can be set to point to nothing until a value has been stored for the field in question.

Second, though you imply it, you don't state whether the checkbox is directly bound to an underlying record. From the discussion, it is at least RELATED to that record, but is it directly related or does it come through a formula? If the box comes from a forumula, it is possible to set it but be unable to clear it if setting it triggered an action that affects more than one field, but clearing doesn't trigger the same action.

Third, referencing a checkbox on a form from a report works if and only if the form is open at the time. Forms can "exist" in the Forms panel of the DB window but that doesn't mean they are open. A form's definitions exist in a collection called "Documents." (Same for reports - both are considered documents by Access.) Only an OPEN form exists in the collection called "Forms." The form's checkbox only exists on the instantiated form. Further, there is the little problem of synchronizing data between the form and the report. A form is a "one-record-at-a-time" thing except when you are using continuous forms. A report is a "whole table/query-at-a-time" thing except when you work in one of the report sections - detail, headers, footers, that sort of thing. So the question is, from the report, only one section of that report will correspond to the contents of the form. Or worse, if the form is continuous, you'll still have to find the form detail section corresponding to that part of the report detail.

So the biggest way to untangle the mess is to take that original DB and try to decide to what that checkbox owed its existence. A formula? A bound field? An arbitrary and capricious unbound field? Phase of the moon? What? Once you have identified the data source, you might have a better handle on the problem. So look in the properties of the checkbox to see its ControlSource. That will be the starting point. After that, my friend, you have a bit of detective work ahead of you.

 

[ChrisO]

The original problem, as posted by John D, was that the filter produced no data on which the report could be based.

Might be the same for Freak81.

 

[JohnD]

ChrisO,
You are right, I went back to when I had the error and ran a few tests. Opening up the form and with out filtering - I attempted to open the report from the form and I recieved the same error '2427'

I then closed the form and opened it up a second time. This time I applied the filter and then ran the report - It worked fine.

So here's my question, can you make the form 'filter' on-open to eliminate the problem all together?

Thanks

 

[ChrisO]

John D.

Three tables, three forms, three reports, no queries, no attempt to normalize and the form code will not compile.

Access is a database not a spreadsheet and filtering is the least of your problems.

That’s about the best I can do.

Regards,
Chris.

 

[JohnD]

Ouch! I certainly wont argue valid points, however, in my place of business, the reports need to be viewed and printed by a number of individuals.

These people are not the best with technology, so if it is a simple fix, then by all means it would be more then helpful to fix this piece of the puzzle.

I am trying to build (not re-build, but build from scratch) another database that has normalization - unfortunetely the process goes slow when there are others who want to see things happen with the existing database. So the attempt is certainly being made and I have another thread in which I was waiting on some feedback on the normalization of my tables. Unfortunately, I currently have no option of dropping what currently exists.

I took this first project into my own hands to make my job easier in which overall it has made it easier by leaps and bounds. Its now about learning from the first DB and moving on and developing a structured DB that has room to grow.
I am a beginner at Access and by no means pretend to be efficient in it, but I certainly am up to the challenge of learning.

So that being said - I understand that the structure is not all there and that no normalization exists, but can this small problem be fixed? I would assume that I would do something like the following

me.filter = ""

Thank you for all the input and previous help and I certainly agree with you Chris - im just taking things one at a time.