Subform not displaying until Click (1 Viewer)

[svtguy02]

I've got a form with 2 subforms in it. The fact that there 2 subforms isn't really the problem, its moreso the fact that the form does not display any data (doesn't initiate ON LOAD) until you CLICK IN it. It litterally just appears blank, then you click in it and it initiates onload (where I have some code to pull values)

Here is what happens when you open up TESTCustomer (the main form)

No data is displayed as you can see..... as soon as you *click* in the subform you get the following:

Data being displayed! This only happens when you CLICK in that subform.

now... something else to consider.. I have ANOTHER Form + Subform combo doing the same thing. You open it, and RIGHT away the subform displays its data (based on a query, just like the other one). Don't have to click in the subform at all, it just shows it:

I've checked the code for both and its VERY similar:

CODE FROM THE SUBFORM THAT DISPLAYS DATA UPON FORMLOAD:

Private Sub Form_Current()

    Dim strSQL As String
    Dim ConInt As Integer
    Dim strCount As String
    Dim cSQL As String
    Dim fSQL As String
    Dim pSQL As String
    Dim vSQL As String
    '***************************************************************************************************************
    '***************************************************************************************************************
    '*** For reference the coresponding c, f, p and v stand for the first letter of their respective table.      ***
    '*** c = Customers                                                                                           ***
    '*** f = Facilities                                                                                          ***
    '*** p = PO                                                                                                  ***
    '*** v = Vendor                                                                                              ***
    '***************************************************************************************************************
    '***************************************************************************************************************
    'ConInt = 3  '<-- for testing STATIC integers rather than a dynamic number
    ConInt = Forms!frmContacts!ContactID
    strSQL = "SELECT ContactDetails.ContactID, ContactDetails.ObjectID, ContactDetails.ContactType FROM ContactDetails WHERE ContactDetails.ContactID = " & ConInt & ";"
    Me.RecordSource = strSQL

    strCount = DCount("[ContactID]", "ContactDetails")

    Me.TxtCount = strCount

    cSQL = "SELECT CustomerID, Customer_Name FROM Customers;"
    fSQL = "SELECT FacilityID, Facility_Name FROM Facilities;"
    pSQL = "SELECT POID, PONumber FROM PO;"
    vSQL = "SELECT VendorID, VendorName FROM Vendor;"
     
        If sbfrmContactDetails_ContactType = "c" Then sbfrmContactDetails_ObjectID.RowSource = cSQL
        If sbfrmContactDetails_ContactType = "f" Then sbfrmContactDetails_ObjectID.RowSource = fSQL
        If sbfrmContactDetails_ContactType = "p" Then sbfrmContactDetails_ObjectID.RowSource = pSQL
        If sbfrmContactDetails_ContactType = "v" Then sbfrmContactDetails_ObjectID.RowSource = vSQL

End Sub

CODE FROM THE FORM THAT DOES NOT DISPLAY DATA ON FORM LOAD:

Private Sub Form_Load()

Dim strSQL As String
Dim CustInt As Integer

'CustInt = Forms!TESTCustomer!CustomerID
CustInt = 3
           
strSQL = "SELECT FacilityID, Facility_Name, Address1, City, State, Zip, IIf([FacilityID]=Null,'','Edit') AS EDIT FROM Facilities WHERE Facilities.CustomerID = " & CustInt & ";"

Me.RecordSource = strSQL

MsgBox "Form LOAD Event!"

End Sub

Any Ideas??

 

[Uncle Gizmo]

I made one example for handling a subform recently, I know that I put the code in the main form's "open event" I don't Recall if I did this for any particular reason, here's the link.







,

 

[svtguy02]

For the record, you need to signup an account at that site. But thanks, i'll check it out!

 

[Uncle Gizmo]

>>>For the record, you need to signup an account at that site<<<

I know,,, its my site.....

 

[svtguy02]

hahah I guess you WOULD know about that one then :s

So just to go over the code that you wrote for that time matrix thing. You created ONE subform, and used that as sort of a template to propegate over to all 4 quadrants of your time matrix. Do you mind if I try to copy that code into my application?? Looks very versatile which is very appealing.

 

[Uncle Gizmo]

Yes of course you can use it. There is some other stuff on my website which you may find useful as well. In particular there's a calendar form that works just about anywhere, and also has the ability to run code on the "calling form" when it closes.

Going back to the single form used four times on the time management matrix, the idea would be particularly useful for a tab control with several tabs looking at the same table of data, you could have one tab for today, one for yesterday, one for tomorrow something like that. I think that would be a useful application of this code.

 

[svtguy02]

sweet. Thanks for letting me use your code.

That being said, I'm having problems with one portion. It doesn't seem to want to stick all the strSQL's together properly. Here's what I've got:

Private Sub Form_Open(Cancel As Integer)

Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim VendInt As Integer

VendInt = Me.VendorID
strSQL1 = "SELECT ContactDetails.ContactID, ContactDetails.ObjectID, ContactDetails.ContactType "
strSQL2 = "FROM ContactDetails "
strSQL3 = "WHERE (((ContactDetails.ContactType)= "
'1  WHERE ContactDetails.ContactType = 'v' AND ContactDetails.ObjectID = " & VendInt & ";
strSQL5 = "))"

Dim varContactCust As String
Dim varContactFac As String
Dim varContactPO As String
Dim varContactVend As String

varContactCust = "c"
varContactFac = "f"
varContactPO = "p"
varContactVend = "v"

    With Me.VendContact
        .SourceObject = "NEWsubform"
        .Form.RecordSource = strSQL1 & strSQL2 & strSQL3 & varContactVend & strSQL4 & strSQL5
        '.Form.txtMatrixIn.DefaultValue = intSubUI
    End With

It keeps prompting me for "Enter Parameter Value V"

doesn't make sense though because the ENTIRE SQL query put together should read:

"SELECT ContactDetails.ContactID, ContactDetails.ObjectID, ContactDetails.ContactType FROM ContactDetails WHERE ContactDetails.ContactType = 'v' AND ContactDetails.ObjectID = " & VendInt & ";"

 

[Uncle Gizmo]

Try:
strSQL3 & "'" & varContactVend & "'" & strSQL4

BTW what happened to strSQL4? whys it an empty string?

Also:
If you build your SQL into a string variable:

strSQL = strSQL1 & strSQL2 & strSQL3 & varContactVend & strSQL4 & strSQL5

then you can have: .Form.RecordSource = strSQL

and the most important bit: MsgBox " >>> " & strSQL

Then the message box will return the string so you can examine it, and the other advantage of the message box is you can use the keyboard press "Ctrl C" and copy the contents of the message box into the clipboard.

 

[svtguy02]

oh... strSQL4 was probably resulted as an empty string due to LOTS of trial and error. trying this, trying that, and just didnt realize it was empty. Thanks for pointing that out!

I'm gonna try to poke around it a bit, wish me luck!

 

[svtguy02]

arg!! ok.... so I got the above code working! It worked beautifully and I'm sure you all know how sweet "victory" over your problem feels. So I go ahead and open it up again after the, maybe, 10th time or so... and I Start getting the following error:

Run-time error '3008'
The table 'ContactDetails' is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically.

Any ideas? When I click debug it highlights the following line of code:

.Form.RecordSource = strSQL1 & strSQL2 & strSQL3

The query that this is running is:

strSQL1 = "SELECT ContactID, ObjectID, ContactType "
strSQL2 = "FROM ContactDetails "
strSQL3 = "WHERE ContactDetails.ContactType = 'v' AND ContactDetails.ObjectID = " & VendInt & ""

The WHOLE sub routine is as follows:

Private Sub Form_Open(Cancel As Integer)

Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim VendInt As Integer

VendInt = Me.VendorID
strSQL1 = "SELECT ContactID, ObjectID, ContactType "
strSQL2 = "FROM ContactDetails "
strSQL3 = "WHERE ContactDetails.ContactType = 'v' AND ContactDetails.ObjectID = " & VendInt & ""

Dim varContactCust As String
Dim varContactFac As String
Dim varContactPO As String
Dim varContactVend As String

MsgBox ">>>" & strSQL1 & strSQL2 & strSQL3

    With Me.VendContact
        .SourceObject = "NEWsubform"
        .Form.RecordSource = strSQL1 & strSQL2 & strSQL3
    End With

End Sub

 

[svtguy02]

For the record this issue has been resolved.

The fix? Copy all the code, copy all the objects from the existing forms over to a NEW form. Change the pointers in the code to point to the new form names and it works!!

Can you say corruption?