Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Scrambled Data (2000)

    Our membership database has begun to mysteriously scramble data. Sometimes it unscrambles on its own, but since we have almost 1700 members, we don't necessarily recognize all scrambled details so this is potentially a serious problem.

    Sometimes when working in a form, a mouse click in a field causes that field to suddenly populate with data from that field in another record. I'm not sure this is what always causes the misplaced data to appear, but I know that is sometimes what precedes this mysterious behaviour. Sometimes closing the database and reopening it makes the displaced data disappear (from where it doesn't belong) but sometimes it does not.

    This database was originally created in Access97 and was converted to 2000 about two years ago. At some point since then, on advice from this forum, I split it, so there is one database (MembershipData) containing all the tables and another (Membership) containing all the queries, forms, reports, etc. The database is on a network and can be accessed by three different users. There is another database on one of these other computers (CynthiasMembership) which links to some of the tables in MembershipData. All the databases are occasionally - though not regularly - compacted.

    Although the database contains numerous tables (T_Committees, T_Positions, T_Participation, T_Companies, and lots more), all of the scrambling that we have noticed so far appears to be confined to the table Member, which contains the name, address, phone and etc type details for each member. Scrambling we have noticed has appeared in various fields, including address, phone, and email. All tables except the one for Company have their own autonumber primary key (Company uses a three-letter company code with no duplicates) which is used to link tables.

    Any clue what is happening or how I can fix it?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Scrambled Data (2000)

    Without lots more details, it's difficult to make any specific suggestions, so let's start with some general possibilities:
    <UL><LI>Are your forms bound or unbound? If they are bound, it is possible the front-end is corrupted - are you compacting it regularly? If you deployed it to each of the users' local hard drive, only one copy might be corrupt. If the forms are unbound, and all record operation is done using VBA and DAO, there could be logic problems that cause occasional scrambling.
    <LI>Are you using any memo fields in table Membership - memo fields are notorious sources of corruption?
    <LI>The key combination CTRL+' will copy data from the previous record to the current record - does someone have a misbehaving keyboard or "finger poking" problems?
    <LI>Do you use the same form for both editing and displaying the Name/Address info? We recommend a separate form for editing, and use a filter so that only one record is displayed on the form.
    <LI>You don't indicate what version of A2000 is being used - is everyone on SR-1a or later?[/list]Hope this gives you some ideas to pursue - post back if it doesn't and we'll do our best to help.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scrambled Data (2000)

    Thanks for all the thought provoking questions Wendell!
    One difficulty with this problem is that it doesn't happen very often. So its hard to know when its fixed and gone.

    I compacted all three databases today (the tables, and the two with forms that link to it) and the two records that were malfunctioning yesterday are now functioning fine (I had previously deleted scrambled data i recognized which had actually been "written" into the tables). Time will tell if anything else will go flukey on me. In the meantime, I'd like to be sure I fully understand the implications of the questions you posed, so I can keep my eye out for what might be triggering this if is continues to occur.

    Someone had told me that if the database is split, you didn't have to bother with compacting. I'm assuming from your question that this is NOT the case (I have been compacting, but not nearly so regularly as I had before splitting). Should we return to a regular compacting schedule?

    Here are some answers or additional questions in response to the questions you raise:

    --- Are your forms bound or unbound?
    Both parts of the main database (Membership and MembershipData) reside on the same computer and are simply accessed via the network - the front end is not distributed separately to the people who use the computer. Most of the forms are bound - including the one where we have noticed this malfunction (which is the one we use most often). There is one field in this form, which, when edited, causes another field to be filled using an event procedure which strips the "mailto:" from the front of the email address so that I can have the hyperlink field, but also can have the address appear normally on reports (such as a membership directory).

    --- Are you using any memo fields?
    There are no memo fields in the MEMBER table. There may possibly be a memo field somewhere else in the database, but I can't offhand think of where I would have used one.

    --- The key combination CTRL+' will copy data.
    I don't think the CTRL+ issue applies here, although that is an interesting tidbit to keep in mind. I know that when I have seen the data appear mysteriously somewhere, it happened on a mouse click - no keyboard action at all. There are only two of us who type anything into the database, and our experiences with the weird records are similar.

    --- Do you use the same form for both editing and displaying?
    Yes - same form. There is a combo box for selecting the name of the person whose record you want, and the form displays in "single form" mode. As we have it now, the After Update event procedure uses a bookmark function:
    Me.RecordsetClone.FindFirst "[MemberID] = " & Me![FindRecord]
    Me.Bookmark = Me.RecordsetClone.Bookmark

    I use this method on nearly all forms where I want to pull up a record or a set of records (because it was the only way I learned how to do it). It sounds like you recommend a different approach.

    --- What version of A2000 is being used?
    If I go to "About Microsoft Access" on my computer, it tells me that I am using SR-1. I know that relative to a previous question I had posted here about page setups resetting to default, it was recommended that we download and install a service pack update with a patch in it from the Microsoft site. Our technician was able to install that on one of our computers but not the other. This problem preceded that activity - it began when we were all running the same version, installed from the same disk.

    If any of this info inspires further ideas about what I should watch out for or try, I would appreciate knowing them.
    Thank you,
    -cynthia

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scrambled Data (2000)

    I don't have an answer for most of your stuff, but in answer to a split database needing compacting; yes it does. A lot of record manipulation (i.e. additions, deletions, etc) will make compacting the backend advisable. Changing forms and reports will make compacting the frontend advisable. Remember that in A2000 compacting also does a repair, so even if you didn't care about bloat you'd still want to occasionally compact the frontend to avoid "surprises" <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Scrambled Data (2000)

    Douglas has answered your question about compacting - do it to both on a fairly regular schedule, weekly perhaps.

    Yes - same form. There is a combo box for selecting the name of the person whose record you want, and the form displays in "single form" mode. As we have it now, the After Update event procedure uses a bookmark function:
    Me.RecordsetClone.FindFirst "[MemberID] = " & Me![FindRecord]
    Me.Bookmark = Me.RecordsetClone.Bookmark


    There is another thread about using this kind of code generated by the combo box wizard that could possibly explain what you are seeing. Using this code simply goes to a given record, but the user can move to a different record with inadvertent keystrokes or the mouse wheel. See the thread <post#=218075>post 218075</post#> Combo box as a locator for some of the issues and workarounds.

    Another thing you might want to consider is to activate security and modify your form so that it captures the date/time and userid each time the record is edited on the form - that is typically done on the BeforeUpdate event on the form, using hidden controls that are bound to the appropriate fields in the record. That lets you determine when a record was changed and who changed it, and is often useful in chasing down the problem in situations like yours. That's about all that comes to mind at the moment, but if you get to the bottom of this, we would all be interested in knowing what caused it.
    Wendell

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Scrambled Data (2000)

    One more thing to check on is whether you are using wireless keyboards/mice. If so, having them located too close together can cause weird problems as well.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scrambled Data (2000)

    Thanks to all for the suggestions.
    Thinking all this over, and given what I know about some peculiar behaviour this database exhibited early in its life, I now suspect that the infrequent compacting is probably root of the problem. Now that I know the info I had on that is wrong, I will put us back on a regular compacting schedule.

    Also, the idea about the hidden fields to track changes seems very helpful. I need to make some fields like that for some other reasons anyway, so that will go on my list to do ASAP.

    Given what I know about how this particular problem exhibits itself, I don't think the bookmarking procedure is the problem, but the info on that will be helpful for the future - as will the info on the cordless mice (we don't have any of those at the moment, but have in the past).

    If I learn anything significant as I go forward, I'll post again on this thread. But I'm hoping that with the compacting the problem will simply not reoccur!

    Thanks again to all.
    -cynthia

  8. #8
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scrambled Data (2000)

    Well, compacting apparently didn't fix the fluke. This morning I changed a member's work phone number, and suddenly her address was Gardiner, Maine, instead of Derry, New Hampshire! It didn't "stick". When I closed the record and then reopened it, she was back in Derry, but something is definitely going wrong behind the scenes here.
    -cynthia

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Scrambled Data (2000)

    Hi Cynthia,
    I would start investigating the form in considerable detail at this point. Two possibilities come to mind in terms of problem areas - the first is the mechanism for selecting records, and the second is code running behind the form (which I guess sort of encompasses the first).

    The first is the use of the wizard to move to a selected record using a combo box. The wizard uses a Recordset.Clone approach, and when the version changes from 97 to 2000 we've seen some strange things happen. It seems especially dicey on systems where some of the data access components have been updated by software installs other than from office, i.e. SQL Server Developer, Visio, Project, to name a few. We prefer to use a filter, which eliminates the ability to scroll through the record source of the form and see different data than the combo box displays. If you would like more details, I can post some sample code.

    The second possibility would be an errant piece of code that under some circumstances causes data on the form to be changed - you indicated in one of your posts that you do have some code running that copies the address in some fashion. You might try posting all of the code behind the form to see if anyone can spot a problem with it. In any event, stick with it - we been doing Access development for 10 years and have yet to see a situation where things like this couldn't ultimately be explained.
    Wendell

  10. #10
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scrambled Data (2000)

    Okay (gulp!). This was the first database I ever built - and this was the first form in that database. I learned Access building this database. So I'll be really curious to see what people who know what they are doing might spot in here! Actually, already I see that the event
    Private Sub HomeE_mail_DblClick(Cancel As Integer)
    is not needed - it never did what I wanted it to and I abandoned it in favor of a separate hyperlink field.

    Also - reading another post today, I realized that I did NOT yet put a separate front-end onto my own machine. So I will do that today (but it also does this to the secretary, who has both front and back-end on her machine).

    Here's the code:

    Option Compare Database
    Option Explicit

    Private Sub AddRecord_Click()
    On Error GoTo Err_AddRecord_Click

    DoCmd.GoToRecord , , acNewRec
    FirstName.SetFocus

    Exit_AddRecord_Click:
    Exit Sub

    Err_AddRecord_Click:
    MsgBox Err.Description
    Resume Exit_AddRecord_Click

    End Sub

    Private Sub EditMailToHome_Click()

    Me.AllowEdits = True
    MailTo.SetFocus

    End Sub


    Private Sub EditMailToWork_Click()

    Me.AllowEdits = True
    MailToWork.SetFocus

    End Sub

    Private Sub EditRecord_Click()

    Me.AllowEdits = True

    End Sub



    Private Sub FindRecord_DblClick(Cancel As Integer)

    Me.AllowEdits = True
    FindRecord.BackColor = -2147483643 'Change the background to match editable fields
    FirstName.SetFocus

    End Sub

    Private Sub FindRecord_Exit(Cancel As Integer)

    Me.AllowEdits = False
    FindRecord.BackColor = 14869218 'turn the background to light gray

    End Sub

    Private Sub FindRecord_KeyDown(KeyCode As Integer, Shift As Integer)

    Me.AllowEdits = True


    End Sub

    Private Sub Form_AfterUpdate()

    Me.AllowEdits = False 'Return the form to it's read-only state'
    MsgBox "Record Saved" 'Message to reassure user that record has been saved'

    End Sub

    Private Sub Form_Current()

    Me.AllowEdits = False 'Return the form to it's read-only state'
    FindRecord = MemberID

    End Sub

    Private Sub Form_Open(Cancel As Integer)

    FindRecord.SetFocus

    End Sub
    Private Sub HomeE_mail_DblClick(Cancel As Integer)

    'Open Outlook and put this email address in the "to" field

    If Not IsNull(Me.[HomeE-mail]) Then
    OpenOutlook (Me.[HomeE-mail])
    End If

    End Sub

    Private Sub MailTo_AfterUpdate()
    If [MailTo] = "" Then
    [HomeE-mail] = Null
    Else
    [HomeE-mail] = Mid$([MailTo], 9, Len([MailTo]) - 9)
    End If
    End Sub

    Private Sub MailToWork_AfterUpdate()
    If [MailToWork] = "" Then
    [WorkE-mail] = Null
    Else
    [WorkE-mail] = Mid$([MailToWork], 9, Len([MailToWork]) - 9)
    End If
    End Sub

    Private Sub MemberID_Click()

    MsgBox "Access generates this number automatically. It cannot be edited"

    End Sub


    Private Sub SaveRecord_Click()
    On Error GoTo Err_SaveRecord_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    Exit_SaveRecord_Click:
    Exit Sub

    Err_SaveRecord_Click:
    MsgBox Err.Description
    Resume Exit_SaveRecord_Click

    End Sub

    Sub FindRecord_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[MemberID] = " & Me![FindRecord]
    Me.Bookmark = Me.RecordsetClone.Bookmark
    FirstName.SetFocus

    End Sub

    Private Sub ContactOptions_Click()
    On Error GoTo Err_ContactOptions_Click
    If IsNull(Me![MemberID]) Then
    MsgBox "Enter member information or select a member before viewing contact options."
    Else
    DoCmd.OpenForm "F_MembContDet"
    End If

    Exit_ContactOptions_Click:
    Exit Sub

    Err_ContactOptions_Click:
    MsgBox Err.Description
    Resume Exit_ContactOptions_Click

    End Sub

    Private Sub PrDir_Click()
    On Error GoTo Err_PrDir_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "F_PrintDialog"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    [Forms]![F_PrintDialog].[RetClose].Visible = True
    [Forms]![F_PrintDialog].[BackMember].Visible = True
    [Forms]![F_PrintDialog].[RetMain].Visible = False

    Exit_PrDir_Click:
    Exit Sub

    Err_PrDir_Click:
    MsgBox Err.Description
    Resume Exit_PrDir_Click

    End Sub
    Private Sub PrEnv_Click()
    On Error GoTo Err_PrEnv_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Envelope"

    stLinkCriteria = "[MemberID]=" & Me![FindRecord]
    DoCmd.OpenReport stDocName, acNormal

    Exit_PrEnv_Click:
    Exit Sub

    Err_PrEnv_Click:
    MsgBox Err.Description
    Resume Exit_PrEnv_Click

    End Sub
    Private Sub ShowPositions_Click()
    On Error GoTo Err_ShowPositions_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    If IsNull(Me![MemberID]) Then
    MsgBox "Enter member information or select a member before viewing position information."
    Else
    stDocName = "F_PositionDetails"

    stLinkCriteria = "[MemberID]=" & Me![FindRecord]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If

    Exit_ShowPositions_Click:
    Exit Sub

    Err_ShowPositions_Click:
    MsgBox Err.Description
    Resume Exit_ShowPositions_Click

    End Sub
    Private Sub ShowComm_Click()
    On Error GoTo Err_ShowComm_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    If IsNull(Me![MemberID]) Then
    MsgBox "Enter member information or select a member before viewing committee information."
    Else
    stDocName = "F_CommDet"

    stLinkCriteria = "[MemberID]=" & Me![FindRecord]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If

    Exit_ShowComm_Click:
    Exit Sub

    Err_ShowComm_Click:
    MsgBox Err.Description
    Resume Exit_ShowComm_Click

    End Sub
    Private Sub Main_Click()
    On Error GoTo Err_Main_Click


    Dim stDocName As String
    Dim stLinkCriteria As String

    DoCmd.Close
    stDocName = "Switchboard"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_Main_Click:
    Exit Sub

    Err_Main_Click:
    MsgBox Err.Description
    Resume Exit_Main_Click

    End Sub
    Private Sub ShowPartic_Click()
    On Error GoTo Err_ShowPartic_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    If IsNull(Me![MemberID]) Then
    MsgBox "Enter member information or select a member before viewing participation information."
    Else
    stDocName = "F_ParticDet"

    stLinkCriteria = "[MemberID]=" & Me![FindRecord]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If

    Exit_ShowPartic_Click:
    Exit Sub

    Err_ShowPartic_Click:
    MsgBox Err.Description
    Resume Exit_ShowPartic_Click

    End Sub
    Private Sub RetPartic_Click()
    On Error GoTo Err_RetPartic_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    DoCmd.Close
    stDocName = "F_Participation"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_RetPartic_Click:
    Exit Sub

    Err_RetPartic_Click:
    MsgBox Err.Description
    Resume Exit_RetPartic_Click

    End Sub
    Private Sub PrEnv2_Click()
    On Error GoTo Err_PrEnv2_Click

    Dim stDocName As String

    stDocName = "EnvelopeRight"
    DoCmd.OpenReport stDocName, acNormal

    Exit_PrEnv2_Click:
    Exit Sub

    Err_PrEnv2_Click:
    MsgBox Err.Description
    Resume Exit_PrEnv2_Click

    End Sub
    Private Sub PrEnvXer_Click()
    On Error GoTo Err_PrEnvXer_Click

    Dim stDocName As String

    stDocName = "EnvleopeXerox"
    DoCmd.OpenReport stDocName, acNormal

    Exit_PrEnvXer_Click:
    Exit Sub

    Err_PrEnvXer_Click:
    MsgBox Err.Description
    Resume Exit_PrEnvXer_Click

    End Sub
    Private Sub ShowMailing_Click()
    On Error GoTo Err_ShowMailing_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "F_MailDetMemb"

    stLinkCriteria = "[MemberID]=" & Me![FindRecord]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_ShowMailing_Click:
    Exit Sub

    Err_ShowMailing_Click:
    MsgBox Err.Description
    Resume Exit_ShowMailing_Click

    End Sub
    Private Sub AddWorkLoc_Click()
    On Error GoTo Err_AddWorkLoc_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "F_WorkLocations"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_AddWorkLoc_Click:
    Exit Sub

    Err_AddWorkLoc_Click:
    MsgBox Err.Description
    Resume Exit_AddWorkLoc_Click

    End Sub
    Private Sub DelRec_Click()
    On Error GoTo Err_DelRec_Click

    Me.AllowEdits = True
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

    Exit_DelRec_Click:
    Exit Sub

    Err_DelRec_Click:
    MsgBox Err.Description
    Resume Exit_DelRec_Click

    End Sub

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Scrambled Data (2000)

    Maybe if you posted the DB's we could get a better perspective of what's going on. If you do post your DB take out any sensitive data first.

    Pat

  12. #12
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scrambled Data (2000)

    I'll make one with benign data tomorrow and post it. I need to do that for another purpose, anyway.
    -cynthia

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Scrambled Data (2000)

    I don't see anything that immediately raises my suspicions.

    There are a few bits of antiquated code in there - nothing to be ashamed of, Microsoft's Wizards keep on churning out Access 2 code even in Access 2002!

    You can replace DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 by the simper equivalent RunCommand acCmdSaveRecord.

    And you can replace DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 / DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70 by the shorter equivalent RunCommand acCmdDeleteRecord.

    One other point - I don't know what type of control FindRecord is, but if the user can enter "free" text, you need to check if a corresponding record is found:

    Sub FindRecord_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[MemberID] = " & Me![FindRecord]
    If Me.RecordsetClone.NoMatch = False Then
    Me.Bookmark = Me.RecordsetClone.Bookmark
    FirstName.SetFocus
    End If
    End Sub

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Scrambled Data (2000)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Actually, the code the wizards spit out is version 7 code, Access 95.
    Charlotte

  15. #15
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scrambled Data (2000)

    Well - it was quite a process to make something that meets the 100K maximum file size, but here it is.
    None of the buttons on the form work, because I had to delete the related forms to get the file small enough.
    But I think you can see what is supposed to be going on with this form.

    The original database has a front end and backend. The frontend contains 26 tables, and the backend contains 48 queries, 40 forms, and 31 reports. It opens to a switchboard (which is just a form with buttons, not created by the switchboard wizard). But, aside from the switchboard, this form is the one we use most often - and it is also the only one where we notice the data changing.

    Thanks for any clues on what might be scrambling the data!
    -cynthia
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •