How to use Exit Function without losing form input values

[Matt171717]

Hello all, this is my first post here, so I hope I'm following all the rules here. I'm also new to using VBA in Access, so I hope this isn't a terribly dumb question. I'm almost done with my first major project using VBA in Access. I've just got one thing left to troubleshoot. Anyway, here it goes...

In the interest of brevity, I'm leaving out a lot of extraneous info here. I can provide more code or information if necessary.

In the program, the user has a form where they input data, click "Submit", and a record is added to a table. I'm trying to do some error checking of their input. Here's the problem (this is not real code, just an example - I can provide the real code if necessary, but it's much longer and more involved):

If (GenderComboBox.Value <> "Male" And GenderComboBox.Value <> "Female") Or GenderComboBox.ListIndex = "-1" Then
   MsgBox "Gender must be either male or female"
   Forms![Input].GenderComboBox.SetFocus
   Exit Function
End If
 

Obviously, this is a combo box where the user drops down and selects their gender as either male or female. I need to make sure that they don't leave the box blank, or type in some other value by mistake.

This line of code prevents that from happening, as it should, but the problem is that it closes the form entirely. It loses all the data the user entered, and they must re-type it all again.

I was hoping that Exit Function would stop the code from running, but without closing the forms. I'd like for the code to stop executing, and have the form be displayed exactly how it was before the user clicked "Submit". That way, they can correct that error on-the-spot, without re-entering everything. There are other fields that need to be checked in this manner.

I feel like I need some sort of alternative line of code instead of "Exit Function", but I'm not having any luck through Google searching. What's the line that I'm looking for right there?

Thanks in advance for any help, I really appreciate it. Let me know if I need to provide any more info, and I'm happy to do so.

[Rabbit]

That's odd. The Exit Function doesn't close a form. I think we will need to see the actual code because I've used Exit Function before and it doesn't close the form or cause it to reload. There must be something else causing it to close and reload, for example, it could be from where you are calling the function.

[Matt171717]

Thanks for your quick reply, Rabbit.

There's about 300-400 lines of code, so I'm reluctant to paste that much, especially since most of it is not relevant to the code. But here's a more higher-level picture of what's going on, that I think will probably shed some light for you.

There is a default page that opens when the Access file is opened. On that page, there is a button that says "insert new record". That opens a form with a big blank box, call it "Input", where the user pastes a bunch of info. Then they click the "OK" button on the "Input" form.

The "OK" button runs a function I wrote that parses their input they just pasted. Let's call it "Parse". It's very lengthy stuff, but none of it is too challenging. At the bottom, I have a line that says:

DoCmd.OpenForm ("Confirm")
With Forms![Confirm]
   .NameTextBoxValue = NameInput
   'and about 25 more of these for other fields
End With
 

The "Confirm" form opens up, and it shows how our code parsed their input, but it does not submit and make a record in the table. The idea is that the user will verify the info is correct first, and edit any mistakes that may have been made. Then the user clicks "OK" on the "Confirm" form, and that runs a function called "Insert".

"Insert" takes the current values of the "Confirm" form and enters them into the table, after the user has made any edits they so desire. The code uses an INSERT INTO VALUES statement from SQL, and it works properly. Then the two forms close, the home page is displayed again, and updated with the new record.

Everything is working exactly as it should, outside of this error checking thing. Do you think the "Exit Function" line in the Insert function is actually exiting the Parse function, since the Parse function opened the Confirm page where the Insert function is ran. Does that make sense? Is that what's going on here? If so, is there an easy fix to it? Preferably without having to re-do the whole organization of everything.

Thanks again for the quick reply, Rabbit. I appreciate it.

[Matt171717]

I was able to confirm that this is what's happening. I put a similar if statement in the Parse function, with an Exit Function line under a certain if condition.

I clicked "OK" on the Confirm form, the Parse function was executed, and I received the error message like I should. I clicked "OK" on the MsgBox, and it returned back to the Input form, with all the input still there. This is exactly what I want to happen for the Confirm form.

But when I click "OK" on that MsgBox, it closes the Confirm form and the Input form, opens the default page again, and does not save any data.

How do I go about fixing this? Hopefully it's not a big nightmare with the way I've written this.

[Rabbit]

I'm having a little trouble following the flow of all the functions.

Can you post the code but leave out all the parsing code and field assignments?

For example, something like this:

Funtion Insert()
   ' parsing code
   ' field assignment code
   If MsgBox() = vbOK Then
      Exit Function
   Else
      ' insert code
      DoCmd.CloseForm "confirm"
   End If
End Function

I want to see the conditionals, the closing and opening of forms, and where you call other functions. But you can leave out the rest of the code. Hopefully that reduces the number of lines you need to paste and distills the essence to the important stuff.

[Matt171717]

Sure thing, Rabbit.

When the Access file is opened, there is a page called "Welcome" that opens automatically by default. There is a button on the Welcome page called "Insert Record". When the user clicks the Insert Record button, it opens a form called "Insert". Insert has a blank box, where the user pastes text. When they click OK, it runs the Parse function:

Option Compare Database
 
'tons of Dim statements
 
Public Function Parse(FormInput As String)
 
'tons of parsing code
 
DoCmd.OpenForm ("Confirm")
'Opens a form that shows the user all of the parsed fields, and allows them to make any changes, in case there were any mistakes
 
With Forms![Confirm CASREP Input]
    .FirstNameTextBox.Value = FirstNameInput
    .LastNameTextBox.Value = LastNameInput
 
'several more of these statements
 
End With
'The form contains blank fields by default when opened. This code fills all of the text boxes with the respective input values that we have parsed here.
 
End Function
 

So now the Confirm form is open, with the Input form open in the background. The Confirm form has text boxes and labels with all the fields. So it has a box labeled "First Name", with the value of the first name in the box. The user can change that value, in case it wasn't parsed correctly, or they had a typo or something. Same goes for all the other values.

Then when the user clicks OK on the Confirm page, it runs the Insert Function:

Option Compare Database
 
Dim strSQL As String
 
Public Function InsertCASREPIntoReportingTable()
 
If (Forms![Confirm].GenderComboBox.Value <> "Male" And Forms![Confirm].GenderComboBox.Value <> "Female") Or Forms![Confirm].GenderComboBox.ListIndex = "-1" Then
   MsgBox "Gender must be either male or female"
   Forms![Input].GenderComboBox.SetFocus
   Exit Function
End If
'I'm hoping this would display the MsgBox, the user clicks OK in the MsgBox, and then the Confirm page would be displayed with the same data entered as before.
'Instead, when OK is clicked in the MsgBox, both Insert and Confirm close, and the Welcome page is displayed without saving data.
 
strSQL = "INSERT INTO ReportingTable ([First Name], [Last Name], [Gender]) VALUES ('" & Forms![Confirm].FirstNameTextBox.Value & "', '" & Forms![Confirm].LastNameTextBox.Value & "', '" & Forms![Confirm].GenderComboBox.Value & "');"
'There are many more fields entered, I just listed a few as an example here
 
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
 
End Function
 

If I comment out the if statement in the Insert function, everything inserts properly (without any input error-checking). If I comment out only the "Exit Function" line, I get an infinite loop of MsgBox's with error messages, but they occur when I want them to.

I'm looking for a line to replace Exit Function that will exit the Insert function, without losing everything that was done in the Parse function, and losing everything that was entered in the Input and Confirm forms.

Does that explain it better, rabbit?

[Matt171717]

When I edited the Parse function, to test this error, I did this:

Option Compare Database
 
'tons of Dim statements
 
Public Function Parse(FormInput As String)
 
'tons of parsing code
 
If FirstNameInput = "foo" Then
   MsgBox ("Error")
   Exit Function
End If
 
DoCmd.OpenForm ("Confirm")
'Opens a form that shows the user all of the parsed fields, and allows them to make any changes, in case there were any mistakes
 
With Forms![Confirm CASREP Input]
    .FirstNameTextBox.Value = FirstNameInput
    .LastNameTextBox.Value = LastNameInput
 
'several more of these statements
 
End With
'The form contains blank fields by default when opened. This code fills all of the text boxes with the respective input values that we have parsed here.
 
End Function
 

Essentially, I put that if statement in place before opening any new forms. When I entered in the data, where the first name was "foo", it displayed the MsgBox with the error. Then it returned me to the Input form, with all data intact. This is what I want to happen in the Confirm form.

[Matt171717]

I've figured out the problem here, don't worry about replying, Rabbit.

For anyone else who may come along and read this some day, here's the problem. I actually excluded some necessary info, which is what the problem was.

The OK button on both forms actually runs a macro. The OK button on the Input page was simply running the Parse function, which was why the error never occurred there.

The OK button on the Confirm page was running the Input function, then closing all the forms and showing the Welcome form again. The Exit Function was actually working properly in the code, as nothing below that line in the function was executed. However, the steps in the MACRO afterwards where still executed, which is why everything closed.

I solved this by checking for the errors in the MACRO instead of in the FUNCTION. I just replicated the exact same if statement I listed earlier, and used the StopMacro command. That has fixed the problem. So it was just a rookie mistake of putting the right statement in the wrong place. I understand 100% now.

Thanks for your replies, Rabbit!

[Rabbit]

Glad you were able to figure it out. Good luck with the rest of your project.