Has anyone ever experimented with TWO updateable tables on one form?
1. Basically I start out with a primary table listing clients I work with.
2. Then I linked it (one-to-many) to a table of medications prescribed for each client:
Clients.DB <-------------->> Medications.DB
One client is displayed at a time on the top section of the screen. On the lower portion is a table listing all medications related to the client (by his/her Client ID). It works great, using F9 allows the information I both tables to be updated. I click in the top section of the form and use the arrow keys to move between clients, and the bottom part of the screen auto-adjusts to list his medications.
Example A: Joe Smith has 3 prescriptions to take:
Aspirin
An antibiotic, and
An arthritic ointment.
Therefore Joe has one row in the Client table and three rows in the Medications table. I can use the bottom section to add new medication rows, delete old ones, or update the existing ones.
3. I wanted to take this one step further and allow users the option of scheduling when the medications are to be taken. Basically I created a third table:
Clients.DB <--->> Medications.DB <--->> Med_Schedule.DB
I changed the bottom of the form to hold both the medication and schedule tables. The table on the left still lists the 3 medications assigned to Joe, but I added a new table (Med Schedule) on the right.
Example B:
a. With Joe’s Client entry displayed at top, I click on the Aspirin row in Table 1 to make it current.
b. Then I click in the Schedule table (on the right) and enter two rows for aspirin, since it should be taken at 11 AM and 4:30 p.m.
c. Next I click on the second med, the Antibiotic in Table 1.
d. Table 2 goes blank because it currently contains no rows.
e. I add 4 entries for this med, to be taken at 9:00 a.m., 1:00 p.m., and 5:00 p.m., and 9:00 p.m.
f. The Arthritic ointment (med 3) I add NO schedule entries for because it should be used as needed.
g. However, I can still move between clients by clicking on the top part of the screen OR view each schedule for either of the first two meds by clicking on it in the left table. The right table finds the rows matching the medications key and displays the schedule rows.
This process DOES work but has intermittent errors. Every once in a while when I click on one of the medications, Paradox becomes confused and locks up. I have to exit out and restart.
One of the remedies I tried for this was to make the Clients.DB table Read-Only. You can still view the client at the top of the screen and use the entry to list the matching medication entries below, but another form must be used if you need to change the client information.
I am wondering if this is just asking TOO much of Paradox. So far I have been unable to get the two-table process to work.
1. Basically I start out with a primary table listing clients I work with.
2. Then I linked it (one-to-many) to a table of medications prescribed for each client:
Clients.DB <-------------->> Medications.DB
One client is displayed at a time on the top section of the screen. On the lower portion is a table listing all medications related to the client (by his/her Client ID). It works great, using F9 allows the information I both tables to be updated. I click in the top section of the form and use the arrow keys to move between clients, and the bottom part of the screen auto-adjusts to list his medications.
Example A: Joe Smith has 3 prescriptions to take:
Aspirin
An antibiotic, and
An arthritic ointment.
Therefore Joe has one row in the Client table and three rows in the Medications table. I can use the bottom section to add new medication rows, delete old ones, or update the existing ones.
3. I wanted to take this one step further and allow users the option of scheduling when the medications are to be taken. Basically I created a third table:
Clients.DB <--->> Medications.DB <--->> Med_Schedule.DB
I changed the bottom of the form to hold both the medication and schedule tables. The table on the left still lists the 3 medications assigned to Joe, but I added a new table (Med Schedule) on the right.
Example B:
a. With Joe’s Client entry displayed at top, I click on the Aspirin row in Table 1 to make it current.
b. Then I click in the Schedule table (on the right) and enter two rows for aspirin, since it should be taken at 11 AM and 4:30 p.m.
c. Next I click on the second med, the Antibiotic in Table 1.
d. Table 2 goes blank because it currently contains no rows.
e. I add 4 entries for this med, to be taken at 9:00 a.m., 1:00 p.m., and 5:00 p.m., and 9:00 p.m.
f. The Arthritic ointment (med 3) I add NO schedule entries for because it should be used as needed.
g. However, I can still move between clients by clicking on the top part of the screen OR view each schedule for either of the first two meds by clicking on it in the left table. The right table finds the rows matching the medications key and displays the schedule rows.
This process DOES work but has intermittent errors. Every once in a while when I click on one of the medications, Paradox becomes confused and locks up. I have to exit out and restart.
One of the remedies I tried for this was to make the Clients.DB table Read-Only. You can still view the client at the top of the screen and use the entry to list the matching medication entries below, but another form must be used if you need to change the client information.
I am wondering if this is just asking TOO much of Paradox. So far I have been unable to get the two-table process to work.