Is this a "stored procedure" situation?

We have 2 SQL tables being accessed through an Access form. The tables are an ORDER table and an ORDER-DETAIL table comprised of data regarding the Parts in any given Order. (Yes -- the classic Order-Entry situation.) The Access form is used to view/create new Orders, and shows ORDER data in fields, plus has a large field which presents a "spreadsheet"-like view of the related records from the ORDER-DETAIL table.

The users enter and modify data in the ORDER-DETAIL table directly through this "spreadsheet" in the Access form. However, because there is no PARTS table yet (that's part of what I'm working on), they have to enter part numbers and descriptions *manually* in each ORDER.

So... here's my question:

After I implement a PARTS table, I would like for users to be able to open an ORDER in the Access form, type in a Part # in a row of the ORDER-DETAIL "spreadsheet", and then have the rest of the row populate with the appropriate Part description and other data from the PARTS table. How do I go about making that a reality? Some kind of stored procedure triggered by a change in the Part # field? Ha ha if so, I am clueless as to how to make that happen. ANY information would greatly appreciated!

Thanks!
whill96205 the Noob :confused:You'll want a few stored procedures for this probably. :) You don't want to bind the datagrid to the order-detail table. You'll need to populate it, then after they enter a part number, you will want to have an ON UPDATE action that:

1. Gets the part information and updates the ORDER-DETAIL table.
2. Refreshed the datagrid.|||[QUOTE=derrickleggett]You don't want to bind the datagrid to the order-detail table.QUOTE]

I think I understand what you mean by "bind" -- that the datagrid is like a *direct* window into the ORDER DETAIL table, right?

Okay, so I DON'T want to bind them. How can I tell if the datagrid that is currently in use is bound or not?

--William|||>> DerrickLeggett said:
>>You don't want to bind the datagrid to the order-detail table. You'll
>>need to populate it, then after they enter a part number, you will
>>want to have an ON UPDATE action that:
>> 1. Gets the part information and updates the ORDER-DETAIL table.
>> 2. Refreshed the datagrid.

The "datagrid" is a subform. Currently, I am using a View as the datasource for the subform, and the View is comprised of a join from the ORDER table and the PART table, and displays the PARTs that are already associated with the ORDER being viewed on the main form. There are two issues I'm trying to nail down:
1) To do what Derrick suggested (above), so that entering a PartNum value into a row of the subform causes the rest of the row to update with other data from the PART table (part description, etc.); and
2) To also allow a user to actually create a *new* entry in the PART table by entering a new PartNum into a row of the subform.

SO, I'd like the subform to recognize if a PartNum being entered into it is new or not. Is that possible? And, if so, how do I do that? PLEASE be explicit - this is all very new to me... :)

Is there any way to have an email sent on an event

I'm using SQL with an Access form for the front end. Is there anyway to have an email sent on the trigger of an event within the form? I'm not sure where to even begin.

A good place to start is Books Online. Look up Triggers, SQL Agent, Jobs.

The path you are headed down has a short but steep learning curve.

|||

Unless you are using SQL Server 2005, you should not use the trigger approach to send email directly. This is due to the fact that triggers behave synchronously letting your transaction wait for the response of the external mail sending application / procedure. This could stale your transaction process and let other users wait for ressources to transaction might block. SQL Server uses SQL Server Service broker for sending mails with the DBMail, putting the mail in a queue, sending the mail asynchronously. For SQL Server 2000 you would have to implement your custom solution, writing an entry in a table with the appropiate mail information and grabbing the information from the table periodically to send the emails via an agent job.

Jens K. Suessmeyer.

http://www.sqlserver2005.de