Solved

checking subform for empty or null values

Posted on 2016-08-23
9
37 Views
Last Modified: 2016-08-24
Access 2010
subform on access main form:  MySubform
command button

What I need:
From a command button,

First:
I have a subform that MUST contain records first.

Second:
if it contains records there may be up to 5 records on the subform.
I have 2 fields on that subform that cannot be  null or "" .
APPROVED_NO   AND  APPROVED_YES

Forms![MainForm]![BookOrderdetails subform].Form![APPROVED_GP].SetFocus
If Me!BookOrderdetails subform.APPROVED_GP Is Null Then
 
MsgBox "Please insert price of the order"
Else
 
    DoCmd.RunCommand acCmdSaveRecord
 
    End If
End Sub

Open in new window



Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 5
  • 4
9 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 41767278
I would do this with a DCount().
You have several conditions but they are not complete.  What do you want to do if the subform contains no records?
If DCount("*", "tblForSubform", "ForeignKey = " = & Me.PrimaryKey) = 0 Then
    Msgbox "There are no subform records.",vbokOnly
    Cancel = True 
    Exit Sub
End If
If DCount("*", "tblForSubform", "ForeignKey = " & Me.PrimaryKey & " AND APPROVED_NO & "" <> "" AND APPROVED_YES & "" <> "") Then
    Msgbox "Approved fields must both be set.", vbOKOnly
     Cancel = True 
    Exit Sub
End If

Open in new window


This code would go into the main form's BeforeUpdate event and prevent the main form from saving.  However, it would be best to put validation code into the subform's BeforeUpdate event to prevent the subform record from being added without the proper values in fields.  Don't forget that Access saves records whenever it thinks it should so you have to do this type of validation in the form's BeforeUpdate event.  You cannot do it in your "save" button because that is not the only event that will cause a record to be saved.

I am confused by why you have both Yes AND No fields.  All you need is a single field with two states.  Approved, Not Approved.  That will simplify your logic in many places.  What happens if both are true or both are false?  That doesn't make sense.  Using a single field eliminates that potential ambiguity.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41767317
the field name are made up..sorry. I will something that make more sense.

I need it on a command button. not an event.

Here is the code, I cant get to work ?
' MUST CHECK THE SUBFORM FOR MISSING VALUES IN APPROVED_PRICE AND APPROVED_GP

Me.redbook_pricing_escalation_detail_subform.SetFocus

If Me![redbook_pricing_escalation_detail_subform].Form!APPROVED_PRICE = "" 
MSGBOX "Then Something is missing"
End If

Open in new window

0
 
LVL 3

Author Comment

by:fordraiders
ID: 41767337
Pat, I guess I would need to loop through all the records ?
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41767341
Pat
"What do you want to do if the subform contains no records?"

Exit the Sub advise. nothing can be saved because no records exist.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 34

Expert Comment

by:PatHartman
ID: 41767501
1. If you want to use the code to prevent the main form record from being saved, the code MUST be in the main form's BeforeUpdate event.  You don't have control over when Access saves records in a bound form.  You can force Access to save a record by doing DoCmd.RunCommand acCmdSaveRecord but you cannot stop Access from initiating a save if it determines that one is necessary.  Therefore, you need to understand how events work and the order in which they fire.  Think of the form's BeforeUpdate event as "The Buck Stops Here".  It is the LAST event to fire PRIOR to a record being saved REGARDLESS of what initiated the save.  Nothing gets past it.  It is the ONLY event you have to use to trap validation errors.
2. When you run a query, there is no loop involved.  The query isn't going to tell you which records are invalid.  It is simply counting the ones that are so if the count > 0 then at least one record is invalid.  I looked at the code I posted and noticed an error which might be causing the confusion.  So, here's the correction.
If DCount("*", "tblForSubform", "ForeignKey = " & Me.PrimaryKey & " AND APPROVED_NO & "" <> "" AND APPROVED_YES & "" <> "")  > 0 Then
    Msgbox "Approved fields must both be set.", vbOKOnly
     Cancel = True 
    Exit Sub
End If

Open in new window

I added the missing "> 0"
Pat
"What do you want to do if the subform contains no records?"

Exit the Sub advise. nothing can be saved because no records exist.
You can warn them all you want but the bad subform records are already saved.  At this point, all you can do is to prevent the main form record from being saved and the only way you're going to do that effectively is to use the form's BeforeUpdate event as I said.

The logic behind your premise is flawed.
If some error then
    display message
Else
   force save
End If

Open in new window

Looks nice but it doesn't work because it does not prevent the record from being saved.  All it does is to not run your save code.  It doesn't stop Access from saving the record when you close the form or move to a new record or click into the subform, etc.  The "Cancel = True" I included in my initial code snippet WILL PREVENT the dirty record from being saved since it cancels the save.  I didn't have it undo the update.  You might want to do that because otherwise unless your user understands how to use the esc key to back out of an update, he'll end up in what seems to him like a loop.  He can't close the form because the record is dirty so depending on what you actually want to accomplish and how sophisticated your users are, you might want to include a "Me.Undo" along with the cancel.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41767566
Pat, I ended up doing this.

recordcount subform.
' get the recordcount
     strInt = Trim(Forms!Main!redbook_pricing_escalation_detail_subform.Form.RecordsetClone.RecordCount)
     strInt = strInt

       If strInt = 0 Then
          MsgBox "There Are No Sku Records To Check!", vbCritical
          Exit Sub
       End If


looping through subform
Set rs = Me.redbook_pricing_escalation_detail_subform.Form.RecordsetClone

 With rs
    .MoveFirst
     Do While Not .EOF
       '''Anything, space filled, null, ZLS
       If Trim(![APPROVED_PRICE] & "") = "" Then
          MsgBox "You Are Required To Enter an Approved Price", vbCritical, "Approved Price Check"
        
         Exit Sub
       End If
      
       If Trim(![APPROVED_GP] & "") = "" Then
          MsgBox "You Are Required To Enter an Approved GP", vbCritical, "Approved Price Check"
         
         Exit Sub
       End If
     .MoveNext
    Loop
 End With

Open in new window

0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41767593
This is not a good idea.

Put the validation code in the BeforeUpdate event of the subform record to prevent the bad records from being saved.  All this code is doing is pummeling the user with error messages.  The bad data is already saved and the bad data is in the database being used by other queries.  You have shut the barn door AFTER the horses have escaped.

You probably didn't have any success with these edits in the subform because you put them into the wrong event.  When you are doing validation the involves more than one field such as comparing two dates and making sure that one is earlier than the other or ensuring that some field has a value, then you MUST use the form's BeforeUpdate event.

I once took over an application that had 10,000 lines of code and 95% of that code was completely irrelevant.  The developer did not understand how form events worked and so  he never used the form's BeforeUpdate event (or even the control's BeforeUpdate event which can be used for some types of validation).  He put it in the lost focus and the got focus and the mouse Move and the Mouse Down and the Mous up and the Enter and the Exit --- virtually EVERY SINGLE event EXCEPT the BeforeUpdate event,  He got so frustrated, he started putting validation for field1 in the events of field2 and then also in field3, etc until he had some cascading code that  just grew bigger and bigger as the number of controls in a form grew.  That's how he got to 10,000 lines.  And the sad part was nothing he had done was effective.  The tables were filled with bad data because none of his validation code actually did anything.  It gave the users lots of warnings and error messages but it never prevented the bad data from being saved.

I rarely recommend code solutions when simple queries will suffice.  All you said you needed to know was if any records had invalid data.  That is what the queries I posted do.  The code may make you feel good because it seems to inform the user correctly but it is ineffective if you wanted it to prevent the bad data from being saved.
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 41768980
Thanks, and appreciate the comments.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41769019
You're welcome.  We have a fiduciary responsibility to our customers to ensure that their data is as safe and correct as we can make it.  Understanding how form events work and how to prevent Access from saving bad data is a critical part of this process.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How count number of unique values at end of report 5 30
Dlookup issue 4 16
Join vs where 2 11
Stored Procedure 2 10
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now