Form Navigation Buttons

[lifeson]

Is it possible to have a user form which when launched shows a row of data in seperate text boxes ( I know hoe to do that bit ) but also has a set of next record/previous record navigation buttons that would show the next row of data - like on an access form?


Thanks in advance

[Bob Phillips]

Yep. You would need to store the last record number and just increment it. If the incremented number is not greater than the data max then display that record.

Similarly for the previous.

[lifeson]

XLD
Thanks for the tip
I have had a play about and come up with this
It works but probably not in the best way

I also want the option to print the form which I can do, but how would I xode the print all option?

[Norie]

Why not just print the actual worksheet?

Userforms can of course be printed but they aren't really meant for that.

[lifeson]

Why not just print the actual worksheet?

Userforms can of course be printed but they aren't really meant for that.

All the data is on one row for each record and there are about 50 columns
When printed one row would need 3 or 4 sheets
If I lay it out on a form I can get it on a A4 sheet.

[Bob Phillips]

Option Explicit

Private ItemIndex As Long
Private ItemCount As Long

Private Sub cmdClose_Click()
    Unload Me
End Sub

Private Sub cmdFirst_Click()
    ItemIndex = 1
    Call DisplayDetails
End Sub

Private Sub cmdLast_Click()
Dim dcount As Double
    ItemIndex = ItemCount
    Call DisplayDetails
End Sub

Private Sub cmdNext_Click()
Dim nxtrow As Long
Dim count As Long
ItemIndex = ItemIndex + 1
    Call DisplayDetails
End Sub

Private Sub cmdPrev_Click()
Dim nxtrow As Long
ItemIndex = ItemIndex - 1
    Call DisplayDetails
End Sub

Private Sub cmdPrintCurrent_Click()
    FrmCompMaint.PrintForm
End Sub

Private Sub UserForm_Initialize()
Dim count As Double
Dim row As Double
'select the correct worksheet
    Sheets("TblComponentDetail").Select
'identify which row is selected
    ItemIndex = 1
    'count how many records
    ItemCount = Application.WorksheetFunction.CountA(Range("A:A")) - 1
Call DisplayDetails
End Sub

Private Sub DisplayDetails()
Me.Caption = "Record " & ItemIndex & " of " & ItemCount
With Worksheets("TblComponentDetail")
    Me.txtDVD.Value = ItemIndex
    Me.txtPrem.Value = .Cells(ItemIndex + 1, 3).Value
    Me.txtGroup.Value = .Cells(ItemIndex + 1, 4).Value
    Me.txtType.Value = .Cells(ItemIndex + 1, 5).Value
    Me.txtDescription.Value = .Cells(ItemIndex + 1, 6).Value
    Me.txtMake.Value = .Cells(ItemIndex + 1, 7).Value
    Me.txtQuoteDesc.Value = .Cells(ItemIndex + 1, 32).Value
    End With
With Me
    .cmdPrev.Enabled = ItemIndex <> 1
    .cmdNext.Enabled = ItemIndex <> ItemCount
    End With
End Sub

[lifeson]

The magnificent XLD strikes again

So much more sensible than my version

Do you think the "Print All" option can be done?

I have amended the print record option to prevent the controls and backcolor being printed

Private Sub cmdPrintCurrent_Click()
Dim Ctrl As Control
For Each Ctrl In Me.Controls
    If TypeName(Ctrl) = "CommandButton" Then Ctrl.Visible = False
Next Ctrl
With FrmCompMaint
    .BackColor = &H80000009
    .PrintForm
    .BackColor = &HC0FFFF
End With
For Each Ctrl In Me.Controls
    If TypeName(Ctrl) = "CommandButton" Then Ctrl.Visible = True
Next Ctrl
End Sub

[Bob Phillips]

Maybe a bit flashy, but you could so

Private Sub cmdPrintAll_Click() 
Dim SaveIndex As Long
    SaveIndex = ItemIndex
    For ItemIndex = 1 To ItemCount 
    Call DisplayDetails
    Next ItemIndex
ItemIndex  SaveIndex
Call DisplayDetails
End Sub

__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

[Norie]

lifeson

Have you considered a Word mail merge?

[lifeson]

lifeson

Have you considered a Word mail merge?

Norie, Good idea! another way of skinning the cat

XLD
As usual Thanks for the help
I ended up with this for the print all option

Private Sub cmdPrintAll_Click()
Dim ctrl As Control
Dim SaveIndex As Long
SaveIndex = ItemIndex
For Each ctrl In Me.Controls
    If TypeName(ctrl) = "CommandButton" Then ctrl.Visible = False
Next ctrl
Application.ScreenUpdating = False
For ItemIndex = 1 To ItemCount
    MsgBox ItemIndex & " of " & ItemCount
    With FrmCompMaint
        .BackColor = &H80000009
        .PrintForm
    End With
    Call DisplayDetails
Next ItemIndex
For Each ctrl In Me.Controls
    If TypeName(ctrl) = "CommandButton" Then ctrl.Visible = True
Next ctrl
Application.ScreenUpdating = True
ItemIndex = SaveIndex
Call DisplayDetails
End Sub