VBA copying question

  • Thread starter Thread starter Andrew Hollis
  • Start date Start date
A

Andrew Hollis

I found a sample database (NotSoComplex) that had a really cool subform
selection layout for a many-to-many database. I'm trying to get my database
to have the same subform. I copied the file in "Modules" section to my
database, and followed the instructions in it to the best of my abilities by
copying and pasting in the "code builder part of the form. I ended up with
something like this:

Private Sub Form_Load()
Set MyList = New MtoMListHandler
With MyList
Set .OptionList = RegList
Set .AddButton = AddReg
Set .DeleteButton = DeleteReg
Set .AddCombo = CmbReg
Set .ComboMask = MskReg
.LookupTable = "Regulations"
.JunctionTable = "Rev-Regs"
.MasterPK = "RevID"
.MasterFK = "JRevID"
.LookupPK = "RegID"
.LookupFK = "JRegs"
.LookupText = "Regulation Number"
.UseNotInListHandler = True
.ProperCaseNewListItems = True ' causes the NewData to be
converted to proper case
.NotInListForm = Regsform
End With
End Sub

Everytime I try to run it, though, I get an error that says: "User defined
type not defined", and It highlights a line of cade from the module I copied.
Does anyone have any advice on how I can get it running?
 
Private Sub Form_Load()
Set MyList = New MtoMListHandler
With MyList
Set .OptionList = RegList
Set .AddButton = AddReg
Set .DeleteButton = DeleteReg
Set .AddCombo = CmbReg
Set .ComboMask = MskReg
.LookupTable = "Regulations"
.JunctionTable = "Rev-Regs"
.MasterPK = "RevID"
.MasterFK = "JRevID"
.LookupPK = "RegID"
.LookupFK = "JRegs"
.LookupText = "Regulation Number"
.UseNotInListHandler = True
.ProperCaseNewListItems = True ' causes the NewData to be
converted to proper case
.NotInListForm = Regsform
End With
End Sub

Everytime I try to run it, though, I get an error that says: "User defined
type not defined", and It highlights a line of cade from the module I copied.
Does anyone have any advice on how I can get it running?
Click to expand...

Go back to NotSoComplex. It must contain a Class Module defining
MtoMListHandler; you will need to import this module into your database (don't
name it MtoMListHandler, the name of the module must be different than the
name of the objects in the module). Compile the database and see if it now
recognizes it.
 
Yeah, I copied and pasted MtoMListHandler from the modules in NotSoComplex to
my database. Should I change the name?
 
Yeah, I copied and pasted MtoMListHandler from the modules in NotSoComplex to
my database. Should I change the name?
Click to expand...

Since I don't know what you pasted in, nor what you have there already, all I
can say is I don't know. Does the application compile now?
 
No it doesn't. It always indicates a problem in the module (MtoMListHandler).
I'm not sure what other information I could give you. Guess I need to
tinker around with it a bit more
 
I'm not clear on whether you copied the module itself, or the
MtoMListHandler code from that module to your form's module. You stated in
your original post that you copied the module, but you also said you copied
the file in Modules section, which does not seem to describe literally
anything you could have done. It sounds as if you copied the module, but it
is hard to be certain. If you copied the module, what is its name?
In any case, when code is giving you a problem it is best to indicate which
line of code gives you the error. It sounds as if the sub (or function)
MtoMListHandler is calling another user-defined sub or function. It would
be included in the line of code that is highlighted as a compile error or as
a run-time error. Identify everything in that line of code (functions,
subs, variables, etc.). If you cannot, you have probably found your
culprit, or one of they anyhow.
Again, details about errors are the only way somebody who is not looking at
your code can reasonably hope to provide a meaningful answer.
 
BruceM said:
I'm not clear on whether you copied the module itself, or the
MtoMListHandler code from that module to your form's module. You stated in
your original post that you copied the module, but you also said you copied
the file in Modules section, which does not seem to describe literally
anything you could have done. It sounds as if you copied the module, but it
is hard to be certain. If you copied the module, what is its name?
In any case, when code is giving you a problem it is best to indicate which
line of code gives you the error. It sounds as if the sub (or function)
MtoMListHandler is calling another user-defined sub or function. It would
be included in the line of code that is highlighted as a compile error or as
a run-time error. Identify everything in that line of code (functions,
subs, variables, etc.). If you cannot, you have probably found your
culprit, or one of they anyhow.
Again, details about errors are the only way somebody who is not looking at
your code can reasonably hope to provide a meaningful answer.
Click to expand...
 
The error I got was in the MtoMListHandler code and it said "user-defined
type not defined" and highlighted a line of code:

Private m_db As DAO.Database

I copied the module itself, and placed it in the modules section of my
database. I then followed the instructions therein and cut and pasted code
for my forms.
 
Did the module work as expected in the sample database? If so, was that the
exact line of code? John Vinson is far more knowledgeable than I about
Access, including VBA, so I hope he is still monitoring this thread.
However, there are a few things to check.
First, make sure your References include Microsoft DAO 3.6 Object Library
(Access 2000-2003). To check this, use Tools > References in the VBA
editor. If it is not there, or to find version information for the DAO
library in other versions of Access, see:
http://allenbrowne.com/ser-38.html
Does the line Private m_db As DAO.Database appear within a function or sub,
or is it "free standing"? If the latter, it is probably at the top of the
module in the VBA editor. By the way, add Option Explicit under Option
Compare Databaase if it is not there. This will force variable declaration,
and will reveal compile errors that may not have appeared otherwise, but
which could affect how the code runs.
If it is within a sub or function, try replacing Private with Dim:
Dim m_db As DAO.Database

Andrew Hollis said:
The error I got was in the MtoMListHandler code and it said "user-defined
type not defined" and highlighted a line of code:

Private m_db As DAO.Database

I copied the module itself, and placed it in the modules section of my
database. I then followed the instructions therein and cut and pasted
code
for my forms.
Click to expand...
 
Great, that almost got. One more question. How do I:
" Declare a private class variable at module level in the form module:
'
' Private MyList As MtoMListHandler"
 
Back
Top