Save Record & Add 2 more Records with 1 Field Changed.

[dmon000]

I have a form that saves a diet order for a patient in an "Order Detail" table. Presently it saves one record for one particular meal (breakfast, lunch or dinner).

Management now wants 3 records (breakfast, lunch, dinner) saved for each order from the same form.

How do I save two more records with all the data the same except for the one field "meal", which will need to be changed to "2" and "3" for lunch and dinner, respectively?

Currently the information in the form is save to the "Order Detail" table with the follwing: "DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70"

Thanks!!!

[maxamis4]

I sort of understand what your saying but i need more details. Per say why would you want to reference two other meals in the day if you are focusing on meal ticket when you take the order, or are you taking the order for the day and your management wants this so you don't have to enter so much data. Let me know.

thanks

[dmon000]

I sort of understand what your saying but i need more details. Per say why would you want to reference two other meals in the day if you are focusing on meal ticket when you take the order, or are you taking the order for the day and your management wants this so you don't have to enter so much data. Let me know.

thanks

Does this matter as to how to do what I need to do?

[missinglinq]

Reading your post, it sounds as if you want 3 records per patient per day. If this is so, I have to tell that this doesn’t sound like good db design, but it's really hard to tell, since you apparently have a problem giving us clarifying information when it's requested. Be that as it may, this code will accomplish your task as stated.

This code depends on the new record(s) being saved by this code only! You’ll have to set up a trap in the sub Form_BeforeUpdate to prevent the user from moving to another record or closing the form without either saving the 3 new records (I’d put the “save” code in a button, myself) or dumping the new records. Something like

Private Sub Form_BeforeUpdate(Cancel As Integer)
   response = MsgBox("Do You Wish To Save This Record?", vbQuestion + vbYesNo)
 End If
 

and then setting up actions to handle their response, telling them to click on the save button or else dump the new records.

Note that for this code to function properly, you should set the the MealCode textbox to Locked = Yes. The code itself fills in the MealCodes. This code assumes that the MealCode field is Text. If it’s Numeric, replace the Me.MealCode = "1" with Me.MealCode = 1.

replace your code

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 

with this

Me.MealCode = "1"
 DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
 DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
 DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
 
 Me.MealCode = "2"
 DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
 DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
 DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
 
 Me.MealCode = "3"
 DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
 DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
 DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
 
 Me.Undo
 

Line # 16 removes the new records' data from the control, since it's already been saved at this point, and prepares the form for entering the next patient's info.

You'll have to do some tweaking with the messageboxes for the Form_BeforeUpdate event, but the append code has been tested and works.

Linq