Alternate colors in an Unbound Continuous Form

[SueHopson]

Hi Everyone,

All my research leads to http://www.lebans.com/alternatecolordetailsection.htm BUT it's a .mdb file I can't open so I'm not sure if it will help my learning curve or not. I also looked at this topic frim Michael R. [Row numbers and alternate colors to a Continuous Form] but couldn't decipher it.

Can anyone help me access the content please or guide me in the right direction?

For the subforms the following is true:

Property Sheet.Data
.Row Source = qryQx1_QuoteList
.Link Master Fields = CxID
.Link Child Fields = QxCxID

Basically, the subform mu users want is using an unbound List.Rowsource to pull the data since my users prefer to be able to double-click anywhere on the whole line to open the next form (onDoubleClick).

Private Sub OpenQxDetails_DblClick(Cancel As Integer)
 
    Dim strOpenQuote As String
    Dim stLinkCriteria As String
    stLinkCriteria = "[QxNbr]=" & Me.[QxNbr]
    strOpenQuote = "frmMainQuote"
    DoCmd.OpenForm strOpenQuote, , , stLinkCriteria, acFormEdit
 
End Sub

I did show them a version where the subform is bound List.Rowsource as a continuous form. Unfortunately, they didn't like that version because it forced them to click an "Open" hyperlink at the end of the record to open the next form (instead of being able to double clikc anywhere on the line). HOwever, they did like the alternate row colors and have asked if it's possible to keep that feature.

Alternatively, is there a way to make the records on the bound form behave the same way they would in an unbound list?

[NeoPa]

Hi Sue.

I'm not absolutely sure I understand what you're asking for so I thought I'd just mention that Forms in ACCDBs (At least from my version 2019 & onwards but likely all of them) have alternating colours for Detail section backgrounds as standard. The properties to set are Back Color & Alternate Back Color. It's no longer necessary to manage this in complicated code.

[SueHopson]

Hey Neo :)

That's the version I have been working off, and it works great.

The original problem came from the previous designer's code. By using an unbound list set with column counts, she was able to display the data in a list display that would highlight the whole row whenever you clicked on any individual field. Then if the user double-clicked anywhere it would open the next form.

I think I have found an inelegant solution to being able to double-click anywhere on the bound form and have the form open, mimicking the original "style" if you will.

Private Sub frmOpenQuote_DblClick(Cancel As Integer)
    Dim strOpenQuote As String
    Dim stLinkCriteria As String
    stLinkCriteria = "[QxNbr]=" & Me.[QxNbr]
    strOpenQuote = "frmMainQuote"
    DoCmd.OpenForm strOpenQuote, , , stLinkCriteria, acFormEdit
End Sub

This works well if I apply the code to each of the fields on the form detail, and since there are only 3 on my first subform it's an easy fix.
So these are all using the same code as above at the moment.

1. Private Sub QxNbr_DblClick(Cancel As Integer)
2. Private Sub QxStatus_DblClick(Cancel As Integer)
3. Private Sub QxDescription_DblClick(Cancel As Integer)
4. Private Sub Form_DblClick(Cancel As Integer)

Private Sub frmOpenQuote_DblClick(Cancel As Integer)

I had originally tried calling the frmOpenQuote code for the other subs, but I kept getting this error, so that's perplexing me a bit...
Compile error: Sub or Function not defined

Private Sub Form_DblClick(Cancel As Integer)
    Call frmOpenQuote
End Sub

I've also set this code to highlight the record when it's selected:

Private Sub Detail_HasFocus()
    Detail.BackColor(Me.Color) = vbBlue
End Sub

So not ideal but it look very similar and is functional.

[NeoPa]

Hi Sue.

I'm guessing this means you fundamentally have what you need now then (If not then let us know).

As for the code that errors on you, this is becaise you are trying to call a Form object directly, rather than the Double-Click Event Procedure associated with that Form. Very different entities.

When you want the same code to run for various different Events, as you seem to be trying to do, it is possible to write one Event Procedure with the (common) code in it (Not to be confused with The Common Cold of course.) and then call that particular Event Procedure from all the other Event Procedures that require the same code to run. However, this is frankly a messy approach. What I always recommend is to separate the common code out into its own separate (standard) procedure and then get each of the Event Procedures to call this code. You can even set the properties of the Events on the object to call this standard procedure directly and avoid even the stub of the Event procedure in your code - but only if it's a Function Procedure --> One that returns a value.

Whichever way you choose to do that it's better to encapsulate that logic in its own procedure and invoke it from the Events. I hope that makes sense.

[SueHopson]

NeoPa = My Access Hero!
Today I learned about inserting procedures - thank you!