Open a form in a external database....... 4

[cavery]

I have a form that I'm using in my current database, called form 1. I want to add a command button on form 1 that will retrieve and open form 2, which is located in another database. Any suggestions?

Both of the forms are exactly alike, but I've archived alot of records from form1 to form2. I basically want to give users the ability to open up form2(archive records) without having to close the current database. By placing a command button on form1, this will eliminate the need to open another database. Any help would be appreciated!

Thanks,
Clark
Honda of America

 

[cavery]

FancyP, trust me I more than appreciate the help and the patience FancyP has displayed..... Usually some folks are not that helpful, so I'm more than happy to post a few hundred stars! but I dont think I can post that many?????


Many Thanks......

Clark

 

[cavery]

Hi FancyP,
Can I burden you with a quick question regarding the Archive database? I wanted to test the code just to make sure data would be different from the Archive database and the Live database. I copied a record from the backend database to the archive database, I then went back to delete that record in the backend database. Logged into my frontend database, ran a filter to look for that one record to see if it(the record that was deleted) was still in my backend database...it was gone, but when I pressed the View archive records, ran the filter it was not there either.

Didnt know if the filter option worked with this code, assumed it would. I'm sure my users would want to filter archive data, just as well as live data.

I also changed "Call LinkTable(strDatabase.....)" to Archive database to my backend, and vice versa, to see if that made a difference in the filter, and it did, I was able to see the record in both the live database and the archive database, even though I deleted from the live database.....Am I missing something here?...when you have time, thanks....

Clark

 

[FancyPrairie]

Doesn't make sense.

Start out by linking all of your tables to the back-end (live) database (and ensure they are by going to Tools|Database Utilities...|Link Table Manager via the database window). Now open your main edit form and delete or modify one of the records. Now press the button that relinks the table(s) to the Arhived database. Now goto Tools|Database Utilities...|Link Table Manager to see if the tables are indeed linked to the Archived database (don't close your form). If the tables are indeed now linked to the Archived database, the you should not see the changes you made to the record in the live database. Or if you deleted the record in the live database, you should still see it in the Archived database.

 

[cavery]

FancyP, Interesting...when I use this code:
Call LinkTable("C:\PD4 ARCHIVE.mdb", "tblCRFMaster&quot
Press button that views live data and archive data (relinks the table) then I checked via LinkManager, the table "tblCRFMaster" is only linked to the archived database "PD4 ARCHIVE".

But when I used the code below:
Call LinkTable("M:\Data\PD4CRF.mdb", "tblCRFMaster&quot
Did the same steps as above, the table "tblCRFMaster" is only linked to "PD4CRF.mdb" (my backend). No matter if I press the button for live or archived.

I guess my problem is, "tblCRFMaster" isnt relinking to the correct database when I press the button. I assumed it worked when I didnt get any errors, but as you suggested while looking at Link Manager it definetly shows you what is what.....my quest continues. Thanks again....

Clark

 

[FancyPrairie]

The problem is that you're not doing it right. When your form first opens, you are linked to the live (back-end) database. And you have a button on the form that says something like "View Archived Data". When the user selects this button, you call the following routine (my 3rd post). In this routine, the caption of the command button tells the function which database to link the table to. It also changes the caption of the command button to "View Live Data". So the next time the user selects the button, the tables are linked to the live database. The button, basically, toggles between archive and live.

Note the variable "strDatabase" is the key.


Private Sub cmdViewArchive_Click()

Dim strDatabase As String

If (InStr(cmdViewArchive.Caption, "Archive&quot) Then
strDatabase = C:\PD4 ARCHIVE.mdb"
cmdViewArchive.Caption = "View Live Data"
Else
strDatabase = "M:\Data\PD4CRF.mdb"
cmdViewArchive.Caption = "View Archived Data"
End If

Call LinkTable(strDatabase, "tblCRFMaster&quot

End Sub

 

[cavery]

FancyP, You're awesome! thanks for the feedback, problem was Call LinkTable, I just needed strDatabase...I tested it and everything works fine, tables are relinking etc. Your definetly a life saver! and you also gave me some important lessons to learn with code etc. Many more stars......

Clark

 

[cavery]

FancyP, quick question, I've disabled a few text boxes, command buttons etc. when my archive database is displayed, and when the live data is displayed everything is enabled.

I have one text box, when you double click, it leads to another subform where you can view commnents. I want users to still be able to double click on the text box and view comments,(while my archive database is open) but I want to disable the command buttons, so no one will be able to update information. Any ideas?....I've tried a few different methods such as:
'Forms!fdlgTitleCommentHistory!cmdAddNextStep.Enabled = False
and also
Forms!fdlgTitleCommentHistory!cmdAddNextStep = Me.cmd

None of this code seems to work....any suggestions would be appreciated.

Thanks.
Clark

 

[FancyPrairie]

I'm assuming your command button resides in your subform. If so, the syntax to enable/disable it is something like this:

forms!MainForm!SubformControlName.form!cmdAddNextStep.Enabled = False

or

Me!SubformControlName.form!cmdAddNextStep.Enabled = False

 

[cavery]

FancyP, so far I've used:
Forms!frmProbUpdate!txtComments.Form!cmdAddNextStep.Enabled = False
and
Me!txtComments.Form!cmdAddNextStep.Enabled = False

got error message, "Object doesnt support this property or method".

Still researching, might have wrong subformcontrol name?.....

Thanks

 

[FancyPrairie]

txtComments doesn't have anything do it with it. If I understand you correctly, you want to be able to double click on a text box to view a subform that has comments in it. And you want to disable cmdAddNextStep. So, in the OnDoubleClick event of the txtComments field, you need to disable the command button cmdAddNextStep, which resides in the subform.

Let's say that the name of the subform is MySubform. In the main form, you have a subform control whose name is MySubformControl and whose controlsource property is MySubform.

The syntax for accessing a control on a subform is:

Me!MySubformControl.form!cmdAddNextStep.Enabled = False

 

[cavery]

Thats correct, but I want to be able to do this (toggle enabled and disabled) everytime I select cmdViewArchive. Example when I click cmdViewArchive I have cmdbutton that I disable, but when I click cmdViewlivedata, the cmd button is enabled. I still want to be able to double click on the text (txtNextStep) and view comments in my subform, but I want to disable the commands button,..only when I view archived data.

Hope I didnt confuse you....Thanks,