Macro issues

[mcbass1]

I have a form that has two controls for name and employee#. The user picks the name from the dropdown box, which fills in the employee# and other information in the subform (address, ph#'s, work schedule).

The user then needs to pick from another dropdown box in the subform, and if they pick "other", they need to type something in another text box. When done, they click a button, which generates a report and attaches it as a snapshot in Outlook.

I've created three macros to verify they have entered required data (name from dropdown on main form, 2nd dropdown in subform, and 3rd text box if they picked "other"). The macro for the 2nd dropdown in the subform works perfectly. So does the text box if they picked "other", but once they click on OK on the msgbox, it runs the report and attaches it to the email instead of making them type something in the text box.

That's my first problem. The 2nd is that I get a 2105 error when I run the macro to check the main dropdown for the name. The problem is that on opening the form, I have it go to a new record to blank out the fields. If the user changes any info (address, ph#, etc.), it needs to be in the report, so when the email button is pushed, the macros run and the form goes to the next record and back again to save it in the table. This works great on the subform, but if they don't pick a name from the main form, I get the error.

The macros all have two lines, one checking if the control is null or "other", creating a msgbox and stopping all macros if it is.

Hopefully this makes sense. On Click:

Private Sub Command46_Click()
DoCmd.RunMacro "CheckEmp"
DoCmd.RunMacro "Checkforwho"
DoCmd.RunMacro "CheckOther"
DoCmd.GoToRecord acActiveDataObject, , acNext
DoCmd.GoToRecord acActiveDataObject, , acPrevious
DoCmd.RunMacro "cmdEmailAssignedTo"
End Sub


Any help would be appreciated - thanks.

 

[vodafrog]

Hello mcbass1

Problem 1 - although your macro spots the problem and halts, the code continues to run step by step, therefore it will continue to carry out the remaining steps.

I would suggest that you carry out your checks with code, for example: -

IF ME.otherfieldname="Other" and ISNULL(ME.othertextfieldname) THEN

msgbox "Sorry, you have left the xxxx field blank", vbinformation, "Incomplete entries"​

ME.othertextfieldname.setfocus​

Exit Sub​

END IF

Problem 2 - I understand the reason for moving to the next and then previous record is to save the information, this can be achieved by doing the following just before you attempt to email the results: -

Docmd.RunCommand (acCmdSaveRecord)


Hope this helps