Invalid Null

[nicwnacw]

I have very little knowledge of VB and am trying to do a stock controls/sales/purchases system on a database - have found some code in a text book that seems to solve all my problems but it doesn't want to work. I get an 'invalid Null' message when I use it. I assume that this is because there is nothing in the field until I select which stock code I want to sell/purchase.

the code is added to the On Exit so I don't understand why it rejects as soon as I go into the field?

Code is:

Dim StockNum as Long
StockNum = StockID (This is what returns the invalid Null)
DoCmd.OpenForm "stock"
StockId.SetFocus
DoCmd.FindRecord StockNum
Forms![sales orders]![sales trans Subform].Form

I also get a message saying that the subform can't be found - syntax error?

I have been struggling with this for days - read Access help files which may as well be written in Martian.

Please help - with explanations (for dummies). I can't attach the d/b it's too big

Thank you.

 

[ghudson]

You should be testing for Nulls at the beginning of your code and exit the sub if the field in question is Null.

If Nz([StockID]) = "" then
Beep
MsgBox "The Stock ID field is empty. Please fix it."
Exit Sub
End If

Dim StockNum as Long
StockNum = StockID (This is what returns the invalid Null)
DoCmd.OpenForm "stock"
StockId.SetFocus
DoCmd.FindRecord StockNum
Forms![sales orders]![sales trans Subform].Form

 

[Rich]

You have to save the record before you can do anything with the value.
What are you expecting this part of the code to do
Forms![sales orders]![sales trans Subform].Form ?

 

[nicwnacw]

Invalid null

the whole process is that entering stock sales (subform) on the sales order form will:
1. open stock and go to the matching stock code
2. enable me to update stock after exiting the stock code field.

 

[Pat Hartman]

ghudson, If Nz([StockID]) = "" then will only work if stodkID is text. You have not specified a value to be returned when StockID is null so the default will be returned. If StockID is numeric, the function will return zero (hence the null to zero name). But if StockID is text, the function will return a zero length string. Bottom line, it is always better to specify what you want returned and be sure of your data types.

nicwnacw, you have a duplicate post where you have other answers.