3021 error handling

[deljr15]

I am trying to put together a report to display a purchase order. For our orders we track the cost to either a work order or a expense account. For displaying/printing the PO I want to consolidate the 2 separate fields to one text box. I would like to do this in VBA so I can learn from it

what i have is

Private Sub Report_Load()

    Dim ea As String
    Dim wo As String
    
    
    [Expense_Account].SetFocus
    
    
    If Expense_Account Is Nothing Then
        ea = Null
    Else
        ea = Expense_Account.Text
    End If
    
    
    [Work_Order].SetFocus
    
    
    If Work_Order Is Nothing Then
        wo = Null
    Else
        wo = Work_Order.Text
    End If
    
    displayAccount.Value = ea & wo

End Sub

the problem is when it hits the empty field for that item it errors with "3021 no current record" I know there is something wrong with my If statements but I do not know what to compare it to that will result in it believing it is empty and skipping to the next item. I have tried

Work_Order.Text = Null
Work_Order.Value = Null
Work_Order.Text = 0
Work_Order.Value = 0

If anyone can point me in the right direction that would be much appreciated

Thanks in advance

 

[pbaldy]

I use this:

If Len(Me.SomeControl & vbNullString) = 0 Then

which tests for both Null and a zero length string.

 

[deljr15]

thanks pbaldy that solved the 3021 error. However this opened up another problem... because I'm assigning a value to a field in a tabular form it applies it to every line weather its applicable or not.

I'm not sure weather I should start a thread based that question or not.

 

[mdlueck]

I would suggest considering my shared function to safely read Form controls:

Safely read form field text controls
http://www.access-programmers.co.uk/forums/showthread.php?p=1131039#post1131115

(The thread is rather long, so put the cursor up in the URL bar, press enter, to make sure the browser is focused on the correct post.)

 

[pbaldy]

thanks pbaldy that solved the 3021 error. However this opened up another problem... because I'm assigning a value to a field in a tabular form it applies it to every line weather its applicable or not.

Based on what it looks like you're trying to do, the code should be in the detail section's format event rather than the load event. You might simply have this in a textbox and no code:

=Nz([Expense_Account], "") & Nz([Work_Order], "")

 

[deljr15]

You might simply have this in a textbox and no code:

=Nz([Expense_Account], "") & Nz([Work_Order], "")

Nice that is much simpler then what I have been trying to do, and it almost works

The problem is it displays the ID of the account/wo not the value of it... I'm sure it is something simple that I am doing wrong.

Edit: after some testing

The individual fields display properly, so I tried

=Nz([txtExpense_Account], "") & Nz([txtWork_Order], "")[/QUOTE]

but it still displays the ID

If I insert the query from one of the individual fields then it will display the 1 correctly wile displaying the other ones ID

SELECT [Work_Orders].[ID], [Work_Orders].[Work_Order] FROM Work_Orders;

I do not see a way to combine the 2 queries to display properly.

Am I over complicating this or I am missing something simple?

 

[pbaldy]

If I had to guess, you have a lookup field at the table level:

http://access.mvps.org/access/lookupfields.htm