Help with simple If code (1 Viewer)

[Debased]

I am checking to see if a memo field and/or a text field has more than 1000 characters and if so then it calls a function that prints legal size paper vs. 8 X 11. It works fine except when the either field is empty. I have searched and searched the Forum and found many that are close but no cigar!

I know this involves a "IsNull" routine and I know I need to check both fields but can't quite come up with the right syntax. Right now if either field is empty I get an error msg = "Invalid use of Null"

Could someone please help?! Thanks in advance once again!
This is the basic code after setting the focus,etc....

If Len(strMsg) > 1000 Then
cmdOpenLegPrnt_Click

Exit Sub
End If

 

[KeithG]

Try below

if IsNull(strMsg)=False then
if Len(strMsg)>1000 then
cmdOpenLegPrnt_Click
End if
End if

 

[rborob]

strTemp = Me.CheckForm
If strTemp = vbNullString Then

Else
x = MsgBox("You need to enter a:" & strTemp, vbCritical)
Exit Sub
End If

Public Function CheckForm() As String

If IsNull(Me.firstfield) = True Or Me.firstfield = 0 Then
CheckForm = CheckForm & vbCrLf & "- FirstfieldDescription"
End If

If IsNull(Me.secondfield) = True Or Me.secondfield = vbNullString Then
CheckForm = CheckForm & vbCrLf & "- secondfielddescription"
End If

 

[rborob]

oops sorry..just typed code and pressed enter. the code i put checks to see whether there is anything in the fields you're using. i put checkforms in all my forms so that all relevant fields are filled in, if not it tells me which ones arent. hope that helps the IsNull problem!
good luck

 

[rborob]

oh yeah..and Dim the strTemp As String

 

[Debased]

thanks to both of u for such quick reply

KeithG: "still getting Invalid use of null" error

rboRob: It should be ok if they leave the field blank. I tried the "vbNullString" however same error.

thanks again

 

[ghudson]

Try...

If Not IsNull(strMsg) Or strMsg <> "" Then
     If Len(strMsg) > 1000 Then
          cmdOpenLegPrnt_Click
     End If
End If

 

[Debased]

ghudson: thanks but still getting "Invalid use of null" error

 

[KeithG]

Can you post your database?

 

[Debased]

Keith: I cannot post as it is a confidential patient grievance log. Is there something specific I could post or detail ? basically, it is a form that has a number of fields. Two fields are comment fields and when the character count of either field goes beyond 1000 the form will not all fit on a printed 8X 11 page. Now I know most would say I should use a report instead of printing the form but this has been working well for several years.

This new sub I added was so the user would not have to go into the print options and change the paper to legal. If the count > 1000 it opens a duplicate form with the print options already set to Legal. I can avoid the problem by simply displaying the Print Legal command button and let the user choose it manually so this is not of paramount importance, just a convenience.

By the way, prior to my adding this sub, all other checks ie, checking to make sure they fill in date fields and that some rules about what dates cannot be entered , worked just fine. When I delete the code for this sub, the error message goes away so I am pretty sure it is not being triggered by some other event.

Thanks for your attempts and interest to help!

 

[ghudson]

What is the name of the control [text box] that you are trying to evaluate? The control [text box] name should NOT be the same as the field name [control source] that the text box is bound to in the table [record source]. Access gets confused and does not know which one to check.

I have made a change to my original code...

If Not IsNull([YourTextBoxNameHere]) Or [YourTextBoxNameHere] <> "" Then
     If Len([YourTextBoxNameHere]) > 1000 Then
          Call cmdOpenLegPrnt_Click
     End If
End If

 

[Debased]

gHudson: thanks so much your last code did it!

I am curious as to why: I declared strMsg = [my Textboxnamehere] so it seems to me your first posted code should have also worked.

Oh well, I am grateful to move on from this little adventure with your help!

Thanks again

 

[ghudson]

It did work but as you noticed my first attempt was altering the value of the text box and then the record was saved so your table records were being converted to ***-**1234 for each record that was displayed in the form. I was rushing when I made my first post so I did not think it through. Messing with a label instead will preserve your data. Good luck!

 

[Debased]

ghudson: Are you sure you are posting your last reply to the correct thread?
man, I am really confused if you are because I don't see the connex! but thanx anyway...I wrote a sub using your code to check for the number of characters that works great!