Scrambled Data (2000)

Topic

New Reply

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?

Reply | Quote

Replies

Without lots more details, it’s difficult to make any specific suggestions, so let’s start with some general possibilities:

• 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.
• Are you using any memo fields in table Membership – memo fields are notorious sources of corruption?
• 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?
• 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.
• 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.

Reply | Quote

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

Reply | Quote

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”

Reply | Quote

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 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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

At first glance, I see nothing special in the design of the table or form, and I haven’t been able to reproduce the problem. I’ll have a closer look later on.

Reply | Quote

This is a bit like a car that makes a noise “sometimes”.
I can’t predictably reproduce the problem, either. And – as you may notice if you look at the dates on this thread – it was quite some time between when I restarted compacting and when we noticed that the problem was not gone. We use this database on a daily basis.

That said, the back of my mind is wondering whether my best bet might be to rebuild the database from scratch. I don’t know a LOT more about VBA than I did when I made this the first time, but I know a little bit more, and I also know a lot more about structure, forms, and reports – and it would avoid any problem that might have been created on the shift from 97 to 2000 – as long as I can figure out how to get the forms to do what I want them to without cutting and pasting code from this db (a trick I rely on heavily!). If I did that , would the data be “clean?” or could it contain the flukes right in it? And could I export the data without those mysterious columns that Access2000 added to the tables (i.e make queries and export those to either text or Exel, and from there into the new db)?

Of course, if we could find the problem here, I have no burning need to spend the time to make a whole new db!

Keeping my fingers crossed…..,
-cynthia

-cynthia

Reply | Quote

Now that you mention “mysterious columns” – I suppose the fields whose name start with “s_”. These have been created by Access because replication has been turned on for the database at some time – there is a database property ReplicateProject with value Yes. If you want to get rid of these fields, you must use a Make Table query to export all columns without the ones starting with “s_”, for you can’t delete these fields.

Good ways to “clean” a database are

• Create a new blank database and import all database objects from the old one into the new one. You will have to re-create the startup options manually.
• Decompile the database, then compile the project and compact the database. If you don’t know what decompiling a database is, do a search for it in this forum. For example: post 44865 and other posts in that thread.
  [/list]HTH

Reply | Quote

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

Reply | Quote

Actually, the code the wizards spit out is version 7 code, Access 95.

Reply | Quote

I’m almost too embarrassed to mention this suggestion, in light of all the wonderful responses that you have gotten, but the very _first_ thing I visualized when you mentioned the problem was — slow screen refresh, network and/or monitor display problems. I have seen records “drag” information from a previous record or screen or not be quick enough about refreshing. Of course, that issue self-corrects after a few seconds. I just thought I’d throw this out, though. I have seen records present on screen not get included in a printed report because of memory problems. We should never under-estimate the amount of memory used for graphic interfaces, and I’ve often wondered if this hasn’t had serious effects somewhere down the line for someone.
Pat, DOS lover

Reply | Quote

Well one of the things that is great about boards like this is that you can learn something from almost ALL the replies, even if in the end it turns out they aren’t the solution to the exact problem that is occurring.

I had not thought of screen refresh problems. Thinking it over now, though, I think they are probably not the problem. But see if you agree:
Here is exactly what I have noticed happening.
I (or the secretary) go to a member’s record to update a phone or email address.
I update that field.
When I finish and the curser moves to the next field, suddenly data in that field that the cursor just landed in and sometimes several other fields also changes.
Once changed, the data seems to stay changed at least until I move to another record. USUALLY when I come back to the “changed” record, it has reverted to the correct information. But not always (hence the desire to solve this problem).

I have only seen this happen when updating phone or email addresses, but then it doesn’t happen often and those are the fields that we have reason to change the most frequently.
Also, I have only seen other phone/address/email data change. I have never noticed a member’s company (for example) or work location change. But – again – I don’t have a lot of instances to compare. Probably less than ten so far. I probably wouldn’t NOTICE if a member’s birth date, join date, or Social Security number changed, unless I was looking right at it when it happend (more reason for worry).

Any more clues in this info?
-cynthia

Reply | Quote

Another remark: you should make StatusCode the primary key field in T_StatusCodes. Then you can set referential integrity for the relationship between MEMBER and T_StatusCodes.

Alternatively, you can add a field StatusCodeID (Numeric, Long Integer) to MEMBER, use an update query to populate it with the values corresponding to the StatusCode, and then delete the existing relationship between MEMBER and T_StatusCodes, and create a new join on StatusCodeID. You can set referential integrity for this relationship.

Reply | Quote

Thanks for both tips. I would very much like to “clean” the database and will look up those posts!

Question on the Status code issue.
The vast majority of our members do not have status codes because they only get one when they REjoin, change their name, die, or leave the organization (it’s a labor union). Does what you are suggesting still apply in this situation? I can’t quite picture a null primary key (can’t quite remember why I joined those tables, either – but maybe I thought I had to, to get the combo box to work). I can check with the secretery, but I think that simply making up a code for current ongoing members (the obvious fix) might interfere with reports she needs to send to the International organization.

-cynthia

Reply | Quote

What I wrote is about the primary key in T_StatusCodes, not in MEMBER. T_StatusCodes is a lookup table, and you should always have a primary key in a lookup table, otherwise you can’t join it to the main table with referential integrity enforced. The status code certainly can’t be the primary key for MEMBER!
In a one-to-many relationship, the field on the “many” side can be empty, i.e. not all members need have a status code.

Reply | Quote

> Any more clues in this info?

Nah. As a matter of fact, as I re-read through the thread, I am more inclined to think it’s a coding problem, maybe a corruption problem, maybe having something to do w/ replication since there were indications you had used it at one time — I’d go w/ the suggestions from everyone here, from separating the add / edit forms (I do that sometimes), maybe having a lookup and then open the form w/ just that record, or making sure you add the “if not found” caveat, also perhaps copy all forms, etc. to a new, clean database, possibly remaking the problem form with the “new better code” that you have learned since then! (I hate to go back and look at my old work, sometimes …)
thx
Pat

Reply | Quote

What you suggest is pretty much what I’ve been thinking I would most likely have to do. Thanks for the concrete suggestions of steps to take in doing it – I’ve thought about it only generally. I did replicate the database once – very early on – not realizing what the feature was for, and I’ve never used it again.

The coding and relationship suggestions from others above will be helpful too.

This will have to be a project for a few weeks down the road when the calendar loosens a bit from more pressing matters (data management is not the focus of my job. I just happen to be the only one in the office who had an interest in taking it on!). I will post back here when I’ve had time to do that, and to work with the results for awhile to see if the flukiness disappears! (or if I run into questions in the process!)

And I visit the forum somewhat regularly just to read, anyway – so I’ll know if anyone else adds anything in the meantime.

Thanks much to all,
-cynthia

Reply | Quote

I think I am going to have to clean up and rebuild this database sooner rather than later. It misbehaved again this morning.
In case anyone else is following this thread and wants to weigh in with any additional suggestions before I start, here is what it did today.

I opened the database to change a members record.

Instead of opening normally to the switchboard, it opened with about ten windows. And it told me the Office Assistant has failed to install and I should repair it from the CD. I closed Access, and reopened it. The database opened normally and the Office Assistant reappeared.

I used the “Find” field on the form to find John Smith. I clicked “Edit Record” and changed his home phone, added a work phone, and then clicked “Other contact Options” and added a third phone number. I then noticed that the third number was invalid (I’d called it last night and they didn’t know him), so I deleted it, and closed the “other contact options” form.
At that point I noticed that John Smith’s name and address had changed to Chris Anderson’s name and address. All other data was still John Smith’s, including the two new phone numbers.

I closed the database, and reopened it. I went to John Smith’s record. His name and address were correct, but the new phone numbers were gone and the old one was back in there (at that point I realized that the “record saved” msgbox had not displayed when I closed the form, as it should have).
I used the Find field to go to Chris Anderson’s record. I typed “Anderson, C”, and hit enter. Now the record said it was Chris Smith, and all the data except the first name was still for John Smith.

I closed the database, turned off the computer, restarted the computer, opened the database and searched for Chris Anderson. All was well at that record. Then I typed in Find “Smith, J” and hit enter. Now it said John Anderson, and all the data except the first name was still Chris Anderson’s.

I closed the database, reopened it, and went to a totally different record – David Jones. All was well. Went to another record. All was well. Went to Chris Anderson. All was well. Went to John Smith. All we well. Went back to Chris Anderson. All was still well. Went back to David Jones. All was well. Database will no longer reproduce the error (and fortunately does not appear to have saved any of the scrambled data).

All this makes me think that the scrambled data that has been saved has been when data has been entered onto a record that the user didn’t realize they were viewing (i.e. when it said Chris Smith, if I had changed any data, whose record would have been changed?). So I do think that the idea of filtering instead of bookmarking or recordset.cloning is probably the way to go with those fields (darn – I have dozens of fields like that scattered through all my databases!) along with the other suggestions already posted here. (I also read an interesting article last night on the Access Web that might apply, about bookmark malfunctions when records are deleted).

For the record, since I obviously couldn’t post the whole database, the T_StatusCode is the only table in the entire database which is not joined by a primary key – in case that has any bearing here.

So this project goes as close to the top of the list as I can get it for next week. Yipes!
-c

Reply | Quote

Why are you using the Office Assistant in the first place? You don’t need it for anything at all, and turning it off might help. If you have a form and subform arrangement, I would strongly suspect that your tables are improperly related or the links are wrong. I have seen things like this when that was the case. The records look OK when you put them in, but they either aren’t there when you go back to them or you see the wrong linked subform records. Have you explored those relationships?

Reply | Quote

Thanks for suggesting that I recheck the relationships. I know that they were all fine at one time – but perhaps something has been inadvertently changed since last I really worked with them (like maybe I messed something up when I split the database). I hadn’t thought of that.

With the exception of T_Status codes, which Hans has already commented on (I did, by the way, understand which key he was suggesting I change – I just didn’t phrase my subsequent question very well), every table that is linked is linked by the ID field – and, with the exception of T_Companies, every ID field is an autonumber field which has absolutely no role other than to be the record ID for that table and for linking – it isn’t actual data that has anything to do with anything in the database. This form has no subforms on it (there are buttons which open related forms, but those are used only occasionally). Data changes right before your eyes while working in this form with no other forms but the switchboard open.

Sorry to learn that the office assistant may be causing problems. I’m one of those apparently rare individuals who actually likes him. But I’d rather turn him off than have the database misbehaving!

I’ll be away most of next week, but I’ve alerted the other database user to be alert for bizarreness and I’m going to start cleaning, inspecting, and rebuilding as soon as I get back.

-cynthia

Reply | Quote