If field = "" Then...

[serak]

Hello,

I'm terrible at VBA, but here's my problem (it's probably very simple):

I have a field, and if it is not empty I want a report to open. This is what I have -


DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAvailableYes", acNormal, acEdit
DoCmd.OpenQuery "qryFineAmount", acNormal, acEdit
If tblLoan!FineAmount = "" Then <- [this is what's causing the problem, I'm
not sure how to write it]
Else:
DoCmd.OpenReport "rptFine", acPreview, "", ""
End If
DoCmd.SetWarnings True


I appreciate any help.

 

[Mile-O]

You'd have found out yourself if you weren't turning the warnings off.

DoCmd.OpenQuery "qryAvailableYes", acNormal, acEdit
DoCmd.OpenQuery "qryFineAmount", acNormal, acEdit

Next up, I don't understand why you are opening two queries. If they are action queries then I can understand that but, if they were, I certainly wouldn't open them for editing.

If tblLoan!FineAmount = "" Then

tblLoan!FineAmount ?

How do you expect to refer to a table like this? If such a thing were possible then there is the problem that FineAmount is a field and, as such, can possibly hold thousands of records. How would that method decide which to evaluate upon? Record 1? Record 2? Record 20,000? It's just not possible.

You should have the recordset bound to the relevant textbox on your form. That way you can refer to it as:

Me.FineAmount

.

Remember to give your controls a good prefix so that you can differentiate a control on your form from a field bound to a control. txt for a textbox is considered the most common. i.e. Me.txtFineAmount

Next, what are you actually testing for? A control with no value (Null) or a control with a null length string ("") ?

For the former you would use the IsNull() function and the latter you would use the method that you have. I suspect you wish to use the former though as a control's default value is Null.

Next up, your IF ELSE END IF structure.

If ... Then
    ' do nothing
Else
   ' do something
End If

What's the point in the Else if nothing is happening? Just reverse the evaluation.

So,

If IsNull(Me.txtFineAmount) Then

becomes

If Not IsNull(Me.txtFineAmount) Then

Thus the structure becomes:

If Not IsNull(Me.txtFineAmount) Then
    DoCmd.OpenReport "rptFine", acPreview, "", ""
End If

One last thing:

The null length strings in the OpenReport method are unnecessary. They are considered strings and waste space. You can leave them out of the statement or replace "" with vbNullString - the compiled constant that represents "".

i.e.

DoCmd.OpenReport "rptFine", acPreview, ,

or

DoCmd.OpenReport "rptFine", acPreview, vbNullString, vbNullString

 

[serak]

Sorry, but I did say I was terrible at this. Thanks alot for your help.

Right, I fixed all those things you said so I'll try and explain what I'm trying to do: "qryFineAmount" is an update query which updates the field "FineAmount", however this field isn't in the form itself. So if the query adds a value to that field I want a report to open, "rptFine". Do you think it can be done?

Thanks for you help and sorry about being...an idiot.

 

[Mile-O]

What's the purpose of opening qryAvailableYes?

 

[serak]

Oh, it's just an updated query for a yes/no field - not that important.

 

[Mile-O]

Does this work?

With DoCmd
    .SetWarnings False
    .OpenQuery "qryAvailableYes"
    .OpenQuery "qryFineAmount"
    .SetWarnings True
End With

If Not IsNull(Me.txtFineAmount) Then
    DoCmd.OpenReport "rptFine", acPreview
End If

 

[serak]

Yes!!! brilliant, thanks alot