Hiding duplicate fields in form

[Hakello]

Hello again,

I am trying to hide duplicate fields in a continues form (like it does in reports) but could not find a proper way to do this so I figured I'd code it in.

I created an unbound [tbVakfilter] which is empty, then run a loop that checks the field [tbVakcode] against it and if it is the same it should hide the field. At first I got an error regarding focus so I made sure it switched focus and then hides the field.

The problem is that setting the field to visible or not changes it for all entries. So my question is does someone know what I could do to fix this or maybe even know a better way to handle this?

Private Sub Form_Load()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = Me.Recordset

If rst.EOF = True Then Exit Sub

rst.MoveFirst
    
Do While rst.EOF = False

If Me.tbVakfilter = Me.tbVakcode Then
    Me.tbToetscode.SetFocus
    Me.tbVakcode.Visible = False
    
Else
    Me.tbToetscode.SetFocus
    Me.tbVakcode.Visible = True
    Me.tbVakfilter = Me.tbVakcode
    
End If

rst.MoveNext
Loop

    Set rst = Nothing
    Set db = Nothing
End Sub

 

[Uncle Gizmo]

The normal way to do this is to remove your duplicate records in the query before showing it in your form.

 

[Hakello]

Thank you Gizmo, maybe I don't understand you correctly but I don't believe I have duplicate -records-.

I'll explain better, I have a form which shows a [Student]s information with a subform that shows all his [Result]s.

These [Result]s are coupled to a [Test] which is coupled to a [Subject] (which in turn are coupled to a [Course] to which a [Student] is also coupled, (and before anyone asks, yes I handled the multiple on multiple relation correctly )

This [Result] is retrieved with a query that also gets the information from the [Test]. Each [Result] shows the [Test]s name and code as well as the [Subject] name. Seeing each [Subject] has multiple [Test]s the [Subject] name appears multiple times. I have a report that shows exactly the same information but that only shows each [Subject] once. I'm trying to achieve the same here but then in a form, as I need people to be able to edit the information shown.

Its purely aesthetic

 

[jdraw]

Since you have a 1 to many relationship

each [Subject] has multiple [Test]s the [Subject] name appears multiple times

and you are displaying all records, you will get replicate fields.

For editing purposes you may have to adjust the process
-select a Student
-select a Subject for that Student
-... perform edits.

 

[Hakello]

You are right Jdraw, this would solve my problem, but it woud also require more user actions per edit than the current system, something I wish to keep to a minimum. After all thats why I'm building this thing in the first place

Everything works fine already, its just that I find it ugly to have the same text displayed a few times. Its really easy to setup with Reports but for some reason not with Forms but I figured I gave it a try.

Here's a picture:

As you can see on the left side is a simple continues form displaying each record completly. On the right side is a report displaying each record but it only shows the vakcode when it is not the same as the one above it

 

[jdraw]

A lot of what you are doing/wanting to is design. And with most design there are several options and trade-offs.

If you are happy with what you have, except it shows duplicates - wee the trade-off is
a) accept what you have, with the inconvenience, or
b) adjust what you have to remove the inconvenience.

I would not advise you to be editing any field that is shown multiple times on your form. This seems confusing as a minimum.

Isolate what exactly you want to change and make the edit. You could always have a button to show the complete record - after the change/edit has been made.

 

[Pat Hartman]

If your subform is showing lots of "duplicate" values, you probably have a design issue and those columns should be moved "up" to a parent table. Aside from that, forms are used for editing data and reports are used for displaying data. Don't let the fact that you can use a form to display data, confuse the issue. Because forms are for editing it doesn't make any sense at all for it to support "hide duplicates". How would you know if a value was hidden or not there at all? Either fix the schema or live with the "duplicates".