Evaluating Textbox for Null Values (1 Viewer)

[brucesilvers]

I am having problems getting Access to evaluate whether a textbox is empty using VBA behind my form. I'm currently using Access 2000 on a Windows98 machine, but I also tested this in Access 2003 on an XP machine and got identical results.

The way I’d like the code to operate is if the “Product1” field is empty, the value taken from another form would be pasted there, and if it isn’t empty, go to the “Product2” field and if that field is empty, paste it there, otherwise go to the “Product3” field, etc.

I have tried using If IsNull(“Product1”) Then… but Access seems to think that the unbound textbox with no default value isn’t empty.

I tried using If ("Product1" <> "*") Then… but Access again seems to think that there is something in that unbound/no-default value textbox.

My code looks like this:
If ("Product1" <> "*") Then
DoCmd.GoToControl "Product1"
DoCmd.RunCommand acCmdPaste
DoCmd.OpenForm "ActiveProduct", acNormal, "", "", , acNormal
DoCmd.GoToControl "Description"
DoCmd.RunCommand acCmdCopy
DoCmd.OpenForm "Order Form", acNormal, "", "", , acNormal
DoCmd.GoToControl "ProdDescr1"
DoCmd.RunCommand acCmdPaste
DoCmd.Close acForm, "ActiveProduct", acSaveNo
DoCmd.GoToControl "ProdQty1"
DoCmd.Close acForm, "Product Lookup form", acSaveNo

ElseIf ("Product2" <> "*") Then
DoCmd.GoToControl "Product2"
DoCmd.RunCommand acCmdPaste …

End If

I admit that my VBA skills are limited, but after having scoured these forums as well as Access help and examining the logic of my code, I am completely stumped. Any suggestions you can offer would be greatly appreciated!

Thanks!

 

[pbaldy]

I use this type of test, which will test for both Null and ZLS:

If Len(Me.Product1 & vbNullString) > 0 Then
  'Me.Product1 contains a value
End If

 

[MarkK]

Also look into the Nz Function, which returns <someValue> if the <expression> is null...

Nz(<expression>, <someValue>)

 

[brucesilvers]

Results...

First, thank you very much for your input on this.

I found that the problem was my syntax. Instead of using:

If IsNull("Product1")...

I changed it to:

IsNull([Forms]![Order Form]![ProdDescr1])...

The code worked perfectly after that. Again, thanks for your help!

 

[Pat Hartman]

Now that you've gotten to the point of writing macros in code, maybe it's time to actually write proper VBA. It is not necessary to open and close forms. It is Not necessary to move focus to a control. It is not necessary to cut and paste. You should be using queries that join to the necessary look up tables as the RecordSource for your form. Then all the fields you need should be in the form's RecordSource and you can actually bind them to controls so you don't need ANY code to view them.

If you actually want to copy a value from a field on an open form, it is just a SINGLE line of code. No cut and paste, no GoToControl to set focus.

Me.SomeField = Forms!SomeForm!SomeField ----- That's it!!

 

[brucesilvers]

You're absolutely right, Pat!

I'll be the first to admit that my coding in Access is neanderthal at best. I'm developing applications that are more sophisticated than my abilities, and doing so on a deadline.

In this case, I'm developing an application for people to take product orders as quickly as possible at a trade show, and the question I posted involved a couple of pop-up forms used to fill in order lines on the actual order form - a user double-clicks a product category from one form which pops up the second form containing products in that category, then double-clicks a product which fills in product #, description, etc. on the actual order form. To continue filling in the product order lines, I needed to check for the first available (blank) product line on the form so that the first line didn't get written over.

My Access development skills have been self-taught, and to be more accurate I should add that many of my skills have been learned from You and others on this site. That being the case, I'd like to again thank you for all your support over these past few years - I'd probably be flipping burgers right now without your help!