Back and Forth Navigation between Access Forms? (2 Viewers)

[jleach]

Access itself has two types of displays for forms: tabbed or (untabbed?) I forget what it's called, but anyway. Tabbed forms are where Access gives it it's own tab at the top (which can be hidden), and the tabbed form is always maximized.

In untabbed forms, the forms are not maximized, and can be given whatever specific sizes you like.

That's Access itself. Then you can do more or less whichever you like: You can open any form with the Popup property set to true, and it will open as a popup form, not a "tabbed" form (note: unlike the old MDI window, and the "untabbed" forms, a popup style form can be taken outside the main access window and placed on another monitor, for example).

So, in either case of the native Access setting, you can have popup forms.

In the scenario I described, you can have it with either setting in Access. There's one maximized form: the shell itself. Then within that is a couple of subforms, then the "panel" form (which takes the size of the subform in the shell), from which you can open popup forms, etc. etc.

That help?

 

[misscrf]

That really does! Thank you. I know your approach is right. I just need to work out how to rewrite at lot of my code (opening and closing of forms, referencing of controls on specific forms, etc ) to use your approach.

The advantage to the subform nav bar would have been less of a rewrite, but in the end that probably won't be the cleanest approach.

p.s. I originally posted this on stack overflow. People got pissed because it wasn't a question about code, and was one looking for different approaches. They eventually closed it as too broad.

This right here, is why I prefer forums like this. I wanted a discussion and got exactly that.

 

[Minty]

Glad you came back.
I post occasionally on SO and they are a bit "Narrow" in their approach, yet seem happy to respond to questions that are answerable in 2 seconds with a google. No logic in that...

 

[jleach]

Depending on the size of your project and your existing code complexity, it may be worthwhile to write some wrapper functions:

(aircode)

Function OpenForm(FormName As String, Optional args As Variant = Null)
   Const PANEL_FORMS As String = ";ThisForm;ThatForm;"
   If Instr(1, PANEL_FORMS, ";" & FormName & ";") Then
     'open panel form
     UIShell.OpenPanel(FormName, args)
   Else
      'regular forms
      If IsNull(args) Then
         DoCmd.OpenForm
      Else
         DoCmd.OpenForm FormName, , , , , args
      End If
    End If
End Function

Then replace DoCmd.OpenForms with OpenForm (and test carefully! This would code most common use cases, but not all)

 

[isladogs]

A few months ago, I tried offering a correction to a major error I spotted in a closed thread at SO.
They refused to allow that on the grounds that it would undermine their system of validating answers

So I asked if I could put the same point in a new thread and refer back to the original. They said no for exactly the same reason.

The error was, last time I looked, still on view as the accepted answer.

 

[misscrf]

Thanks, JLeach. Not sure I understand exactly what that code will help me do, but I trust it would be helpful. I'm just starting to get myself set up to do 1 maximized form, with the nav bar, with a subform control, which will take on any of the other forms, by a button click on the nav bar. Then I'll have to work on updating all my open and close code on every form, to just load /unload what is in the subform control.

Then I can see what I broke hahahaha.

 

[jleach]

S/O is definitely very strict and a major pain (almost impossible) to use if you don't fit inside their cookie cutter. Discussion isn't exactly well received. On the other hand, for strict Q/A and finding fast answers to common issues, you can't beat it. The SoftwareEngineering site of the StackExchange network is a lot better for more open-discussion type of things, but it's still only a slight step up from S/O and nowhere near the discussion levels that you'd see in a forum (and to be honest, I don't think I've seen a single Access related question on SoftwareEngineering in the years I've been a member there).

 

[static]

Well, it looks like I confused things here, sorry.

When I said the search screen should be the first/main screen, I was just looking at the diagram.
Normally in a database application, you drill down into data.
You should try to follow a linear path, but the last three boxes in the diagram are circular.


You are only making this change because searching for projects on the project screen is slow.
You have created a new search screen that is quicker but you don't know how to plug it into your other screens.

1) I researched the ways to allow a search in string with a combo, and it's not directly possible.
It takes a workaround and with that info being tied to another system, the refresh is too slow.

2) That made me move instead towards putting a button in the header, for "find system projects".
This opens a pop-up form, for the user to type in a text box.
A listbox has all projects and will filter based on what they type in the text box.
This seems like a much more effective way to allow a use verify which projects are in the system currently,
while allowing the user to search any part of the project name or project number.

Statement 2 contradicts statement 1.

A combo isn't that different from a list. If you can filter a list, you can filter a combo.
Why is refreshing the list box faster than refreshing a combo box?
Find out why it is slow.
Perhaps, there is an event in the main form that fires when the project combo changes?
Disconnect it and put that code into your button so that it doesn't fire automatically.

If you want to use your new search form anyway, it could just be a modal pop-up that sets the project on the main form.
Which is probably what Pat Hartman was saying back in post 5.

 

[misscrf]

Thanks for your thoughts and for taking the time to respond. I think part of the draw back of the combo, is that it isn't directly (or easily) possible to search a partial string.

For instance, if the project, someone wants to find, is called "ABC Excellent Stinking Project (2439387)" and they just remember stinking, they can't just type that. It won't find an instring, so to speak. It has to begin with what they are typing.

When I researched solutions for that, I found that they were all kludgey work arounds. Having a text box with a listbox that would filter any part of the string (or the number in parens) would be easier for them. I have combined that with the "add projects to system" form, as you suggested. It was definitely the way to go. It simplified the amount of forms the user had to jump around to.

Now I am taking JLeach's advice and working on putting everything on 1 main entry form, with a nav bar on the left and a subform control in the bulk of the detail. When a user clicks any button on the nav bar, I will simply load that as the source object in the subform control, instead of opening a new form.

@JLeach, I do have a question on that. I am used to hiding a form, if I don't want to completely unload what I am doing (stay on record/recordset) and closing it, when I do want to be done with those records/recordset. With this solution of setting the source object, I don't see how I get to completely load or unload a form.

Let's say I have the Project's form set as the source object of the entry form's sub form control. Then the user clicks on the sub-projects or project plan button. I will then set the source object of the subform control to the form they want to go to. But how am I "correctly" or "safely" closing the previous form?

Is that a legitimate concern or am I in left field looking at butterflies? lol

 

[jleach]

Off the top of my head, I think when you set the SourceObject to some other form, it will close/unload the prior form as if you had hit the close button (any objects without an active reference should be destroyed, so if the only active reference is the SourceObject, and you remove it, it should deconstruct the form as well).

For a quick test, you can do something like this:

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
    Debug.Print "form is opening"
End Sub

Private Sub Form_Unload(Cancel As Integer)
   Debug.Print "form unloading"
End Sub

Private Sub Form_Close()
  Debug.Print "form closing"
End Sub

Then run a quick test for setting/unsetting the sourceobject and see which of these events are firing. I expect you'll find them all firing. You can even try setting cancel = true in one of them to see what happens, but I expect this will be a slipperly slope (even in "normal" forms, cancelling a form's close is really wacky - I have a pretty in depth article on the topic elsewhere)

If you do want to hide the form without unloading it (which is nice for performance reasons): have a different subform control for each main form you want to host. This is ok if you have a relatively small amount of forms and you know which will wind up here. Then you can just set the .Visible property of the subform controls accordingly: the forms stay in memory, but become hidden/visible as you dictate.

 

[misscrf]

Thanks. It's a lot to think about. I think it will be cleaner to unload the form, instead of bogging down the memory with potentially every form being open at once. I will probably run some of that debug testing too. That sounds like a really good idea, so I can see whats up. I have a function to list all forms that are open, which I use when I'm developing and could have a bunch of hidden forms that I need to unload for a good test:

Sub AllForms()
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentProject
    
    ' Search for open AccessObject objects in AllForms collection.
    For Each obj In dbs.AllForms
        If obj.IsLoaded = True Then
            ' Print name of obj.
            Debug.Print obj.Name
        End If
    Next obj
End Sub

I can run that, after each click, to see if it really unloads it or not.

Thanks!

 

[misscrf]

So I'm running into a hurdle, attempting to move some code to a public function. It's just bare bones, but this will help with eventually determine what should be loaded and what should not be.

I've made the lines that are failing, bold:

Public Function ProjectFindAddPage()
Dim Listctrl As Control
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim cSQL As String

For Each Listctrl In [B]Forms!frmMainEntryForm.frmProjects_AddFindProjects.Form!Controls[/B]
    If (Listctrl.ControlType = acListBox) Then
        With Listctrl
            .RowSource = ""
        End With
    End If
Next Listctrl

Select Case [B]Forms!frmMainEntryForm.frmProjects_AddFindProjects.Form.ActivePage.Name[/B]
    Case "pgAddProjects"
        cmd.ActiveConnection = GetCatalog()
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "sp_RefreshProjectsAdd"
        cmd.Execute

        Forms!frmMainEntryForm!SubEntryForm.Form!lstAllProjects.RowSource = "Select * From qryProjectsYes"
        Forms!frmMainEntryForm!SubEntryForm.Form!lstAllProjects.Requery
        Forms!frmMainEntryForm!SubEntryForm.Form!lstAddProjects.RowSource = "Select * From qryAddProjectsNo"
        Forms!frmMainEntryForm!SubEntryForm.Form!lstAddProjects.Requery
    Case "pgAddProjects"
        cSQL = "SELECT vw_CMP_Projects.CM_CID, [vw_CMP_Projects]![projectName] &" & Chr$(34) & " (" & Chr$(34) & "& [vw_CMP_Projects]![projectNo] &" & Chr$(34) & ") " & Chr$(34) & " AS Projects FROM vw_CMP_Projects ORDER BY [vw_CMP_Projects]![projectName] &" & Chr$(34) & " (" & Chr$(34) & "& [vw_CMP_Projects]![projectNo] &" & Chr$(34) & ")" & Chr$(34)
        Forms!frmMainEntryForm!SubEntryForm.Form!lstProjects.RowSource = cSQL
        Forms!frmMainEntryForm!SubEntryForm.Form!lstProjects.Requery
End Select
End Function

Before, when I had them in the form's module as a private function, I could use me! now I need the right full reference and I seem to be going in circles. Can anyone help?

Run-time error '2465' is the error I'm getting, which seems to suggest I'm referencing the objects wrong.


:banghead:

 

[Uncle Gizmo]

Are the forms open?

 

[jleach]

Not sure exactly what the issue is, but for good measure: a) store the value in a variable before using the Select Case, and b) read it via VBA instead of the Expression Service:

Dim ActivePageName As String

...

ActivePageName = Forms("frmMainEntryForm").Controls("frmProjects_AddFindProjects").Form.ActivePage.Name
...
Select Case ActivePageName
...

That gives you a little more control and the ability to get some better error handling/debugging

 

[isladogs]

Error 2465 - Access cannot find the field name (name specified in the error)

To get a clear answer to your question, you need to clarify some things

...
Forms!frmMainEntryForm.frmProjects_AddFindProjects.Form!Controls
...
Forms!frmMainEntryForm.frmProjects_AddFindProjects.Form.ActivePage.Name

Is frmProjects_AddFindProjects a subform of frmMainEntryForm OR is it a separate form opened from frmMainEntryForm?

If it is a subform, is that how its referenced from the main form as later in the code you have several lines like this:

Forms!frmMainEntryForm!SubEntryForm.Form!lstAllProjects.RowSource

 

[misscrf]

Thanks to both of you. I'll answer part first, and then I need to go check into some of the stuff you asked and see what I can verify. The frmMainEntryForm is the form I open. The SubEntryForm is the subform CONTROL on that form. Iset it's source object to a form called frmProjects_AddFindProjects. So that is a subform on the main entry form. That "subform" has the tab control on it. 1 tab has 2 listboxes and the other has 1. I want to only have the rowsource set for the listbox(es) on the active page, so that the form will run quicker.

This is just the beginning of what I am testing. Eventually I will be switching the subform control source object to a different form, based on the user clicking one of a series of buttons, displayed as a nav bar on the left of the main entry form.

For now, I started at the top 1st form, in the natural order of things. I figure I'll work through getting each form load, and refresh, and take info and go somewhere else processes working, and then move on to getting the next form to be set as the subform object and that all it's code is working properly as a subform. These were all originally top level forms, all independent of each other.

Hope that makes sense.

 

[misscrf]

So I found this:
https://access-programmers.co.uk/forums/showthread.php?t=42701

and I changed the public function to this:

Public Function ProjectFindAddPage(frm As Form) As String
Dim ctl As Control
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim cSQL As String

For Each ctl In frm
    If ctl.ControlType = acListBox Then
        With ctl
            .RowSource = ""
        End With
    End If
Next ctl

Select Case frm.ActivePage.Name
    Case "pgAddProjects"
        cmd.ActiveConnection = GetCatalog()
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "sp_RefreshProjectsAdd"
        cmd.Execute

        frm.lstAllProjects.RowSource = "Select * From qryProjectsYes"
        frm.lstAllProjects.Requery
        frm.lstAddProjects.RowSource = "Select * From qryAddProjectsNo"
        frm.lstAddProjects.Requery
        frm.lstProjects.RowSource = ""
    Case "pgAddProjects"
        cSQL = "SELECT vw_CMP_Projects.CM_CID, [vw_CMP_Projects]![projectName] &" & Chr$(34) & " (" & Chr$(34) & "& [vw_CMP_Projects]![projectNo] &" & Chr$(34) & ") " & Chr$(34) & " AS Projects FROM vw_CMP_Projects ORDER BY [vw_CMP_Projects]![projectName] &" & Chr$(34) & " (" & Chr$(34) & "& [vw_CMP_Projects]![projectNo] &" & Chr$(34) & ")" & Chr$(34)
        frm.lstProjects.RowSource = cSQL
        frm.lstProjects.Requery
        frm.lstAllProjects.RowSource = ""
        frm.lstAddProjects.RowSource = ""
End Select
End Function

Then I tried calling it like

ProjectFindAddPage (me)

Which gets a run-time error '13'

When I make it ProjectFindAddPage (Forms!frmMainEntryForm!Form.frmProjects_AddFindProjects)

or

ProjectFindAddPage (Forms!frmMainEntryForm!Form.frmProjects_AddFindProjects.Form)

I get a 2465 error again.

 

[misscrf]

ok, I took the public function and made it into a private function in the form module, just to simplify things.

It looks like this now (with some debugging print steps to let me see the state of things)

Private Function projectFindAddPage()
Dim ctl As Control
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim cSQL As String
Dim ActivePageName As String

ActivePageName = Me.ActivePage.Name
Debug.Print ActivePageName

For Each ctl In Me.Form
    If ctl.ControlType = acListBox Then
        With ctl
            .RowSource = ""
        End With
    End If
Next ctl


Select Case ActivePageName
    Case "pgAddprojects"
        cmd.ActiveConnection = GetCatalog()
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "sp_RefreshprojectsAdd"
        cmd.Execute

        Me.lstAllprojects.RowSource = "Select * From qryprojectsYes"
        Me.lstAllprojects.Requery
        Me.lstAddprojects.RowSource = "Select * From qryAddprojectsNo"
        Me.lstAddprojects.Requery
        Me.lstprojects.RowSource = ""
        Debug.Print "pgAddprojects is now set...."
        Debug.Print Me.lstAllprojects.RowSource
        Debug.Print Me.lstAddprojects.RowSource
        Me.Repaint
    Case "pgFindprojects"
        cSQL = "SELECT vw_CMP_projects.CM_CID, [vw_CMP_projects]![projectName] &" & Chr$(34) & " (" & Chr$(34) & "& [vw_CMP_projects]![projectNo] &" & Chr$(34) & ") " & Chr$(34) & " AS projects FROM vw_CMP_projects ORDER BY [vw_CMP_projects]![projectName] &" & Chr$(34) & " (" & Chr$(34) & "& [vw_CMP_projects]![projectNo] &" & Chr$(34) & ")" & Chr$(34)
        Me.lstprojects.RowSource = cSQL
        Me.lstprojects.Requery
        Me.lstAllprojects.RowSource = ""
        Me.lstAddprojects.RowSource = ""
        Debug.Print "pgFindprojects is now set...."
End Select

To test, I am simply changing from 1 tab control page (pgAddprojects) to the other (pgFindprojects). When I switch to the find projects, the 1 listbox on there shows all the results it should, based on the initial rowsource I set on it.

When I switch to the add projects, however, it doesn't show anything in the lstAllprojects listbox, which should show everything. I checked the query that is set as it's rowsource (qryprojectsYes) and there are results. For some reason the listbox shows nothing. Not even headers.

What I don't get is how I can troubleshoot this part of things, beyond what I'm checking. I get no errors, and the debug.print steps show that everything looks to be set right. I added that repaint just to make sure that the form didn't need to repaint, so I would see the changes. That didn't help.

Once I get passed this one problem, I can move forward on the rest of my changes, which I'm hoping will flow fairly quickly. I know what I need to do, and have an internal running list of the changes I need to make, but I have to start with this issue, as this form is the most likely form to start at.

 

[misscrf]

nevermind!
I spelled the query name wrong (I'm such a damn moron)

Me.lstAllprojects.RowSource = "Select * From qryprojectsYes"
Me.lstAllprojects.Requery
Me.lstAddprojects.RowSource = "Select * From qryAddprojectsNo"

should be

Me.lstAllprojects.RowSource = "Select * From qry[B]Add[/B]projectsYes"
Me.lstAllprojects.Requery
Me.lstAddprojects.RowSource = "Select * From qryAddprojectsNo"

 

[misscrf]

I moved on to a new issue, but I'm wondering if I should make it a new post. I feel like I'm dragging this thread out lol. It's about using a text box to hide the work being done to change from one tab page to another. Let me know if you think I should post it here or start a new thread.