multiple criteria for finding record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to use the DLookup function 3 times connected by AND in the
criteria for running a macro for data entry? The issue, I think, is that a
master table with an autonumbered primary key, may be part of overall data
entry. Say that EventID is an autonumbered field that is related to unique
combinations of site, date, and time. If one were to enter a new record for
the master tables and junction table, would it not be important to determine
if the site, date, and time were the same as a prevous record. If so, the
EventID of that previous record should be the EventID of the current record
being entered. It seems that a macro with the specified condition would do
the trick. Thus, is it possible that DLookup could be used for site, date,
and time in association with macro actions? The user could enter site, date,
and time on an unbound form with a control to run the macro (or code).
 
Yes, you can, but I wouldn't advise it. Domain functions such as DLookup are
notoriosuly slow, and having 3 of them back-to-back can cause latency
problems in large tables.

I'd be more inclined to use a single recordset:
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb
sSQL = "SELECT ID FROM MyTable " & _
"WHERE site = " & Me.txtSite & _
" AND [date] = " & Me!txtDate & _
" AND [time] = " & Me!txtTime

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
'site, date and time already exist in the table
End If

rs.Close
Set rs = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Thanks, Graham. I am somewhat new at development, so could I put your
response in context? The code would be run from an unbound form in which the
user had entered site, date, and time. The issue is whether a new record
should be written in MyTable, with an additional autonumbered ID, or whether
further data entry in a related junction table should simply use the existing
ID. I assume that the If-Then portion of the code would be the area in which
a bound data entry form for MyTable could be opened and a new record written
(if necessary) and in which a bound data entry form for MyJunctionTable could
be opened and the appropriate ID value could be set in that field. Is this
the right approach?

Regards,
LAF

Graham R Seach said:
Yes, you can, but I wouldn't advise it. Domain functions such as DLookup are
notoriosuly slow, and having 3 of them back-to-back can cause latency
problems in large tables.

I'd be more inclined to use a single recordset:
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb
sSQL = "SELECT ID FROM MyTable " & _
"WHERE site = " & Me.txtSite & _
" AND [date] = " & Me!txtDate & _
" AND [time] = " & Me!txtTime

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
'site, date and time already exist in the table
End If

rs.Close
Set rs = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

LAF said:
Is it possible to use the DLookup function 3 times connected by AND in the
criteria for running a macro for data entry? The issue, I think, is that
a
master table with an autonumbered primary key, may be part of overall data
entry. Say that EventID is an autonumbered field that is related to
unique
combinations of site, date, and time. If one were to enter a new record
for
the master tables and junction table, would it not be important to
determine
if the site, date, and time were the same as a prevous record. If so, the
EventID of that previous record should be the EventID of the current
record
being entered. It seems that a macro with the specified condition would
do
the trick. Thus, is it possible that DLookup could be used for site,
date,
and time in association with macro actions? The user could enter site,
date,
and time on an unbound form with a control to run the macro (or code).
Click to expand...
Click to expand...
 
No, the other way around...

If rs.AbsolutePosition > -1 Then
'Use the existing record
Else
'Create a new record
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

LAF said:
Thanks, Graham. I am somewhat new at development, so could I put your
response in context? The code would be run from an unbound form in which
the
user had entered site, date, and time. The issue is whether a new record
should be written in MyTable, with an additional autonumbered ID, or
whether
further data entry in a related junction table should simply use the
existing
ID. I assume that the If-Then portion of the code would be the area in
which
a bound data entry form for MyTable could be opened and a new record
written
(if necessary) and in which a bound data entry form for MyJunctionTable
could
be opened and the appropriate ID value could be set in that field. Is
this
the right approach?

Regards,
LAF

Graham R Seach said:
Yes, you can, but I wouldn't advise it. Domain functions such as DLookup
are
notoriosuly slow, and having 3 of them back-to-back can cause latency
problems in large tables.

I'd be more inclined to use a single recordset:
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb
sSQL = "SELECT ID FROM MyTable " & _
"WHERE site = " & Me.txtSite & _
" AND [date] = " & Me!txtDate & _
" AND [time] = " & Me!txtTime

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
'site, date and time already exist in the table
End If

rs.Close
Set rs = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

LAF said:
Is it possible to use the DLookup function 3 times connected by AND in
the
criteria for running a macro for data entry? The issue, I think, is
that
a
master table with an autonumbered primary key, may be part of overall
data
entry. Say that EventID is an autonumbered field that is related to
unique
combinations of site, date, and time. If one were to enter a new
record
for
the master tables and junction table, would it not be important to
determine
if the site, date, and time were the same as a prevous record. If so,
the
EventID of that previous record should be the EventID of the current
record
being entered. It seems that a macro with the specified condition
would
do
the trick. Thus, is it possible that DLookup could be used for site,
date,
and time in association with macro actions? The user could enter site,
date,
and time on an unbound form with a control to run the macro (or code).
Click to expand...
Click to expand...
Click to expand...
 
Aloha Graham,

Could you explain why in the SQL string, site is not bracketed but date and
time are, and Me is followed by a dot for site but by bangs for date and
time?

Thanks,

LAF

Graham R Seach said:
Yes, you can, but I wouldn't advise it. Domain functions such as DLookup are
notoriosuly slow, and having 3 of them back-to-back can cause latency
problems in large tables.

I'd be more inclined to use a single recordset:
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb
sSQL = "SELECT ID FROM MyTable " & _
"WHERE site = " & Me.txtSite & _
" AND [date] = " & Me!txtDate & _
" AND [time] = " & Me!txtTime

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
'site, date and time already exist in the table
End If

rs.Close
Set rs = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

LAF said:
Is it possible to use the DLookup function 3 times connected by AND in the
criteria for running a macro for data entry? The issue, I think, is that
a
master table with an autonumbered primary key, may be part of overall data
entry. Say that EventID is an autonumbered field that is related to
unique
combinations of site, date, and time. If one were to enter a new record
for
the master tables and junction table, would it not be important to
determine
if the site, date, and time were the same as a prevous record. If so, the
EventID of that previous record should be the EventID of the current
record
being entered. It seems that a macro with the specified condition would
do
the trick. Thus, is it possible that DLookup could be used for site,
date,
and time in association with macro actions? The user could enter site,
date,
and time on an unbound form with a control to run the macro (or code).
Click to expand...
Click to expand...
 
[date] and [time] are reserved keywords, and you can experience problems if
you don't bracket them.

As for using a dot for [site] - that's a mistake. For consistency, use a
bang for all.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

LAF said:
Aloha Graham,

Could you explain why in the SQL string, site is not bracketed but date
and
time are, and Me is followed by a dot for site but by bangs for date and
time?

Thanks,

LAF

Graham R Seach said:
Yes, you can, but I wouldn't advise it. Domain functions such as DLookup
are
notoriosuly slow, and having 3 of them back-to-back can cause latency
problems in large tables.

I'd be more inclined to use a single recordset:
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb
sSQL = "SELECT ID FROM MyTable " & _
"WHERE site = " & Me.txtSite & _
" AND [date] = " & Me!txtDate & _
" AND [time] = " & Me!txtTime

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
'site, date and time already exist in the table
End If

rs.Close
Set rs = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

LAF said:
Is it possible to use the DLookup function 3 times connected by AND in
the
criteria for running a macro for data entry? The issue, I think, is
that
a
master table with an autonumbered primary key, may be part of overall
data
entry. Say that EventID is an autonumbered field that is related to
unique
combinations of site, date, and time. If one were to enter a new
record
for
the master tables and junction table, would it not be important to
determine
if the site, date, and time were the same as a prevous record. If so,
the
EventID of that previous record should be the EventID of the current
record
being entered. It seems that a macro with the specified condition
would
do
the trick. Thus, is it possible that DLookup could be used for site,
date,
and time in association with macro actions? The user could enter site,
date,
and time on an unbound form with a control to run the macro (or code).
Click to expand...
Click to expand...
Click to expand...
 
Hi again Graham,

I have been studying your book on Access 2003 VBA, and I have some questions
on the sSQL string. It seems like more quotation marks are in order. How is
this for a complete string:

sSQL = "SELECT ID FROM MyTable " & _
"WHERE site = """ & Me!txtSite & """" & _
"AND [date] = """ & Me!txtDate & """" & _
"AND [time] = """ & Me!txtTime & """"

If this is correct full syntax, it may be useful to others. If not, if you
could correct it, it would be useful to even more access programmers. Your
book is great. It covers some things, like SQL strings, that other books do
not.

All the best,

LAF



Graham R Seach said:
[date] and [time] are reserved keywords, and you can experience problems if
you don't bracket them.

As for using a dot for [site] - that's a mistake. For consistency, use a
bang for all.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

LAF said:
Aloha Graham,

Could you explain why in the SQL string, site is not bracketed but date
and
time are, and Me is followed by a dot for site but by bangs for date and
time?

Thanks,

LAF

Graham R Seach said:
Yes, you can, but I wouldn't advise it. Domain functions such as DLookup
are
notoriosuly slow, and having 3 of them back-to-back can cause latency
problems in large tables.

I'd be more inclined to use a single recordset:
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb
sSQL = "SELECT ID FROM MyTable " & _
"WHERE site = " & Me.txtSite & _
" AND [date] = " & Me!txtDate & _
" AND [time] = " & Me!txtTime

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
'site, date and time already exist in the table
End If

rs.Close
Set rs = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Is it possible to use the DLookup function 3 times connected by AND in
the
criteria for running a macro for data entry? The issue, I think, is
that
a
master table with an autonumbered primary key, may be part of overall
data
entry. Say that EventID is an autonumbered field that is related to
unique
combinations of site, date, and time. If one were to enter a new
record
for
the master tables and junction table, would it not be important to
determine
if the site, date, and time were the same as a prevous record. If so,
the
EventID of that previous record should be the EventID of the current
record
being entered. It seems that a macro with the specified condition
would
do
the trick. Thus, is it possible that DLookup could be used for site,
date,
and time in association with macro actions? The user could enter site,
date,
and time on an unbound form with a control to run the macro (or code).
Click to expand...
Click to expand...
Click to expand...
 
No, there are plenty of quotation marks, but I forgot to add a couple of
spaces:
sSQL = "SELECT ID FROM MyTable " & _
"WHERE site = """ & Me!txtSite & """ " & _
"AND [date] = """ & Me!txtDate & """ " & _
"AND [time] = """ & Me!txtTime & """"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

LAF said:
Hi again Graham,

I have been studying your book on Access 2003 VBA, and I have some
questions
on the sSQL string. It seems like more quotation marks are in order. How
is
this for a complete string:

sSQL = "SELECT ID FROM MyTable " & _
"WHERE site = """ & Me!txtSite & """" & _
"AND [date] = """ & Me!txtDate & """" & _
"AND [time] = """ & Me!txtTime & """"

If this is correct full syntax, it may be useful to others. If not, if
you
could correct it, it would be useful to even more access programmers.
Your
book is great. It covers some things, like SQL strings, that other books
do
not.

All the best,

LAF



Graham R Seach said:
[date] and [time] are reserved keywords, and you can experience problems
if
you don't bracket them.

As for using a dot for [site] - that's a mistake. For consistency, use a
bang for all.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

LAF said:
Aloha Graham,

Could you explain why in the SQL string, site is not bracketed but date
and
time are, and Me is followed by a dot for site but by bangs for date
and
time?

Thanks,

LAF

:

Yes, you can, but I wouldn't advise it. Domain functions such as
DLookup
are
notoriosuly slow, and having 3 of them back-to-back can cause latency
problems in large tables.

I'd be more inclined to use a single recordset:
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb
sSQL = "SELECT ID FROM MyTable " & _
"WHERE site = " & Me.txtSite & _
" AND [date] = " & Me!txtDate & _
" AND [time] = " & Me!txtTime

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
'site, date and time already exist in the table
End If

rs.Close
Set rs = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Is it possible to use the DLookup function 3 times connected by AND
in
the
criteria for running a macro for data entry? The issue, I think, is
that
a
master table with an autonumbered primary key, may be part of
overall
data
entry. Say that EventID is an autonumbered field that is related to
unique
combinations of site, date, and time. If one were to enter a new
record
for
the master tables and junction table, would it not be important to
determine
if the site, date, and time were the same as a prevous record. If
so,
the
EventID of that previous record should be the EventID of the current
record
being entered. It seems that a macro with the specified condition
would
do
the trick. Thus, is it possible that DLookup could be used for
site,
date,
and time in association with macro actions? The user could enter
site,
date,
and time on an unbound form with a control to run the macro (or
code).
Click to expand...
Click to expand...
Click to expand...
 
Hi Graham,

I get a run time error saying FROM syntax is wrong for the following:

sSQL = "SELECT Bandnum FROM tblBirds" & _
"WHERE Bandnum = """ & Me!txtBandnum & """ " & _
"AND Species = """ & Me!txtSpecies & """ " & _
"AND Leftleg = """ & Me!txtLeftleg & """ " & _
"AND Rightleg = """ & Me!Rightleg & """"

I tried modelling the statement after your example. Can you identify the
problem, and perhaps help others learn the tricks about lengthy sql strings?
Thanks,
LAF

Graham R Seach said:
No, there are plenty of quotation marks, but I forgot to add a couple of
spaces:
sSQL = "SELECT ID FROM MyTable " & _
"WHERE site = """ & Me!txtSite & """ " & _
"AND [date] = """ & Me!txtDate & """ " & _
"AND [time] = """ & Me!txtTime & """"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

LAF said:
Hi again Graham,

I have been studying your book on Access 2003 VBA, and I have some
questions
on the sSQL string. It seems like more quotation marks are in order. How
is
this for a complete string:

sSQL = "SELECT ID FROM MyTable " & _
"WHERE site = """ & Me!txtSite & """" & _
"AND [date] = """ & Me!txtDate & """" & _
"AND [time] = """ & Me!txtTime & """"

If this is correct full syntax, it may be useful to others. If not, if
you
could correct it, it would be useful to even more access programmers.
Your
book is great. It covers some things, like SQL strings, that other books
do
not.

All the best,

LAF



Graham R Seach said:
[date] and [time] are reserved keywords, and you can experience problems
if
you don't bracket them.

As for using a dot for [site] - that's a mistake. For consistency, use a
bang for all.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Aloha Graham,

Could you explain why in the SQL string, site is not bracketed but date
and
time are, and Me is followed by a dot for site but by bangs for date
and
time?

Thanks,

LAF

:

Yes, you can, but I wouldn't advise it. Domain functions such as
DLookup
are
notoriosuly slow, and having 3 of them back-to-back can cause latency
problems in large tables.

I'd be more inclined to use a single recordset:
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb
sSQL = "SELECT ID FROM MyTable " & _
"WHERE site = " & Me.txtSite & _
" AND [date] = " & Me!txtDate & _
" AND [time] = " & Me!txtTime

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
'site, date and time already exist in the table
End If

rs.Close
Set rs = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Is it possible to use the DLookup function 3 times connected by AND
in
the
criteria for running a macro for data entry? The issue, I think, is
that
a
master table with an autonumbered primary key, may be part of
overall
data
entry. Say that EventID is an autonumbered field that is related to
unique
combinations of site, date, and time. If one were to enter a new
record
for
the master tables and junction table, would it not be important to
determine
if the site, date, and time were the same as a prevous record. If
so,
the
EventID of that previous record should be the EventID of the current
record
being entered. It seems that a macro with the specified condition
would
do
the trick. Thus, is it possible that DLookup could be used for
site,
date,
and time in association with macro actions? The user could enter
site,
date,
and time on an unbound form with a control to run the macro (or
code).
Click to expand...
Click to expand...
Click to expand...
 
*You* forgot a space this time - after tblBirds:
"SELECT Bandnum FROM tblBirds " & _

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

LAF said:
Hi Graham,

I get a run time error saying FROM syntax is wrong for the following:

sSQL = "SELECT Bandnum FROM tblBirds" & _
"WHERE Bandnum = """ & Me!txtBandnum & """ " & _
"AND Species = """ & Me!txtSpecies & """ " & _
"AND Leftleg = """ & Me!txtLeftleg & """ " & _
"AND Rightleg = """ & Me!Rightleg & """"

I tried modelling the statement after your example. Can you identify the
problem, and perhaps help others learn the tricks about lengthy sql
strings?
Thanks,
LAF

Graham R Seach said:
No, there are plenty of quotation marks, but I forgot to add a couple of
spaces:
sSQL = "SELECT ID FROM MyTable " & _
"WHERE site = """ & Me!txtSite & """ " & _
"AND [date] = """ & Me!txtDate & """ " & _
"AND [time] = """ & Me!txtTime & """"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

LAF said:
Hi again Graham,

I have been studying your book on Access 2003 VBA, and I have some
questions
on the sSQL string. It seems like more quotation marks are in order.
How
is
this for a complete string:

sSQL = "SELECT ID FROM MyTable " & _
"WHERE site = """ & Me!txtSite & """" & _
"AND [date] = """ & Me!txtDate & """" & _
"AND [time] = """ & Me!txtTime & """"

If this is correct full syntax, it may be useful to others. If not, if
you
could correct it, it would be useful to even more access programmers.
Your
book is great. It covers some things, like SQL strings, that other
books
do
not.

All the best,

LAF



:

[date] and [time] are reserved keywords, and you can experience
problems
if
you don't bracket them.

As for using a dot for [site] - that's a mistake. For consistency, use
a
bang for all.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Aloha Graham,

Could you explain why in the SQL string, site is not bracketed but
date
and
time are, and Me is followed by a dot for site but by bangs for date
and
time?

Thanks,

LAF

:

Yes, you can, but I wouldn't advise it. Domain functions such as
DLookup
are
notoriosuly slow, and having 3 of them back-to-back can cause
latency
problems in large tables.

I'd be more inclined to use a single recordset:
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb
sSQL = "SELECT ID FROM MyTable " & _
"WHERE site = " & Me.txtSite & _
" AND [date] = " & Me!txtDate & _
" AND [time] = " & Me!txtTime

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
'site, date and time already exist in the table
End If

rs.Close
Set rs = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Is it possible to use the DLookup function 3 times connected by
AND
in
the
criteria for running a macro for data entry? The issue, I think,
is
that
a
master table with an autonumbered primary key, may be part of
overall
data
entry. Say that EventID is an autonumbered field that is related
to
unique
combinations of site, date, and time. If one were to enter a new
record
for
the master tables and junction table, would it not be important
to
determine
if the site, date, and time were the same as a prevous record.
If
so,
the
EventID of that previous record should be the EventID of the
current
record
being entered. It seems that a macro with the specified
condition
would
do
the trick. Thus, is it possible that DLookup could be used for
site,
date,
and time in association with macro actions? The user could enter
site,
date,
and time on an unbound form with a control to run the macro (or
code).
Click to expand...
Click to expand...
Click to expand...
 
Back
Top