Digital Workshop

Normally I love mysteries, but this one I don’t even like. I have a problem I cannot figure out and it makes no sense to me.

I have a recipe book using MS Access and 14 individual databases. On the first one (Beverages.mdb) I can make a change to any record in the database, then click the save button, and at that point, after the record is saved, I can go forward and backwards through the records. I can go all the way to the end (last record) or all the way to the beginning (first record). Ok, so no big deal right? That’s the way it should work.

Now, if I go to the next database (breads.mdb) I can change a record, click on the save button (which does save the record). I can go back and forth through the records, but only if I use the next record, or the previous record buttons. If I click to go to the last record, or the first record the navigation buttons no longer work, whether I’m going forward or backwards one record at a time, or if I’m going all the way to the end or beginning.

I have gone over this a thousand time (seriously). Everything is identical with the exception of what should be different. I’ve double triple checked the (DNS) records, and everything else, but I cannot get the navigation buttons to work on the second database (Breads.mdb) once I click on the last record button or the first record button, even though the commands are identical.

Here are the four buttons:

ScrollToBeginning
Left Mouse Click
First Record Query: (Beverage-Records)
Script – searchby = ""

Left Mouse Click
Script -
IngredientsField.Scroll("LineTo",0);
PreparationField.Scroll("LineTo",0);
NotesField.Scroll("LineTo",0);

ScrollLeft
Left Mouse Click
If (searchby = letter) then
Script – prevrec()

If not (searchby = letter) then
Previous Record Query: (Beverage-Records)

Left Mouse Click
Script
IngredientsField.Scroll("LineTo",0);
PreparationField.Scroll("LineTo",0);
NotesField.Scroll("LineTo",0);

ScrollRight
Left Mouse Click
If (searchby = letter) then
Script – nextrec()

If not (searchby = letter) then
Next Record Query: (Beverage-Records)

Left Mouse Click
Script
IngredientsField.Scroll("LineTo",0);
PreparationField.Scroll("LineTo",0);
NotesField.Scroll("LineTo",0);

ScrollToEnd
Left Mouse Click
Last Record Query: (Beverage-Records)
Script - searchby = ""

Left Mouse Click
IngredientsField.Scroll("LineTo",0);
PreparationField.Scroll("LineTo",0);
NotesField.Scroll("LineTo",0);

I’m hoping that one of you guys likes mysteries. I have been over and over this to the point of pulling out my hair. The buttons above work perfect on the first database (Beverages.mdb). But they do not work right on the second database, but only if you use ScrollToBeginning or ScrollToEnd following a save.

What does the Save button commands look like?
Left Mouse Click
End Query (Beverage-Records)
Database Query – Query ID: (Beverage-Save) – only the record is queried
Update Current Record – Query: Beverage-Save – All fields are updated except for the DataRecord.
<edit> = 1 (set this variable to 1)
<editrec> = <Title>
Go to Page “Beverages”

Your kind help would be apprciated greatly...

Denn,

Do you Close each DB, or end the DB Connection, when you have finished work on that DB?

One statement from you gets me to thinking down a different path. Ok, to answer your question I am not closing out each database before opening a new one. I'll go and try something like that now. Do you just happen to have a small script that will close all open databases? I have a hidden button on each page (of a database like Beverages) it opens that database, but I had not thought about the fact that one is already open if I’m going to a different one…

Thanks once again Lar...

Update: I do not see anything that allows me to close all open databases and that pretty much is what I need. I have 14 databases that can be opened from a common page (Categories Page), so I don't know which page they are coming from before going to another page. So my task now is to see if there is a CloseAllDatabases() function or something to this effect that can be written into a script...

Good call though. I really believe this could be the problem. Up until now I'd found nothing that made any sense. The one that works right is the first database in the program.

Denn,

I do not know specifically how to answer the 'close all DB'. Think of it this way, if multiple open db is a source of a conflict, then never open a 2nd DB without first closing the 1st. I cannot say that is necessary, but I think it depends on methods (of DB) being used (due to connection limitations).

A lot of it depends on whether (and how-well) you have organized your scripts into reusable opuscript functions -- for example, if a DB name or a DB path is passed as a parameter to a function... then you could presumably Close a DB (this) after the queries and updates have been run on that DB.

Or, alternatively: store the name, or path, of the currently open DB in a variable, say: CurrOpenDB
Then, simply call a single function which has the code to close the CurrOpenDB, or set its connection to null. That way you can use that function before opening any other DB.
see thread for discussion --- viewtopic.php?f=4&t=2773&hilit=close+connection

BTW, do remember to commit any transactions before you close a DB. (if you use 'commit').

sidenote: I just looked in opusscript help and do not see a close db command. (in the last few years I've worked with DB, I bypassed Opus' and Windows' ways of connecting/opening a DB -- since I had available scripted methods to do that w/o ODBC using SQLite).