By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,474 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,474 IT Pros & Developers. It's quick & easy.

How to use Exit Function without losing form input values

P: 6
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):

Expand|Select|Wrap|Line Numbers
  1. If (GenderComboBox.Value <> "Male" And GenderComboBox.Value <> "Female") Or GenderComboBox.ListIndex = "-1" Then
  2.    MsgBox "Gender must be either male or female"
  3.    Forms![Input].GenderComboBox.SetFocus
  4.    Exit Function
  5. End If
  6.  
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.
May 4 '15 #1
Share this Question
Share on Google+
8 Replies


Rabbit
Expert Mod 10K+
P: 12,430
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.
May 4 '15 #2

P: 6
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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm ("Confirm")
  2. With Forms![Confirm]
  3.    .NameTextBoxValue = NameInput
  4.    'and about 25 more of these for other fields
  5. End With
  6.  
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.
May 4 '15 #3

P: 6
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.
May 4 '15 #4

Rabbit
Expert Mod 10K+
P: 12,430
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:
Expand|Select|Wrap|Line Numbers
  1. Funtion Insert()
  2.    ' parsing code
  3.    ' field assignment code
  4.    If MsgBox() = vbOK Then
  5.       Exit Function
  6.    Else
  7.       ' insert code
  8.       DoCmd.CloseForm "confirm"
  9.    End If
  10. 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.
May 4 '15 #5

P: 6
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. 'tons of Dim statements
  4.  
  5. Public Function Parse(FormInput As String)
  6.  
  7. 'tons of parsing code
  8.  
  9. DoCmd.OpenForm ("Confirm")
  10. '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
  11.  
  12. With Forms![Confirm CASREP Input]
  13.     .FirstNameTextBox.Value = FirstNameInput
  14.     .LastNameTextBox.Value = LastNameInput
  15.  
  16. 'several more of these statements
  17.  
  18. End With
  19. '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.
  20.  
  21. End Function
  22.  
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Dim strSQL As String
  4.  
  5. Public Function InsertCASREPIntoReportingTable()
  6.  
  7. If (Forms![Confirm].GenderComboBox.Value <> "Male" And Forms![Confirm].GenderComboBox.Value <> "Female") Or Forms![Confirm].GenderComboBox.ListIndex = "-1" Then
  8.    MsgBox "Gender must be either male or female"
  9.    Forms![Input].GenderComboBox.SetFocus
  10.    Exit Function
  11. End If
  12. '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.
  13. 'Instead, when OK is clicked in the MsgBox, both Insert and Confirm close, and the Welcome page is displayed without saving data.
  14.  
  15. strSQL = "INSERT INTO ReportingTable ([First Name], [Last Name], [Gender]) VALUES ('" & Forms![Confirm].FirstNameTextBox.Value & "', '" & Forms![Confirm].LastNameTextBox.Value & "', '" & Forms![Confirm].GenderComboBox.Value & "');"
  16. 'There are many more fields entered, I just listed a few as an example here
  17.  
  18. DoCmd.SetWarnings False
  19. DoCmd.RunSQL strSQL
  20.  
  21. End Function
  22.  
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?
May 4 '15 #6

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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. 'tons of Dim statements
  4.  
  5. Public Function Parse(FormInput As String)
  6.  
  7. 'tons of parsing code
  8.  
  9. If FirstNameInput = "foo" Then
  10.    MsgBox ("Error")
  11.    Exit Function
  12. End If
  13.  
  14. DoCmd.OpenForm ("Confirm")
  15. '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
  16.  
  17. With Forms![Confirm CASREP Input]
  18.     .FirstNameTextBox.Value = FirstNameInput
  19.     .LastNameTextBox.Value = LastNameInput
  20.  
  21. 'several more of these statements
  22.  
  23. End With
  24. '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.
  25.  
  26. End Function
  27.  
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.
May 4 '15 #7

P: 6
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!
May 4 '15 #8

Rabbit
Expert Mod 10K+
P: 12,430
Glad you were able to figure it out. Good luck with the rest of your project.
May 4 '15 #9

Post your reply

Sign in to post your reply or Sign up for a free account.