I am using a SQL server backend with a linked Access Db front end through an ODBC connection.

I need to produce the following but I can’t seem to figure out the SQL to do it.
I am trying to use fields from 5 tables and two new fields which should only exist for the life of the query as the source for a continuous form

Each Customer may have multiple orders. When a customer calls for a refill they may only want certain items they have ordered before, but they may not all be in the same order. So the form must have contain all the drugs they have ordered in the past but only show one of each thing they have ordered.

So for instance a customer may have ordered

Order 1

drug1 1 bottle of 100 tab
drug2 1 bottle of 90 tab
drug3 3 bottles of 30 tab

Order 2
drug1 3 bottles of 30 tab
drug4 4 packs of 28 tab

Order 3
drug2 1 bottle of 90 tab
drug1 3 bottle of 30 tab

Now they need a refill. The form should be able to get the following result from this customer
Which of the following would you like to refill:

drug1 3 bottle of 30 tab y / n
drug1 1 bottle of 100 tab y / n
drug2 1 bottle of 90 tab y / n
drug3 3 bottles of 30 tab y / n
drug4 4 packs of 28 tab y / n

Rather than creating a new order from scratch or refilling an old order, adding to it and deleting from it. I would like to put a consolidated refill form in the application.

This could can be done with SQL, however I run into the following problems.
1. When adding a temporary field to the query for the y / n part of the structure to control some check boxes, the check boxes are not updateable. It creates constants.
2. When I create a view in the SQL backend and use it as a linked table If I add some fields 0 as refill, 0 as nrx where refill and nrx are the source for the checkboxes that are needed choose whether to include this particular drug in the refill. They come out as constants and are also not updateable.
3. When the distinct operator is used in any SQL it becomes non updateable, which makes sense.
4. When the order by clause is used in SQL server it uses the top 100 percent operator which make s it non updateable.


To recap, the query needs to select distinct values from a combined group of tables and add two new Boolean or bit fields which must be updateable in a form.

Thanks in advance for any advice you might have, I have not been able to successfully implement this, yet.

-Roni


Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com

__________________
Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com

You can use an instead of trigger on the view and instead of updating the view for

drug1 3 bottle of 30 tab y / n
drug1 1 bottle of 100 tab y / n
drug2 1 bottle of 90 tab y / n
drug3 3 bottles of 30 tab y / n
drug4 4 packs of 28 tab y / n

you update the corresponding tables.
You could also use ADO to make the update on a new recordset.
By the way, i am glad you moved to SQL Server.




Sal

I'm only halfway there Sal, probably even less. Alright, time to read the chapter on triggers. But I have to ask, how would you do this with ADO?

I am sure I can build the recordset, yet can you introduce new columns? the main problem for me is the introducing the new columns that don't correspond to anything, they just add the selection functionality to the insert query for the new order. These values are never stored, and couldn't be otherwise, I would have to use transactions which I can't do with access.

To clarify, what if I, employee 1 do a refill for drug 1, and employee 2 does a refill for drug 1 at the same time for a different patient? Even though we will not interfere with one and other via locks, once I let go of the inherent transaction on the insert or update if it worked as a bound form but lets leave it to the insert level; the other employee would get get his customer with preselected yes for that drug because as soon as my lock is released he will aquire it in its current state! Resetting the value back to unselected requires another implicit transaction and I can't force the issue without declaring a transaction. Which I can't do with the access mdb?

I hope that made more sense reading it than it made writing it. But you get the point no?

Best Reagrds as Always sal, and happy holidays.
-Roni

Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com

A trigger is inside of a transaction, therefore, you can set some type of date field in your table that tells you when the last time was that the patient refilled a prescription. You can check this inside of the trigger.

Check for the date. If lets' say that a prescription can only be refilled every 30 days and this one is being refilled 10 days from the last refill, then you do not allow to continue.
If the date is within the range that is acceptable, you go ahead end set that date to the current date and refill the prescription.

You can pass a parameter from a form to a recordset to filter your recordset. Just refer to the text box in the query string for the recordset.



Sal