database object keeps dissapearing

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I have a global variable "db" declared in a module, and then in the first
bits of code I execute "Set db = CurrentDB". As the rest of my program runs,
I suddenly have a problem that "db" is nothing. It has been terminated
somewhee and I don't see where.

Any pointers?

Thanks
Michael
 
Is it set in a form module, or in a standard module?
Is it declared at the top of the module (not inside a procedure)?
Is it declared as Public or Global?
 
I have one module in the MDB that contains the following lines
-------------------
Option Compare Database
'Some global settings
Public db As DAO.Database

-------------------

(They are the first lines of the module)

I have tried declaring it as GLOBAL as well, to no avail.
 
In the procedure where you have
set db=currentdb
do you have another declaration of db?
In fact, do you have another declaration of db anywhere?

To trace where db becomes nothing, you could open a code window and use
Debug - Add Watch to set a watch on db.
If you set it to break when value changes (with maximum scope), it should
break once when you set it to currentdb, then again wherever it becomes
nothing.

HTH
- Turtle
 
On the first point, there is one and only one declaration of "db"

I have tried the " break when value changes" and it doesn't help much.

I have some code that builds a complete form with controls and everything.
It also builds the events in the form. The problem occurs when I click (for
example) one of the command buttons on this constructed form. The break
watch kicks in and I see that "db" is now nothing.

I keep expecting that because db is global, the only way to kill it is to
deliberatly set it to nothing. But here it seem that a new form being opened
has that effect.
 
I certainly don't have that level of insight into the inner workings of VBA,
but it doesn't surprise me at all that code created and compiled "on the
fly" is not aware of globals set outside of it.
Especially in Access, it's rarely a good idea to build an entire new form,
or even add controls in code.
The preferred method is generally to keep things invisible until you
need them.

When you add the code module to your new form, do you put Option Explicit at
the top?
Another thing you might try would be to precede your reference to the global
variable by the name of the module where it's declared, i.e. use
MyModule.db
instead of just
db

HTH
- Turtle
 
I hear what you're saying.

OK, maybe my whole approach is wrong. Maybe I can pose this question then

I'm trying to create a "Point of Sale" system. It needs groups of PLU
buttons (Price Lookup). A group will be called "Breakfasts" and the PLU's
will be called "Eggs & Bacon", "Farmhouse special" etc.

So I have a form, with a tab control where each tab is a group, and then on
the particular page, a series of buttons that are the PLU's. I chose the
approach of designing the form on the fly because I'll never know how many
PLU's have been defined in the admin section of the database.

I could have a form with say 10 pages, and then each page has the capacity
for say, 10 PLU's, but it strikes me as inefficient, and potentially a
memory overhead. Maybe I'm wrong.
 
Most of my Access work is still in A2K, so I hope other users will correct
me if I'm wrong on this, but I'd be very surprised if that feature were
quietly added.

- Turtle
 
It's just a human interface issue. Having 21 buttons to simulate a keypad is
more effective than a dropdown combo box.
 
Back
Top