Calling function not returning as expected...

[sstiebinger]

Hi all, I'm still a Novice at VBA, but here's what I'm trying to do...

I have several controls that do certain things, and before they do them, they save the form. But I have some data validation I want to do before I save the form...


Rather than copy that data validation to each sub, I thought I'd throw it in a function to make it cleaner and easier to update later...

I'm not sure if I wrote the function wrong, or I'm just not calling it correctly, but it doesn't seem to work...

here's the function:

Public Function IncompleteData() As Integer
    If (Me.PartNum & "") = "" Then
        MsgBox "Please Enter a Part Number", vbInformation, "Incomplete Data"
        Me!PartNum.SetFocus
        IncompleteData = 1
        Exit Function
    ElseIf (Me.Manufacturer & "") = "" Then
        MsgBox "Please Enter a Manufacturer", vbInformation, "Incomplete Data"
        Me.Manufacturer.SetFocus
        IncompleteData = 2
        Exit Function
    End If
    IncompleteData = 0
End Function

And here's an example of my calling the function:

Private Sub cmdSave_Click()
    If IncompleteData() <> 0 Then Exit Sub
    If Me.Dirty Then Me.Dirty = False
    DoCmd.Close
End Sub

When I click cmdSave with PartNum blank, I get no messagebox and Partnum is set to focus.
When I click cmdSave with Partnum filled in, but Manufacturer blank, I get no message box, and the record is SAVED with a blank manufacturer field...

What am I doing wrong??

[ranman256]

try to avoid ELSEIF , use case instead...
but no where in your code do you actually set values to the missing fields. you just set focus to them.

Public Function IncompleteData() As Integer
    Select Case True
      Case (Me.PartNum & "") = ""
        MsgBox "Please Enter a Part Number", vbInformation, "Incomplete Data"
        Me!PartNum.SetFocus
         Me!PartNum  = "SOMETHINEg"
        IncompleteData = 1
        
    Case (Me.Manufacturer & "") = ""
        MsgBox "Please Enter a Manufacturer", vbInformation, "Incomplete Data"
        Me.Manufacturer.SetFocus
        IncompleteData = 2
    
    Case Else
        IncompleteData = 0
    End Select
End Function

[sstiebinger]

Right... I want the message box to pop up to indicate to the user that it's a required field, then set the focus to that field so they can then immediately enter data... I'm not programmatically entering data into those fields, I'm requesting user input.

I'm happy to change this to CASE - is there a programmatic reason for this (efficiency, etc.) or just a style, readability reason?

Edit note - Spelling Error.

[rpeare]

Have you tried checking for null instead of a blank string?

Public Function IncompleteData() As Integer
'    If (Me.PartNum & "") = "" Then
    If isnull(PartNum) Then
        MsgBox "Please Enter a Part Number", vbInformation, "Incomplete Data"
        Me!PartNum.SetFocus
        IncompleteData = 1
        Exit Function
'    ElseIf (Me.Manufacturer & "") = "" Then
    ElseIf isnull(Manufacturer) Then
        MsgBox "Please Enter a Manufacturer", vbInformation, "Incomplete Data"
        Me.Manufacturer.SetFocus
        IncompleteData = 2
        Exit Function
    End If
    IncompleteData = 0
End Function

[sstiebinger]

Changed to:

Public Function IncompleteData() As Integer
    Select Case True
        Case Len(Me.PartNum & "") = 0
            MsgBox "Please Enter a Part Number", vbInformation, "Incomplete Data"
            Me.PartNum.SetFocus
            IncompleteData = 1
        Case Len(Me.Manufacturer & "") = 0
            MsgBox "Please Enter a Manufacturer", vbInformation, "Incomplete Data"
            Me.Manufacturer.SetFocus
            IncompleteData = 2
        Case Else
            IncompleteData = 0
    End Select
End Function

Same issue as original.

@rpeare

Concatenating "" to a blank string or a NULL value would result in a blank string ("") so that if works regardless of whether the field is null or contains a blank string.

[rpeare]

I disagree with your use of the "" to create a blank string, if it's a text field it should be null if it's a number field it should be null, needlessly converting a string to another string or a number to a string can lead to all kinds of issues with no real need. If you are determined to keep the conversion that's up to you but I think the problem is in your function call (and returning value) maybe not the function.

If you're going to try and capture an error in the function use your debug.print statement to see if it's being generated at all and generate message boxes on the 'main' form.

Private Sub cmdSave_Click()
dim ReturnValue
    Returnvalue = incompletedata()    
    'do the debug.print to make sure you're getting the right result
    Debug.print returnvalue    
    If Returnvalue = 1 Then 
         msgbox "Missing Part No", vbinformation, "Incomplete Data"
         Exit Sub
    If Returnvalue = 2 then
         msgbox "Missing Manufacturer", vbinformation, "Incomplete data"
         Exit Sub
    Else
    If Me.Dirty Then Me.Dirty = False
    DoCmd.Close
    Endif
End Sub

[sstiebinger]

rpeare, I'll try the debugging... I'm still new to debugging, so your code helps.

Regarding the concatenation to "", it's kind of a catch all that works regardless of whether the field is null, a string, a number, etc... since all I'm doing with the created string is running a logical test against it. I used this method because I find it cleaner than multiple IFs or multiple ORs.

However I do understand your concern about converting field types and I will do some thinking on this later.

[rpeare]

Alternately, you can use a device I use

if you use the TAG property of your fields and put in something like REQ for required you can generate an error message with all the relevant fields

So let's say your field names are

PartNum and Manufacturer
for each of these fields you have an attached label
name those PartNum_Caption and Manufacturer_Caption
i.e.

Function CheckRequired()
dim ctl as control
dim smsg as string

for each ctl in me.controls
    if instr(ctl.tag, "REQ") >0 then
        smsg = smsg & me.controls(ctl.name & "_Caption).caption & vbcrlf
    endif
next ctl
checkrequired = smsg

then from your main form do

dim ReturnValue as string

returnvalue = checkrequired()
if len(returnvalue) > 0 then
    msgbox "WARNING:" & vbcrlf & vbcrlf & "the following fields are required" & vbcrlf & vbcrlf & returnvalue, vbinformation, "Required Fields Missing"
else
    blah blah
endif

I do things this way so that if the 'required' set of data changes over time it's much easier to handle than recoding the whole form.

[rpeare]

using a blank string, I would venture, is from working in excel. There's really no such thing as a blank string in MS access

A text data type with no data will still register as null
a text data type with a bunch of spaces will register as non null, which, in your code would be 'valid' (assuming you are not using a combo box, or if you are using a combo box allowing items not in the list) so if you're going to check the length you should use it in conjunction with the TRIM function to avoid false positives

i.e. Len(Trim([FieldName]))

and avoid the concantenation that converts numbers to text fields etc.

[sstiebinger]

So, assuming I would accept a blank space as valid, I could just do Len(me.field) = 0 and not concatenate.... that makes sense and is even cleaner yet...
And if I would NOT accept a blank space as valid, then I add trim()... I like it.

I'll get back to the original issue and run some debugs so I can get this back on track... not a useless tangent though.

Regarding your other suggestion using the tag fields... it's a good suggestion, but at this point a larger project than I have time for... I'll file it for future reference though!

[sstiebinger]

So as a quick aside... Len(me.field) returns an error about invalid use of NULL if that field is null... so for now, back to concatenation to a blank string... I need to concentrate on one problem at a time.

I changed my calling sub to:

Private Sub cmdSave_Click()
    If IncompleteData() <> 0 Then
        Debug.Print IncompleteData()
        Exit Sub
    End If
    If Me.Dirty Then Me.Dirty = False
    DoCmd.Close
End Sub

And if I leave the part number blank, it prints 1 to the debug window and actually displays my popup message... but if I fill in partnum and leave manufacturer blank then nothing prints to the debug window and no message... but partnum.setfocus occurs.... I'm so confused!

[rpeare]

the .setfocus likely is not occurring put in debug statements before and after it like

debug.print "before setfocus command"
docmd.setfocus blahblah
debug.print "after setfocus command"

to see if it's actually firing off. If it's not printing a 2 it's not getting there and you are ending up on the appropriate control by luck (by virtue of tab order)

If you want to provide a sample of your database with just enough in it to simulate the problem and upload it.

use len(trim(fieldname & "")) if you are insisting on concantenating

isnull(fieldname) is really so much easier

[sstiebinger]

Right... as you said many posts ago, there's no such thing as an empty string in Access... so you're correct that i can just use isnull since I don't have to catch field = "".

Anyways, I changed my function to:

Public Function IncompleteData() As Integer
    Select Case True
        Case IsNull(Me.PartNum)
            Debug.Print "Before partnum msgbox"
            MsgBox "Please Enter a Part Number", vbInformation, "Incomplete Data"
            Debug.Print "After Partnum MsgBox and Before Partnum Setfocus"
            Me.PartNum.SetFocus
            Debug.Print "After partnum Setfocus"
            IncompleteData = 1
        Case IsNull(Me.Manufacturer)
            Debug.Print "Before Manuf MsgBox"
            MsgBox "Please Enter a Manufacturer", vbInformation, "Incomplete Data"
            Debug.Print "After Manuf Msgbox and before .setfocus"
            Me.Manufacturer.SetFocus
            Debug.Print "After Manuf Setfocus"
            IncompleteData = 2
        Case Else
            IncompleteData = 0
    End Select
End Function

Then I opened the form and clicked save, I got this in the debug window:

Before partnum msgbox
After Partnum MsgBox and Before Partnum Setfocus
After partnum Setfocus
and Incomplete data returned 1

and then I put a "1" in partnum and clicked save and I got:


Before Manuf MsgBox
After Manuf Msgbox and before .setfocus
After Manuf Setfocus
and incomplete data returned 2

In the second case though, the form SAVED with 1 in partnum and a null manufacturer.

so all the putzing with the validations seems to have worked.... (apparently the concat to string WAS an issue... just goes to show we novices shouldn't argue :-/ )
Except for some reason, it's not properly exiting the sub before saving... to save a scroll up, my save button code is:

Private Sub cmdSave_Click()
    If IncompleteData() <> 0 Then Exit Sub
    If Me.Dirty Then Me.Dirty = False
    DoCmd.Close
End Sub

Any idea why this button would save the record when incompletedata returns a non-zero?

[sstiebinger]

I should note... DoCmd.close is not running either when there's a blank manuf... (The form stays open) so it appears that the issue is that the record is saving outside of the code when I put in Partnum and not manuf... so how do I prevent that?

[rpeare]

in your cmdSave code put in some debug statements as well

dim ReturnValue as long

Returnvalue = incompletedata()
if Returnvalue <> 0 then 
    debug.print "I GOT HERE, INCOMPLETE RECORD"
else
    debug.print "COMPLETE RECORD, SAVE AND CLOSE
endif

start with this and see if you're getting the appropriate message

if you do then put in your dirty/close statement in the second part of the if loop and see what happens.