Would like to require meeting criteria before moving to next record
- Thread starter PamelaJoy
- Start date
Let me try again with db3 - sorry. I didn't think I had that many drop-downs.
I am getting 'somewhere' though. Now I'm on a record (which I had already entered with only 30% total as a test) and it 'beeps' me with my 'Total must equal 100%' message (YES!) but it won't let me go anywhere so I can fix it! I want to be able to go anywhere within this record EXCEPT to a new record. That's the only time I want 'him' to warn me.
I changed the control to If Me.TotalPercent <> 1 thinking I could still move around in the subform. But when I enter a brand new record and try to add ingredients, I get the message:
Run-time error '438': Object doesn't support this property or method
But I am encouraged! Being yelled at is better than being ignored. I think?
I am getting 'somewhere' though. Now I'm on a record (which I had already entered with only 30% total as a test) and it 'beeps' me with my 'Total must equal 100%' message (YES!) but it won't let me go anywhere so I can fix it! I want to be able to go anywhere within this record EXCEPT to a new record. That's the only time I want 'him' to warn me.
I changed the control to If Me.TotalPercent <> 1 thinking I could still move around in the subform. But when I enter a brand new record and try to add ingredients, I get the message:
Run-time error '438': Object doesn't support this property or method
But I am encouraged! Being yelled at is better than being ignored. I think?
boblarson
Smeghead
- Local time
- Today, 07:00
- Joined
- Jan 12, 2001
- Messages
- 32,059
I have been mulling this over and, based on what I'm seeing, we do have a bit of a problem. We can't use the main form's Before Update event because it fires only if something changed on the Main Form and not in the subform. By the time you go to the subform the main form's Before Update has already fired and completed. I'm not sure what to do to keep from navigating to a new record if the total is less than 100%.
Did I design the form/subform poorly or incorrectly, or just wrong for being able to plant this kind of safeguard?
Well, perhaps the 'flag' on the Main form's footer asking if '100% rule met?' will have to suffice. We do not have many data entry people - it was simply a safeguard. Thanks anyway.
Well, perhaps the 'flag' on the Main form's footer asking if '100% rule met?' will have to suffice. We do not have many data entry people - it was simply a safeguard. Thanks anyway.
boblarson
Smeghead
- Local time
- Today, 07:00
- Joined
- Jan 12, 2001
- Messages
- 32,059
Not that it is designed badly (although I would get rid of the Supplier Lookup and not have duplicates in the Raw Material table (a lookup to supplier and a manual input), but that the requirement about not moving to a new record if the total items are less than 100% is just difficult due to the timing issues as to when things are saved.
boblarson
Smeghead
- Local time
- Today, 07:00
- Joined
- Jan 12, 2001
- Messages
- 32,059
That isn't necessarily a bad idea, but you definitely would be stuck there until you finished
wiklendt
i recommend chocolate
- Local time
- Tomorrow, 00:00
- Joined
- Mar 10, 2008
- Messages
- 1,746
as an aside: i notice you have several forms for adding new things to dropdown lists. you could get rid fo two (category and containersize) by using a clever not-in-list code in conjunction with appropriate settings for the combobox:
this setup allows the user to type in what they want. if what they want is not already available, they are prompted to say that "this item is not currently in the list. do you wnat to add this item to the list now?"... which give the user the option to cancel and chose from those available, or click ok and add the item to the list - without using extra forms (you will need the form for the container, as i see you have many things to enter for each new container - this can also be handled by a not-in-list event, but instead of adding a new record to the container table, you use the code to open the form)
here's a link to an excellent tutorial on how to make a not-in-list even and all the things that go with it:
http://www.fontstuff.com/access/acctut20.htm
also, to make your forms look a little prettier, you can switch the properties of "record select" and "navigation buttons" to "no" for the Add forms, and 'no' to "record selectors" in your main form.
this setup allows the user to type in what they want. if what they want is not already available, they are prompted to say that "this item is not currently in the list. do you wnat to add this item to the list now?"... which give the user the option to cancel and chose from those available, or click ok and add the item to the list - without using extra forms (you will need the form for the container, as i see you have many things to enter for each new container - this can also be handled by a not-in-list event, but instead of adding a new record to the container table, you use the code to open the form)
here's a link to an excellent tutorial on how to make a not-in-list even and all the things that go with it:
http://www.fontstuff.com/access/acctut20.htm
also, to make your forms look a little prettier, you can switch the properties of "record select" and "navigation buttons" to "no" for the Add forms, and 'no' to "record selectors" in your main form.
Thank you Wiklendt - I will definitely want to 'pretty up' these forms/reports once I get things working the way I need/want them to. There will only be a limited number of categories and container sizes anyway - I think I got carried away with the concept of normalizing data and simplifying every single aspect of data entry. The more I learn, the more I realize what I still need to learn. But I guess that's always the way.
By the way, I tried your suggestion to put code in the BeforeUpdate event of the subform, but it yells at me if every ingredient/record (not the total) isn't 100%. I'm giving up, at least right now - I have much bigger fish to fry.
I started entering recipes in earnest this week, but now the 'finished product report' I created at the onset (when I only had five recipes) is still only showing those same five recipes and none of the new ones.
But this forum and the help here is a lifesaver. Thanks!
By the way, I tried your suggestion to put code in the BeforeUpdate event of the subform, but it yells at me if every ingredient/record (not the total) isn't 100%. I'm giving up, at least right now - I have much bigger fish to fry.
I started entering recipes in earnest this week, but now the 'finished product report' I created at the onset (when I only had five recipes) is still only showing those same five recipes and none of the new ones.
But this forum and the help here is a lifesaver. Thanks!
Fixed it - when I created the report initially, my finished product primary key was based on both finished product ID and batch ID. It worked fine, so I went on to tweak my form/subform for entering recipes. In its development, I took some great advice from Pat Hartman to build recipes on percentage of final rather than individual batches, and now multiply the final weight by desired batch amount after the fact. But when I changed the primary key to simply finished product ID, my report wouldn't play with the new guys! Change one thing, sometimes change a whole lot more than you meant to, or realize!
Back to the original post: I have a running sum invisible on the subform footer, adding the percentage of each ingredient as the recipe is being entered. That is referenced and visible on the main form (a reminder for data entry to not go on until and unless it is 100%). The problem with BeforeUpdate on the subform starts with adding the very first ingredient - unless it's 100% it won't let me continue and add another since the =sum is not 100% yet. Perhaps I need to put the BeforeUpdate on the main form's footer instead of on the form itself. That's what I'm going to play with next anyway.
Thank you again for your help and advice - it is all greatly appreciated!
Back to the original post: I have a running sum invisible on the subform footer, adding the percentage of each ingredient as the recipe is being entered. That is referenced and visible on the main form (a reminder for data entry to not go on until and unless it is 100%). The problem with BeforeUpdate on the subform starts with adding the very first ingredient - unless it's 100% it won't let me continue and add another since the =sum is not 100% yet. Perhaps I need to put the BeforeUpdate on the main form's footer instead of on the form itself. That's what I'm going to play with next anyway.
Thank you again for your help and advice - it is all greatly appreciated!
boblarson
Smeghead
- Local time
- Today, 07:00
- Joined
- Jan 12, 2001
- Messages
- 32,059
Sorry, but that won't do you any good for this.
Similar threads
