Replies

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

Well, I never did figure out how to create more than one identity in Outlook. So I am still using Outlook98 for my personal email and OE6 for the work email. I’m probably about to upgrade to Outlook 2000 – and if I do and if I figure anything out there, I’ll let you know.

I did read something recently – on an unrelated topic – which referred to “identities” in Outlook. But I can’t remember what it was – I just know that it referred to them as if the reader knew what they were. I can’t find anything about that in the Outlook help – as you probably know from my original post. It MIGHT have been something I just read in the most recent issue of PCWorld which was talking about where on the hard drive address books are stored for Outlook and Outlook Express.

Also, I did happen to find one downside of using a single program for both types of email (which is what would happen if I could move everything into Outlook but under separate identities, as I was wanting to). I recently had reason to temporarily set up both accounts – personal and work – in OE6 under separate identities. The problem is completely in my head – and not technical in nature – I discovered that I had to be VERY careful to observe which identity was currently open or I would inadvertently write and send mail from the wrong one! I don’t generally think about it much – probably because the screens look just enough different that it is obvious to me without any conscious thought which one I am using. I was actually very happy to go back to using the two different programs.

But, since upgrading the REST of Office to 2000 (I kept Outlook98 so I could continue to share address books) it seems that even without upgrading Outlook itself I have lost some of the transparency of the shared address books. I now have to TELL it to go to the shared addresses if I want it to use one that is not local to OE6. I haven’t had time to play around with it much yet to see if I can get it back under control.

So – in short, guess I can’t offer any answers yet! Let me know if you find any, and I’ll write again if I do.
-cynthia

I’ll be back at my home computer Sunday night or Monday, and will try that approach then. Thanks!
-cynthia

I must have done something wrong the first time. It works now!
Thanks Mark and Charlotte.
-c

Thank you Charlotte. I found and downloaded it. It looks just like the one I used to use (and love) for Access97.
-cynthia

Thank you Charlotte. It had, in fact, stripped the leading zeros off the group of four digits on the left (the database has 24,000 records, so I hadn’t noticed it at first). Removing the Val from my code seems to have everything working just dandy. I couldn’t get Mark’s suggestion to work in a query form – though I suspect that it would work just fine in an event procedure.

-cynthia

Thank you for all the extra tips.
I used “Val” only because I don’t really fully know what I’m doing here ;-), so I was copying and changing an example someone had given on another post for a date. It worked, so I left it there. The additional tips Charlotte, Tom, Pat and Mark have posted are helping me understand better what was actually happening there – and how to handle future situations. Thanks.
-cynthia

Thank you.
I’m getting it slowly but surely.

You are right. They have an OEM version (no tech support) of Professional for $340.00.

Thanks for pointing me toward it!
-cynthia

I solved it! While my initial search had not brought up anything relevant, I was just browsing a post about Dates and was able to extract from there a function that will do it.
Val(Mid([Telephone],4,3)) & “-” & Val(Mid([Telephone],7,4))

So now, so I can understand it (I took the text from the Date solution and did trials and errors with it until it did the right thing to my phone field), I understand – through trial and error – what the digits in the function are doing, and I knew how to add the text in, but can someone just explain to me what “Val”, and “Mid” are?

Thanks,
-cynthia

Access is why I need Prof or Prem, and I already have problems moving between 97 and 2000 with that – as I see you are aware. Even the VBA has some changes between versions, so code sometimes doesn’t run!

I’ll try writing to VioSoft. They have no price listed for Prof or Prem, and they say it is “out of stock”. Perhaps I was too hasty in concluding this means they won’t be getting more.

Well, I just logged on to ask exactly this question!
But looks to me like VioSoft has only Small Business and Standard editions available. I also need either Professional or Premium. Any other suggestions out there?
I have disks available at work – I’m just trying to get a legal copy to put on my home computer because it is such a nuisance not to be able to work at home.
I guess Microsoft wants us to buy XP – but NO ONE I need to be compatible with has plans to upgrade, so XP won’t do me a bit of good!

I am also in the US.
Thanks for any additional suggestions,
-cynthia

Thanks Jayden. I was able to figure out and use your suggestion.
I don’t find VBA too scarey (even today – on Halloween!). I just don’t know how to do it from scratch. I generally get the wizard to start things for me and then tweak them to be what I want (but I’ve noticed that sometimes even the wizard writes code that just plain doesn’t work!), or find something in a book that is close enough to what I am trying to do that I can make sense out of the code enough to change it to what I want – and I’ve only done fairly simple things. Twice I have signed up for a class on VBA – and both times it was canceled!!! So I really appreciate the help when I can’t even figure out where to start.

Thanks again.
-cynthia

Well, I’ve solved the problem. Yay!
But I couldn’t find the solutions9.mdb.
I don’t have an Access CD, I have Office 2000 Professional. It seems like I had all kinds of sample databases on my Office 97 CD, but I couldn’t find any on the Office 2000 CD – at least nothing with the name “solutions” and nothing with a name that suggested it was a sample database. I used my Access97 samples all the time to figure things out – can I find them on the Office 2000 CD? Or are they only on the Access Standalone CD? If they are hiding somewhere on the Office2000 CD I’d love to find them!

While I was typing my reply, more replies were arriving! (This is a report I am creating – phonebook style).
I think I can follow Jayden’s second post, and I will also look at that example on the CD when I am back at the office where the CD is. Thank you very much. I’ll let you know how it turns out in the next couple days.
-cynthia