A Little Help With Tab Control

[pbaldy]

I understand, I just don't know if disabling it will prevent it from loading data when the form loads. I'm not saying you're wrong, I honestly don't know. My gut says it will still load data, because a disabled control still shows its underlying data, you just can't click in it. I'll try to test if I get a minute.

 

[pbaldy]

I was curious so did an exceedingly brief test. I had a bound form with a bound subform, with the subform control disabled. When opened, the subform still displayed the records it normally would have, I just couldn't click into the subform.

I guess another idea for quicker loading of multiple subforms is to set their record sources to return zero records by default, or no record source at all. When their tab is clicked on, set the record source back to what it would normally be.

 

[Mark_]

Paul,

Did the same test, but came up with a slightly different way of doing this..
I created a table, TblBlank, with one field and one record.
I created a query that MATCHED the normal query used by a subform but with all of the fields change to expressions; [LastName] became LastName: "Last Name", [FirstName] became FirstName: "First Name", ect...
In the OnLoad for the subform I set the form's recordsource = "MyBlankTableQuery"
On the TAB, I set the OnClick for the subform to set Me.Subform.Form.Recordsource = "MyRealQuery".

Means that when I first moved to the tab, I got "Last Name" and "First Name" for a single row. When I CLICKED on the subform, it loaded the proper data.

psyc0tic1,
If you add a table that has one column and one row to your app (TblBlank is what I called mine) you can do the same trick. Means you'll have to make a copy of each of the initial queries and change them to look at tlbBlank and have them return the same fields in the same order, but it should avoid any issue with pulling data across the network.

 

[pbaldy]

That's similar to where I was heading in post 42. I have this vague memory of the recordsource being evaluated in the open/load events, so if performance is still a problem, you may want to set the record source to the blank query in design view rather than the load event.

 

[MarkK]

What I do with a tab control with subforms on each tab, is only load the subform for the currently visible tab by programmatically setting the SourceObject property of a single subform control.

Doing it that way is fast and simple. Handle the Change event of the tab control, detect the current page, load the subform that belongs on that page.

Mark

 

[Mark_]

Paul,

Not sure of the OPs back end and back end progression. Local table in the FE that is looked at by "Blank" queries" will make sure their users don't hit the back end early. Hate to have them work out a "No records" query that is talking to the back end only to find out there is something like ODBC in there causing it to slow down.

On the plus side? He's got to people coming up with related ways of doing the same so he can choose which one will work best for him.

Course he could surprise both of us and say "Well, I just ask for all records with an ID =0 and that solved it... LOL...

 

[psyc0tic1]

Paul,

Did the same test, but came up with a slightly different way of doing this..
I created a table, TblBlank, with one field and one record.
I created a query that MATCHED the normal query used by a subform but with all of the fields change to expressions; [LastName] became LastName: "Last Name", [FirstName] became FirstName: "First Name", ect...
In the OnLoad for the subform I set the form's recordsource = "MyBlankTableQuery"
On the TAB, I set the OnClick for the subform to set Me.Subform.Form.Recordsource = "MyRealQuery".

Means that when I first moved to the tab, I got "Last Name" and "First Name" for a single row. When I CLICKED on the subform, it loaded the proper data.

psyc0tic1,
If you add a table that has one column and one row to your app (TblBlank is what I called mine) you can do the same trick. Means you'll have to make a copy of each of the initial queries and change them to look at tlbBlank and have them return the same fields in the same order, but it should avoid any issue with pulling data across the network.

Mark_,

I am trying to understand what you are saying here but I am not savy enough.

I created a table called tbl_blank that has one field which is an autonumber. I do not know what to put in for the "one record" you mention.

I made a copy of the query for this form (frm_visualinspectioninput) which is page index 1 (named (VisInputForm) of my tabbed control named TabCtl87. The original is called qry_visinspectinputform. The copy is called Zqry_visinspectinputform

How would I turn this query into the one you describe? This is the query for one of the forms in my tabbed control.

SELECT tbl_auditdata.Status, tbl_auditdata.AuditID, tbl_auditdata.Facility, tbl_auditdata.PONumber, tbl_auditdata.PartNumber, tbl_auditdata.TotalReceived, tbl_auditdata.VisInspectDate, tbl_auditdata.PartProdDate, tbl_auditdata.QCLine, tbl_auditdata.BlackGreenDot, tbl_auditdata.TotalVisInspected, tbl_auditdata.TotalVisBad, tbl_auditdata.TotalVisGood, tbl_auditdata.VisDefectFound, tbl_auditdata.VisAtt, tbl_auditdata.VisualInspectorUserID, tbl_auditdata.LabInspectorUserID, tbl_auditdata.VisUpdate, tbl_auditdata.RecDate, tbl_auditdata.MfgPONum
FROM tbl_auditdata
WHERE (((tbl_auditdata.Status)="Waiting on Visual Inspection"));

And this is the code in the forms module:

Option Compare Database
Private blnGood As Boolean

Private Sub cboGoToRecord_AfterUpdate()
     On Error Resume Next
     Dim rst As Object
     Set rst = Me.RecordsetClone
     rst.FindFirst "AuditID = " & Me.cboGoToRecord.Value
     Me.Bookmark = rst.Bookmark
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strMsg As String
    
    If Not blnGood Then
        Cancel = True
        strMsg = "Please click the Update button to save your changes, " & vbNewLine & "or Escape to reset them."
        Call MsgBox(Prompt:=strMsg, Title:="Before Update")
        Else
         Me![VisUpdate] = Date
    End If
   
End Sub

Private Sub Form_Current()
    Me.cboGoToRecord.Value = Me.AuditID.Value
End Sub

Private Sub Vis_Input_Submit_Click()
    Dim strMsg As String
    
    blnGood = True
    
    If (validate) Then
        Me.Recordset.Edit
        Me.Recordset.Fields("status").Value = "Waiting On Lab"
        Me.Recordset.Fields("VisualInspectorUserId").Value = Credentials.UserId
        Me.Recordset.Update
        If Me.CurrentRecord < Me.Recordset.RecordCount Then
            Me.Recordset.MoveNext
        Else
            Me.Recordset.MoveFirst
        End If
    Else
        strMsg = "All Fields are required."
        Call MsgBox(Prompt:=strMsg, Title:="Before Update")
    End If
        Application.Echo False
        DoCmd.Close
        DoCmd.OpenForm "frm_home"
        Form_frm_home.Visual_Inspection_Input_Form.SetFocus
        Application.Echo True
    blnGood = False
End Sub

Private Function validate() As Boolean
    validate = True
    If (IsNull(Me.VisInspectDate.Value)) Then
        validate = False
    End If
    If (IsNull(Me.QCLine.Value)) Then
        validate = False
    End If
    If (IsNull(Me.BlackGreenDot.Value)) Then
        validate = False
    End If
    If (IsNull(Me.PartProdDate.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalVisInspected.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalVisBad.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalVisGood.Value)) Then
        validate = False
    End If
    If (IsNull(Me.MfgPONum)) Then
        validate = False
    End If
End Function

I was reading somewhere the other day about something like this but didn't understand it so I would really like to give this a shot.

 

[psyc0tic1]

Please?

I really do not know how to accomplish this in my current query/code.

 

[pbaldy]

Where exactly are you stuck? I'd try simply saving the record source in design view as:

SELECT tbl_auditdata.Status, tbl_auditdata.AuditID, tbl_auditdata.Facility, tbl_auditdata.PONumber, tbl_auditdata.PartNumber, tbl_auditdata.TotalReceived, tbl_auditdata.VisInspectDate, tbl_auditdata.PartProdDate, tbl_auditdata.QCLine, tbl_auditdata.BlackGreenDot, tbl_auditdata.TotalVisInspected, tbl_auditdata.TotalVisBad, tbl_auditdata.TotalVisGood, tbl_auditdata.VisDefectFound, tbl_auditdata.VisAtt, tbl_auditdata.VisualInspectorUserID, tbl_auditdata.LabInspectorUserID, tbl_auditdata.VisUpdate, tbl_auditdata.RecDate, tbl_auditdata.MfgPONum
FROM tbl_auditdata
WHERE KeyFIeldName = 0

Then when you want that form active set it to what you have above.

 

[Mark_]

Have you tried compiling?

If (validate) Then

looks like it should be

If validate() Then

Where are you stuck? What is or isn't working?

 

[pbaldy]

I doubt that's a problem. You can have:

If (Variable = x) Then

This is really no different since the function will return true/false, and without the Call keyword the parentheses following the function name would probably be dropped or at least not required.

 

[psyc0tic1]

Where exactly are you stuck? I'd try simply saving the record source in design view as:

SELECT tbl_auditdata.Status, tbl_auditdata.AuditID, tbl_auditdata.Facility, tbl_auditdata.PONumber, tbl_auditdata.PartNumber, tbl_auditdata.TotalReceived, tbl_auditdata.VisInspectDate, tbl_auditdata.PartProdDate, tbl_auditdata.QCLine, tbl_auditdata.BlackGreenDot, tbl_auditdata.TotalVisInspected, tbl_auditdata.TotalVisBad, tbl_auditdata.TotalVisGood, tbl_auditdata.VisDefectFound, tbl_auditdata.VisAtt, tbl_auditdata.VisualInspectorUserID, tbl_auditdata.LabInspectorUserID, tbl_auditdata.VisUpdate, tbl_auditdata.RecDate, tbl_auditdata.MfgPONum
FROM tbl_auditdata
WHERE KeyFIeldName = 0

Then when you want that form active set it to what you have above.

I did like you said... I created the query called Zqry_visinspectinputform with the code you showed and I set the record source for the frm_visualinspectioninput to that query.

The part I am stuck on at the moment is your WHERE clause and how to call the correct record source when the tab is selected.

Where did "KeyFieldName" come from and how does that work? I get a parameter popup for that.

 

[pbaldy]

The object is to pull zero records, so whatever the key field is (presumably an autonumber field but not necessarily), your criteria should be 0 on that field.

In the tab control's change event you test it's value, which will be the page index value of the tab clicked on.

 

[psyc0tic1]

The object is to pull zero records, so whatever the key field is (presumably an autonumber field but not necessarily), your criteria should be 0 on that field.

In the tab control's change event you test it's value, which will be the page index value of the tab clicked on.

Ok if I understand correctly... the keyfieldname in the query (Zqry_visinspectinputform) would be tbl_auditdata.AuditID which is the autonumber field of the table (tbl_auditdata) so I set that to 0

now in the tab control change event would I use something like this? Case 1 being the one that I am working on with the query mentioned.

Private Sub TabCtl87_Change()

    Case 0:
        Stuff Here
    Case 1:
        If Me.VisInputForm.Form.RecordSource & "" = "" Then
           Me.VisInputForm.Form.RecordSource = qry_visinspectinputform
        End If

 

[pbaldy]

I don't think you want the If test, and the name of the query needs to be in quotes.

 

[psyc0tic1]

I don't think you want the If test, and the name of the query needs to be in quotes.

So all I need is this?

Case 1:
         Me.VisInputForm.Form.RecordSource = "qry_visinspectinputform"

 

[pbaldy]

I believe so. Did you try it?

 

[Minty]

I'd second Markk suggestion (post #45) , simply set the source object of all the sub forms to "" , then load them on tab change.

Much more simples...

 

[psyc0tic1]

I believe so. Did you try it?

It pops up a parameter box for AuditID and when I x out of the parameter box it highlights this code on another form (frm_labtestinput)

Private Sub Form_Current()
    Me.cboGoToRecord.Value = Me.AuditID.Value
End Sub

 

[psyc0tic1]

I'd second Markk suggestion (post #45) , simply set the source object of all the sub forms to "" , then load them on tab change.

Much more simples...

I would like to try that but I am limited in my experience and do not know how.