Requiring Data Input for a Field in a Dialog Box

[nytwodees]

I am using Access 2000.

I have a Pop-Up Dialog Box that requests the user to supply 3 different
pieces of information. The choices are from 3 different List Boxes. When the
3 different pieces of data are selected by the user, the user can then
preview or print an invoicing report (by clicking a control button on the
form). I do not want the report to process unless all 3 fields have data
selected. Basically I would like all 3 fields data to be required before any
other action can be taken.

I would appreciate some help. Thanks!

 

[Klatuu]

In design mode, set the command button's Enabled property to False.

Now use a function in your form something like:

Function SetCommandButton() As Boolean
Dim blnEnable As Boolean

With Me
If Isnull(.List1) Then
blnEnabled = False
ElseIf IsNull(.List2) Then
blnEnabled = False
ElseIf IsNull(.List3) Then
blnEnabled = False
Else
blnEnabled = True
End If
.My CommandButton.Enabled = blnEnabled
End With


End Function

And to complete it, call the function from the After Update event of each
List box. You just the the following directly in the After Update event text
box in the property sheet:
=SetCommandButton()

 

[nytwodees]

Thanks Dave for your speedy and knowledgable response.

Note: PreviewLabelSheetCommand is my command button
InvoiceListPUPYear is my 1st List
InvoiceListPUPPaymentsNumber is my 2nd List
InvoiceListPUPVillage is my 3rd List

If I understand you correctly my function should look like this:

Function SetPreviewLabelSheetCommand () As Boolean
Dim blnEnable As Boolean

With Me
If Isnull(.InvoiceListPUPYear) Then
blnEnabled = False
ElseIf IsNull(.InvoiceListPUPPaymentsNumber) Then
blnEnabled = False
ElseIf IsNull(.InvoiceListPUPVillage) Then
blnEnabled = False
Else
blnEnabled = True
End If
..My PreviewLabelSheetCommand.Enabled = blnEnabled
End With


End Function
__________________________________________________________

And to complete it, call the function from the After Update event of each
List box. You just the the following directly in the After Update event text
box in the property sheet:

=SetPreviewLabelSheetCommand ()

Please inform me if this is correct as well as the grammer. I know how
important placing a period or coma or semi-colon or space can be if it is
omitted or inserted improperly.

One other thing. You refer to this as a Function. Is this the same as
building VBA code?

Thanks!

 

[Klatuu]

It appears to be correct, but if there is a syntax error, The VB editor will
let you know. There is one comment I would make. Your code, as written, is
very hard to read because you don't use any indentation. It makes no
difference in the execution of the code, but it is not obvious what the
purpose of the code is. You indent whereever there is a logical break.

For example:
If x = y then
msgbox "Good Show"
End If

Is not as obvious as
If x = y then
msgbox "Good Show"
End If

So here is how I would indent your code:


Function SetPreviewLabelSheetCommand () As Boolean
Dim blnEnable As Boolean

With Me
If Isnull(.InvoiceListPUPYear) Then
blnEnabled = False
ElseIf IsNull(.InvoiceListPUPPaymentsNumber) Then
blnEnabled = False
ElseIf IsNull(.InvoiceListPUPVillage) Then
blnEnabled = False
Else
blnEnabled = True
End If
.My PreviewLabelSheetCommand.Enabled = blnEnabled
End With

End Function

Note everything between the With and End With is indented one tab so we can
immediately see that all the properties and methods belong to the Me object.
Me being the reference to the form.

Then after each If and Else we indent a tab so it is easy to differentiate
between the conditions and the actions.

Sorry to rant but this is just some good practice you will see in
professional level coding.

As to your question. It is VBA code and it is a function. A function can
either be an Intrinsic function, that is one that is built into VBA, or a
User Defined Function. In Access, User Defined Functions are written in VBA.

In VBA, you have two types of procedures, Subs and Functions. The only
difference between a Sub and a Function is a Function will return a value but
a Sub will not.

 

[nytwodees]

Hi Dave:

Thanks again.

The function fails as follows:

Compile Error:
Method or Data member not found

"Function SetPreviewLabelSheetCommand () As Boolean" is in yellow

..My in ".My PreviewLabelSheetCommand.Enabled = blnEnabled" is highlighted.


Perhaps I'm confused in where to put the code you suggested. I opened the
pop-up Form (frmInvoicingStubsPopUP) in Design View. I chose Build Event.
Chose Code Builder. Deleted the already written

Private Sub Form_Load()

End Sub

and inserted the Function and saved it.

Regarding the formatting, I typed it as you suggested, but the editor
changes it for the message.

 

[Klatuu]

I missed this before, but this line:
..My PreviewLabelSheetCommand.Enabled = blnEnabled
Should be:
..PreviewLabelSheetCommand.Enabled = blnEnabled

The code does not go in any control event. it is a stand alone function and
should go in the form' code module. It is best to put user defined functions
at the top of the code module. You can do that by opening the form in design
mode and clicking the module button or by typing <alt>F11.

Go to the top of the module and paste the code there.

Then put the call to the function in the after update event of each list box.

 

[nytwodees]

Hi Dave:

Thanks again.

The Function now works perfectly!

Is there any reason why placing the code as I described on my last post the
incorrect method. If so, what are its drawbacks?

 

[Klatuu]

You can't embed a function or sub in another function or sub. It just
doesn't work.

 

[nytwodees]

Hi Dave:

I do not think I did what you suggested in your last post.

To reiterate:

I opened the pop-up Form (frmInvoicingStubsPopUP) in Design View. I chose
Build Event. I Chose Code Builder.

Deleted the already written

Private Sub Form_Load()

End Sub

and inserted the Function and saved it.


As far as I can tell, the function works perfectly exactly where I placed
it. I did not do what you suggested with <Alt> F11 etc. as you suggested. I
would have taken your suggestion if the function still did not work.

 

[Klatuu]

You do not choose build event. This is not an event, it is a function.
Please do it as I described.

 

[nytwodees]

Hi Dave:

I deleted what I did and followed your instructions to a T.

The Function fails"

Compile Error:
Invalid Use of Me keyword

With Me is highlighted.

I apologize for pestering you.

 

[Klatuu]

You are not pestering me at all. We are just working together to solve a
problem.
Copy you code directly from where you put it and post it back so I can have
a look, please.

I have to go out for a while, so I might not get back right away.

 

[nytwodees]

Thank you for your patience and time and effort!

When I followed your Instructions I noticed That in the MODULES section is
the Function code. I named it "PreviewLabelSheetModule". Well anyway, here
is the exact code as copied and pasted:

Function SetPreviewLabelSheetCommand() As Boolean
Dim blnEnable As Boolean

With .Me
If IsNull(.InvoiceListPUPYear) Then
blnEnabled = False
ElseIf IsNull(.InvoiceListPUPPaymentsNumber) Then
blnEnabled = False
ElseIf IsNull(.InvoiceListPUPVillage) Then
blnEnabled = False
Else
blnEnabled = True
End If
.PreviewLabelSheetCommand.Enabled = blnEnabled
End With

End Function

 

[Klatuu]

Me should not have a period in front of it. Also fix the indents:

Function SetPreviewLabelSheetCommand() As Boolean
Dim blnEnable As Boolean

With Me
If IsNull(.InvoiceListPUPYear) Then
blnEnabled = False
ElseIf IsNull(.InvoiceListPUPPaymentsNumber) Then
blnEnabled = Fal
ElseIf IsNull(.InvoiceListPUPVillage) Then
blnEnabled = False
Else
blnEnabled = True
End If
.PreviewLabelSheetCommand.Enabled = blnEnabled
End With

End Function

 

[nytwodees]

Hi Dave:

The error occurs without the period in front of Me. I experimented with the
..Me but that did not work. When I copied my code in the last post I failed
to put the Me back without the period. In any case it fails both ways.

My indents are as you directed me. Th problem seems to be with the
Discusions Group editor.

 

[Klatuu]

Is the code in the form's code module or is it in a standard module? By that
I mean did you click on modules to put the code in? Me is a short cut for
typting Forms!NameOfForm, but if the code is not in a form module, it will
cause that error.

 

[nytwodees]

I did not create the form's code in the "Standard Module." I believe you're
referring to the main section of the database with choices like
Tables,Queries, Forms, Reports, Macros, and Modules.

When I created the module in the form, the drop-down had choices like Module
and Class Module. I selected Module and pasted the code there and named the
module, and saved it. When I returned to the main section and looked in the
Module section I saw the module I created plus a blank module named Classs1.

 

[Klatuu]

You did put the code in a standard module. It has to be in the form's
module. You do not select modules. You open the form in design mode, click
on the VBA button so that it opens the form's code module.

 

[nytwodees]

Hi Dave:

You say, "click on the VBA button so that it opens the form's code module. "

In Design View, there is NO button that says VBA. There is a button that
says CODE when I hover over it. Is that the VBA button? If not, please
supply me with a more complete description to locate it. By the way, if I
click the Code button, I am brought to the coding section with "Option
Compare Database" already wriiten with the top left reading "General" and the
top right reading "Declarations." If this is the correct place shout I keep
"Option Compare Database" and paste the function below it. or delete it?