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 |
#81
|
|||
|
|||
VBA - GET / SET ??
I will repost what I have as soon as I have a little more time.
Oh, please don't |
#82
|
|||
|
|||
VBA - GET / SET ??
Thanks Cliff, you've been a big help.
The most changes I've made are to this sub, other suggestions made I've addressed below with ------ Private Sub Ub_LostFocus() On Error GoTo ProcError If Me.ID = 10 Or Me.ID = 11 Then GoTo ExitProc Dim rst As Recordset Dim fld As Field Dim strSearchName As String Me.ctl_prvUb = Me.Ub Me.ctl_prvID = Me.ID Set rst = Me.RecordsetClone strSearchName = Str(Me!ID) + 1 rst.FindFirst "ID = " & strSearchName Me.RiskDescipt.SetFocus If rst.NoMatch Then GoTo ExitProc Me.Bookmark = rst.Bookmark If Me.ID = (Me.ctl_prvID + 1) Then If Me.Lb Me.ctl_prvUb Then Me.Lb = Me.ctl_prvUb Me.CreateDate = Now() rst.MovePrevious Me.Bookmark = rst.Bookmark End If End If ExitProc: Set rst = Nothing Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdEndDate_Click..." Resume ExitProc End Sub ================================================== ===== OH YES, this did start with just a simple question, didn't it. I have some thing that almost works, but I am certain that it is written poorly. While I am ashamed to show it, I need to if I am going to get this finished, so here goes.. I've noted my problem with comments on the problem lines with I'm using ONLOSTFOCUS in order to capture any differences that may occur, which would not be noticed if this only ran on a value change. Not quite sure what you meant by that statement. Are you perhaps saying that you want this code to test for any changes that might have happened to the data outside of this form? If that's the case, then I'd sure like to hear from some other more experienced users on the subject of custom data integrity enforcement! Otherwise nothing comes to mind as a possible difference outside of a value change. I'm going to flag comments below with ************** ------ No. This would not be to test for changes made from another form!! There is no other way to change this data This is just in case there is a breakdown on the form and a user looses Connection to the table before the changes can take effect. This is why I am using the LostFocus rather then the AfterUpdate event ------ Option Compare Database Option Explicit ------------------------ ************** in general I use form Load instead of form Open because all the initialization is not complete during the Open event. Look at the sequence of events provided in the help topic "Load Event", for instance. ------ Thank you, I was curious about which event to use !! ------ Private Sub Form_Open(Cancel As Integer) On Error GoTo ProcError Me.RiskDescipt.SetFocus ExitProc: Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdEndDate_Click..." Resume ExitProc End Sub ------------------------ Private Sub Lb_GotFocus() On Error GoTo ProcError Dim rst As Recordset ************** Whenever you create an object, I gather that it is good programming practice to be sure that you release the reference when you are finished with it, by use of an explicit Set (object) = Nothing before you exit the proc: Set rst = Nothing. [The topic of memory leaks comes to mind.] Question for more experienced developers: in this case, where this recordset object will (resumably) be used multiple times during each instance of the form being opened for use would it be better (more efficient?) to move the Dim rst As Recordset to the module level, the Set rst = Me.RecordsetClone to the form load event, and the Set rst = Nothing to the form unload event? Dim strSearchName As String ' Lb = LowerBoundry will always be 0 If Me.ID = 1 Then Me.RiskDescipt.SetFocus Exit Sub End If ' Records can not be added, and the Lb of 1 and 11 can not be changed If IsNull(Me.ID) Or Me.ID = 1 Or Me.ID = 11 Then strSearchName = 1 Else strSearchName = Str(Me!ID) - 1 End If ************** strSearchName = Str(Me.ID - 1) Some observations: When comparing string data types (If, FindFirst, etc) the comparison is done byte by byte, but numeric comparisons are done in a single operation. In general it is better to use a numeric data type for "lookup" fields (keys, indexes, etc.) so I'd suggest making ID an integer instead of string. Formatting of strings can be a huge issue in matching: leading zeroes and/or spaces, for instance. Sometime search these archives for discussion on when to use DOT (Me.ID) (vs) when to use BANG (Me!ID). There is quite informative discussion out there, and good advice on the various benefits of using one over the other when either syntax will work. They are not identical. One difference is that the DOT syntax is recognized by Intellisense (control names can be selected from a dropdown list - helpful with longer names) and the compiler validates the control name; whereas a mis-spelled control name following a BANG will not be detected by the compiler. You might set the bookmark first, and SetFocus second. ------ Found the right way to use .bookmark (I THINK) rst.MovePrevious Me.Bookmark = rst.Bookmark ------ Set rst = Me.RecordsetClone rst.FindFirst "ID = " & strSearchName Me.RiskDescipt.SetFocus Me.Bookmark = rst.Bookmark *************** Consider using: With Me.RecordsetClone .FindFirst "ID = " & strSearchName Me.Bookmark = .Bookmark End With Me.RiskDescipt.SetFocus ------ Will consider, but can it be used like this With Me.RecordsetClone .FindFirst "ID = " & strSearchName Me.RiskDescipt.SetFocus If .NoMatch Then GoTo ExitProc Me.Bookmark = .Bookmark End With ------ And doing away with the whole issue of creating and annihilating the rst recordset object. (So throughout your code.) ExitProc: Me.Ub.SetFocus *************** Hmm ... this setfocus overrides RiskDescript?? ------ Maybe LEFTOVER FROM some testing, will look at this, thanks. ------ Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdEndDate_Click..." Resume ExitProc End Sub ------------------------ Private Sub Ub_GotFocus() On Error GoTo ProcError ' Prevent changes to Ub of record IDs 10 and 11 If IsNull(Me.ID) Or Me.ID = 10 Or Me.ID = 11 Then Me.RiskDescipt.SetFocus ExitProc: Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdEndDate_Click..." Resume ExitProc End Sub ------------------------ Private Sub Ub_AfterUpdate() Me.CreateDate = Now() End Sub ------------------------ Private Sub Ub_LostFocus() On Error GoTo ProcError If IsNull(Me.ID) Or Me.ID = 10 Or Me.ID = 11 Then GoTo ExitProc Dim rst As Recordset Dim fld As Field Dim strSearchName As String Dim bmk As Object I'm doing something wrong here, not yet sure what. *************** Try Dim bmk As Variant Me.ctl_prvUb = Me.Ub Me.ctl_prvID = Me.ID Set rst = Me.RecordsetClone strSearchName = Str(Me!ID) + 1 ************** strSearchName = Str(Me.ID + 1) Set bmk = rst.Bookmark wishing to capture the current bookmark rst.FindFirst "ID = " & strSearchName Me.RiskDescipt.SetFocus If rst.NoMatch Then GoTo ExitProc Me.Bookmark = rst.Bookmark If Me.ID = 1 Then Me.ctl_prvUb = Me.Ub Else If Me.ID = (Me.ctl_prvID + 1) Then If Me.Lb Me.ctl_prvUb Then Me.Lb = Me.ctl_prvUb Me.CreateDate = Now() Me.Bookmark = bmk so the changes made are recorded ************ Sequence error here, I think (I'm skimming code, not picking through it in detail --- but setting the bookmark is equivalent to navigating to a different record using the mouse or keyboard; and all bound controls will be reset to the values of the record just navigated to -- losing any changes you justy attempted to make. ------ The purpose of navegating to another record is to implement the changes Made and I think I have it working correctly in the code provided above. ------ End If End If End If ExitProc: Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdEndDate_Click..." Resume ExitProc End Sub *************** I didn't try to repeat myself when I saw a repeat of an issue I'd already commented on. HTH! -- Clif Still learning Access 2003 |
#83
|
|||
|
|||
VBA - GET / SET ??
"MVP - WannaB" wrote in message
... Thanks Cliff, you've been a big help. You're welcome. Comments throughout. The most changes I've made are to this sub, other suggestions made I've addressed below with ------ Private Sub Ub_LostFocus() On Error GoTo ProcError If Me.ID = 10 Or Me.ID = 11 Then GoTo ExitProc Dim rst As Recordset ************ Dim fld As Field looks like you're not using fld --- you can drop this line. Dim strSearchName As String Me.ctl_prvUb = Me.Ub Me.ctl_prvID = Me.ID Set rst = Me.RecordsetClone strSearchName = Str(Me!ID) + 1 rst.FindFirst "ID = " & strSearchName ******** Me.RiskDescipt.SetFocus Focus pertains to the Me.Recordset record currently displayed on the form. RecordsetClone is a completely independant recordset and has no interaction with focus. I'd separate SetFocus and RecordsetClone activity simply for reasons of clarity (disambiguation?). Also -- is your intent to force the user into the RiskDescipt control regardless of where on the form they clicked? Another thought on LostFocus -- it is possible in the standard Access UI to navigate between records without changing the control with the focus: ie, I believe that it is possible for the user to enter a new value for Ub and navigate to a different record without ever triggering the Lost Focus event for Ub. If rst.NoMatch Then GoTo ExitProc Me.Bookmark = rst.Bookmark If Me.ID = (Me.ctl_prvID + 1) Then If Me.Lb Me.ctl_prvUb Then Me.Lb = Me.ctl_prvUb Me.CreateDate = Now() rst.MovePrevious Me.Bookmark = rst.Bookmark ********** you just lost the changes you made to Me.Lb and Me.CreateDate. If I'm reading your intent correctly, you need Me.Dirty = False (I think Me.[Bound Control] = [some value] sets the Me.Dirty flag) to commit your changes to the current record before using the bookmark to navigate to a different record. End If End If ExitProc: Set rst = Nothing Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdEndDate_Click..." Resume ExitProc End Sub snip I'm using ONLOSTFOCUS in order to capture any differences that may occur, which would not be noticed if this only ran on a value change. Not quite sure what you meant by that statement. Are you perhaps saying that you want this code to test for any changes that might have happened to the data outside of this form? If that's the case, then I'd sure like to hear from some other more experienced users on the subject of custom data integrity enforcement! Otherwise nothing comes to mind as a possible difference outside of a value change. ------ No. This would not be to test for changes made from another form!! There is no other way to change this data This is just in case there is a breakdown on the form and a user looses Connection to the table before the changes can take effect. This is why I am using the LostFocus rather then the AfterUpdate event (See my note above on losing or not losing focus.) Sounds like you're trying to create an independent data validity test. If that's what you really want, then I think you'd be better served by having a routine driven from navigation to the parent record which walks through each of the 11 child records and verifies validity. My programming experience has taught me that it's impossible to predict all the possible things that can go wrong -- in most cases when a data validity error is detected it's better to provide tools to allow the user to correct the error rather than attempting to code a comprehensive correction routine. Prevention is far easier than correction -- that is why they gave us the BeforeUpdate event. snip in general I use form Load instead of form Open because all the initialization is not complete during the Open event. Look at the sequence of events provided in the help topic "Load Event", for instance. ------ Thank you, I was curious about which event to use !! [I find myself frequently going to the help file to remind myself of event sequence! g] snip You might set the bookmark first, and SetFocus second. ------ Found the right way to use .bookmark (I THINK) rst.MovePrevious Me.Bookmark = rst.Bookmark (See comkment above.) snip ------ Will consider, but can it be used like this With Me.RecordsetClone .FindFirst "ID = " & strSearchName Me.RiskDescipt.SetFocus If .NoMatch Then GoTo ExitProc Me.Bookmark = .Bookmark End With Yes, early exits from With...End With constructs are permitted (also For and Do loops). I'm trying to recall (without success) which regular it was that has posted fairly extensively on using With...End With instead of creating (and annihilating) new objects. With...End With allows you to operate directly on the object that Access has already provided; using new objects requires the additional, and frequently unnecessary processes of creating, populating, and annihilating those objects. The compiler also allows you to nest With...End With constructs. ------ snip Sequence error here, I think (I'm skimming code, not picking through it in detail --- but setting the bookmark is equivalent to navigating to a different record using the mouse or keyboard; and all bound controls will be reset to the values of the record just navigated to -- losing any changes you justy attempted to make. ------ The purpose of navegating to another record is to implement the changes Made and I think I have it working correctly in the code provided above. My inexperience is showing here. Come to think of it, Access does commit pending changes when you navigate away from a record. I don't know what would be considered 'best practice' in this case: explicitly committing the changes (Me.Dirty = False) or assuming that the implicit behavior will a) not change in the future and b) will be understood by the poor soul who in the future has to make sense of your code. Also, if navigating away from the current record is not required the explicit committment would require less I/O overhead than unnecessary navigation. ------ snip -- Clif Still learning Access 2003 |
#84
|
|||
|
|||
VBA - GET / SET ??
I've been hanging around in this thread for a while. I can't say I follow
just what you are doing, but here are some observations. This line is puzzling: strSearchName = Str(Me!ID) + 1 It appears that Me.ID is a number field, but you are converting it to a text string, then performing a mathematical operation on the string. It would be better if ID is a number to do something like this: Dim lngID as Long, lngSearchName as Long lngID = Me.ID ...... lngSearchName = lngID + 1 I don't see why you are adding 1 to the number, but again I don't know all the details, and don't have the ambition to wade through the earlier parts of this branch of the thread. By the way, it could be that you get the expected result when using a string for a number, but it is best to declare a number variable as a number (or maybe a variant in some cases) rather than as text. Helps keep things straight. "MVP - WannaB" wrote in message ... Thanks Cliff, you've been a big help. The most changes I've made are to this sub, other suggestions made I've addressed below with ------ Private Sub Ub_LostFocus() On Error GoTo ProcError If Me.ID = 10 Or Me.ID = 11 Then GoTo ExitProc Dim rst As Recordset Dim fld As Field Dim strSearchName As String Me.ctl_prvUb = Me.Ub Me.ctl_prvID = Me.ID Set rst = Me.RecordsetClone strSearchName = Str(Me!ID) + 1 rst.FindFirst "ID = " & strSearchName Me.RiskDescipt.SetFocus If rst.NoMatch Then GoTo ExitProc Me.Bookmark = rst.Bookmark If Me.ID = (Me.ctl_prvID + 1) Then If Me.Lb Me.ctl_prvUb Then Me.Lb = Me.ctl_prvUb Me.CreateDate = Now() rst.MovePrevious Me.Bookmark = rst.Bookmark End If End If ExitProc: Set rst = Nothing Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdEndDate_Click..." Resume ExitProc End Sub ================================================== ===== OH YES, this did start with just a simple question, didn't it. I have some thing that almost works, but I am certain that it is written poorly. While I am ashamed to show it, I need to if I am going to get this finished, so here goes.. I've noted my problem with comments on the problem lines with I'm using ONLOSTFOCUS in order to capture any differences that may occur, which would not be noticed if this only ran on a value change. Not quite sure what you meant by that statement. Are you perhaps saying that you want this code to test for any changes that might have happened to the data outside of this form? If that's the case, then I'd sure like to hear from some other more experienced users on the subject of custom data integrity enforcement! Otherwise nothing comes to mind as a possible difference outside of a value change. I'm going to flag comments below with ************** ------ No. This would not be to test for changes made from another form!! There is no other way to change this data This is just in case there is a breakdown on the form and a user looses Connection to the table before the changes can take effect. This is why I am using the LostFocus rather then the AfterUpdate event ------ Option Compare Database Option Explicit ------------------------ ************** in general I use form Load instead of form Open because all the initialization is not complete during the Open event. Look at the sequence of events provided in the help topic "Load Event", for instance. ------ Thank you, I was curious about which event to use !! ------ Private Sub Form_Open(Cancel As Integer) On Error GoTo ProcError Me.RiskDescipt.SetFocus ExitProc: Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdEndDate_Click..." Resume ExitProc End Sub ------------------------ Private Sub Lb_GotFocus() On Error GoTo ProcError Dim rst As Recordset ************** Whenever you create an object, I gather that it is good programming practice to be sure that you release the reference when you are finished with it, by use of an explicit Set (object) = Nothing before you exit the proc: Set rst = Nothing. [The topic of memory leaks comes to mind.] Question for more experienced developers: in this case, where this recordset object will (resumably) be used multiple times during each instance of the form being opened for use would it be better (more efficient?) to move the Dim rst As Recordset to the module level, the Set rst = Me.RecordsetClone to the form load event, and the Set rst = Nothing to the form unload event? Dim strSearchName As String ' Lb = LowerBoundry will always be 0 If Me.ID = 1 Then Me.RiskDescipt.SetFocus Exit Sub End If ' Records can not be added, and the Lb of 1 and 11 can not be changed If IsNull(Me.ID) Or Me.ID = 1 Or Me.ID = 11 Then strSearchName = 1 Else strSearchName = Str(Me!ID) - 1 End If ************** strSearchName = Str(Me.ID - 1) Some observations: When comparing string data types (If, FindFirst, etc) the comparison is done byte by byte, but numeric comparisons are done in a single operation. In general it is better to use a numeric data type for "lookup" fields (keys, indexes, etc.) so I'd suggest making ID an integer instead of string. Formatting of strings can be a huge issue in matching: leading zeroes and/or spaces, for instance. Sometime search these archives for discussion on when to use DOT (Me.ID) (vs) when to use BANG (Me!ID). There is quite informative discussion out there, and good advice on the various benefits of using one over the other when either syntax will work. They are not identical. One difference is that the DOT syntax is recognized by Intellisense (control names can be selected from a dropdown list - helpful with longer names) and the compiler validates the control name; whereas a mis-spelled control name following a BANG will not be detected by the compiler. You might set the bookmark first, and SetFocus second. ------ Found the right way to use .bookmark (I THINK) rst.MovePrevious Me.Bookmark = rst.Bookmark ------ Set rst = Me.RecordsetClone rst.FindFirst "ID = " & strSearchName Me.RiskDescipt.SetFocus Me.Bookmark = rst.Bookmark *************** Consider using: With Me.RecordsetClone .FindFirst "ID = " & strSearchName Me.Bookmark = .Bookmark End With Me.RiskDescipt.SetFocus ------ Will consider, but can it be used like this With Me.RecordsetClone .FindFirst "ID = " & strSearchName Me.RiskDescipt.SetFocus If .NoMatch Then GoTo ExitProc Me.Bookmark = .Bookmark End With ------ And doing away with the whole issue of creating and annihilating the rst recordset object. (So throughout your code.) ExitProc: Me.Ub.SetFocus *************** Hmm ... this setfocus overrides RiskDescript?? ------ Maybe LEFTOVER FROM some testing, will look at this, thanks. ------ Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdEndDate_Click..." Resume ExitProc End Sub ------------------------ Private Sub Ub_GotFocus() On Error GoTo ProcError ' Prevent changes to Ub of record IDs 10 and 11 If IsNull(Me.ID) Or Me.ID = 10 Or Me.ID = 11 Then Me.RiskDescipt.SetFocus ExitProc: Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdEndDate_Click..." Resume ExitProc End Sub ------------------------ Private Sub Ub_AfterUpdate() Me.CreateDate = Now() End Sub ------------------------ Private Sub Ub_LostFocus() On Error GoTo ProcError If IsNull(Me.ID) Or Me.ID = 10 Or Me.ID = 11 Then GoTo ExitProc Dim rst As Recordset Dim fld As Field Dim strSearchName As String Dim bmk As Object I'm doing something wrong here, not yet sure what. *************** Try Dim bmk As Variant Me.ctl_prvUb = Me.Ub Me.ctl_prvID = Me.ID Set rst = Me.RecordsetClone strSearchName = Str(Me!ID) + 1 ************** strSearchName = Str(Me.ID + 1) Set bmk = rst.Bookmark wishing to capture the current bookmark rst.FindFirst "ID = " & strSearchName Me.RiskDescipt.SetFocus If rst.NoMatch Then GoTo ExitProc Me.Bookmark = rst.Bookmark If Me.ID = 1 Then Me.ctl_prvUb = Me.Ub Else If Me.ID = (Me.ctl_prvID + 1) Then If Me.Lb Me.ctl_prvUb Then Me.Lb = Me.ctl_prvUb Me.CreateDate = Now() Me.Bookmark = bmk so the changes made are recorded ************ Sequence error here, I think (I'm skimming code, not picking through it in detail --- but setting the bookmark is equivalent to navigating to a different record using the mouse or keyboard; and all bound controls will be reset to the values of the record just navigated to -- losing any changes you justy attempted to make. ------ The purpose of navegating to another record is to implement the changes Made and I think I have it working correctly in the code provided above. ------ End If End If End If ExitProc: Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdEndDate_Click..." Resume ExitProc End Sub *************** I didn't try to repeat myself when I saw a repeat of an issue I'd already commented on. HTH! -- Clif Still learning Access 2003 |
#85
|
|||
|
|||
VBA - GET / SET ??
"Clif McIrvin" wrote:
Question for more experienced developers: in this case, where this recordset object will (resumably) be used multiple times during each instance of the form being opened for use would it be better (more efficient?) to move the Dim rst As Recordset to the module level, the Set rst = Me.RecordsetClone to the form load event, and the Set rst = Nothing to the form unload event? No. The Dim doesn't take any resources other than a small amount of RAM. The Set rs = currentdb.openrecordset does consume resources and takes time. Also to open a form in a separate instance takes a certain amount of unusual VBA code. Opening a form which is already open will essentially close the existing form and open it again. Darn, I thought I had a web page documenting that but looks like I never got around to it. Real Soon Now (TM Jerry Pournelle) Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#86
|
|||
|
|||
VBA - GET / SET ??
wrote in message ... I will repost what I have as soon as I have a little more time. Oh, please don't Please don't discourage people from seeking help in these newsgroups, rumkus -- that's exactly why they and we are here. Larry Linson Microsoft Office Access MVP |
Thread Tools | |
Display Modes | |
|
|