close and reopen tab form to record and page 1

[dbar10]

I have a Form called [Clients Add/Edit] it is a tab form with 5 tabs controls. I am working on the [Physicians] tab. I have a command button that opens a form to add new physicians. When I add new physicians here, I close the forn and then I need to requery the main form to pickup the new physician. I have tried many different solutions with no avail. I believe I need to close the Clients Add/Edit form and reopen but I need help with the code. On the close event of my add Physician form I want to:
Echo off
Close Clients Add/Edit
Open Clinets Add Edit
But I need to come back to the same record which is determined by ClientID and to the Physicians tab on that form. Can you please help? Thanks

 

[TheAceMan1]

how are ya dbar10 . . .

There's a big [blue]gray area[/blue] here as to tables and tab control structure, but I'll see if I can work around it with the info provided.

What I purpose is performing the requery and tab page selection from the [blue]AddNewPhysicians[/blue] form and then closing it. In the AddNewPhysicians [blue]After Update[/blue] event, we perform the following sequence of events:

[ol][li][blue]Hold ClientID[/blue] of the saved record in a crieria statement, which is used to find the record after requery.[/li]
[li]Using form referencing we requery form [blue][Clients Add/Edit][/blue].[/li]
[li]Find ClientID using the [blue]held ClientID[/blue].[/li]
[li]Finally you [blue]select the approriate page[/blue] of the tab control[/li][/ol]
Before we go on you need a handy piece of code that tells you wether a form is open or not! Since were manipilating [blue][Clients Add/Edit][/blue] from [blue]AddNewPhysicians[/blue], we need to know if [blue][Clients Add/Edit][/blue] is Open! ... or we'll generate an error. If [blue][Clients Add/Edit][/blue] in not open, we allow [blue]AddNewPhysicians[/blue] to operately independently!

So ... in a module in the [blue]modules window[/blue], copy/paste the following function:

[blue]Function IsOpenForm(frmName As String) As Boolean
   
   If CurrentProject.AllForms(frmName).IsLoaded Then
      [green]'if form is loaded and not in design view, then ok![/green]
      If Forms(frmName).CurrentView > 0 Then IsOpenForm = True
   End If
   
End Function[/blue]

Getting on with it, code in the [blue]After Update[/blue] of form [blue]AddNewPhysicians[/blue] would look like:

[blue]   Dim frm As Form, TC As TabControl, Cri As String
   
   If isopenform("Clients Add/Edit") Then
      Set frm = Forms![Clients Add/Edit]
      Set TC = frm![[purple][B][I]YourTabControlName[/I][/B][/purple]]
      Cri = "[ClientID] = " & Me!ClientID [green]'ClientID held here[/green]
      
      frm.Requery
      frm.Recordset.FindFirst Cri [green]'Goto record matching held ClientID[/green]
      TC = 2 [green]'The numeric here is the page index of the physicians tab.
             'Note: tab index starts at zero![/green]
      DoCmd.Close acForm, "[purple][B][I]YourAddNewPhysiciansFormName[/I][/B][/purple]", acSaveNo
   End If
      
   Set TC = Nothing
   Set frm = Nothing[/blue]

Give it a whirl and let me know.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

dbar10 . . .

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] [blue]Its Worthy Reading![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dbar10]

Thanks TheAceMan1, I did what you said and I get the debug error when I add a new Physician:

Compile error:
Expected variable or procedure, not module

Here is the module named IsOpenForm:
Option Compare Database

Function IsOpenForm(frmName As String) As Boolean

If CurrentProject.AllForms(frmName).IsLoaded Then
'if form is loaded and not in design view, then ok!
If Forms(frmName).CurrentView > 0 Then IsOpenForm = True
End If

End Function



Here is the AfterUpdate on Physicians Demographics:


Private Sub Form_AfterUpdate()
Dim frm As Form, TC As TabControl, Cri As String

If IsOpenForm("Clients Add/Edit") Then
Set frm = Forms![Clients Add/Edit]
Set TC = frm![Physicians]
Cri = "[ClientID] = " & Me!ClientID 'ClientID held here

frm.Requery
frm.Recordset.FindFirst Cri 'Goto record matching held ClientID
TC = 4 'The numeric here is the page index of the physicians tab.
'Note: tab index starts at zero!
DoCmd.Close acForm, "Physician Demographics", acSaveNo
End If

Set TC = Nothing
Set frm = Nothing
End Sub

What am I doing wrong?

 

[PHV]

You can't have a module with the same name as a function !

 

[dbar10]

I corrected the module name. Now I am getting a Run-time error 13 - Type mismatch and VB editor is highlighting:
Set TC = frm![PhysicanTab]
Yes the spelling matches the tab control name, HaHa.


I cleaned my registry and still had no change. Any ideas?

Thanks

 

[PHV]

Replace this:
TC As TabControl
with this:
TC As Control

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dbar10]

OK, that solved the runtime error 13. Now I get a syntax error missing operator on:

frm.Recordset.FindFirst Cri

 

[dbar10]

I want to thank PHV and AceMan for your help.

I still can't figure out why I am now getting a syntax error - missing operator on the line:

frm.Recordset.FindFirst Cri

I can see the light and the end of the tunnel. Can you help? Thanks

 

[dbar10]

This is what I have so far. A module name ReOpenForm:

Option Compare Database

Function IsOpenForm(frmName As String) As Boolean

If CurrentProject.AllForms(frmName).IsLoaded Then
'if form is loaded and not in design view, then ok!
If Forms(frmName).CurrentView > 0 Then IsOpenForm = True
End If

End Function


Then I have the following code running on the AfterUpdate event in the [Physician Demographics] form:

Private Sub Form_AfterUpdate()
Dim frm As Form, TC As Control, Cri As String

If IsOpenForm("Clients Add/Edit") Then
Set frm = Forms![Clients Add/Edit]
Set TC = frm![PhysicanTab]
Cri = "[ClientID] = " & Me!ClientID 'ClientID held here

frm.Requery
frm.Recordset.FindFirst , Cri 'Goto record matching held ClientID
TC = 4 'The numeric here is the page index of the physicians tab.
'Note: tab index starts at zero!
DoCmd.Close acForm, "Physician Demographics", acSaveNo
End If

Set TC = Nothing
Set frm = Nothing
End Sub

I am getting a runtime error '3001' - Invalid Argument

can anyone help me with this, pleease. Thanks

 

[dbar10]

Sorry the break is happening at:

frm.Recordset.FindFirst , Cri 'Goto record matching held ClientID

 

[PHV]

If ClientID isn't defined as numeric:

Cri = "ClientID='" & Me!ClientID & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dbar10]

Thanks for your response PHV.
The ClientID is numeric however, I tried the code just for "kicks and grins" but received the same response. I put it back to original. Any other ideas? oR is there anything else I can send you that might help?

 

[dbar10]

OK maybe this will help. I am running a DAO database and using Jet Engine in Access 2003. Can anyone help me? I'm stuck. Thanks

 

[TheAceMan1]

dbar10 . . .

So sorry for my absence (had to take care of some tax problems), but Wow! ... I never thought this thread would come this far. Be aware ... I have a working simulation of what you presented!

As a matter of expidiency, is it possible you can provide us with a copy of the db? ... or a scaled down version thereof? You can use 4shared.com for this (its free!). I very carefully parsed thru this thread several times and feel your upload of the db is the quickest path to resolution.

Note: if your using 2007, save to an earlier version (2000 for me) before you upload.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dbar10]

Thanks AceMan! I have converted db from 2003 to 2000 and trimmed it way down, but I believe all is there that you may need. This is a FE and BE db I sent part of the FE. You will find at CareEase 2000.mdb

Hope you can help.

 

[TheAceMan1]

dbar10 . . .

Wow ... talk about a gray area!

Before we continue, I need a download of the backend db so I can view data. I only need those tables involved in the forms in this thread. To keep from violating any [blue]proprietary[/blue] data, from a copy of the backend, remove all records save one and overwrite with dummy info and send the copy as 2K.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dbar10]

Here ya go!!

http://www.4shared.com/file/105794284/c2e1c415/CareEase_BE.html

 

[TheAceMan1]

dbar10 . . .

Just a side step here to apprise you of [blue]the right name to use for a [blue]Tab Control Object[/blue] in VBA.

A tab control has [blue]Pages[/blue]. The tabs themselves are [blue]labels[/blue] for those pages. The tab control itself and all its pages each have a name as depicted by the [blue]Name[/blue] property in the properties window. The name property of the pages is normally the name assigned to the tab caption. But they can be different. Just be aware that its the [blue]Name[/blue] property thats used in VBA and you can't go wrong.

Why would you want a name different than the caption you ask? ... Simple ... Ease of programming! Consider you have a page caption of [blue]Physician_Special_Practice_Area[/blue]. I wouldn't want such a long name in my code so I'd change the name property to something like [blue]PSPA[/blue]! Looks easier much easier to read ehhh!

So how do you get the name of the infamous Tab Control Object! ... Easy ... Either click on the outline of the tab control or click in the blank area just to thr right of the last tab and look at the name property. In the thread you have:

[blue]   Set TC = frm![Physicians] 'a page caption Label!
   Set TC = frm![PhysicanTab] 'a page name

[purple][b]It should be:[/b][/purple]
   Set TC = frm!TabCtl89[/blue]

Also Note: Dim TC As [blue]TabControl[/blue] is specific whereas Dim TC As [blue]Control[/blue] can be any control! This why [blue]TabControl[/blue] appeared to fail (you never selected a tab control object), and pass when you changed to [blue]Control[/blue].

BTW when speaking/writing of a [blue]Tab Control Object[/blue] is less confusing to speak of its page(s) then to keep saying/writing Tab!.

The above marks changes that have to be made in the code (we'll get to those). We only have the [blue]ClientID[/blue] to take care of. I see the backend is in, so I'll have a look see.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

dbar10 . . .

Didn't get to take a good look until this mourning. I see your fighting a [blue]many to many[/blue] table relationship here. The reason your having such a hard time with [blue]ClientID[/blue]. Note: [purple]make a copy of the db and use that to incoorperate changes[/purple]. IF all works well you can delete the old and rename the copy proper.

What you need to do is complete the the client side of your many to many relationship. So open up the backend and perform the following:
[ol][li]Open the relationships window. [blue]Position the following forms[/blue] (left to right) so you can clearly see their relationship lines. Move other tables out the way if necessary.
[blue]Client Table[/blue] --- [blue]ClientPhysicianTbl[/blue] --- [blue]Physician Demographics[/blue][/li]
[li]Now make the relationship from [blue][Client Table].ClientID[/blue] to [blue]ClientPhysicianTbl.ClientID[/blue]. With [blue][Client Table].ClientID[/blue] on the one side.[/li]
[li]In the Client Table remove [red]PhysiciansID[/red].
In the Physician Demographics table remove [red]ClientID[/red].
These fields are not needed and can only cause you confusion.[/li]
[li]Open the [blue]ClientPhysicianTbl[/blue] in design view.
Select [blue]PhysicianID[/blue].
At the bottom select the [blue]Lookup[/blue] tab.
In the [blue]Display Control[/blue] property select [blue]Text Box[/blue].
The combobox is [red]preventing updating[/red] of [blue]PhysicianID[/blue] and is not needed It will raise a datatype mismatch error! Note: PhysicianID is maintained when switching to text box.
Save & close the table.[/li]
[li]Close the be and open the fe.[/li][/ol]
Bear in mind ... the whole idea is to get [blue]ClientPhysicianTbl[/blue] updated properly. Were also adding a button to [blue]Physician Demographics[/blue]. The reason is you may be simply selecting physicians to add to the subform in the physicians page. The AfterUpdate event only works when you add a new record. Remember you still need to have [blue]Physician Demographics[/blue] operate independently if your adding new physicians alone or editing previous records.

So:
Special Note:The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.
[ol][li]Open [blue]Physician Demographics[/blue] form in design view and add some height to the forms footer (enough to hold the button).[/li]
[li]Click the toolbar button for [blue]command button[/blue] then click inside the form footer area. When the wizard starts ... click [blue]cancel[/blue].[/li]
[li]Give the button some length as its going to hold the name of client in the [blue]Client Add/Edit[/blue] form.[/li]
[li]Now set the following properties for the button:
Caption [blue]Link to[/blue]
Enabled [blue]No[/blue]
Name [blue]lnkClient[/blue][/li]
[li]Now the code. In the [blue]On Click[/blue] event of the button, copy/paste the following:

[blue]   Dim db As DAO.Database, SQL As String, Cri As String, cliName As String, phyName As String
   Dim frm As Form, sfrm As Form, CID As Long, DL As String
   
   If IsOpenForm("Clients Add/Edit") Then
      Set db = CurrentDb
      Set frm = Forms("Clients Add/Edit")
      Set sfrm = frm("ClientPhysician Query subform").Form
      CID = frm!ClientID
      
      If Me.Dirty Then Me.Dirty = True [green]'Save if New Record[/green]
      Cri = "[ClientID] = " & CID & " AND [PhysicianID] = " & Me!PhysicianID
      
      If IsNull(DLookup("[ClientID]", "ClientPhysicianTbl", Cri)) Then
         SQL = "INSERT INTO ClientPhysicianTbl (ClientID, PhysicianID) " & _
               "VALUES (" & CID & ", " & Me!PhysicianID & ");"
         db.Execute SQL, dbFailOnError
         
         Cri = "[PhysicianID] = " & Me!PhysicianID  [green]'PhysicianID held here[/green]
         sfrm.Requery
         sfrm.Recordset.FindFirst Cri
         
         Set db = Nothing
         Set frm = Nothing
         Set sfrm = Nothing
      Else
         cliName = frm!FullName
         phyName = Me![Last Name] & "," & Me![First Name]
         DL = vbNewLine & vbNewLine
         
         MsgBox phyName & " already linked to " & cliName & "!" & DL & _
               "New Link is aborted . . .", _
                vbCritical + vbOKOnly, _
               "Duplicate Physician Detected! . . ."
      End If
   End If[/blue]

[/li]
[li]Don't forget to remove the code in the [blue]After Update[/blue] event![/li]
[li]For a better chronological view give the forms some sorting on LastName & FirstName.[/li]
[li]Your [blue]Naming Convention[/blue] as far as VBA is concerned needs improving. Us the labels in your forms/reports to fully spell out the intended, and make the name property of your objects a shorter version ... no spaces, only numbers & letters. This simply makes your code easier to read ... [blue]to You![/blue] Examples:
a form name [blue]POCFunctionalLimitationstbl Query subform[/blue] > [green]sfrmPOCFuncLim[/green]
a check box [blue]Confidentiality Agreement signed[/blue] > [green]Agree[/green][/li]
[li]Thats it ... [blue]perform your testing[/blue] ...[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]