ADP Help

  • Thread starter Thread starter Randel Bjorkquist
  • Start date Start date
R

Randel Bjorkquist

Hello,

This is my first attempt to try and migrate an existing Access database (a
standard Access database mdb file) to an Access Project adp file. All data
transfer, table, view, stored procedure and function creation aside, I'm
having problems with a form that contains two combo boxes (an employee list
and a shift list) and one check box (terminated). The Shift combo box used
a value list and I used the query defined below fill in the Employee combo
box.

SELECT
EmployeeID,
LastName & [, ] & FirstName & [ ] & MiddleInt AS Employee

FROM
tbl_Employee

WHERE
Terminated = [Forms]![frm_EmployeeSelector]![chk_Terminated]
AND
Shift = [Forms]![frm_EmployeeSelector]![cbo_Shifts]

ORDER BY
LastName & [, ] & FirstName & [ ] & MiddleInt ASC

What I get is an error stating that it doesn't like the "!" signs and
basically I need to re-write my code to use the Input Parameters. I do not
have any Row Source assigned to the form, I don't need any to be or at least
I didn't with the mdb format. All I'm trying to do here is to filter out
all unwanted employeesloaded in. I want to be able to display in the
Employee combo box, all employees for 1st shift (Shift combo box set to 1),
that are not terminated (the Terminated check box not checked). Can someone
help me here?

Thanks in advance,

Randel Bjorkquist
 
Randel

Assume the name of your employee pick list form field is cbxEmployees. Add
this code to OnCurrent event handler for the form (the SQL is standard
MSSQL):

Dim strEmplSource As String
strEmplSource = "SELECT " & _
" EmployeeID, " & _
" LastName + ', ' + FirstName + ' ' + MiddleInit AS
Employee " & _
" FROM tbl_Employee " & _
" WHERE Terminated = " &
[Forms]![frm_EmployeeSelector]![chk_Terminated] & _
" AND Shift = " &
[Forms]![frm_EmployeeSelector]![cbo_Shifts] & ";"
cbxEmployees.RowSource = strEmplSource

If either of these fields contain non-numeric data, simply add the
apostrophes to the constant, e.g.

" AND Shift = '" & [Forms]![frm_EmployeeSelector]![cbo_Shifts] & "';"

HTH,
Joe De Moor
 
Hey Joe,

Thanks for the help. You put me onto the right path. I'm actually ended up
putting the code in the OnEnter event of the Combo Box. I am now just
starting to run into more issues with how my old queries were build to how
they are now on the SQL Server.

An example of what I mean is this. I have a parameter query that uses a
Start and Stop date from the main form. I use that query as a base query
for almost all of my reports. Access, if the query was written correct as
shown below in the "WHERE" clause, would automatically fill in the
parameters, but now I'll have to figure out a better/different way of doing
this. If you have any ideas, I'll be more then happy to hear them.

:
WHERE
[tbl_Employee].[Terminated] =
[Forms]![frm_EmployeeSelector]![chk_Terminated]
AND
[tbl_Employee].[Shift] = [Forms]![frm_EmployeeSelector]![cbo_Shifts]
:

I hope you understand and again thank you for your help,

Randel Bjorkquist
 
If you are using parameterized queries for your reports, add the following
to the input parameters property:

@chk_Terminated datetime = Forms!frm_EmployeeSelector!chk_Terminated,
@cbo_Shifts datetime = Forms!frm_EmployeeSelector!cbo_Shifts

Replace the comma "," with whatever is good if you have set up the list
separator to something else in the regional settings of your computer (this
is often the semi-comma).

You can also set up a Server Filter but I don't use them personally;
however, it might be a good solution in your case.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Randel Bjorkquist said:
Hey Joe,

Thanks for the help. You put me onto the right path. I'm actually ended
up putting the code in the OnEnter event of the Combo Box. I am now just
starting to run into more issues with how my old queries were build to how
they are now on the SQL Server.

An example of what I mean is this. I have a parameter query that uses a
Start and Stop date from the main form. I use that query as a base query
for almost all of my reports. Access, if the query was written correct as
shown below in the "WHERE" clause, would automatically fill in the
parameters, but now I'll have to figure out a better/different way of
doing this. If you have any ideas, I'll be more then happy to hear them.

:
WHERE
[tbl_Employee].[Terminated] =
[Forms]![frm_EmployeeSelector]![chk_Terminated]
AND
[tbl_Employee].[Shift] = [Forms]![frm_EmployeeSelector]![cbo_Shifts]
:

I hope you understand and again thank you for your help,

Randel Bjorkquist


J. M. De Moor said:
Randel

Assume the name of your employee pick list form field is cbxEmployees.
Add
this code to OnCurrent event handler for the form (the SQL is standard
MSSQL):

Dim strEmplSource As String
strEmplSource = "SELECT " & _
" EmployeeID, " & _
" LastName + ', ' + FirstName + ' ' + MiddleInit AS
Employee " & _
" FROM tbl_Employee " & _
" WHERE Terminated = " &
[Forms]![frm_EmployeeSelector]![chk_Terminated] & _
" AND Shift = " &
[Forms]![frm_EmployeeSelector]![cbo_Shifts] & ";"
cbxEmployees.RowSource = strEmplSource

If either of these fields contain non-numeric data, simply add the
apostrophes to the constant, e.g.

" AND Shift = '" & [Forms]![frm_EmployeeSelector]![cbo_Shifts] &
"';"

HTH,
Joe De Moor
Click to expand...
Click to expand...
 
Randel
I'm actually ended up
putting the code in the OnEnter event of the Combo Box.
Click to expand...

If that works for you, great. I choose OnCurrent so that it only happens
once per time the content of form's underlying row changes. OnEnter would
rebuild every time you use the pick list. There are obviously pros and cons
to each way, but I find with ADO, you have to think about performance.
I have a parameter query that uses a
Start and Stop date from the main form. I use that query as a base query
for almost all of my reports. Access, if the query was written correct as
shown below in the "WHERE" clause, would automatically fill in the
parameters, but now I'll have to figure out a better/different way of doing
this. If you have any ideas, I'll be more then happy to hear them.
Click to expand...

Sylvain's approach (see his post in this thread) is the most
straight-forward way. However, since your query is used so extensively, you
might also consider using a stored procedure, especially if the criteria
were always on the same few columns. The ADO help has good write-ups on
setting up parameters that way.

Joe
 
i still believe that you can do all of this without any
inputParameters; at least i've done it this way before.

a) if your sproc is lookig for parameter @PLU it will auto-resolve to a
control named PLU
b) if your sproc is looking for a parameter @txtPLU it will auto
resolve to a control named txtPLU

you get the picture?

i hope that is still the case; i haven't had to do anything along those
lines in a while i only write sprocs and all that anymore

-Aaron
 
Joe,

I did finally understand, using the OnCurrent event, what you were trying.
Unfortunately, I did not thoroughly explain what I was trying to do with
this form. So just as an FYI, the form I was talking about,
"frm_EmployeeSelector", is simply used to retrieve an Employee's ID, I have
no data source assigned to the form. The returned ID is then used to fill
"EmployeeID" parameters of any query used by an employee based report. My
goal was to create a simple user interface, that did not display/list
unnecessarily hundreds of employee names. This is where the "cbo_Shifts"
combo box and "chk_Terminated" check box help filter down the list. Sorry
about the confusion, I didn't think to add all this information and also
didn't want to add unnecessary information.

But again, thanks for the help.

Randel
 
Hello Sylvain,

Thanks for the information; I'll give it a try. However, I'm still a little
bit confused here. Just to help clarify something here, I think I may need
to give you a bit more information. The form I've been talking about has no
data source assigned to it. The form is only used to create a simple
employee selection form, selecting a single employee from the combo box,
"cbo_Employee". I was using the check box "chk_Terminated" and combo box
"cbo_Shifts" to filter down the number of employee names that were
displayed/listed.

Back to you suggestion. When I've try referencing a control on a from, like
"Forms!frm_EmployeeSelector!chk_Terminated", I get an error saying something
like the SQL statement can not understand/contain the "!" and that I should
set the parameter values another way. But my question is this, if all the
SQL statements are on the SQL Server, how would the SQL statement know how
to resolve the form referencing? How would it know what
"Forms!frm_EmployeeSelector!chk_Terminated" is? Does that make sense?

I'm looking forward to your reply,

Randel

Sylvain Lafontaine said:
If you are using parameterized queries for your reports, add the following
to the input parameters property:

@chk_Terminated datetime = Forms!frm_EmployeeSelector!chk_Terminated,
@cbo_Shifts datetime = Forms!frm_EmployeeSelector!cbo_Shifts

Replace the comma "," with whatever is good if you have set up the list
separator to something else in the regional settings of your computer
(this is often the semi-comma).

You can also set up a Server Filter but I don't use them personally;
however, it might be a good solution in your case.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Randel Bjorkquist said:
Hey Joe,

Thanks for the help. You put me onto the right path. I'm actually ended
up putting the code in the OnEnter event of the Combo Box. I am now just
starting to run into more issues with how my old queries were build to
how they are now on the SQL Server.

An example of what I mean is this. I have a parameter query that uses a
Start and Stop date from the main form. I use that query as a base query
for almost all of my reports. Access, if the query was written correct
as shown below in the "WHERE" clause, would automatically fill in the
parameters, but now I'll have to figure out a better/different way of
doing this. If you have any ideas, I'll be more then happy to hear them.

:
WHERE
[tbl_Employee].[Terminated] =
[Forms]![frm_EmployeeSelector]![chk_Terminated]
AND
[tbl_Employee].[Shift] = [Forms]![frm_EmployeeSelector]![cbo_Shifts]
:

I hope you understand and again thank you for your help,

Randel Bjorkquist


J. M. De Moor said:
Randel

Assume the name of your employee pick list form field is cbxEmployees.
Add
this code to OnCurrent event handler for the form (the SQL is standard
MSSQL):

Dim strEmplSource As String
strEmplSource = "SELECT " & _
" EmployeeID, " & _
" LastName + ', ' + FirstName + ' ' + MiddleInit AS
Employee " & _
" FROM tbl_Employee " & _
" WHERE Terminated = " &
[Forms]![frm_EmployeeSelector]![chk_Terminated] & _
" AND Shift = " &
[Forms]![frm_EmployeeSelector]![cbo_Shifts] & ";"
cbxEmployees.RowSource = strEmplSource

If either of these fields contain non-numeric data, simply add the
apostrophes to the constant, e.g.

" AND Shift = '" & [Forms]![frm_EmployeeSelector]![cbo_Shifts] &
"';"

HTH,
Joe De Moor
Click to expand...
Click to expand...
Click to expand...
 
You're right, the the SQL-Server doesn't know anything about «
Forms!frm_EmployeeSelector!chk_Terminated ». This is to be used on the
client side only, inside the property "Input Parameters" of a form or report
and in association with a SQL-Server parameter for a stored procedure:

Me.InputParameters = "@ComboDate1 datetime = Forms!f_Rp!ComboDate1, ..... "

And the stored procedure would be something like:

CREATE PROCEDURE dbo.MyStoredProcedure (@ComboDate1 datetime)
AS
Select * from A where A.date1 = @ComboDate1
GO


You cannot use ADP if you don't know T-SQL. Besides SP, you can also use
Views and User Defined Functions (UDF) but you cannot pass them parameters
from ADP; so you must create the whole string as the record source (either
literaly or using the operator &), for example:

Me.RecordSource = "Select * from A where A.Date = '2005/01/01' "

or:
Me.RecordSource = "Select * from MyUDF ('2005/01/01') "

or:
Me.RecordSource = "exec dbo.MyStoredProcedure '2005/01/01' "

or:
Me.RecordSource = "exec dbo.MyStoredProcedure '" & MyDate & "' "

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Randel Bjorkquist said:
Hello Sylvain,

Thanks for the information; I'll give it a try. However, I'm still a
little bit confused here. Just to help clarify something here, I think I
may need to give you a bit more information. The form I've been talking
about has no data source assigned to it. The form is only used to create
a simple employee selection form, selecting a single employee from the
combo box, "cbo_Employee". I was using the check box "chk_Terminated" and
combo box "cbo_Shifts" to filter down the number of employee names that
were displayed/listed.

Back to you suggestion. When I've try referencing a control on a from,
like "Forms!frm_EmployeeSelector!chk_Terminated", I get an error saying
something like the SQL statement can not understand/contain the "!" and
that I should set the parameter values another way. But my question is
this, if all the SQL statements are on the SQL Server, how would the SQL
statement know how to resolve the form referencing? How would it know
what "Forms!frm_EmployeeSelector!chk_Terminated" is? Does that make
sense?

I'm looking forward to your reply,

Randel

Sylvain Lafontaine said:
If you are using parameterized queries for your reports, add the
following to the input parameters property:

@chk_Terminated datetime = Forms!frm_EmployeeSelector!chk_Terminated,
@cbo_Shifts datetime = Forms!frm_EmployeeSelector!cbo_Shifts

Replace the comma "," with whatever is good if you have set up the list
separator to something else in the regional settings of your computer
(this is often the semi-comma).

You can also set up a Server Filter but I don't use them personally;
however, it might be a good solution in your case.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Randel Bjorkquist said:
Hey Joe,

Thanks for the help. You put me onto the right path. I'm actually
ended up putting the code in the OnEnter event of the Combo Box. I am
now just starting to run into more issues with how my old queries were
build to how they are now on the SQL Server.

An example of what I mean is this. I have a parameter query that uses a
Start and Stop date from the main form. I use that query as a base
query for almost all of my reports. Access, if the query was written
correct as shown below in the "WHERE" clause, would automatically fill
in the parameters, but now I'll have to figure out a better/different
way of doing this. If you have any ideas, I'll be more then happy to
hear them.

:
WHERE
[tbl_Employee].[Terminated] =
[Forms]![frm_EmployeeSelector]![chk_Terminated]
AND
[tbl_Employee].[Shift] = [Forms]![frm_EmployeeSelector]![cbo_Shifts]
:

I hope you understand and again thank you for your help,

Randel Bjorkquist


Randel

Assume the name of your employee pick list form field is cbxEmployees.
Add
this code to OnCurrent event handler for the form (the SQL is standard
MSSQL):

Dim strEmplSource As String
strEmplSource = "SELECT " & _
" EmployeeID, " & _
" LastName + ', ' + FirstName + ' ' + MiddleInit AS
Employee " & _
" FROM tbl_Employee " & _
" WHERE Terminated = " &
[Forms]![frm_EmployeeSelector]![chk_Terminated] & _
" AND Shift = " &
[Forms]![frm_EmployeeSelector]![cbo_Shifts] & ";"
cbxEmployees.RowSource = strEmplSource

If either of these fields contain non-numeric data, simply add the
apostrophes to the constant, e.g.

" AND Shift = '" & [Forms]![frm_EmployeeSelector]![cbo_Shifts] &
"';"

HTH,
Joe De Moor
Click to expand...
Click to expand...
Click to expand...
 
Sylvain,

Okay, I don't think you have said anything that surprises me or that I don't
understand. But I still have a question. Lets say I have two queries as
defined below

-------------------------------------------------------------
Query 1 (Saved as "Query1")
SELECT
ALL

FROM
tbl_Order

WHERE
tbl_Order.OrderDate
BETWEEN Forms!frm_OrderLookup!dtp_StartDate
AND Forms!frm_OrderLookup!dtp_EndDate

-------------------------------------------------------------
Query 2 (Saved as "Query2")
SELECT
tbl_Customer.CustomerName,
tbl_Order.OrderDate

FROM
tbl_Customer
INNER JOIN Query1
ON tbl_Customer.CustomerID = Query1.OrderDate

-------------------------------------------------------------
As you can Query2 uses Query1. How does the to parameters of Query1 get
set? I know this is an extremely easy query, but its the concept that I
just cant seem to graps.

I look forward to hearing back from you soon,

Randel


Sylvain Lafontaine said:
You're right, the the SQL-Server doesn't know anything about «
Forms!frm_EmployeeSelector!chk_Terminated ». This is to be used on the
client side only, inside the property "Input Parameters" of a form or
report and in association with a SQL-Server parameter for a stored
procedure:

Me.InputParameters = "@ComboDate1 datetime = Forms!f_Rp!ComboDate1, .....
"

And the stored procedure would be something like:

CREATE PROCEDURE dbo.MyStoredProcedure (@ComboDate1 datetime)
AS
Select * from A where A.date1 = @ComboDate1
GO


You cannot use ADP if you don't know T-SQL. Besides SP, you can also use
Views and User Defined Functions (UDF) but you cannot pass them parameters
from ADP; so you must create the whole string as the record source (either
literaly or using the operator &), for example:

Me.RecordSource = "Select * from A where A.Date = '2005/01/01' "

or:
Me.RecordSource = "Select * from MyUDF ('2005/01/01') "

or:
Me.RecordSource = "exec dbo.MyStoredProcedure '2005/01/01' "

or:
Me.RecordSource = "exec dbo.MyStoredProcedure '" & MyDate & "' "

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Randel Bjorkquist said:
Hello Sylvain,

Thanks for the information; I'll give it a try. However, I'm still a
little bit confused here. Just to help clarify something here, I think I
may need to give you a bit more information. The form I've been talking
about has no data source assigned to it. The form is only used to create
a simple employee selection form, selecting a single employee from the
combo box, "cbo_Employee". I was using the check box "chk_Terminated"
and combo box "cbo_Shifts" to filter down the number of employee names
that were displayed/listed.

Back to you suggestion. When I've try referencing a control on a from,
like "Forms!frm_EmployeeSelector!chk_Terminated", I get an error saying
something like the SQL statement can not understand/contain the "!" and
that I should set the parameter values another way. But my question is
this, if all the SQL statements are on the SQL Server, how would the SQL
statement know how to resolve the form referencing? How would it know
what "Forms!frm_EmployeeSelector!chk_Terminated" is? Does that make
sense?

I'm looking forward to your reply,

Randel

Sylvain Lafontaine said:
If you are using parameterized queries for your reports, add the
following to the input parameters property:

@chk_Terminated datetime = Forms!frm_EmployeeSelector!chk_Terminated,
@cbo_Shifts datetime = Forms!frm_EmployeeSelector!cbo_Shifts

Replace the comma "," with whatever is good if you have set up the list
separator to something else in the regional settings of your computer
(this is often the semi-comma).

You can also set up a Server Filter but I don't use them personally;
however, it might be a good solution in your case.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Hey Joe,

Thanks for the help. You put me onto the right path. I'm actually
ended up putting the code in the OnEnter event of the Combo Box. I am
now just starting to run into more issues with how my old queries were
build to how they are now on the SQL Server.

An example of what I mean is this. I have a parameter query that uses
a Start and Stop date from the main form. I use that query as a base
query for almost all of my reports. Access, if the query was written
correct as shown below in the "WHERE" clause, would automatically fill
in the parameters, but now I'll have to figure out a better/different
way of doing this. If you have any ideas, I'll be more then happy to
hear them.

:
WHERE
[tbl_Employee].[Terminated] =
[Forms]![frm_EmployeeSelector]![chk_Terminated]
AND
[tbl_Employee].[Shift] = [Forms]![frm_EmployeeSelector]![cbo_Shifts]
:

I hope you understand and again thank you for your help,

Randel Bjorkquist


Randel

Assume the name of your employee pick list form field is cbxEmployees.
Add
this code to OnCurrent event handler for the form (the SQL is standard
MSSQL):

Dim strEmplSource As String
strEmplSource = "SELECT " & _
" EmployeeID, " & _
" LastName + ', ' + FirstName + ' ' + MiddleInit
AS
Employee " & _
" FROM tbl_Employee " & _
" WHERE Terminated = " &
[Forms]![frm_EmployeeSelector]![chk_Terminated] & _
" AND Shift = " &
[Forms]![frm_EmployeeSelector]![cbo_Shifts] & ";"
cbxEmployees.RowSource = strEmplSource

If either of these fields contain non-numeric data, simply add the
apostrophes to the constant, e.g.

" AND Shift = '" & [Forms]![frm_EmployeeSelector]![cbo_Shifts] &
"';"

HTH,
Joe De Moor
Click to expand...
Click to expand...
Click to expand...
 
Randel
As you can Query2 uses Query1. How does the to parameters of Query1 get
set?
Click to expand...

You can use Query1 as a subquery in Query2 making a single query or, if
Query1 is used in a number of other queries, you can setup a parameter table
(a table with a row that holds only your selection criteria) and set up a
View that accesses that table for its criteria.
FROM
tbl_Customer
INNER JOIN Query1
ON tbl_Customer.CustomerID = Query1.OrderDate
Click to expand...

I don't think you intended to JOIN ON CustomerID = OrderDate, huh? (hehe)

Joe
 
Well, the location for setting the record source and refreshing the queries
when necessary will depends heavily on the overall design of the form and
the method that you will use for passing the parameters. If you are using
the InputParameters method, there is nothing to be do when the opening the
form but you will have to requery the second combobox when the first value
for the first one will change. This is usually done on the OnChange event
of the first combobox:

Private Sub Combo1_AfterUpdate()
Me.Combo2.Requery
Call Combo2_AfterUpdate
End Sub


If you building the query string yourself using the operator &, then you
don't have to make a call to the Requery method as the control is
automatically refreshed when its data source is changed. Notice that the
name of the data source for controls is RowSource and not RecordSource as it
is for forms:

Private Sub Combo1_AfterUpdate()
Me.Combo2.RowSource = "exec storedprocedure1 " & Combo1.Value & .......
Call Combo2_AfterUpdate
End Sub

Notice that the new value of the first combobox is used to change the row
source of the second combobox and then its AfterUpdate event is called.

If you also want to build the the record source string for the form instead
of using the InputParameters property, then this is usually done in the
OnOpen event of the form or in the AfterUpdate event of the last ComboBox to
be called. If you have subforms that you also want to change, then usually
you will do it in the OnCurrent event of the main form.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Randel Bjorkquist said:
Sylvain,

Okay, I don't think you have said anything that surprises me or that I
don't
understand. But I still have a question. Lets say I have two queries as
defined below

-------------------------------------------------------------
Query 1 (Saved as "Query1")
SELECT
ALL

FROM
tbl_Order

WHERE
tbl_Order.OrderDate
BETWEEN Forms!frm_OrderLookup!dtp_StartDate
AND Forms!frm_OrderLookup!dtp_EndDate

-------------------------------------------------------------
Query 2 (Saved as "Query2")
SELECT
tbl_Customer.CustomerName,
tbl_Order.OrderDate

FROM
tbl_Customer
INNER JOIN Query1
ON tbl_Customer.CustomerID = Query1.OrderDate

-------------------------------------------------------------
As you can Query2 uses Query1. How does the to parameters of Query1 get
set? I know this is an extremely easy query, but its the concept that I
just cant seem to graps.

I look forward to hearing back from you soon,

Randel


Sylvain Lafontaine said:
You're right, the the SQL-Server doesn't know anything about «
Forms!frm_EmployeeSelector!chk_Terminated ». This is to be used on the
client side only, inside the property "Input Parameters" of a form or
report and in association with a SQL-Server parameter for a stored
procedure:

Me.InputParameters = "@ComboDate1 datetime = Forms!f_Rp!ComboDate1, .....
"

And the stored procedure would be something like:

CREATE PROCEDURE dbo.MyStoredProcedure (@ComboDate1 datetime)
AS
Select * from A where A.date1 = @ComboDate1
GO


You cannot use ADP if you don't know T-SQL. Besides SP, you can also use
Views and User Defined Functions (UDF) but you cannot pass them
parameters
from ADP; so you must create the whole string as the record source
(either
literaly or using the operator &), for example:

Me.RecordSource = "Select * from A where A.Date = '2005/01/01' "

or:
Me.RecordSource = "Select * from MyUDF ('2005/01/01') "

or:
Me.RecordSource = "exec dbo.MyStoredProcedure '2005/01/01' "

or:
Me.RecordSource = "exec dbo.MyStoredProcedure '" & MyDate & "' "

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Randel Bjorkquist said:
Hello Sylvain,

Thanks for the information; I'll give it a try. However, I'm still a
little bit confused here. Just to help clarify something here, I think
I
may need to give you a bit more information. The form I've been talking
about has no data source assigned to it. The form is only used to
create
a simple employee selection form, selecting a single employee from the
combo box, "cbo_Employee". I was using the check box "chk_Terminated"
and combo box "cbo_Shifts" to filter down the number of employee names
that were displayed/listed.

Back to you suggestion. When I've try referencing a control on a from,
like "Forms!frm_EmployeeSelector!chk_Terminated", I get an error saying
something like the SQL statement can not understand/contain the "!" and
that I should set the parameter values another way. But my question is
this, if all the SQL statements are on the SQL Server, how would the SQL
statement know how to resolve the form referencing? How would it know
what "Forms!frm_EmployeeSelector!chk_Terminated" is? Does that make
sense?

I'm looking forward to your reply,

Randel

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message If you are using parameterized queries for your reports, add the
following to the input parameters property:

@chk_Terminated datetime = Forms!frm_EmployeeSelector!chk_Terminated,
@cbo_Shifts datetime = Forms!frm_EmployeeSelector!cbo_Shifts

Replace the comma "," with whatever is good if you have set up the list
separator to something else in the regional settings of your computer
(this is often the semi-comma).

You can also set up a Server Filter but I don't use them personally;
however, it might be a good solution in your case.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Hey Joe,

Thanks for the help. You put me onto the right path. I'm actually
ended up putting the code in the OnEnter event of the Combo Box. I am
now just starting to run into more issues with how my old queries were
build to how they are now on the SQL Server.

An example of what I mean is this. I have a parameter query that uses
a Start and Stop date from the main form. I use that query as a base
query for almost all of my reports. Access, if the query was written
correct as shown below in the "WHERE" clause, would automatically fill
in the parameters, but now I'll have to figure out a better/different
way of doing this. If you have any ideas, I'll be more then happy to
hear them.

:
WHERE
[tbl_Employee].[Terminated] =
[Forms]![frm_EmployeeSelector]![chk_Terminated]
AND
[tbl_Employee].[Shift] = [Forms]![frm_EmployeeSelector]![cbo_Shifts]
:

I hope you understand and again thank you for your help,

Randel Bjorkquist


Randel

Assume the name of your employee pick list form field is
cbxEmployees.
Add
this code to OnCurrent event handler for the form (the SQL is
standard
MSSQL):

Dim strEmplSource As String
strEmplSource = "SELECT " & _
" EmployeeID, " & _
" LastName + ', ' + FirstName + ' ' + MiddleInit
AS
Employee " & _
" FROM tbl_Employee " & _
" WHERE Terminated = " &
[Forms]![frm_EmployeeSelector]![chk_Terminated] & _
" AND Shift = " &
[Forms]![frm_EmployeeSelector]![cbo_Shifts] & ";"
cbxEmployees.RowSource = strEmplSource

If either of these fields contain non-numeric data, simply add the
apostrophes to the constant, e.g.

" AND Shift = '" & [Forms]![frm_EmployeeSelector]![cbo_Shifts] &
"';"

HTH,
Joe De Moor
Click to expand...
Click to expand...
Click to expand...
 
Hey Joe,

Yep, you are correct, I think it would be hard to tie those to attributes
together. The weekend is just about here, so I'll look into this a bit next
week. But thanks for the information.

Randel
 
Sylvain,

Thanks for the information. I think I might be starting to understand. But
the weekend is just about here, and my work has me punching a clock, so I'll
look at it some more next week.

Thanks again,

Randel

Sylvain Lafontaine said:
Well, the location for setting the record source and refreshing the
queries when necessary will depends heavily on the overall design of the
form and the method that you will use for passing the parameters. If you
are using the InputParameters method, there is nothing to be do when the
opening the form but you will have to requery the second combobox when the
first value for the first one will change. This is usually done on the
OnChange event of the first combobox:

Private Sub Combo1_AfterUpdate()
Me.Combo2.Requery
Call Combo2_AfterUpdate
End Sub


If you building the query string yourself using the operator &, then you
don't have to make a call to the Requery method as the control is
automatically refreshed when its data source is changed. Notice that the
name of the data source for controls is RowSource and not RecordSource as
it is for forms:

Private Sub Combo1_AfterUpdate()
Me.Combo2.RowSource = "exec storedprocedure1 " & Combo1.Value & .......
Call Combo2_AfterUpdate
End Sub

Notice that the new value of the first combobox is used to change the row
source of the second combobox and then its AfterUpdate event is called.

If you also want to build the the record source string for the form
instead of using the InputParameters property, then this is usually done
in the OnOpen event of the form or in the AfterUpdate event of the last
ComboBox to be called. If you have subforms that you also want to change,
then usually you will do it in the OnCurrent event of the main form.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Randel Bjorkquist said:
Sylvain,

Okay, I don't think you have said anything that surprises me or that I
don't
understand. But I still have a question. Lets say I have two queries as
defined below

-------------------------------------------------------------
Query 1 (Saved as "Query1")
SELECT
ALL

FROM
tbl_Order

WHERE
tbl_Order.OrderDate
BETWEEN Forms!frm_OrderLookup!dtp_StartDate
AND Forms!frm_OrderLookup!dtp_EndDate

-------------------------------------------------------------
Query 2 (Saved as "Query2")
SELECT
tbl_Customer.CustomerName,
tbl_Order.OrderDate

FROM
tbl_Customer
INNER JOIN Query1
ON tbl_Customer.CustomerID = Query1.OrderDate

-------------------------------------------------------------
As you can Query2 uses Query1. How does the to parameters of Query1 get
set? I know this is an extremely easy query, but its the concept that I
just cant seem to graps.

I look forward to hearing back from you soon,

Randel


Sylvain Lafontaine said:
You're right, the the SQL-Server doesn't know anything about «
Forms!frm_EmployeeSelector!chk_Terminated ». This is to be used on the
client side only, inside the property "Input Parameters" of a form or
report and in association with a SQL-Server parameter for a stored
procedure:

Me.InputParameters = "@ComboDate1 datetime = Forms!f_Rp!ComboDate1,
.....
"

And the stored procedure would be something like:

CREATE PROCEDURE dbo.MyStoredProcedure (@ComboDate1 datetime)
AS
Select * from A where A.date1 = @ComboDate1
GO


You cannot use ADP if you don't know T-SQL. Besides SP, you can also
use
Views and User Defined Functions (UDF) but you cannot pass them
parameters
from ADP; so you must create the whole string as the record source
(either
literaly or using the operator &), for example:

Me.RecordSource = "Select * from A where A.Date = '2005/01/01' "

or:
Me.RecordSource = "Select * from MyUDF ('2005/01/01') "

or:
Me.RecordSource = "exec dbo.MyStoredProcedure '2005/01/01' "

or:
Me.RecordSource = "exec dbo.MyStoredProcedure '" & MyDate & "' "

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Hello Sylvain,

Thanks for the information; I'll give it a try. However, I'm still a
little bit confused here. Just to help clarify something here, I think
I
may need to give you a bit more information. The form I've been
talking
about has no data source assigned to it. The form is only used to
create
a simple employee selection form, selecting a single employee from the
combo box, "cbo_Employee". I was using the check box "chk_Terminated"
and combo box "cbo_Shifts" to filter down the number of employee names
that were displayed/listed.

Back to you suggestion. When I've try referencing a control on a from,
like "Forms!frm_EmployeeSelector!chk_Terminated", I get an error saying
something like the SQL statement can not understand/contain the "!" and
that I should set the parameter values another way. But my question is
this, if all the SQL statements are on the SQL Server, how would the
SQL
statement know how to resolve the form referencing? How would it know
what "Forms!frm_EmployeeSelector!chk_Terminated" is? Does that make
sense?

I'm looking forward to your reply,

Randel

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message If you are using parameterized queries for your reports, add the
following to the input parameters property:

@chk_Terminated datetime = Forms!frm_EmployeeSelector!chk_Terminated,
@cbo_Shifts datetime = Forms!frm_EmployeeSelector!cbo_Shifts

Replace the comma "," with whatever is good if you have set up the
list
separator to something else in the regional settings of your computer
(this is often the semi-comma).

You can also set up a Server Filter but I don't use them personally;
however, it might be a good solution in your case.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Hey Joe,

Thanks for the help. You put me onto the right path. I'm actually
ended up putting the code in the OnEnter event of the Combo Box. I
am
now just starting to run into more issues with how my old queries
were
build to how they are now on the SQL Server.

An example of what I mean is this. I have a parameter query that
uses
a Start and Stop date from the main form. I use that query as a base
query for almost all of my reports. Access, if the query was written
correct as shown below in the "WHERE" clause, would automatically
fill
in the parameters, but now I'll have to figure out a better/different
way of doing this. If you have any ideas, I'll be more then happy to
hear them.

:
WHERE
[tbl_Employee].[Terminated] =
[Forms]![frm_EmployeeSelector]![chk_Terminated]
AND
[tbl_Employee].[Shift] = [Forms]![frm_EmployeeSelector]![cbo_Shifts]
:

I hope you understand and again thank you for your help,

Randel Bjorkquist


Randel

Assume the name of your employee pick list form field is
cbxEmployees.
Add
this code to OnCurrent event handler for the form (the SQL is
standard
MSSQL):

Dim strEmplSource As String
strEmplSource = "SELECT " & _
" EmployeeID, " & _
" LastName + ', ' + FirstName + ' ' + MiddleInit
AS
Employee " & _
" FROM tbl_Employee " & _
" WHERE Terminated = " &
[Forms]![frm_EmployeeSelector]![chk_Terminated] & _
" AND Shift = " &
[Forms]![frm_EmployeeSelector]![cbo_Shifts] & ";"
cbxEmployees.RowSource = strEmplSource

If either of these fields contain non-numeric data, simply add the
apostrophes to the constant, e.g.

" AND Shift = '" & [Forms]![frm_EmployeeSelector]![cbo_Shifts]
&
"';"

HTH,
Joe De Moor
Click to expand...
Click to expand...
Click to expand...
 
i disagree with sylvain

ADP is a tool to use to learn TSQL. I mean-- are you going to learn
TSQL by using MDB?

ADP is the right tool for the right job. I mean-- it helps you to
write TSQL.. you can download SQL Server Books Online for a full
reference.. it's a great book; by far the most read book in the
industry lol

i just love havnig a tool that comes with office; i mean-- in the
oracle world; you have to either buy toad and / or buy oracle forms and
oracle reports.

ADP is just an awesome platform.
 
Randel Bjorkquist said:
Hello,

This is my first attempt to try and migrate an existing Access database (a
standard Access database mdb file) to an Access Project adp file. All
data transfer, table, view, stored procedure and function creation aside,
I'm having problems with a form that contains two combo boxes (an employee
list and a shift list) and one check box (terminated). The Shift combo
box used a value list and I used the query defined below fill in the
Employee combo box.

SELECT
EmployeeID,
LastName & [, ] & FirstName & [ ] & MiddleInt AS Employee

FROM
tbl_Employee

WHERE
Terminated = [Forms]![frm_EmployeeSelector]![chk_Terminated]
AND
Shift = [Forms]![frm_EmployeeSelector]![cbo_Shifts]

ORDER BY
LastName & [, ] & FirstName & [ ] & MiddleInt ASC

What I get is an error stating that it doesn't like the "!" signs and
basically I need to re-write my code to use the Input Parameters. I do
not have any Row Source assigned to the form, I don't need any to be or at
least I didn't with the mdb format. All I'm trying to do here is to
filter out all unwanted employeesloaded in. I want to be able to display
in the Employee combo box, all employees for 1st shift (Shift combo box
set to 1), that are not terminated (the Terminated check box not checked).
Can someone help me here?

Thanks in advance,

Randel Bjorkquist
Click to expand...
 
Back
Top