How do I check for individual values in detail records of Access Subform?

I have a relatively straightforward order form set up as a form/subform with a main order section and the corresponding details on a subform which is set up as a continuous form.  Before the overall order is saved I want to be able to check the values of certain fields in each detail such as [Quantity] to make sure it has an appropriate value.  I have it close to working but right now my message pops up even when the fields are filled out correctly so I think its counting the blank line for adding a "new record" in the details section.  I need it to check the detail records for the values I want to specify and then allow the system to save the order if that criteria is met.
yoducatiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Dale FyeOwner, Developing Solutions LLCCommented:
if you can provide the code you are currently using, that would be helpful.
yoducatiAuthor Commented:
I don't have anything final, Im just testing right now by trial and error.  I know somewhere there is a method of cycling through a recordset and checking each value based on criteria I specify I just can't remember how to do it.  That's what I am looking for.  Right now I just have a simple statement using "If IsNull(quantity) then...."

Like I said it works sort of, but I need to be able to cycle through each record in the recordset and check other values as I specify.  I know my method isn't the right one which is what prompted me to post the question.

What I am thinking of had something to do with .EOF and when the user pressed save the code would check each detail record individually until it had reached the end of the recordset for whatever criteria was required and then only allow the save if the criteria had been met.
Jeffrey CoachmanMIS LiasonCommented:
What you are explaining there all seems like over-kill

Please take a moment to explain your exact user scenario.
Or provide a simple sample db with an explanation for its use.

In the most basic sense, you might want to set a default value for the quantity field.
Set this value to one (1).

Thus all new records will have at least a 1 for the quantity,
Hence no real need to do any IsNull() expressions.

The count may also be off because the user is trying to get a count while in the middle of editing a new record.

But theses are all guesses here.

You have to explain to us exactly what you have, ...then explain what you are trying to accomplish.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

PatHartmanCommented:
I also think you are doing this the hard way.  Normally, edits for line items would go in the BeforeUpdate event of the subform which would prevent an item from being saved if something important were missing.  The only time I would run code in the main form to check the records of the subform is if I had some requirement that meant I had to look at the rows as a set.  For example, I might have an accounting application where I enter the amount of a check and then I would have a subform where I allocated that amount to different accounts.  Since I have to validate that the entire amount is allocated but not over allocated, I can't do that until all details are entered so at the end of the detail process but before marking the check as entered, I would run a query (not loop through a recordset) to sum the amounts in the detail records.  If it equaled the correct amount, I would mark the check as allocated, otherwise, I would mark it as in suspense.

Keep in mind that when working with a main form and subform, the main form record is saved when you enter the subform so the order is already saved.  If you want to do further validation, you need a flag that defaults to unvalidated and then after you do the validation, you set the flag to validated.  Every other function in the app that looks at orders MUST take into account the setting of this flag and ignore or include unvalidated items as required.

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
yoducatiAuthor Commented:
Pat it sounds like your situation is similar to what I am trying to do.  I don't want a default value for quantity.  In cases where something ordered should only be a quantity of 1 I will check for that.  If the item ordered can have more than one I want the user to have to specify it as opposed to them saving the default value accidentally.  

As far as beforeUpdate goes, it may be as simple as that but I thought when using a continuous form there was some sort of method that had to be used to check each record.  I know if you try to apply an attribute to a field in a continuous form like "enabled" or "disabled" it applies it to every instance instead of just the current record.  I was thinking that while I could use beforeUpdate in the subform to check that the PK in the main form wasn't blank, it wouldn't work to check values in each record of a continuous form.

I will give it a shot now and let you know what happens.
Dale FyeOwner, Developing Solutions LLCCommented:
No points please.

I agree with Pat, I'd use the subforms Form_BeforeUpdate() event to check that all of the required fields are filled in, then I would cancel the update if they are not.  Something like:
Private Sub Form_BeforeUpdate(Cancel as integer)

    dim strMsg as string
    
    if Trim(me.txt_Quantity & "") = "" Then
        strmsg = "Enter a quantity"
        me.txt_Quantity.setfocus
    elseif Trim(me.txt_Price & "") = "" then
        strmsg = "Enter a price"
        me.txt_Price.setfocus
    elseif Val(me.txt_Price) <= 0 then
        strmsg "Price must be > 0"
        me.txt_Price.setfocus
     endif

     if strMsg <> "" then 
         msgbox strmsg, vbokonly, "Incomplete entry"
         Cancel = true
     End If

End Sub

Open in new window

yoducatiAuthor Commented:
Ok. As usual this site saved me a lot of frustration.  Before update is working.  I just need to figure out my criteria and write it but I can do that.  Having it in the before update instead of the save button works much better.  Eventually this event will sink in I hope!  

Thanks for the help everyone!
PatHartmanCommented:
When code is running in a form's event procedures, the only data reference is to the current record.  You can loop through other records but it is more involved and I would never do it anyway.  Although I'm sure there are reasons, I can't think of one at the moment that would make me use the form's recordset object for recordset processing.  I have used it for repositioning when I had to requery a form and I want to reposition to the record I was on previously but in all other cases (I'm sure someone will contrive an exception), I would use a recordset I opened with DAO.  

Even a single record form has the same situation.  As you navigate through the recordset, you can access a different record each time you press the forward or backward button.  The confusion with the continuous form is that you can see more than one record at once.  But just because you can see it, doesn't mean you can address it.  Me.somecontrolname refers to that control on the CURRENT record only.  The properties of a form are similar.  That is why it is not easy to control a continuous form visually and show different things for different rows.  Each form has a single set of properties.  So if you set the background color of Me.txtAmt to vbRed, it would change for all rows.  Then when you moved the record pointer to the next row, code could run that would set the background color of Me.txtAmt back to the color of the form's background and voila!  all the red goes away.  So, it is actually the same concept of "one set of properties" that includes "one record" and it is through navigation that you move to different records.

I'm not sure if I made that clearer or more confusing.
yoducatiAuthor Commented:
Thanks for the clarification.  I think what makes it more clear to me is the stipulation that its the current record only.  We got sent home early Friday for the long weekend so Im just getting back to this.  I'm sure Ill have more questions but I think the beforeupdate will help me a lot for things I want to check before saving.  Thanks again for the help!
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.