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
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.
