• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

URGENT! VBA form, validate data and re-entering the form (if necessary)

I have a user form in VBA  that will submit data to an external database.  If the data ALREADY exists in the Database then an
 error is caused and a value of 10 is set for a variable.  If the variable rc =  10 then I need to access the Form again so that the information my be changed.

How to I get BACK into my form?  I have all the If statements I think.  I just can't seem to remember how to "re-initialize" the form in order to correct the problem data.

Can anyone help?  I'm in a bit of time crunch!  Thanks in advance!
If rc < 0 Then 'There was an error processing.
   msg = MsgBox("DM Document #:  " & strECADocReference & "            Client/Matter #:  " & strECACombineCM & _
                vbCrLf & vbCrLf & "                                        SUBMISSION ERROR!" & _
                vbCrLf & vbCrLf & "        THE CLIENT/MATTER NUMBER PROVIDED IS INACTIVE OR DOES NOT EXIST." & _
                vbCritical + vbOKOnly, "Case Management Plan Error!")
       
ElseIf rc = 10 Then 'Document was already submitted
    msg = MsgBox("No action taken. Document of this type already attached to matter.", vbInformation, "Case Management Plan")

Else
    msg = MsgBox("Submission Complete!", vbExclamation, "Case Management Plan")
    
End If

Open in new window

0
Stacy Brown
Asked:
Stacy Brown
1 Solution
 
Maheshwar RSoftware DeveloperCommented:
try Me.load()
0
 
GrahamSkanRetiredCommented:
Only Hide or unload the form if the submission is successful
Sub cmdOK_Click()

'Code to submit data here


If rc < 0 Then 'There was an error processing.
   msg = MsgBox("DM Document #:  " & strECADocReference & "            Client/Matter #:  " & strECACombineCM & _
                vbCrLf & vbCrLf & "                                        SUBMISSION ERROR!" & _
                vbCrLf & vbCrLf & "        THE CLIENT/MATTER NUMBER PROVIDED IS INACTIVE OR DOES NOT EXIST." & _
                vbCritical + vbOKOnly, "Case Management Plan Error!")
       
ElseIf rc = 10 Then 'Document was already submitted
    msg = MsgBox("No action taken. Document of this type already attached to matter.", vbInformation, "Case Management Plan")

Else
    msg = MsgBox("Submission Complete!", vbExclamation, "Case Management Plan")
    Me.Hide
End If
End Sub

Open in new window

0
 
Stacy BrownSenior Applications AdministratorAuthor Commented:
Me. Load () didn't work for me.

I am unloading the form most of the time.  I haven't tried hide lately though.  I can try giving it a shot.

I think I will need to create a separate little form to allow users to "try again" at entering their data.

Thanks for the info folks!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
wobbledCommented:
I would recommend that you control your form from a module.  This way you can assign the form to a variable.

Eg

Dim objMyForm as Object

set objMyForm as frmName (whatever the name of the form is)

I would then set a public class within the form itself such as blnFormSubmit this is set when a button is clicked on the form.  This button should call the function that checks if the value is in the database and depending on the result you set the blnFormSubmit to true or false and close the form

So you have something like this in a module:

Dim objMyForm as Object

set objMyForm as new frmName

with objMyForm
                 .Show
                If .blnFormSubmit = True then .Show  'if the value on the form set to true then it re-shows it
End With

Set objMyForm = Nothing  'throw away the form

Controlling your forms this way gives you much greater control of them, it also allows you to kill off the object easily so it is not hanging around
0
 
GrahamSkanRetiredCommented:
"Try again" could be implemented as a MsgBox
0
 
Stacy BrownSenior Applications AdministratorAuthor Commented:
Thanks for the advice wobbled.  The form is giving us lots of trouble on the validation end right now.  Once we get that figured out, I think I'll try your Form control in Module and see if that works better.
0
 
Stacy BrownSenior Applications AdministratorAuthor Commented:
Change the way the validation worked and placed it in a Change event instead!  Thanks for all the ideas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now