closing a form that uses a Dlookup

K

Karen

I have a form I use to enter new production requirements in to a
table. On that form I have a the following controls
Code
Qty Needed
Date Needed
Plan Date

The Code control has the following VBA on the On Exit event:
Private Sub fldcode_Exit(Cancel As Integer)
Dim varrtgno As String
Dim vardays As Double
Dim varfstop As Date
Dim varplandate As Date

varrtgno = DLookup("[rtg_no]", "dbo_imitmidx_sql", "[item_no]='" &
[Forms]![frm enter fillroom requirements]![fldcode] & "'")

'Me!rtgno = varrtgno

Select Case varrtgno

' some cases in here

End Select

'some if statements here


Me!fldplandate = DateAdd("ww", 4, Date)

varplandate = DateAdd("ww", 4, Date)

End If


End Sub

I want to put a button to close the form but when I do, I get an error
94, Invalid use of Null. I know the field Code is null.

So what should I do to close the form without seeing the error?

Karen S
 
D

Douglas J. Steele

It's not obvious to me what you're saying is Null. Is it the content of
[Forms]![frm enter fillroom requirements]![fldcode]?

If so, use something like:

If IsNull([Forms]![frm enter fillroom requirements]![fldcode]) = False Then
varrtgno = DLookup("[rtg_no]", "dbo_imitmidx_sql", "[item_no]='" &
[Forms]![frm enter fillroom requirements]![fldcode] & "'")

' rest of your code
End If

I would also advise changing

Dim varrtgno As String

to

Dim varrtgno As Variant

just in case the DLookup doesn't return a value.
 
K

Karen

It's not obvious to me what you're saying is Null. Is it the content of
[Forms]![frm enter fillroom requirements]![fldcode]?

If so, use something like:

If IsNull([Forms]![frm enter fillroom requirements]![fldcode]) = False Then
varrtgno = DLookup("[rtg_no]", "dbo_imitmidx_sql", "[item_no]='" &
[Forms]![frm enter fillroom requirements]![fldcode] & "'")

' rest of your code
End If

I would also advise changing

Dim varrtgno As String

to

Dim varrtgno As Variant

just in case the DLookup doesn't return a value.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)




I have a form I use to enter new production requirements in to a
table. On that form I have a the following controls
Code
Qty Needed
Date Needed
Plan Date
The Code control has the following VBA on the On Exit event:
Private Sub fldcode_Exit(Cancel As Integer)
Dim varrtgno As String
Dim vardays As Double
Dim varfstop As Date
Dim varplandate As Date
varrtgno = DLookup("[rtg_no]", "dbo_imitmidx_sql", "[item_no]='" &
[Forms]![frm enter fillroom requirements]![fldcode] & "'")
'Me!rtgno = varrtgno
Select Case varrtgno
' some cases in here
End Select
'some if statements here
Me!fldplandate = DateAdd("ww", 4, Date)
varplandate = DateAdd("ww", 4, Date)
I want to put a button to close the form but when I do, I get an error
94, Invalid use of Null. I know the field Code is null.
So what should I do to close the form without seeing the error?
Karen S- Hide quoted text -

- Show quoted text -

Thanks, that was pretty simple now that I see what you did.

Yes, the field that is null is [Forms]![frm enter fillroom
requirements]![fldcode]. Sorry I wasn't clear about that. After I
re-read my post I see how confused it all was. But the problem is
fixed now; Thanks!

K-
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top