yoducati
asked on
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.
if you can provide the code you are currently using, that would be helpful.
ASKER
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.
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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:
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
ASKER
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!
Thanks for the help everyone!
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.
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.
ASKER
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!