VB Code explanation - please

[Guest]

Hi All,

I found an example of a databse on the internet that does what I am trying
to achieve. That is:
Add several records to a form, when a new form is created.

When I place the exact same code in my database it crashes on the line:
Dim db AS Database.

But when I modify the downloaded database with my tables it works how I want
it to work.

This is the database I downloaded:

http://www.techonthenet.com/access/forms/subform_data.htm

and this is the code:

Private Sub Form_AfterInsert()
Dim db As Database
Dim LSQL As String
Dim LCntr As Integer

'Establish connection to current database
Set db = CurrentDb()

LCntr = 1

'Create SQL to insert item_numbers 1 to 24
Do Until LCntr > 22

LSQL = "insert into inventory_details (StudyNum, StageID)"
LSQL = LSQL & " values ("
LSQL = LSQL & "'" & StudyNum & "', " & LCntr & ")"

'Perform SQL
db.Execute LSQL

'Increment counter variable
LCntr = LCntr + 1
Loop

'Requery subform that new records are listing

inventory_details_Subform.Requery

End Sub

Any ideas or explanations of the code (my VB is limited)

Thanks Brian

 

[Guest]

Hi Brian,

I would expect the problem is likely that your database does not have a
reference to DAO, which it looks like you will need to run the referenced
code. To check this, go to Tools|References in the programming window to
view/modify which object libraries are specified (referenced) in your
database.

One other thing that you should know about, some object libraries can have
the same object names (such as Recordset in ADO and DAO). In those cases, if
VBA will assume that you are referencing the object from the library that is
highest on the reference list (you can modify their order as you will see
when you look at them). But, if you want to specify a lower priority object
with a common name, or if you just want to explicitly tell access which
library you are using so that it doesn't search through the libraries, you
can preface your library object variables with the library prefix. For
instance, for your database object you could use:

Dim db as DAO.Database

I believe the prefix for ADO is ADODB (I mostly use DAO and my memory isn't
the greatest, so it is possible that I could be off on that). Assuming that
is correct though, when dimensioning recordset objects you could use:

Dim rst as DAO.Recordset
Dim rst2 as ADODB.Recordset

I prefer to use the prefixes even if I am using only a single library at the
time for a few reasons. First, if it becomes necessary to use the other
library later for some reason, you may have to go back through your code and
add the prefixes. Second, when you or someone else looks at the code later
it is immediately apparent what library is being used.

HTH, Ted Allen

 

[Guest]

Thanks Ted,

That solved my problem

Hi Brian,

I would expect the problem is likely that your database does not have a
reference to DAO, which it looks like you will need to run the referenced
code. To check this, go to Tools|References in the programming window to
view/modify which object libraries are specified (referenced) in your
database.

One other thing that you should know about, some object libraries can have
the same object names (such as Recordset in ADO and DAO). In those cases, if
VBA will assume that you are referencing the object from the library that is
highest on the reference list (you can modify their order as you will see
when you look at them). But, if you want to specify a lower priority object
with a common name, or if you just want to explicitly tell access which
library you are using so that it doesn't search through the libraries, you
can preface your library object variables with the library prefix. For
instance, for your database object you could use:

Dim db as DAO.Database

I believe the prefix for ADO is ADODB (I mostly use DAO and my memory isn't
the greatest, so it is possible that I could be off on that). Assuming that
is correct though, when dimensioning recordset objects you could use:

Dim rst as DAO.Recordset
Dim rst2 as ADODB.Recordset

I prefer to use the prefixes even if I am using only a single library at the
time for a few reasons. First, if it becomes necessary to use the other
library later for some reason, you may have to go back through your code and
add the prefixes. Second, when you or someone else looks at the code later
it is immediately apparent what library is being used.

HTH, Ted Allen

 

[Guest]

My pleasure, glad it helped.

Thanks Ted,

That solved my problem

 

[RD]

Hi Brian,

This is a References issue. Go into the VBA editor of the downloaded db. Click
Tools then References... . Notice that it references DAO 3.6. Access 2000 and
up reference ADO by default. There are also a couple of other references there
(like a calendar control). Record all those references and then go to the code
editor in your own db and make make sure your references match. Should work
fine after that.

HTH,
RD