Subform Not Showing Data in Read Only Mode

[PETE314]

Access 2000 Project
SQL2000 Backend

I have a Search Engine that opens a form(B) based on the selected record from the Search Results. Form B has a recordset based off of a SQL Statement with Input Parameters. Form B has 2 subforms one that is just informatory and is always Read Only (Edits, Additions, and Deletions set to No) the other subform is to add additional figures of which are summed and then added to the figures on the main From(B). The subforms, are for speed sake, not using the tradition subfrom/mainform links. They grab their recordset in the same way as the main form (SQL Statement with Input Parameters) As a matter of fact the subforms and the main form all have the same input Parameter.

The problem is as such. When opening the main form in Edit mode, both sub forms display their information and work normally. However when opening the Main form in Read Only Mode...the Informatory subform shows all of its data but the second subform for figures does not show its data. It does show its header and footer but it acts as there is a null set for a recordsource. and that messes up the the info dispayed on the main form as it pulls the summed figures from the now non displaying subform.

Why would the subform work while the Main form is in edit mode....but not while in Read Only mode?????

I have tried setting the allow Edits, Additions, Deletions properties(as this seems to be a main difference between the 2 subforms) upon opening in read only mode but that has no effect.

Any Suggestions????

 

[PETE314]

Everyone else stumped too??????

 

[PETE314]

It doesn't make sense to me either....LOL And I tried to recreate without any real success.

So I did an exhuastive investigation on the data......I have JUST concluded that I have been given bad test data. There are holes in the data. Hence the null recordset, which just sets the entire dominoe chain into effect. In edit mode you can have a null recordset but you are given a field in which you can add a record and the default values(usually zero) give the other fields something to calculate. Not to mention you can see a record even if not filled completely....

So now I have to figure out how to get 0 to come up in my summations in read only mode when there is a null recordset.......my current summation field has a Control Source as such =Nz(Sum([curAdditionalPrice]),0) But it is not displaying zero in the Read only mode....It apparently doesn't know what to do with a null recordset.....

 

[PHV]

And this ?
=Sum(Nz([curAdditionalPrice],0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PETE314]

Thank You PHV....

You know I thought that would work when I first saw it....and how I missed that I cannot fathom...LOL...but unfortunately it doesn't seem to be working

I even added another NZ around that....no luck.....hmmmmm...strange....everything is telling me that it should work...but no go

 

[PETE314]

Office 200 ADP
SQL2000 Backend

My solution to this is a little brutish but I think it will work.

I can determine in what mode I am opening the form in by evaluating option buttons on the form before it. So If I am opening in Read Only Mode....I can take the SQL statement for the subform....make a recordset and count the records....if less than one(a null recordset should return a value of -1) then I create a new ADO recordset, add a single record with values of my choosing(basically 0) and set the Recordsorce property for the subform to that ADO Recordset.

I have everything written but I am having a type mismatch error when trying to set the Record Source Property.....I am including the code I have written. I cannot find the type mismatch problem. Maybe some one else can

            AddCostSQL = "SELECT dbo.tblAdditionalCosts.* FROM dbo.tblAdditionalCosts WHERE (strOrderInfoID = " & MyJobID & ")"

            With AddCostCmd
                .ActiveConnection = CurrentProject.Connection
                .CommandText = AddCostSQL
                .CommandType = adCmdText
            End With

            Set AddCostRs = AddCostCmd.Execute

            'Check for null recordset, if so, create new recordset
            If Nz(AddCostRs.RecordCount, 0) < 1 Then
                With ZeroRs
                    With .Fields
                        .Append "strAdditionalCostsID", adBigInt
                        .Append "strOrderInfoID", adBigInt
                        .Append "strCostDescription", adVarChar, 50
                        .Append "curAdditionalPrice", adCurrency
                    End With
                    .CursorLocation = adUseClient
                    .CursorType = adOpenStatic
                    .Open
                End With

                'Add one record
                ZeroRs.AddNew Array("strAdditionalCostsID", "strOrderInfoID", "strCostDescription", "curAdditionalPrice"), Array(1, MyJobID, "none", 0)

                'Set the Subforms RecordSource to the recordset
                Forms![frmInvoiceInformation]![subfrmAdditionalCosts].Form.RecordSource = ZeroRs

Note: MyJobID is set earlier in the code.....The type mismatch error comes up on the last line.

the original table for subfrmAdditionalCosts is built as such
Column Name _________Datatype_____ Length______ Precision_____ Scale
strAdditionalCostsID___bigint_________8___________19__________0
strOrderInfoID________bigint_________8___________19__________0
strCostDescription_____nvarchar_______50__________0 ___________0
curAdditionalPrice_____ money________ 8___________19 __________ 4

 

[PHV]

Have you tried this ?
Set Forms![frmInvoiceInformation]![subfrmAdditionalCosts].Form.Recordset = ZeroRs

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PETE314]

Just tried it.....

It said that the object I entered was not a valid recordset property.....

Runtime error 7965.....

so is it talking about ZeroRs.....or is it talking about the property "Forms![frmInvoiceInformation]![subfrmAdditionalCosts].Form.Recordset"??????

 

[PETE314]

Could this also be a problem with references????I am pretty sure I have everything I needed clicked...

I have in this order

VBA
Access 9.0 Object Library
OLE Automation
Active X Data Objects 2.1 Library
Microsoft Forms 2.0 Object Library

 

[PHV]

And this ?
Forms![frmInvoiceInformation]![subfrmAdditionalCosts].Form.RecordSource = AddCostSQL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PETE314]

Well AddCostSQL is just a string expression of what is already there...with one exception. The Where Clause uses a ? and I use input parameters in the properties of the subform. So my guess is that would work...except that AddCostSQL ends up being a null recordset which is what I am trying to avoid and the whole reason for creating a new recordset to base the subform off of.

So the RecordSource Property isn't working because ZeroRs is not a string expression...Is that correct???....which is why I had to try it for the Recordset Property. Am I thinking along the correct lines?

 

[PHV]

Unfortunatly, I don't think an ac2k Form object exposes the Recordset property as does my ac2003...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PETE314]

Right...IIRC Access2K does not "expose" it but it is still there and can be called in VBA....

 

[PETE314]

Well I think my research is telling me that it is not working because in Access 2000, the recordset needs to have an Active connection. And since I am not pulling any data, my connection is null or disconnected. 2002 and up can handle it, I guess. So when I do my conversion later it could be an option. But for now I think I am going to have to do a REAL UGLY fix and make a 1 record table for this subform to pull from when the form is read only and the subfrom recordset has no records.

This is a really brutish answer but I am running out of time...lol

Any thoughts on this?