It's Official...Access makes me crazy

[DCinFRANCE]

I have a form, it has a subform in continuous forms. On the detail lines I have a button, with a macro to load a popup form. In the macro, I have a where condition such that data on the detail line is used to drive the data on the popup.

It has been working fine. For weeks on end. Suddenly, I click it today and the circle spins for a second...then...nothing. No popup.

You would be right to ask, what did you do? Of course my response would be "nothing". However, the other day I split the database to put the back end on a server. That worked wonderfully...accept for this now.

Here's the macro on the button:

OpenForm

Form Name sfrmOSRCmtgs Friends
View Form
Filter Name
Where Condition = ="[FRDS_osrc]=" & [OSR_ID]
Data Mode
Window Mode Normal

That's it, and like I said, it was working beautifully. Anyone see any clues or hints?

:banghead:

 

[Minty]

Did you get an error message ?
If you convert your macro to VBA you will possibly get a more meaningful error - or at least highlight of the place it errors out.

It could be a time out - maybe the BE became unavailable briefly?

 

[DCinFRANCE]

Hi Minty, thanks for responding.

Nope. No error message. Just the 'busy' wheel turns (Windows 10) for about a second then nothing.

I did the conversion to VBA before I wrote the post, and indeed it had a syntax error...but on the very first statement (PRIVATE whatever, I'm not a vba programmer.

I spent all morning on it. And, now I've found the problem. The other day I redefined a field that appears on the form to be a lookup field. The insight happened at lunch over a Filet of Fish. I had to go back to the form, delete the original field, reply it and save. Violà, problem solved.

 

[Minty]

Don't use Lookup fields, they are not nice to play with - http://access.mvps.org/access/lookupfields.htm !

 

[DCinFRANCE]

LOL...thanks, and I've heard that, but many times I have no choice. Maybe for everyone's edification, I should explain, but only after I rant on not getting proper error messages out of Access - and happens to be why I stopped using Excel (as, I've heard it a million times, Excel is not a database -- but what do you think underlies Access?).

OK, rant /off

Some days ago, on my todo list, I had to do a minor mod to a table to make a field within a "Lookup Wizard" field to look up values in another file to fill the field. That's the look up I'm talking about. Anyway, it was quick and forgot all about it. I got far more preoccupied splitting the database, and I swore I didn't make any more modifications.

Well, I did and that was it - changing the datatype of the field.

So, this field is on a form, and that's the form that wouldn't load for no apparent reason and for which I had no error message. What I had to do was edit the form to delete the existing field, and replace it with the same field, post datatype modification.

After that, life was good again.

I read your article, and yes, those are good points. And true enough, if you look at the relationships chart, what a mess! In my defense, I knew nothing about Access when I started and have been learning on the fly. So what's done is done. Having said that, I've able to realize a lot of minor miracles.

 

[Galaxiom]

(as, I've heard it a million times, Excel is not a database -- but what do you think underlies Access?).

A database engine. Nothing even remotely like Excel.

 

[isladogs]

I've heard that (multivalue fields are a bad idea), but many times I have no choice

There's always a choice and whenever the choice is to use a multivalued field, it's the wrong choice.
You MAY arguably get a short term benefit but you WILL ALWAYS get long term problems

 

[DCinFRANCE]

Who said anything about a multi-value field? Are we talking about the same thing? We were talking about using the Lookup Wizard when defining a field in a table. The only multi-value fields I use are for attachments...and Access defines them, not I.

 

[Minty]

I think Colin's point applies equally well to a lookup field. You should simply create a separate lookup table and manually make the forms/ combo's use it.

The lookup wizard and the subsequent way that Access handles the lookup field, and hiding the actual values stored are very confusing with no long term benefits, and added complications..

 

[isladogs]

Who said anything about a multi-value field? Are we talking about the same thing? We were talking about using the Lookup Wizard when defining a field in a table. The only multi-value fields I use are for attachments...and Access defines them, not I.

Sorry I misread what you had written earlier.
However, I would still avoid using lookup fields at the table level.

In my view all of these should be avoided as they cause problems 'down the line':
- attachment fields
- multivalue fields
- table level lookup fields