If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Skip a line of code and continue
well, you're very welcome. it took some preserverance on both our parts, but
we got there. note: AFAIK, the only difference between a function in a standard module vs a form (or report) module is one of scope. a public function in a standard module can be "seen" and called from anywhere in the db at any time. a private function in a form/report module is available only to the object the module belongs to; a public function in a form/report module is available to be called from anywhere in the db, but only while the "owner" object is open (i'm fairly sure this is correct, if not hopefully somebody will set me straight). the problem you were having was not with the function per se, but with the Me keyword. in context, Me refers to the "owner" object of a module - but a standard module has no "owner", so you can't use the Me syntax in a standard module. the code i gave you could easily run from a standard module, if it were modified to remove the Me keyword and replace it with a complete form reference. hth "Bob Waggoner" wrote in message ... This response - your patience - has earned my undying gratitude. I didn't know there was a difference between function in a module and in a form... THANK YOU. And there're a dozen other things to say thanks for - but the code works! Thank you! "tina" wrote: comments inline. "Bob Waggoner" wrote in message ... Thank you. Now I get "Invalid use of me keyword." the error you cited above normally occurs when you use the Me keyword in a *standard* module. so if you put the code in a standard module, delete it, and paste it into your form's module, as i instructed before. My real question is: how do I trigger a function (as opposed to a sub procedure)? How do I call functions? For example, I can click a control - "Check work" and in the "on click" event, call the function. How do I do that? Private Sub Check_work_Click() TypeNameOfFunctionHere End Sub or, if the function is declared as Public, you can call it directly from the Event "line" in control [Check work]'s Properties box, as =TypeNameOfFunctionHere() if the function has arguments, include the argument values between the parentheses. hth I know that sometimes functions are called in the properties dialogue box event and sometimes in code but I'm confused as how to call them. Thanks, Bob "tina" wrote: from my first post in this thread, i've copied the function itself, below. did you paste this function into your form module? if not, do so now. do NOT put it inside an event procedure, or any other procedure. just scroll down to the bottom of the form module, and paste it below the last line of code in the module. then try compiling your code, again. hth Private Function isMissingData() As Boolean Dim ctl As Control For Each ctl In Me.Controls If ctl.Tag = "r" Then If IsNull(ctl) Then ctl.BackColor = yello isMissingData = True Else ctl.BackColor = wite End If End If Next End Function "Bob Waggoner" wrote in message ... Tina, I get the compile error: variable not defined - then it opens the form event code and highlights "If IsMissingData then" What did I do wrong? "Bob Waggoner" wrote: Thanks for correcting the code and answering my question. I've been trying to take my VBA knowledge to the next level and it's frustrating trying to figure out how to handle modules. I can convert macros to functions and then "gut" the function and rewrite it to private subs but using a function without copying/gutting and pasting it to the form procedure events is where I'm stuck. Do you know of any on line course/help I can get to teach the basics of using functions / calling functions, and etc? Thanks again. Bob "tina" wrote: well, i'm glad you posted back, Bob. first, let me fix the code - i left off the line that closes the If expression, sorry! here's the corrected code, as Private Sub Form_BeforeUpdate(Cancel As Integer) If isMissingData Then Cancel = True MsgBox "Enter the missing information in the " _ & "highlighted fields, please.", vbExclamation, _ "RECORD SUBMISSION CANCELLED" End If End Sub okay, to answer your question: copy the entire function code (posted previously) and paste it into the form's module. then, in the form's Design view, in the Properties box, click on the Event tab and find the BeforeUpdate event. double click the white space beside the event name, it will fill in automatically with [Event Procedure] at the right is a "build" button (...); click the button and it will open the form module with the cursor inside the newly create event procedure, as Private Sub Form_BeforeUpdate(Cancel As Integer) cursor blinking here, at the left margin End Sub where the cursor is blinking, paste in the "guts" of the code above, as If isMissingData Then Cancel = True MsgBox "Enter the missing information in the " _ & "highlighted fields, please.", vbExclamation, _ "RECORD SUBMISSION CANCELLED" End If so that the complete procedure in your module ends up looking like the first code i posted above. the code will run every time the form's BeforeUpdate event fires; that is, when you add a new record or edit an existing record and then 1) move to another record, or 2) close the form, or 3) move from a mainform into a subform, or vice versa, or 4) explicitly save the record from a menu bar or toolbar option or by running code - from a command button, for instance - to save the record. hth "Bob Waggoner" wrote in message ... Tina, Thank you for your help on this. I'm a relative novice - if you could help me in one more thing...how do I call a function? I don't know where or how to place this code so that it activates at the right time. Thanks Bob "tina" wrote: here's some code that i use to highlight required controls in a SingleForm view, when the data isn't entered, as Private Function isMissingData() As Boolean Dim ctl As Control For Each ctl In Me.Controls If ctl.Tag = "r" Then If IsNull(ctl) Then ctl.BackColor = yello isMissingData = True Else ctl.BackColor = wite End If End If Next End Function for each control i want to have evaluated, i enter an "r" in the Tag property of the control. the "yello" and "wite" variables are global variables that i use throughout my db for consistent coloring. you can set variables for the colors you want, or just use the number values directly. in my case, i run the code from a command button that releases the record from one dept's control to the next dept. but it would work equally well in a form's BeforeUpdate event procedure, as Private Sub Form_BeforeUpdate(Cancel As Integer) If isMissingData Then Cancel = True MsgBox "Enter the missing information in the " _ & "highlighted fields, please.", vbExclamation, _ "RECORD SUBMISSION CANCELLED" End Sub hth "Bob Waggoner" wrote in message ... I have a "Check Work" button a user can click to see if they've completed a record. Can anyone show me a bit of code that simply pops up a list of skipped items? For example: VendorCode, TypeofComment, Comment, ContactPerson are some of the fields the program checks. Right now, I have this code evaluating the fields to see if they are complete: DoCmd.Echo True, "" If (IsNull(.EmployeeName)) Then Beep MsgBox "Advisory - Please enter the Employee Name.", vbInformation, "You Forgot the Employee Name" DoCmd.GoToControl "WebEmployeeName" Exit Sub End If Instead of having code that notifies the user of each skipped box and then stops, I'd either like to list the skipped fields or allow the user to allow the code to continue checking. My attempt to allow the user to continue the check goes like this: DoCmd.Echo True, "" If (IsNull(.[EmployeeName])) Then Dim intanswerEmployeeName As Integer intanswerEmployeeName = MsgBox("Continue?", _ vbQuestion + vbYesNo, "Continue?") |
|
Thread Tools | |
Display Modes | |
|
|