Loop through a continuous form in Access

hi Susan,

records are saved automatically when you leave them -- not saving is the tricky part.

Are the textbox and the combobox bound to data? How is the code being triggered?

Would you explain a little bit about your data and what your form does? thanks

Hi Crystal,

I don't think the not saving is an issue at this point. Currently the form only saves and closes if the IF statement is false. If the IF statement is true then a message box pops up. This is how I want it. What is the issue is getting the VBA to loop through each record on the continuous form.

Thank you,

Susan

why are you wanting to do that? You mention a continuous form so that implies several records may be created or edited. I can give you code ... just want to understand the business process, thanks

Sorry Crystal, Just saw the rest of your post. Both the text box and combo boxes are bound. The code is run once the user clicks on a command button. The form is populated from a query, so all results meeting the criteria are populated. Most fields are non-editable with the exception of the text box and the combo box. The idea is if a user inputs anything in the text box and the combo box is null, then a message box will pop up, otherwise the form will save and close.

I hope this helps clarify.

Susan

thanks, Susan

if the textbox and combobox are bound, it is a good idea to change the Name property to match the control source.  Some like to prefix the name but I find it easiest not to, unless they are unbound.

It seems to me that perhaps information needs to be validated before a record is saved too?* Otherwise a user could change a bunch of records and the code would never get triggered until a command button was clicked.

* use the form BeforeUpdate event to validate information before a record is saved

Hi Crystal,

I think it is ok if the VBA is tied to the command button because it is just looking to make sure there are no combo boxes that are null when an associated text box is not null. Also I normally do not change the control name and it has never been an issue for me.

Thank you,

Susan

Hi Crystal,

Your code worked for exactly how you want it. However I need it to be tied to the command button. Is there any way to do this? Let me try to explain a little better. I have a form (screenshot attached) where if the user enters anything in the "Is rationale aligned with the procedures?" field then they must enter something in the "Finding Description" field. Also if they enter anything in the "Finding Description Field, they must enter something in the "Is rationale aligned with the procedures" field. If they have anything in one field and not the other, I want a pop up message. I believe its best to tie this to the "Save" command" button. I added the below code however it does not loop through each record. I need to adjust the VBA to loop through each record, not just look at the first record.

Private Sub Command301_Click()


If Not IsNull(Me.Text33.Value) Then
   If IsNull(Me.Combo24.Value) Then
MsgBox "You must state if the rationale is aligned with procedures for each disputed subcategory."

Else
'do nothing
End If
End If

DoCmd.Save
DoCmd.Close


End Sub

Does this help clarify?

Thank you for all your help.

Susan

hi Susan,

you're welcome.  My question, however, is what do you want to do if the record is not good? Put the user there and let them change it again? Obviously, they already changed it once and made it bad.

Rather than Save, how about a Close button and check records as they actually ARE saved?

What if they close form and don't click Save? The user might think they didn't save ... but the truth is, they did ...

there was no screenshot attached

it would also be helpful to the the CODE tags before and after code you post ... click the icon on the message editing toolbar

The "Save" button not only saves but closes. I am kind of a beginner so I am not following. If there is a way to not allow them to save without the above then that would be great, however you code above requires the user to delete what they enter in the text box in order to enter something in the combo box. I do not think that will work. I just want: a command button that saves and closes the form that gives the user a pop up if either A) they have something in the combo box and nothing in the text field or B) something in the text field and nothing in the combo box.

Is that even possible?

Access automatically Saves.  This is not the standard behaviour of applications where you have to explicitely save. It is easiest to use Access the way it is designed to work. Step back and look at it from a different perspective.

I need to go. Hopefully someone else will jump in from here

By the way, the name of the event procedure (Form_BeforeUpdate) might be confusing because it is really run before the current record, whether it is a single record or multi-record form, is updated (before data on that record is saved).  The Cancel argument allows you to block the record from being saved if you set the value of that argument to True.

HTH
Dale