How to search for blank fields on a subform

I have a form bound to a table that displays a subform whose contents come from a different table.  The 2 tables are related one-to-many.  The subform displays a list of items in a shipment and allows the user to enter a tariff code, weight, and country of origin.  The user can also add items to the shipment (add records to the table).  The main form collects information about the shipment, such as ship date and FedEx number.  

When the user is finished editing the shipment details in the subform and entering the shipment information on the main form, he clicks on a Done button which prints shipping documents and exports a text file.

Here's what I need:

When the user clicks on the Done button, search through the data in the subform and make sure that all fields are filled out (not blank) and all fields have valid data ( e.g. weights and unit prices are not zero).  If any field fails this check, display a message and place the cursor in the field that has the problem.

Thx!
monkeybiz12345Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonLarsenCommented:
Dim c As Control

For Each c In Me.Controls
    MsgBox c.Name
Next


Will msgbox the name of each controls name.

What you might want to do is put a case statment in there.

replace msgbox c.name with

    Select Case Left(c.Name, 5)
        Case Is = "Label"
            'do nothing, jsut a label
        Case Is = "comma"
            'command button
        Case Else
            If IsNull(c.Value) Then
                MsgBox "This control must have a value: " & c.Name
                Me(c.Name).SetFocus
            End If
End Select


Now you are going to have problems here. There will be some other controls that don't work and so on you can add extra case statements for them.
0
monkeybiz12345Author Commented:
Ok.  Possibly dumb question to follow.... using the above code am I searching through the data in the table or the controls on the form?

The subform displays a list of records in datasheet view.  The user can edit any record in the list as well as add new ones.  I really need to search through the values in the fields I guess....
0
SimonLarsenCommented:
the controls on the form

For Each c In Me.Controls

the me means the active form the code is running from.

depending on the complexity of the form it may prove to be easier to jsut do a bunch of if statements:

if isnull(me.Field1) then me.field1.setfocus
if isnull(me.Field2) then me.field2.setfocus

and so on. painful but probably an easier solution on a complex form if there is no strong naming convention.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

shanesuebsahakarnCommented:
Hmm - could you not simply set a validation rule onto the field(s) at table level?
0
SimonLarsenCommented:
that wouldn't necessarily set the focus to the problematic control though...
0
shanesuebsahakarnCommented:
No it wouldn't - you would have to trap the form error and set the focus accordingly in the event procedure.
0
SimonLarsenCommented:
Actually that's not such a bad idea, it avoids the labels and buttons and stuff.....

I guess you'd have to have all fields in the table displayed though.

would you get the fieldname from that error?
0
shanesuebsahakarnCommented:
No, but you could then execute a loop through the controls at the point that the error is raised. This would be similar to putting a record-by-record verification in the form's BeforeUpdate event, which is also a viable option. Either way, you'd be validating on each record as it is entered rather than the whole dataset before it is committed as "real" data.
0
monkeybiz12345Author Commented:
Thanks, shanesuebsahakan, for chiming in.   Actually, I wish I could do as you suggest.  However, the table in question gets filled by an ODBC call to an inventory control system.  The data in that system is incomplete and the user must complete it at the time of shipment.

Also, the user must be able to fill in the fields in whatever order they like without validation on change of record.  If we had 3 records that the user had to supply the tariff code, weight, and country for, and they chose to fill in all the country fields first, I wouldn't want validation messages to happen between each entry.
0
shanesuebsahakarnCommented:
In that case, the simplest thing to do would be to loop through the form's recordset. Something like this:

Dim rst As DAO.Recordset
Dim BadRecordID As Long
Dim ControlName As String

Set rst=Forms!MyForm!MySubformControl.Form.Recordset
Do While Not rst.EOF
   If IsNull(rst!MyFirstField) Then
      BadRecordID=rst!ID
      ControlName="txtMyTextbox"
      Exit Do
   End If
   If IsNull(rst!MySecondField) Then
      BadRecordID=rst!ID
      ControlName="txtMyTextbox2"
      Exit Do
   End If
   ...etc...
Loop
If BadRecordID<>0 Then
   Forms!MyForm!MySubformControl.Form.Recordset.Find "[RecordID]=" & BadRecordID
   Forms!MyForm!MySubformControl.Form(ControlName).SetFocus
End If
Set rst=Nothing
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
monkeybiz12345Author Commented:
I'm kinda liking the "if isnull(me.Field1) then me.field1.setfocus" idea.....  

Can I put this in some sort of loop so the validation happens when the user indicates he's finished? Check all the fields in the first record, if something's blank, set focus there and let the user fix it; if all are filled in repeat on the next record.....

 .... and maybe trigger it with a DCount so it runs only if it finds a blank field somewhere?  Earlier in the development process I had DCount checking for null values or zero length strings in the fields in question and displaying a message if any were found.  But that still leaves the user without indication of which fields need filling in.  If there are 300 rows in the list, it's a problem for them to scroll through and try to spot the one thing they missed.

Actually, I'd be happy with pinpointing the record that contains the blank field.  And maybe trying to change the background color or something to make it obvious.
0
shanesuebsahakarnCommented:
The code I provided will locate the problem record and set the focus appropriately. You could change the background colour using conditional formatting - I use a similar method, although it's rather tedious to apply.
0
monkeybiz12345Author Commented:
Thanks, shanesuebsahakarn!  Looks like we cross-posted.  This looks *very* promising.  I'm gonna try it right now.
0
monkeybiz12345Author Commented:
I've modified the above code so it references my form names, but I'm getting an error message on the line below

 BadRecordID = rst!ID

'Runtime error '3265'. 'Item not found in this collection'

Am I missing something in the reference library perhaps?   Or perhaps I didn't define it correctly?
Thx!
0
monkeybiz12345Author Commented:
... I did find a property for the Forms collection called CurrentRecord.  Is this what I need?  I can't figure out how to use it.  I'm still trying to wrap my head around the Recordset and Collections concerts.  Can you tell?  :)
0
shanesuebsahakarnCommented:
Just replace ID with the name of your primary key field (I assumed that it is a number field in this case). You'll also need to replace RecordID in the FindFirst line with the name of your ID field as well.
0
monkeybiz12345Author Commented:
Thanks for the clue.  Actually what I needed to do was add the [RecordID] field to my field list for the subform.  Duh!

'Kay, so now it correctly identifies my blank field and sets BadRecordID <>0

It doesn't like the .Find method. Using .FindFirst works though.  And, somehow I've created a loop that takes a REALLY long time to execute.  I stopped it after 10 minutes and there are only 68 records in the subform.  This is a pretty small subset - more normal for the user will be 200 or 300 records in the subform.  Is it validating the fields in question for every record in the file, not just the subset of records displayed in the subform?

if so,is there a way to more narrowly define the recordset?  
0
shanesuebsahakarnCommented:
Err - add this line before the Loop line:

rst.MoveNext

I forgot to tell it to move onto the next record....!!
0
monkeybiz12345Author Commented:
Cool!  We're *so* almost there and you've helped *so* much!!!  Had to use .SetFocus twice - once to set focus on the subform and again to set focus on the control.  See below:

Forms!MyForm!MySubformControl.Form.SetFocus
Forms!MyForm!MySubformControl.Form.Form(ControlName).SetFocus

I've discovered though that I don't know where in the subform the user is when we start the loop.  It works great if the blank field is somewhere below where they left the record pointer when they left the subform.  But doesn't catch it if they missed one in an earlier record.  This is hard to explain so here's an example.

Record1
Record2 -- blankfield
Record3
Record4

If the user fills in the blanks in Record1, Record3, and Record4 (in that order) and misses the field in Record2, then presses the Done button which triggers the validation code.  The code seems to start looking at Record4 and doesn't loop back around to catch the blank field in Record2.

How can I tell it to always start at the first record in the recordset (RecordID will never be 1 so I can't use that and the number of records displayed changes every time.)?
0
shanesuebsahakarnCommented:
Try this to move the record to the first one:

rst.MoveFirst
0
monkeybiz12345Author Commented:
Works beautifully!  Thanks! ...Extra points for hanging in there with me...:)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.