Form bound to more than one table, need to update and delete. Dlo

[Computermike]

I am converting an Access 2003 database to an Access Data Project using a
SQL Server back end.

I have a form that is bound to an Employee table and inserts, updates and
deletes records. My table does not have Employee name, it is another table.
This field would be read only and is not in the table I'm bound too.

EmployeeName comes from a seperate table, in Access they are using a
dlookup() formula.

I would like the dlookup to read a stored procedure and return an Employee's
Name.

Here's the code

=DLookUp("EmployeeName","sp_NameLookup","@EMPLOYEE =
Forms![frmLastStepDates]!txtEMPLOYEE")

I was unsuccessful.

I also tried joining the two tables, then using a select statement as the
record source. I set the UniqueTable property to the table I want to update
and put the same SQL statement I used for the record source in the
ResyncCommand. I disaled the employeeName field too. This failed as well,
It would allow updates.

Mike











Thanks,


Mike

 

[Sylvain Lafontaine]

You cannot use a DLookup to read from a stored procedure, this function must
be used only with tables or views (but personnally, I never tried with
something like a Select statement or an UDF function).

The usual way would be to join the two tables and set up the UniqueTable
property and also the Resync command (albeit I'm not sure about the strict
necessity of the Resync command). For the Resync command, set up a stored
procedure with a single parameter that would be the primary key of the
unique table and call it using ? as the parameter:

Me.ResyncCommand = "MySP_Resync ?"

Of course, you can write the above instruction (without the quotes) directly
in the properties window.

Finally, don't use the prefix sp_ for you own stored procedures. It has a
special meaning for SQL-Server and its use for user stored procedures can
lead to some very hard to debug problems under certain conditions.