?
Solved

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

Posted on 2009-12-17
7
Medium Priority
?
376 Views
Last Modified: 2013-12-26
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
Comment
Question by:Stacy Brown
7 Comments
 
LVL 5

Expert Comment

by:Maheshwar R
ID: 26074145
try Me.load()
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 26074577
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
 
LVL 14

Author Comment

by:Stacy Brown
ID: 26075108
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:wobbled
ID: 26076095
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 26076415
"Try again" could be implemented as a MsgBox
0
 
LVL 14

Author Comment

by:Stacy Brown
ID: 26081414
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
 
LVL 14

Accepted Solution

by:
Stacy Brown earned 0 total points
ID: 26108014
Change the way the validation worked and placed it in a Change event instead!  Thanks for all the ideas
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this video, we show how to convert an image-only PDF file into a PDF Searchable Image file, that is, a file with both the image (typically from scanning) and text, which is created in an automated fashion with Optical Character Recognition (OCR) …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Suggested Courses
Course of the Month14 days, 11 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question