Trying to get rid of "AutoNumber" on continuous inquiry form 1

[rafe]

Sorry about the last post. I forgot the "descriptive title"

Hi all,

This isn't life or death but it’s annoying me & this kind of thing usually indicates a bad design so...

I have a continuous form linked to a multi-table query. All query data are bound to controls in the detail section. All detail fields are disabled - this form just shows history, no updating. In the header section I have two unbound controls that I’m trying to feed to the form's Me.Filter (as per code below). Filter works dandy. However…

The annoying thing is that the last detail line shows "AutoNumber" it's a primary key field (DocID) of one tables of the query. If I disable the Me.AllowAdditions field the “AutoNumber” goes away but the unbound header controls will sometimes blank themselves. I.e. filter works just as expected, based upon the entered data in the header controls, but the controls set themselves to NULL (sometimes) after the filter.

Option Compare Database
Option Explicit

Dim FilterStr As String

Private Sub Form_Load()
    Me.SelPayee = ""
    Me.SelType = ""
    FilterStr = ""
    Me.Filter = FilterStr
    Me.FilterOn = True
End Sub

Private Sub SelPayee_AfterUpdate()
    FilterStr = ""
    If Nz(Me.SelPayee, 0) Then _
        FilterStr = FilterStr & "PayeeID = " & Me.SelPayee
    If Nz(Me.SelPayee, 0) And Nz(Me.SelType, &quot;&quot;) <> &quot;&quot; Then _
        FilterStr = FilterStr &amp; &quot; AND &quot;
    If Nz(Me.SelType, &quot;&quot;) <> &quot;&quot; Then _
        FilterStr = FilterStr &amp; &quot;Payments.Type = '&quot; &amp; Me.SelType &amp; &quot;'&quot;
    Me.Filter = FilterStr
    Me.FilterOn = True
End Sub

Private Sub SelType_AfterUpdate()
    FilterStr = &quot;&quot;
    If Nz(Me.SelPayee, 0) Then _
        FilterStr = FilterStr &amp; &quot;PayeeID = &quot; &amp; Me.SelPayee
    If Nz(Me.SelPayee, 0) And Nz(Me.SelType, &quot;&quot;) <> &quot;&quot; Then _
        FilterStr = FilterStr &amp; &quot; AND &quot;
    If Nz(Me.SelType, &quot;&quot;) <> &quot;&quot; Then _
        FilterStr = FilterStr &amp; &quot;Payments.Type = '&quot; &amp; Me.SelType &amp; &quot;'&quot;
    Me.Filter = FilterStr
    Me.FilterOn = True
End Sub

 

[RickSpr]

Rafe,

I built a replica from your very thorough description and tested it the best I could. The combo boxes never nulled out for me, even when there were no records for the payee and payment type. Is there any other code in the form that might be doing it? What are the exact queries you're using for the combo box Row Source properties? Rick Sprague

 

[rafe]

mmmm.... if you can get it to work then it sounds like I have a setting wrong somewhere... but I'll lay it all out anyway...

Me.RecordSource = SELECT Documents.PayeeID, Payments.Type, Payments.Rate, Payments.Quantity, Payments.Reference, Documents.DocID, Payments.FromDate, Payments.ToDate, Payments.Amount, Documents.Status, Documents.Stamp, Payments.Index, Payments.Account, Payments.Description, Payments.PayID
FROM Documents INNER JOIN Payments ON Documents.DocID = Payments.DocID
ORDER BY Payments.PayID DESC;

Me.SelPayee.RowSource = is actually a selparate query but equlivalent to ...
SELECT Payees.PayeeID, Trim([LastName] & ' ' & [FirstName]) AS Name FROM Payees
ORDER BY Trim([LastName] & ' ' & [FirstName]);

Me.SelType.RowSource is a value list...
"Appt";"GRA";"Cons";"Intm";"Vend";"Extn";"TC";"Remb";"Cash";"Subc";"PO"

No other code. But i suspect these may be wrong...

Me.AllowFilters = Yes
Me.AllowDeletions = No
Me.AllowEdit = Yes
Me.DataEntry = Yes

then again, it may be something else

thanks for the help

rafe

 

[rafe]

ps: it's only the Me.SelType (2nd & last field in header) that's blanking

 

[RickSpr]

Now this stretches my credulity! Just last night, I worked out a problem for ImpMan (thread702-50788) that turned out to be an unreported Access bug. In his case, a union query that returned no records aborts the painting of the form, with the result that form header fields were not being displayed properly.

After building a version of your database, the same thing is happening except that in your case, it's not a union query. However, it's still that your form painting is being aborted, and the header fields aren't painted properly.

But even when your SelType combo box appears blank, there is actually data there. The font isn't rendered because of the form painting being aborted, which is happening because your recordset is empty. If you select the contents of SelType and copy and paste it to Notepad, though, you'll see there is actual data in the control. (The screen updating seems to lag behind by 1 cycle, so very oddly, the data that does appear is sometimes not the current value of the control, but its value from the previous combo box setting!)

In your case, the empty recordset is apparently being caused by your filter settings.

So this is either the same, or a very similar, Access bug. It seems to work the same in A97 or A2K, and a search of Knowledge Base didn't find it (but then, last night I was searching for "union query", so maybe it's there after all).

Here's a workaround: After changing your Filter and FilterOn properties, set the ForeColor of your misbehaving controls to 0 (black). That seems to get them repainted properly. Rick Sprague

 

[rafe]

Thank you!

Although I haven't gotten the workaround to work just yet & the symptoms are (just slightly) different, knowing that this is an MS problem & not a Rafe problem is important. It completely changes how I go about looking for solutions.

I must say, that I'm quite impressed by your level help & knowledge … in this thread & in others.

Thank you again.

rafe