First, select the Queries from the database window and click on the Refresh
property (F5) in the menu. Use this command every time you are making a
modification in the parameters of a stored procedure or the number of fields
returned, otherwise your SP will be callled with the wrong arguments/list of
fields.
Second, the Record Source of the form to the name of the SP.
Third, not a bad idea to set the Record Source Qualifier to "dbo" (without
the quote) or to the name of the user who own the ownership of the SP. A
good idea is to make sure that the owner of your SP is dbo by setting it in
the create procedure instruction: CREATE PROCEDURE dbo.select_employee .
As I don't use Access to create my procedure, I don't know how this will
looks like or can be done from Access.
Fourth, set the Unique Table property to the name of the table with the
primary key. If necessary, set it in the On_Load event of your form. This
will greatly help in having a form with a Read/Write property for the fields
instead of a Read-Only form.
Fifth, set the Resync Command if you know how, otherwise skip this step or
set the resync command to a dummy procedure with the wrong number of
arguments; as this will force Access to use the resync command of ADO, which
simply returns all the values, instead of trying to requery the SQL-Server
for the current record, which is an error prone procedure. If you want to
create a Resync Command, create a stored procedure with only the primary key
as its argument and add a "?" after its name in the Resync Command property.
In your case, your SP can be used directly as the resync command, as it have
only one argument:
select_employee ?
Sixth, set the Input Parameters to indicate Access how to set the value for
each parameters of your SP. For example, if the Id of the employee is in a
combobox in the current form with a name of ComboBoxEmployee and the name of
the form is MyForm:
@IdEmployee varchar(6) = Forms!MyForm!ComboBoxEmployee
In your case, it will be a better idea to keep the int type for the
IdEmploye from your previous question. Don't forget that the "int" type for
SQL is the equivalent to the "long" type in VBA and not to the "integer"
type, the latter being equivalent to "short" inside SQL-Server. You can
also add other parameters if you want too:
@IdEmployee int = Forms!MyForm!ComboBoxEmployee, @FirstDate datetime =
.....
Not a bad idea to use the zoom function: Shift-F2. Also, sometime ago,
there were a strange bug
Finally, set the Control Source for each text box to the required field of
the query.
S. L.
