SUBFORM CONTROLS (1 Viewer)

[euphonium01]

I have a database I did that lists Electrical connections, Modules, Fuses etc for an old Jaguar. I have a Form, ConnectorsF, that lists all the connectors on the vehicle and a Subform, ConnectorPinsSF, that lists how many pins/wires are in that Connector. Sometimes, there's only 1 pin/wire, others can be up to 50 or so.

The Subform has navigation buttons I created to advance through each pin/wire. What I need is for the navigation buttons to disappear if there's less than 1 pin/wire, and reappear when there's more than 1. The Subform has a counter control, PINCOUNT.

How do I get the Navigation Buttons on the Subform to disappear/appear. I am very new to VBA, so please help it be simple... I have an Event Procedure attached to the Subform's OnCurrent Event -
If Me.PINCOUNT > 1 Then
Me.First Record.Visible = True
Else: Me.FirstRecord.Visible=False
End If

The NextRecord, PreviousRecord etc were done the same, but none of it works.

I realise this is probably very simple, but its not to me... can anyone help please?
Paul

 

[CJ_London]

what about if there is just 1?

 

[theDBguy]

Are you able to post a sample db with test data? Are we talking about the built-in navigation buttons or something you created? To see how many records are in the subform, you can check its RecordCount property.

Sent from phone...

 

[CJ_London]

The Subform has navigation buttons I created.....

 

[DHookom]

I’m a bit afraid to ask but could you share the relationship of the connections database?

 

[arnelgp]

you may try this on the subform's Current Event:

Private Sub Form_Current()
Dim bolVisible As Boolean
With Me
    bolVisible = (.Recordset.RecordCount > 1)
    !FirstRecord.Visible = bolVisible
    !NextRecord.Visible = bolVisible
    !PrevousRecord.Visible = bolVisible
    !LastRecord.Visible = bolVisible
End With
End Sub

 

[MarkK]

...or...

Private Property Get Buttons() As Access.CommandButton()
    Buttons = Array(Me.cmdFirst, Me.cmdPrev, Me.cmdNext, Me.cmdLast)
End Property

Private Sub Form_Current()
    SetVisibility Buttons, Me.Recordset.RecordCount > 1
End Sub

' in a library somewhere, not on the form
Public Sub SetVisibility(vEnumerable, State As Boolean)
    Dim obj As Object
    For Each obj In vEnumerable
        obj.Visible = State
    Next
End Sub

 

[euphonium01]

Hi all, many thanks for the replies so far, I intend trying these out tomorrow. For the moment, a few have asked questions.
I think the answer to the question on relationship between ConnectorsF and ConnectorPinsSF is it is a One to Many
The Subform buttons are those I created using VBA, I don't usually use the built-in Navigation Buttons
If there is just the one record in the Subform, this is the reason I want the created navigation buttons to disappear.. if there's only one record there then the created navigation buttos arent needed.
If the suggestions above don't work I will try and suss out how to post samples
Hope this answers the questions so far?

So many thanks for these replies,,,
Paul

 

[euphonium01]

Just a little bit more about the relationships question. All the Connectors have a designation such as CA1, or CA2, BB10 etc. The Connector PINs would be CA1-1, CA1-2 etc, or BB10-1 etc depending on the amount of pins there are in the connector.

 

[Edgar_]

What I need is for the navigation buttons to disappear if there's less than 1 pin/wire, and reappear when there's more than 1. The Subform has a counter control, PINCOUNT.

I have an Event Procedure attached to the Subform's OnCurrent Event -
If Me.PINCOUNT > 1 Then
Me.First Record.Visible = True
Else: Me.FirstRecord.Visible=False
End If

I realise this is probably very simple, but its not to me... can anyone help please?
Paul

That should totally work.

That said, the problem is probably that the value of PINCOUNT isn't set when the OnCurrent event runs. How are you populating the thing? I assume you're doing this, are you not?

Private Sub Form_Current()
    Me.PINCOUNT.Value = Me.Recordset.RecordCount

    If Me.PINCOUNT.Value > 1 Then
        Me.FirstRecord.Visible = True
    Else
        Me.FirstRecord.Visible = False
    End If
End Sub

Because that code up there should work.

 

[mike60smart]

Just a little bit more about the relationships question. All the Connectors have a designation such as CA1, or CA2, BB10 etc. The Connector PINs would be CA1-1, CA1-2 etc, or BB10-1 etc depending on the amount of pins there are in the connector.
View attachment 122537

In your tables what data type are the fields "Connector ID" and "Pin ID"?

 

[Ken Sheridan]

Just a little bit more about the relationships question. All the Connectors have a designation such as CA1, or CA2, BB10 etc. The Connector PINs would be CA1-1, CA1-2 etc, or BB10-1 etc depending on the amount of pins there are in the connector.

Is the PIN ID column shown in your screenshot a concatenation of two separate columns, or is it an actual column in the Pins table? What I would expect with a simple one to many relationship like this would be the following tables:

Connectors
....ConnectorID (PK)
....ConnectorName
....etc

Pins
....ConnectorID (FK)
....PinNumber
....etc

The primary key of Pins would be a composite of the two columns ConnectorID and PinNumber. The value for the latter can be easily computed automatically when a row is inserted into the table. You can then show the Pin ID as a computed column in a query or a computed control in a form or report, in each case using the expression [ConnectorID] & "-" & [PinNumber]. If, in addition to the ConnectorID and PinNumber columns, the Pin ID value were to be computed and stored in a separate column, then this would be introducing redundancy, and the consequent risk of update anomalies.

Note BTW that I have not included spaces in the table or column names. The inclusion of spaces or other special characters in object names can cause notational problems. Instead use CamelCase, as I have above, or represent a space with an underscore chacter like_this. Spaces can of course be included in the captions used in forms or reports.