Recordset syntax

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

Guest

Hello all,
I have an unbound form with a list box. I only want this form to open at
startup if there will be data in the list box. So.... I figured I would run
the same SQL or ADO or DAO, and if any record(s) are found, open the form...

Here is the rowsource of the List box. Can someone help me with how I can
check for data in this SQL?

SELECT *
FROM qry_Reminders_Open
WHERE
(((qry_Reminders_Open.Reminder_Owner)=Eval("[Forms]![Opening].[EmployeeNum].Column(4)")) AND ((qry_Reminders_Open.Reminder_Status)="Open"))
ORDER BY qry_Reminders_Open.Date;

I have tried this, but get an error that it is not a valid SQL statement...

Dim sql As String

sql = "SELECT * " & _
"FROM qry_Reminders_Open " & _
"where (((qry_Reminders_Open.Reminder_Owner) =
Eval('[Forms]![Opening].[EmployeeNum].Column(4)')) And
((qry_Reminders_Open.Reminder_Status) = 'Open'));"

DoCmd.RunSQL sql

If IsNull(sql) Then
MsgBox "NULL"
Else
MsgBox "NOT NULL"
End If


Thank you in advance for your assistance.
 
you say you want the form to open at startup, yet you're referencing a form
in the VBA code - form "Opening". so is this code supposed to run from form
"Opening"? also, what is the data type of the Reminder_Owner field - Text or
Number?

once the SQL statement is fixed, you'll need to use it in a Recordset to
check for records. using DoCmd.RunSQL on a SELECT query is useless; that
command is used for running Action queries.

hth


David said:
Hello all,
I have an unbound form with a list box. I only want this form to open at
startup if there will be data in the list box. So.... I figured I would run
the same SQL or ADO or DAO, and if any record(s) are found, open the form...

Here is the rowsource of the List box. Can someone help me with how I can
check for data in this SQL?

SELECT *
FROM qry_Reminders_Open
WHERE
Click to expand...
(((qry_Reminders_Open.Reminder_Owner)=Eval("[Forms]![Opening].[EmployeeNum].
Column(4)")) AND ((qry_Reminders_Open.Reminder_Status)="Open"))
ORDER BY qry_Reminders_Open.Date;

I have tried this, but get an error that it is not a valid SQL statement...

Dim sql As String

sql = "SELECT * " & _
"FROM qry_Reminders_Open " & _
"where (((qry_Reminders_Open.Reminder_Owner) =
Eval('[Forms]![Opening].[EmployeeNum].Column(4)')) And
((qry_Reminders_Open.Reminder_Status) = 'Open'));"

DoCmd.RunSQL sql

If IsNull(sql) Then
MsgBox "NULL"
Else
MsgBox "NOT NULL"
End If


Thank you in advance for your assistance.
Click to expand...
 
Tina,
Thank you for your response.
Yes, this code is in the ON OPEN event in another form. This other form is
the MAIN MENU form for the user. If the user has any reminders, I will open
the reminders form. If not do nothing.
The Reminder_Owner field is text. Is my SQL incorrect?

I will search the newsgroup of hints on using SQL in a recordset, but if
anyone knows it well and can take a few minutes, I would appreciate it.


tina said:
you say you want the form to open at startup, yet you're referencing a form
in the VBA code - form "Opening". so is this code supposed to run from form
"Opening"? also, what is the data type of the Reminder_Owner field - Text or
Number?

once the SQL statement is fixed, you'll need to use it in a Recordset to
check for records. using DoCmd.RunSQL on a SELECT query is useless; that
command is used for running Action queries.

hth


David said:
Hello all,
I have an unbound form with a list box. I only want this form to open at
startup if there will be data in the list box. So.... I figured I would run
the same SQL or ADO or DAO, and if any record(s) are found, open the form...

Here is the rowsource of the List box. Can someone help me with how I can
check for data in this SQL?

SELECT *
FROM qry_Reminders_Open
WHERE
Click to expand...
(((qry_Reminders_Open.Reminder_Owner)=Eval("[Forms]![Opening].[EmployeeNum].
Column(4)")) AND ((qry_Reminders_Open.Reminder_Status)="Open"))
ORDER BY qry_Reminders_Open.Date;

I have tried this, but get an error that it is not a valid SQL statement...

Dim sql As String

sql = "SELECT * " & _
"FROM qry_Reminders_Open " & _
"where (((qry_Reminders_Open.Reminder_Owner) =
Eval('[Forms]![Opening].[EmployeeNum].Column(4)')) And
((qry_Reminders_Open.Reminder_Status) = 'Open'));"

DoCmd.RunSQL sql

If IsNull(sql) Then
MsgBox "NULL"
Else
MsgBox "NOT NULL"
End If


Thank you in advance for your assistance.
Click to expand...
Click to expand...
 
yes, the SQL statement is incorrect. i can tell you how to fix it, and how
to use the SQL string in a recordset to accomplish your goal - both are
reasonably simple. but first: is "MAIN MENU" the actual name of the form
that the code is running from? if so, then again you're referencing a form
called "Opening" in the SQL statement you originally posted:

....qry_Reminders_Open.Reminder_Owner)=Eval("[Forms]![Opening].[EmployeeNum].
Column(4)"))...

is this the "main menu" form? or is "Opening" the name of another form that
is already open when the main menu form's Open event runs? i can't offer a
correct SQL statement until these questions are answered.

hth


David said:
Tina,
Thank you for your response.
Yes, this code is in the ON OPEN event in another form. This other form is
the MAIN MENU form for the user. If the user has any reminders, I will open
the reminders form. If not do nothing.
The Reminder_Owner field is text. Is my SQL incorrect?

I will search the newsgroup of hints on using SQL in a recordset, but if
anyone knows it well and can take a few minutes, I would appreciate it.


tina said:
you say you want the form to open at startup, yet you're referencing a form
in the VBA code - form "Opening". so is this code supposed to run from form
"Opening"? also, what is the data type of the Reminder_Owner field - Text or
Number?

once the SQL statement is fixed, you'll need to use it in a Recordset to
check for records. using DoCmd.RunSQL on a SELECT query is useless; that
command is used for running Action queries.

hth


David said:
Hello all,
I have an unbound form with a list box. I only want this form to open at
startup if there will be data in the list box. So.... I figured I
Click to expand...
would
run
the same SQL or ADO or DAO, and if any record(s) are found, open the form...

Here is the rowsource of the List box. Can someone help me with how I can
check for data in this SQL?

SELECT *
FROM qry_Reminders_Open
WHERE
Click to expand...
(((qry_Reminders_Open.Reminder_Owner)=Eval("[Forms]![Opening].[EmployeeNum].
Column(4)")) AND ((qry_Reminders_Open.Reminder_Status)="Open"))
ORDER BY qry_Reminders_Open.Date;

I have tried this, but get an error that it is not a valid SQL statement...

Dim sql As String

sql = "SELECT * " & _
"FROM qry_Reminders_Open " & _
"where (((qry_Reminders_Open.Reminder_Owner) =
Eval('[Forms]![Opening].[EmployeeNum].Column(4)')) And
((qry_Reminders_Open.Reminder_Status) = 'Open'));"

DoCmd.RunSQL sql

If IsNull(sql) Then
MsgBox "NULL"
Else
MsgBox "NOT NULL"
End If


Thank you in advance for your assistance.
Click to expand...
Click to expand...
Click to expand...
 
Tina,
The devil is in the details, huh... Sorry about that, here is ALL the
info... FYI, the SQL works in a query grid.
Very 1st form is titled, Opening
This form is used for the user to login with ID and password. if
successful, it launches a 2nd form.
2nd form is titled, Main
My goal is in the On Open Event of 2nd form (Main), I will check if there
are any OPEN reminders for the user's name. The user's name is
[Forms]![Opening].[EmployeeNum].Column(4). I have learned that the way to
call this value in a query is to use
Eval('[Forms]![Opening].[EmployeeNum].Column(4)')
Here is the sql of qry_Reminders_Open

'**SQL for qry_Reminders_Open
SELECT tbl_Reminder.Reminder_Note AS [Note], tbl_Reminder.Reminder_Date AS
[Date], tbl_Reminder.Reminder_Owner, tbl_Reminder.Reminder_Status,
tbl_Reminder.Owner, [First Name] & " " & [Last Name] AS [Donor Name],
tbl_Reminder.[Donor ID Number], Donors.[Company Name], tbl_Reminder.ID
FROM tbl_Reminder LEFT JOIN Donors ON tbl_Reminder.[Donor ID Number] =
Donors.[Donor ID Number]
WHERE (((tbl_Reminder.Reminder_Status)="Open"))
ORDER BY tbl_Reminder.Reminder_Date;
'**End SQL

Here is my SQL to check if user has any open reminders. Works ok in Query
design grid. Trying to get it to work in VBA.
'**SQL
SELECT * FROM qry_Reminders_Open where (((qry_Reminders_Open.Reminder_Owner)
= Eval('[Forms]![Opening].[EmployeeNum].Column(4)')) And
((qry_Reminders_Open.Reminder_Status) = 'Open'));

'** End SQL



Thank you for your help.



tina said:
yes, the SQL statement is incorrect. i can tell you how to fix it, and how
to use the SQL string in a recordset to accomplish your goal - both are
reasonably simple. but first: is "MAIN MENU" the actual name of the form
that the code is running from? if so, then again you're referencing a form
called "Opening" in the SQL statement you originally posted:

....qry_Reminders_Open.Reminder_Owner)=Eval("[Forms]![Opening].[EmployeeNum].
Column(4)"))...

is this the "main menu" form? or is "Opening" the name of another form that
is already open when the main menu form's Open event runs? i can't offer a
correct SQL statement until these questions are answered.

hth


David said:
Tina,
Thank you for your response.
Yes, this code is in the ON OPEN event in another form. This other form is
the MAIN MENU form for the user. If the user has any reminders, I will open
the reminders form. If not do nothing.
The Reminder_Owner field is text. Is my SQL incorrect?

I will search the newsgroup of hints on using SQL in a recordset, but if
anyone knows it well and can take a few minutes, I would appreciate it.


tina said:
you say you want the form to open at startup, yet you're referencing a form
in the VBA code - form "Opening". so is this code supposed to run from form
"Opening"? also, what is the data type of the Reminder_Owner field - Text or
Number?

once the SQL statement is fixed, you'll need to use it in a Recordset to
check for records. using DoCmd.RunSQL on a SELECT query is useless; that
command is used for running Action queries.

hth


Hello all,
I have an unbound form with a list box. I only want this form to open at
startup if there will be data in the list box. So.... I figured I would
run
the same SQL or ADO or DAO, and if any record(s) are found, open the
form...

Here is the rowsource of the List box. Can someone help me with how I can
check for data in this SQL?

SELECT *
FROM qry_Reminders_Open
WHERE

(((qry_Reminders_Open.Reminder_Owner)=Eval("[Forms]![Opening].[EmployeeNum].
Column(4)")) AND ((qry_Reminders_Open.Reminder_Status)="Open"))
ORDER BY qry_Reminders_Open.Date;

I have tried this, but get an error that it is not a valid SQL
statement...

Dim sql As String

sql = "SELECT * " & _
"FROM qry_Reminders_Open " & _
"where (((qry_Reminders_Open.Reminder_Owner) =
Eval('[Forms]![Opening].[EmployeeNum].Column(4)')) And
((qry_Reminders_Open.Reminder_Status) = 'Open'));"

DoCmd.RunSQL sql

If IsNull(sql) Then
MsgBox "NULL"
Else
MsgBox "NOT NULL"
End If


Thank you in advance for your assistance.
Click to expand...
Click to expand...
Click to expand...
 
... FYI, the SQL works in a query grid.

i'm not surprised; but you use different syntax to refer to form and control
objects when you're writing the SQL statement in VBA. at any rate, i take it
that the SQL statement you posted (in the query called qry_Reminders_Open),
is the basis for the listbox in the unbound form you're using to display a
user's open reminder records, if s/he has any. since you already have a
query object that returns the appropriate records stored in the database,
you don't need to use a recordset at all - a simple DCount() function will
suffice. in form Main's Open event (or perhaps the Load event), try the
following, as

If Not DCount(1, "qry_Reminders_Open") < 1 Then
DoCmd.OpenForm "NameOfUnboundForm"
End If

hth


David said:
Tina,
The devil is in the details, huh... Sorry about that, here is ALL the
info... FYI, the SQL works in a query grid.
Very 1st form is titled, Opening
This form is used for the user to login with ID and password. if
successful, it launches a 2nd form.
2nd form is titled, Main
My goal is in the On Open Event of 2nd form (Main), I will check if there
are any OPEN reminders for the user's name. The user's name is
[Forms]![Opening].[EmployeeNum].Column(4). I have learned that the way to
call this value in a query is to use
Eval('[Forms]![Opening].[EmployeeNum].Column(4)')
Here is the sql of qry_Reminders_Open

'**SQL for qry_Reminders_Open
SELECT tbl_Reminder.Reminder_Note AS [Note], tbl_Reminder.Reminder_Date AS
[Date], tbl_Reminder.Reminder_Owner, tbl_Reminder.Reminder_Status,
tbl_Reminder.Owner, [First Name] & " " & [Last Name] AS [Donor Name],
tbl_Reminder.[Donor ID Number], Donors.[Company Name], tbl_Reminder.ID
FROM tbl_Reminder LEFT JOIN Donors ON tbl_Reminder.[Donor ID Number] =
Donors.[Donor ID Number]
WHERE (((tbl_Reminder.Reminder_Status)="Open"))
ORDER BY tbl_Reminder.Reminder_Date;
'**End SQL

Here is my SQL to check if user has any open reminders. Works ok in Query
design grid. Trying to get it to work in VBA.
'**SQL
SELECT * FROM qry_Reminders_Open where (((qry_Reminders_Open.Reminder_Owner)
= Eval('[Forms]![Opening].[EmployeeNum].Column(4)')) And
((qry_Reminders_Open.Reminder_Status) = 'Open'));

'** End SQL



Thank you for your help.



tina said:
yes, the SQL statement is incorrect. i can tell you how to fix it, and how
to use the SQL string in a recordset to accomplish your goal - both are
reasonably simple. but first: is "MAIN MENU" the actual name of the form
that the code is running from? if so, then again you're referencing a form
called "Opening" in the SQL statement you originally posted:
Click to expand...
.....qry_Reminders_Open.Reminder_Owner)=Eval("[Forms]![Opening].[EmployeeNum]
..
Column(4)"))...

is this the "main menu" form? or is "Opening" the name of another form that
is already open when the main menu form's Open event runs? i can't offer a
correct SQL statement until these questions are answered.

hth


David said:
Tina,
Thank you for your response.
Yes, this code is in the ON OPEN event in another form. This other
Click to expand...
form
is
the MAIN MENU form for the user. If the user has any reminders, I
Click to expand...
will
open
the reminders form. If not do nothing.
The Reminder_Owner field is text. Is my SQL incorrect?

I will search the newsgroup of hints on using SQL in a recordset, but if
anyone knows it well and can take a few minutes, I would appreciate it.


:

you say you want the form to open at startup, yet you're referencing
Click to expand...
a
form
in the VBA code - form "Opening". so is this code supposed to run
Click to expand...
from
form
"Opening"? also, what is the data type of the Reminder_Owner field - Text or
Number?

once the SQL statement is fixed, you'll need to use it in a Recordset to
check for records. using DoCmd.RunSQL on a SELECT query is useless; that
command is used for running Action queries.

hth


Hello all,
I have an unbound form with a list box. I only want this form to
Click to expand...
open
at
startup if there will be data in the list box. So.... I figured I would
run
the same SQL or ADO or DAO, and if any record(s) are found, open the
form...

Here is the rowsource of the List box. Can someone help me with
Click to expand...
how I
can
check for data in this SQL?

SELECT *
FROM qry_Reminders_Open
WHERE
Click to expand...
(((qry_Reminders_Open.Reminder_Owner)=Eval("[Forms]![Opening].[EmployeeNum].
Column(4)")) AND ((qry_Reminders_Open.Reminder_Status)="Open"))
ORDER BY qry_Reminders_Open.Date;

I have tried this, but get an error that it is not a valid SQL
statement...

Dim sql As String

sql = "SELECT * " & _
"FROM qry_Reminders_Open " & _
"where (((qry_Reminders_Open.Reminder_Owner) =
Eval('[Forms]![Opening].[EmployeeNum].Column(4)')) And
((qry_Reminders_Open.Reminder_Status) = 'Open'));"

DoCmd.RunSQL sql

If IsNull(sql) Then
MsgBox "NULL"
Else
MsgBox "NOT NULL"
End If


Thank you in advance for your assistance.
Click to expand...
Click to expand...
Click to expand...
 
Thank you tina,
This works very well.

tina said:
... FYI, the SQL works in a query grid.
Click to expand...

i'm not surprised; but you use different syntax to refer to form and control
objects when you're writing the SQL statement in VBA. at any rate, i take it
that the SQL statement you posted (in the query called qry_Reminders_Open),
is the basis for the listbox in the unbound form you're using to display a
user's open reminder records, if s/he has any. since you already have a
query object that returns the appropriate records stored in the database,
you don't need to use a recordset at all - a simple DCount() function will
suffice. in form Main's Open event (or perhaps the Load event), try the
following, as

If Not DCount(1, "qry_Reminders_Open") < 1 Then
DoCmd.OpenForm "NameOfUnboundForm"
End If

hth


David said:
Tina,
The devil is in the details, huh... Sorry about that, here is ALL the
info... FYI, the SQL works in a query grid.
Very 1st form is titled, Opening
This form is used for the user to login with ID and password. if
successful, it launches a 2nd form.
2nd form is titled, Main
My goal is in the On Open Event of 2nd form (Main), I will check if there
are any OPEN reminders for the user's name. The user's name is
[Forms]![Opening].[EmployeeNum].Column(4). I have learned that the way to
call this value in a query is to use
Eval('[Forms]![Opening].[EmployeeNum].Column(4)')
Here is the sql of qry_Reminders_Open

'**SQL for qry_Reminders_Open
SELECT tbl_Reminder.Reminder_Note AS [Note], tbl_Reminder.Reminder_Date AS
[Date], tbl_Reminder.Reminder_Owner, tbl_Reminder.Reminder_Status,
tbl_Reminder.Owner, [First Name] & " " & [Last Name] AS [Donor Name],
tbl_Reminder.[Donor ID Number], Donors.[Company Name], tbl_Reminder.ID
FROM tbl_Reminder LEFT JOIN Donors ON tbl_Reminder.[Donor ID Number] =
Donors.[Donor ID Number]
WHERE (((tbl_Reminder.Reminder_Status)="Open"))
ORDER BY tbl_Reminder.Reminder_Date;
'**End SQL

Here is my SQL to check if user has any open reminders. Works ok in Query
design grid. Trying to get it to work in VBA.
'**SQL
SELECT * FROM qry_Reminders_Open where (((qry_Reminders_Open.Reminder_Owner)
= Eval('[Forms]![Opening].[EmployeeNum].Column(4)')) And
((qry_Reminders_Open.Reminder_Status) = 'Open'));

'** End SQL



Thank you for your help.



tina said:
yes, the SQL statement is incorrect. i can tell you how to fix it, and how
to use the SQL string in a recordset to accomplish your goal - both are
reasonably simple. but first: is "MAIN MENU" the actual name of the form
that the code is running from? if so, then again you're referencing a form
called "Opening" in the SQL statement you originally posted:
Click to expand...
.....qry_Reminders_Open.Reminder_Owner)=Eval("[Forms]![Opening].[EmployeeNum]
..
Column(4)"))...

is this the "main menu" form? or is "Opening" the name of another form that
is already open when the main menu form's Open event runs? i can't offer a
correct SQL statement until these questions are answered.

hth


Tina,
Thank you for your response.
Yes, this code is in the ON OPEN event in another form. This other form
is
the MAIN MENU form for the user. If the user has any reminders, I will
open
the reminders form. If not do nothing.
The Reminder_Owner field is text. Is my SQL incorrect?

I will search the newsgroup of hints on using SQL in a recordset, but if
anyone knows it well and can take a few minutes, I would appreciate it.


:

you say you want the form to open at startup, yet you're referencing a
form
in the VBA code - form "Opening". so is this code supposed to run from
form
"Opening"? also, what is the data type of the Reminder_Owner field -
Text or
Number?

once the SQL statement is fixed, you'll need to use it in a Recordset to
check for records. using DoCmd.RunSQL on a SELECT query is useless; that
command is used for running Action queries.

hth


Hello all,
I have an unbound form with a list box. I only want this form to open
at
startup if there will be data in the list box. So.... I figured I
would
run
the same SQL or ADO or DAO, and if any record(s) are found, open the
form...

Here is the rowsource of the List box. Can someone help me with how I
can
check for data in this SQL?

SELECT *
FROM qry_Reminders_Open
WHERE


(((qry_Reminders_Open.Reminder_Owner)=Eval("[Forms]![Opening].[EmployeeNum].
Column(4)")) AND ((qry_Reminders_Open.Reminder_Status)="Open"))
ORDER BY qry_Reminders_Open.Date;

I have tried this, but get an error that it is not a valid SQL
statement...

Dim sql As String

sql = "SELECT * " & _
"FROM qry_Reminders_Open " & _
"where (((qry_Reminders_Open.Reminder_Owner) =
Eval('[Forms]![Opening].[EmployeeNum].Column(4)')) And
((qry_Reminders_Open.Reminder_Status) = 'Open'));"

DoCmd.RunSQL sql

If IsNull(sql) Then
MsgBox "NULL"
Else
MsgBox "NOT NULL"
End If


Thank you in advance for your assistance.
Click to expand...
Click to expand...
Click to expand...
 
you're welcome :)


David said:
Thank you tina,
This works very well.

tina said:
... FYI, the SQL works in a query grid.
Click to expand...

i'm not surprised; but you use different syntax to refer to form and control
objects when you're writing the SQL statement in VBA. at any rate, i take it
that the SQL statement you posted (in the query called qry_Reminders_Open),
is the basis for the listbox in the unbound form you're using to display a
user's open reminder records, if s/he has any. since you already have a
query object that returns the appropriate records stored in the database,
you don't need to use a recordset at all - a simple DCount() function will
suffice. in form Main's Open event (or perhaps the Load event), try the
following, as

If Not DCount(1, "qry_Reminders_Open") < 1 Then
DoCmd.OpenForm "NameOfUnboundForm"
End If

hth


David said:
Tina,
The devil is in the details, huh... Sorry about that, here is ALL the
info... FYI, the SQL works in a query grid.
Very 1st form is titled, Opening
This form is used for the user to login with ID and password. if
successful, it launches a 2nd form.
2nd form is titled, Main
My goal is in the On Open Event of 2nd form (Main), I will check if there
are any OPEN reminders for the user's name. The user's name is
[Forms]![Opening].[EmployeeNum].Column(4). I have learned that the way to
call this value in a query is to use
Eval('[Forms]![Opening].[EmployeeNum].Column(4)')
Here is the sql of qry_Reminders_Open

'**SQL for qry_Reminders_Open
SELECT tbl_Reminder.Reminder_Note AS [Note], tbl_Reminder.Reminder_Date AS
[Date], tbl_Reminder.Reminder_Owner, tbl_Reminder.Reminder_Status,
tbl_Reminder.Owner, [First Name] & " " & [Last Name] AS [Donor Name],
tbl_Reminder.[Donor ID Number], Donors.[Company Name], tbl_Reminder.ID
FROM tbl_Reminder LEFT JOIN Donors ON tbl_Reminder.[Donor ID Number] =
Donors.[Donor ID Number]
WHERE (((tbl_Reminder.Reminder_Status)="Open"))
ORDER BY tbl_Reminder.Reminder_Date;
'**End SQL

Here is my SQL to check if user has any open reminders. Works ok in Query
design grid. Trying to get it to work in VBA.
'**SQL
SELECT * FROM qry_Reminders_Open where (((qry_Reminders_Open.Reminder_Owner)
= Eval('[Forms]![Opening].[EmployeeNum].Column(4)')) And
((qry_Reminders_Open.Reminder_Status) = 'Open'));

'** End SQL



Thank you for your help.



:

yes, the SQL statement is incorrect. i can tell you how to fix it,
Click to expand...
and
how
to use the SQL string in a recordset to accomplish your goal - both are
reasonably simple. but first: is "MAIN MENU" the actual name of the form
that the code is running from? if so, then again you're referencing
Click to expand...
a
form
called "Opening" in the SQL statement you originally posted:
Click to expand...
Click to expand...
......qry_Reminders_Open.Reminder_Owner)=Eval("[Forms]![Opening].[EmployeeNum
]
..
Column(4)"))...

is this the "main menu" form? or is "Opening" the name of another
Click to expand...
form
that
is already open when the main menu form's Open event runs? i can't
Click to expand...
offer
a
correct SQL statement until these questions are answered.

hth


Tina,
Thank you for your response.
Yes, this code is in the ON OPEN event in another form. This
Click to expand...
other
form
is
the MAIN MENU form for the user. If the user has any reminders, I will
open
the reminders form. If not do nothing.
The Reminder_Owner field is text. Is my SQL incorrect?

I will search the newsgroup of hints on using SQL in a recordset,
Click to expand...
but
if
anyone knows it well and can take a few minutes, I would
Click to expand...
appreciate
it.
:

you say you want the form to open at startup, yet you're
Click to expand...
referencing
a
form
in the VBA code - form "Opening". so is this code supposed to
Click to expand...
run
from
form
"Opening"? also, what is the data type of the Reminder_Owner field -
Text or
Number?

once the SQL statement is fixed, you'll need to use it in a Recordset to
check for records. using DoCmd.RunSQL on a SELECT query is
Click to expand...
useless;
that
command is used for running Action queries.

hth


Hello all,
I have an unbound form with a list box. I only want this form
Click to expand...
to
open
at
startup if there will be data in the list box. So.... I figured I
would
run
the same SQL or ADO or DAO, and if any record(s) are found,
Click to expand...
open
the
form...

Here is the rowsource of the List box. Can someone help me
Click to expand...
with
how I
can
check for data in this SQL?

SELECT *
FROM qry_Reminders_Open
WHERE
Click to expand...
(((qry_Reminders_Open.Reminder_Owner)=Eval("[Forms]![Opening].[EmployeeNum].
Column(4)")) AND ((qry_Reminders_Open.Reminder_Status)="Open"))
ORDER BY qry_Reminders_Open.Date;

I have tried this, but get an error that it is not a valid SQL
statement...

Dim sql As String

sql = "SELECT * " & _
"FROM qry_Reminders_Open " & _
"where (((qry_Reminders_Open.Reminder_Owner) =
Eval('[Forms]![Opening].[EmployeeNum].Column(4)')) And
((qry_Reminders_Open.Reminder_Status) = 'Open'));"

DoCmd.RunSQL sql

If IsNull(sql) Then
MsgBox "NULL"
Else
MsgBox "NOT NULL"
End If


Thank you in advance for your assistance.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL syntax for INSERT 5
Syntax Error WHY? 16
Help with VBA sql code 2
VBA - Code stops running correctly 2
refer to a particular column in a listbox in a query 4
Requery a record in a form 2
Error with combobox.column(1) in DoCmd.runSQL 5
Validation of multiple check boxes 5

Back
Top