K
Kim
why won't VBA 6.3 recognize .NoMatch or .Exists? What
library do I need to import in order to recognize these
objects?
library do I need to import in order to recognize these
objects?
S
Stuart
I think it's probably DAO that's missing (it isn't a
default in 2000)
p.s. Are there any other Access forums apart from security
& setup?
default in 2000)
p.s. Are there any other Access forums apart from security
& setup?
K
Kim
I have imported the Microsoft DAO 3.6 Object Library
already.
I can use the Find object, but not the FindFirst, and
there is no Found, Exists, NoMatch, or any other object
available in order to verify that the record was found.
Any other ideas?
Another Community Newsgroup I use is...
http://support.microsoft.com/newsgroups/default.aspx
already.
I can use the Find object, but not the FindFirst, and
there is no Found, Exists, NoMatch, or any other object
available in order to verify that the record was found.
Any other ideas?
Another Community Newsgroup I use is...
http://support.microsoft.com/newsgroups/default.aspx
S
Stuart
Have you explicitly declared your recordset as
DAO.Recordset?
e.g. Dim rst as DAO.Recordset
Stuart
DAO.Recordset?
e.g. Dim rst as DAO.Recordset
Stuart
S
Stuart
Hi Kim,
Your new problem is down to the database not being set. If
you're using MS Access & want to refer to the current
database you can get away with sticking the following line
just above the line "Set rst...".
Set gdbCurrent = CurrentDb
The trouble you were having before was with ADO & DAO
having some objects with the same name (field, recordset
etc.) & the priority of the references. The priority of
ADO (which I think in MS Access 2000 is loaded by default)
in your references is higher than that of DAO, so if
variables aren't prefixed with the object type (DAO or
ADODB) then VBA assumes that the reference with the
highest priority is the correct one. Best practice is to
always prefix any DAO or ADO object variables. This one
use to really baffle me too until I got my head around it.
Go to Tools menu & select References. I'm pretty sure that
ADO will be above DAO in the list. Select "Microsoft DAO
3.6 Library" & click the up arrow until it's above ADO in
the list of references. This should sort out your old
problem, but beware - if you're using ADO elsewhere in
your application then you might transfer exactly the same
problem to somewhere else...
You might also want to release the objects from memory at
the end of your procedure by adding the following lines of
code:
Set rst = Nothing
Set gdbCurrent = Nothing
Stuart
p.s. I was looking for some way of getting to the contents
of Access macros via VBA - no objects for Macros which
sucks...
Your new problem is down to the database not being set. If
you're using MS Access & want to refer to the current
database you can get away with sticking the following line
just above the line "Set rst...".
Set gdbCurrent = CurrentDb
The trouble you were having before was with ADO & DAO
having some objects with the same name (field, recordset
etc.) & the priority of the references. The priority of
ADO (which I think in MS Access 2000 is loaded by default)
in your references is higher than that of DAO, so if
variables aren't prefixed with the object type (DAO or
ADODB) then VBA assumes that the reference with the
highest priority is the correct one. Best practice is to
always prefix any DAO or ADO object variables. This one
use to really baffle me too until I got my head around it.
Go to Tools menu & select References. I'm pretty sure that
ADO will be above DAO in the list. Select "Microsoft DAO
3.6 Library" & click the up arrow until it's above ADO in
the list of references. This should sort out your old
problem, but beware - if you're using ADO elsewhere in
your application then you might transfer exactly the same
problem to somewhere else...
You might also want to release the objects from memory at
the end of your procedure by adding the following lines of
code:
Set rst = Nothing
Set gdbCurrent = Nothing
Stuart
p.s. I was looking for some way of getting to the contents
of Access macros via VBA - no objects for Macros which
sucks...
S
Stuart
You're lucky - it's Friday afternoon, I'm at work and if I
start another job now I'll end up going home late, so I've
just tidied the code up a touch to make myself look busy
(you might want to stick some error handling in).
Public Sub cmdEnter_Click()
Dim gdbCurrent As DAO.Database
Dim rst As DAO.Recordset
Dim strCriteria As String
Set gdbCurrent = CurrentDb
Set rst = gdbCurrent.OpenRecordset("SignOnQuery")
strCriteria = "[Employee Number] = fldEmployee Number"
And "[Password] = fldPassword"
With rst
.FindFirst strCriteria
If .NoMatch Then
MsgBox "Error - Please enter your employee
number and Password "
Else
If ![Status] = 4 Then
DoCmd.OpenForm "Employee Option Form", , ,
strCriteria
ElseIf ![Status] = 3 Then
DoCmd.OpenForm "Team Leader Option
Form", , , strCriteria
ElseIf ![Status] = 2 Then
DoCmd.OpenForm "Manager Option Form", , ,
strCriteria
ElseIf ![Status] = 1 Then
DoCmd.OpenForm "Admin Option Form", , ,
strCriteria
End If
End If
.Close
End With
Set rst = Nothing
Set gdbCurrent = Nothing
End Sub
start another job now I'll end up going home late, so I've
just tidied the code up a touch to make myself look busy
(you might want to stick some error handling in).
Public Sub cmdEnter_Click()
Dim gdbCurrent As DAO.Database
Dim rst As DAO.Recordset
Dim strCriteria As String
Set gdbCurrent = CurrentDb
Set rst = gdbCurrent.OpenRecordset("SignOnQuery")
strCriteria = "[Employee Number] = fldEmployee Number"
And "[Password] = fldPassword"
With rst
.FindFirst strCriteria
If .NoMatch Then
MsgBox "Error - Please enter your employee
number and Password "
Else
If ![Status] = 4 Then
DoCmd.OpenForm "Employee Option Form", , ,
strCriteria
ElseIf ![Status] = 3 Then
DoCmd.OpenForm "Team Leader Option
Form", , , strCriteria
ElseIf ![Status] = 2 Then
DoCmd.OpenForm "Manager Option Form", , ,
strCriteria
ElseIf ![Status] = 1 Then
DoCmd.OpenForm "Admin Option Form", , ,
strCriteria
End If
End If
.Close
End With
Set rst = Nothing
Set gdbCurrent = Nothing
End Sub
-----Original Message-----
Hi Kim,
Your new problem is down to the database not being set. If
you're using MS Access & want to refer to the current
database you can get away with sticking the following line
just above the line "Set rst...".
Set gdbCurrent = CurrentDb
The trouble you were having before was with ADO & DAO
having some objects with the same name (field, recordset
etc.) & the priority of the references. The priority of
ADO (which I think in MS Access 2000 is loaded by default)
in your references is higher than that of DAO, so if
variables aren't prefixed with the object type (DAO or
ADODB) then VBA assumes that the reference with the
highest priority is the correct one. Best practice is to
always prefix any DAO or ADO object variables. This one
use to really baffle me too until I got my head around it.
Go to Tools menu & select References. I'm pretty sure that
ADO will be above DAO in the list. Select "Microsoft DAO
3.6 Library" & click the up arrow until it's above ADO in
the list of references. This should sort out your old
problem, but beware - if you're using ADO elsewhere in
your application then you might transfer exactly the same
problem to somewhere else...
You might also want to release the objects from memory at
the end of your procedure by adding the following lines of
code:
Set rst = Nothing
Set gdbCurrent = Nothing
Stuart
p.s. I was looking for some way of getting to the contents
of Access macros via VBA - no objects for Macros which
sucks...
.
D
Douglas J. Steele
Assuming you're trying to have them prompted to enter the employee number
and password, try:
strCriteria = "[Employee Number] = fldEmployeeNumber And [Password] =
fldPassword"
--
Doug Steele, Microsoft Access MVP
(No private e-mails, please)
and password, try:
strCriteria = "[Employee Number] = fldEmployeeNumber And [Password] =
fldPassword"
--
Doug Steele, Microsoft Access MVP
(No private e-mails, please)
Thanks Stuart,
You've been great, I just don't seem to have any luck with
this. The strCriteria statement gives a type mismatch
error. I just don't get it. I never had so many errors
when I was taking VB in school. It's really disheartening.
Thanks again,
I'll talk to you Monday... maybe? (I hope)
Kim
-----Original Message-----
You're lucky - it's Friday afternoon, I'm at work and if I
start another job now I'll end up going home late, so I've
just tidied the code up a touch to make myself look busy
(you might want to stick some error handling in).
Public Sub cmdEnter_Click()
Dim gdbCurrent As DAO.Database
Dim rst As DAO.Recordset
Dim strCriteria As String
Set gdbCurrent = CurrentDb
Set rst = gdbCurrent.OpenRecordset("SignOnQuery")
strCriteria = "[Employee Number] = fldEmployee Number"
And "[Password] = fldPassword"
With rst
.FindFirst strCriteria
If .NoMatch Then
MsgBox "Error - Please enter your employee
number and Password "
Else
If ![Status] = 4 Then
DoCmd.OpenForm "Employee Option Form", , ,
strCriteria
ElseIf ![Status] = 3 Then
DoCmd.OpenForm "Team Leader Option
Form", , , strCriteria
ElseIf ![Status] = 2 Then
DoCmd.OpenForm "Manager Option Form", , ,
strCriteria
ElseIf ![Status] = 1 Then
DoCmd.OpenForm "Admin Option Form", , ,
strCriteria
End If
End If
.Close
End With
Set rst = Nothing
Set gdbCurrent = Nothing
End Sub
.-----Original Message-----
Hi Kim,
Your new problem is down to the database not being set. If
you're using MS Access & want to refer to the current
database you can get away with sticking the following line
just above the line "Set rst...".
Set gdbCurrent = CurrentDb
The trouble you were having before was with ADO & DAO
having some objects with the same name (field, recordset
etc.) & the priority of the references. The priority of
ADO (which I think in MS Access 2000 is loaded by default)
in your references is higher than that of DAO, so if
variables aren't prefixed with the object type (DAO or
ADODB) then VBA assumes that the reference with the
highest priority is the correct one. Best practice is to
always prefix any DAO or ADO object variables. This one
use to really baffle me too until I got my head around it.
Go to Tools menu & select References. I'm pretty sure that
ADO will be above DAO in the list. Select "Microsoft DAO
3.6 Library" & click the up arrow until it's above ADO in
the list of references. This should sort out your old
problem, but beware - if you're using ADO elsewhere in
your application then you might transfer exactly the same
problem to somewhere else...
You might also want to release the objects from memory at
the end of your procedure by adding the following lines of
code:
Set rst = Nothing
Set gdbCurrent = Nothing
Stuart
p.s. I was looking for some way of getting to the contents
of Access macros via VBA - no objects for Macros which
sucks...
-----Original Message-----
Thanks, that found the objects, now it points to my Set
Recordset statement and says...
Object variable or With block variable not set.
Here's my code so far...
Public Sub cmdEnter_Click()
Dim strCriteria As String
Dim stDocName As String
Dim stLinkCriteria As String
Dim gdbCurrent As Database
Dim rst As DAO.Recordset
Set rst = gdbCurrent.OpenRecordset("SignOnQuery")
strCriteria = "[Employee Number] = fldEmployee
Number"
And "[Password] = fldPassword"
rst.FindFirst strCriteria
If rst.NoMatch Then
MsgBox "Error - Please enter your employee number and
password"
Else
If rst![Status] = 4 Then
stDocName = "Employee Option Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria
ElseIf rst![Status] = 3 Then
stDocName = "Team Leader Option Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria
ElseIf rst![Status] = 2 Then
stDocName = "Manager Option Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria
ElseIf [!Status] = 1 Then
stDocName = "Admin Option Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If
rst.Close
End Sub
Am I way off or what? Visual Basic 6.0 is way easier to
work with than this VBA 6.3 crap. It amazes me that the
library that contains the Database object would not be a
default library?!?!
Did you visit the other newsgroup? Was it what you're
looking for?
-----Original Message-----
Have you explicitly declared your recordset as
DAO.Recordset?
e.g. Dim rst as DAO.Recordset
Stuart
-----Original Message-----
I have imported the Microsoft DAO 3.6 Object Library
already.
I can use the Find object, but not the FindFirst, and
there is no Found, Exists, NoMatch, or any other object
available in order to verify that the record was
found.
Any other ideas?
Another Community Newsgroup I use is...
http://support.microsoft.com/newsgroups/default.aspx
-----Original Message-----
I think it's probably DAO that's missing (it isn't a
default in 2000)
p.s. Are there any other Access forums apart from
security
& setup?
-----Original Message-----
why won't VBA 6.3 recognize .NoMatch or .Exists?
What
library do I need to import in order to recognize
these
objects?
.
.
.
.
.
.
G
Guest
Thanks Stuart,
You've been great, I just don't seem to have any luck with
this. The strCriteria statement gives a type mismatch
error. I just don't get it. I never had so many errors
when I was taking VB in school. It's really disheartening.
Thanks again,
I'll talk to you Monday... maybe? (I hope)
Kim
You've been great, I just don't seem to have any luck with
this. The strCriteria statement gives a type mismatch
error. I just don't get it. I never had so many errors
when I was taking VB in school. It's really disheartening.
Thanks again,
I'll talk to you Monday... maybe? (I hope)
Kim
-----Original Message-----
You're lucky - it's Friday afternoon, I'm at work and if I
start another job now I'll end up going home late, so I've
just tidied the code up a touch to make myself look busy
(you might want to stick some error handling in).
Public Sub cmdEnter_Click()
Dim gdbCurrent As DAO.Database
Dim rst As DAO.Recordset
Dim strCriteria As String
Set gdbCurrent = CurrentDb
Set rst = gdbCurrent.OpenRecordset("SignOnQuery")
strCriteria = "[Employee Number] = fldEmployee Number"
And "[Password] = fldPassword"
With rst
.FindFirst strCriteria
If .NoMatch Then
MsgBox "Error - Please enter your employee
number and Password "
Else
If ![Status] = 4 Then
DoCmd.OpenForm "Employee Option Form", , ,
strCriteria
ElseIf ![Status] = 3 Then
DoCmd.OpenForm "Team Leader Option
Form", , , strCriteria
ElseIf ![Status] = 2 Then
DoCmd.OpenForm "Manager Option Form", , ,
strCriteria
ElseIf ![Status] = 1 Then
DoCmd.OpenForm "Admin Option Form", , ,
strCriteria
End If
End If
.Close
End With
Set rst = Nothing
Set gdbCurrent = Nothing
End Sub
.-----Original Message-----
Hi Kim,
Your new problem is down to the database not being set. If
you're using MS Access & want to refer to the current
database you can get away with sticking the following line
just above the line "Set rst...".
Set gdbCurrent = CurrentDb
The trouble you were having before was with ADO & DAO
having some objects with the same name (field, recordset
etc.) & the priority of the references. The priority of
ADO (which I think in MS Access 2000 is loaded by default)
in your references is higher than that of DAO, so if
variables aren't prefixed with the object type (DAO or
ADODB) then VBA assumes that the reference with the
highest priority is the correct one. Best practice is to
always prefix any DAO or ADO object variables. This one
use to really baffle me too until I got my head around it.
Go to Tools menu & select References. I'm pretty sure that
ADO will be above DAO in the list. Select "Microsoft DAO
3.6 Library" & click the up arrow until it's above ADO in
the list of references. This should sort out your old
problem, but beware - if you're using ADO elsewhere in
your application then you might transfer exactly the same
problem to somewhere else...
You might also want to release the objects from memory at
the end of your procedure by adding the following lines of
code:
Set rst = Nothing
Set gdbCurrent = Nothing
Stuart
p.s. I was looking for some way of getting to the contents
of Access macros via VBA - no objects for Macros which
sucks...
.
T
TC
(snip)
Just a tip from a pedant
- Find, Findfirst etc. are "methods";
- Found, NoMatch etc. are "properties";
- databases, recordsets, fields etc. are "objects";
- Form_Open, txtMyTextbox_BeforeUpdate etc. are "events".
So you might say, "I'm getting errors using the Find method, and the NoMatch
property, of the Recordset object, from a Form_Open event".
There is no such thing as the Find or NoMatch "objects".
HTH,
TC
Just a tip from a pedant
- Find, Findfirst etc. are "methods";
- Found, NoMatch etc. are "properties";
- databases, recordsets, fields etc. are "objects";
- Form_Open, txtMyTextbox_BeforeUpdate etc. are "events".
So you might say, "I'm getting errors using the Find method, and the NoMatch
property, of the Recordset object, from a Form_Open event".
There is no such thing as the Find or NoMatch "objects".
HTH,
TC