First, sorry for my previous answer who might have been a little short.
Contrary to a MDB file, where the requests are performed locally by the JET
engine with a full access to all variables present in the frontend; in an
ADP project, the queries are performed remotely by the SQL-Server and the
later don't know anything about what is present in your local frontend (the
ADP application running at front of you). By using the InputParameters
properties, it is possible to send - along with your request - the content
of one or more variables but when the request reach the SQL-Server, the
value of anything that is not inside the request or travelling with it is
now out of reach of the SQL-Server. This is true even when both the
frontend (the ADP application) and the SQL-Server are running on the same
machine.
In your case, you have forgottent to enclose the right parenthesis between
double quotes:
WHERE ((EmployeeTable.DeptNumber)= " & Me.[DepartmentNumber] & ")"
and if the DepartementNumber is not a number but an alphanumeric (both
letters and numbers) value or a date, you must enclose it between single
quotes (make attention to not put any blank space at the wrong place):
WHERE ((EmployeeTable.DeptNumber)= '" & Me.[DepartmentNumber] & "')"
This is because the single quote ' is used as both the string and the date
delimiter for the SQL-Server. With the right option set, it's also possible
to use the double quote " as the string/date delimiter but I do not
encourage you to do this. If there is a possibility that an alphanumeric
value will itself contains one or more single quote than you'll have to
double them:
WHERE ((EmployeeTable.LastName)= '" & Replace (Me.[LastName], "'", "''") &
"')"
As for a suggestion on how to learn VBA, I suppose that reading some
previous posts in this newsgroup and other newsgroups dedicated to Access
would be a good starting point.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
el zorro said:
Thanks. I'm having a little trouble with the syntax. I guees the VBA code
for
the AfterUpdate event should look SOPMETHING like this, where the
Department
list box is where the user selects a department number, and the Employee
list
box shows the corresponding employees:
Private Sub DepartmentNumber_AfterUpdate()
ListBoxEmployee.RowSource = "SELECT EmployeeTable.EmplNumber,
EmployeeTable.EmplLastName
FROM EmployeeTable
WHERE ((EmployeeTable.DeptNumber)= " & Me.[DepartmentNumber])
End Sub
But I am missing something, Do I need to do a Dim statement first, or a
QueryDef... can you help? And I always have a problem with the quotation
marks and the ampersands (can you recommend a website or book I should
look
at?).
Thanks!
Sylvain Lafontaine said:
Right now in the mdb version, the row source for the Employee table is
a
select query, and it reads the Department from the Form. Are you saying
that
for the adp version I should move the statement to VBA code in the
AfterUpdate event of the first box?
Yes.
Another possibility would be to use the InputParameters property but the
list of parameters used by the control (the combobox here) must be the
same
or a subset of the parameters used for the Record Source of the form.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Right now in the mdb version, the row source for the Employee table is
a
select query, and it reads the Department from the Form. Are you saying
that
for the adp version I should move the statement to VBA code in the
AfterUpdate event of the first box?
:
The easiest way of doing this would be to simply rebuild the record
source
of your second combox after the first combox is updated (using its
OnUpdate
event):
ComboEmployee.RowSource = "Select ... Where Employee.Dept=" &
Me.Department
Of course, if Departement is not a number but an alphanumerical, you
must
enclose its value between single quote. You don't have to make a
requery
after changing the rowsource of the combobox because a requery is
already
automatically made by Access when you change the rowsource.
There are other ways of transmitting a parameter to SQL-Server but
this
one
is the easiest way.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
I have 2 linked list boxes on a form. One lists Departments, and the
second
shows the corresponding Employees of the selected Department. In the
Acccess
.mdb version, When a department is selected from the first list, the
After
Update event runs VBA code that requeries the second list. When the
Employee
list is requeried, it reads the Department selected from the first
list
and
uses it as a query parameter, so that the Employee list shows only
the
employees in the selected Department: WHERE
(((Employee.Dept)=[Department]))
-
FOr my conversion of the form to the adp version linked to SQL
Server,
the
two lists are NOT linked because (apparently) the SQL statement for
the
Employee list is clueless about what Department is selected on the
form.
-
SO I'm thinking that I need to supply the selected Department to the
query
(View) the underlies the Employee list via the VBA code that
requeries
the
list. SOmething that says: After the Department list is updated,
requery
the
Employee list using the selected Department as a criteria.
-
Anybody know how I go about that? Thanks!
