TAB For each record - Is it possible? (1 Viewer)

[Laser]

Hello all.. I am hoping that the gurus on this forum can answer a query for me. I would like to be able to create a tab control where there is a separate tab for each record in the same table. There would be no more than five records at any given time within the table so no more than five tabs would be needed. Is this possible within the limitations of Microsoft Access or am I blowing in the wind?.

Although I have searched Google extensively I cannot find much reference to the creation of tabs from records in a table. Or it may just be that my search criteria is flawed. Basically I have an order form with a order details subform and it is this subform that I would like tabs to reflect each record from order details.

As usual all and any help will be appreciated.

 

[Minty]

This would be an intrinsically poor design, simply because what happens when there are 6 records in the order detail.
Although you say it's not currently an issue, things change in business processes all the time, sometimes drastically.

If there is a lot of text in the order details lines then consider a pop-out form to display it all, or maybe just that field on a separate tab.
So tab 1 most of the details for all the orederlines , tab2 just the big detail of the currently selected subform records on tab 1

 

[Laser]

Hello Minty.. Thanks for your prompt reply. I accept and understand your concerns regarding future expansion however that will not apply in this database due to the business structure of the customer who is in the service sector as opposed to the consumables sector so therefore does not require many entries within order details. But Just for clarification.. what I already have is as follows:

As you can see, I have two order detail records visible to the user on a continious form. If the user wants to add another record or see a third record they are currently required to use the scroll bar to do so. Showing two records of this size takes up a considerable amount of screen real estate which is why I am looking for a tab option as below which will allow more real estate to be used.

My idea is that item1-item5 would have the TAB name changed programmatically to reflect the product ID so the customer can easily identify and click on the tab to see that record and make adjustments to price etc instead of having to scoll up and down which can confuse the end user as to which record they are viewing.

The data within the tab control would be exactly the same as in the continuous forms shown above. Is it doable? Or am I wasting my time researching this.

 

[MajP]

See demo. This handles up to 20 records, but you could do more.

 

[Minty]

@MajP That's a nice demo, and I can see how that works for that data.

I still think I would simply put the order lines on one side of the form and the larger details section on the other, save tabbing around the form.

 

[MajP]

I had that demo just to show a concept, I doubt I would ever do it this way. I can think of lots of betters ways to do it. Might want to look at @isladogs emulated split form that would be one way to have a list and a detail section. Reducing scrolling and real estate.

Emulating the split form

Like many users of this forum, I abandoned split forms a long time ago as I found them difficult to adapt if you want to modify them in any way In a recent thread another user wanted to keep the idea but to emulate it using a single form with a continuous subform. See this thread...

www.access-programmers.co.uk

Here is another simple approach which is a listbox to show your items (partners)

 

[The_Doc_Man]

I think I understand what you are suggesting. People are trying to steer you away from the design implied by your original question but they are not telling you WHY their way is more desirable. I will tell you. It is certainly possible to do exactly what you said you wanted to do, but I doubt you will enjoy the extreme headache it implies.

In order to populate a bound form you need to anchor it in some way. This anchor is usually an underlying recordset. Normally, for a parent/child form, the parent has a .RecordSource and the child has a different .RecordSource which is somehow dependent on some field in the parent. Access ties them together with a parent/child linking field as part of the parent/child form declaration, usually a prime-key/foreign-key relationship. In fact, if you had a formal relationship, the form wizard would see it and use it, skipping any questions about how you wanted to link the two recordsets. This is an automatic, behind-the-scenes linkage where one parent record (at a time) links to one child record (at a time). Access does this for you quietly without much effort. Building this is trivial.

When you are using Tab controls, however, the tab "surfaces" are all counted as being extensions of the same form. In that case, you have one and only one complex form with one and only one .RecordSource - but your desired design needs to work with to up to six record sources - the parent and up to five different child records simultaneously. This means that each tab must "roll its own" because "straight up" Access will not be able to easily see the child records for the various tabs. There is no implied mechanism for it, which differs from the normal parent/child form layout. Navigating the parent form from one customer record to another will not automatically move anything else. You will be forced to write your own record maintenance code just to be able to see the child records. The Form_Current routine will be a true nightmare.

Your life gets even more complicated if you not only wanted to SEE the five detail records but also wanted to somehow interact with them, like edit them in some way, or delete or add a child record. Since they cannot be part of the primary .Recordsource, the detail data cannot be held in normal bound fields. They would have to be unbound. Therefore, Access will not automatically update them if you try to edit anything. Since they would be unbound, Access would not automatically sense a "dirty" form for you.

The code to manage these child records becomes incredibly complex and - if you are lucky - will only make you write an ugly, lengthy, and confusing SQL statement. But since it potentially involves a complex set of combined records with some uncertainty as to the NUMBER of combined records, I'm not even sure you can write an updateable SELECT query to drive this because of its complexity.

I don't know what you tell your client but if they demanded that design, I hope you had a high profit margin built in, 'cause to implement that exact design, you would earn it.

 

[MajP]

@The_Doc_Man,
I am guessing you missed my example but a lot of what you are saying is extremely exaggerated and not really accurate. This is not that complex as you are saying. It is simply looping the child records and then setting the captions of the tabs and hiding unused tabs. As long as the max number of possible tabs is known and reasonable this is not that hard. I did 20, but i could see realistically having up to say 100. There are no complex queries and really no complex way to manage the shown child records. Further there is no issue with editing the child records. I did not add a feature to add a new child record, but that should not be overly challenging.
As I said this is a reasonable thing to attempt, but there are probably cleaner user interfaces. Most of these can be done with little code. However the entire code to do the demo is below and none of it is really complicated.

Private Sub cmboFirm_AfterUpdate()
  If Not IsNull(Me.cmboFirm) Then LoadPages (Me.cmboFirm)
    
End Sub
Private Sub Form_Load()
  Dim i As Integer
  Me.TxtFocus.SetFocus
  For i = 0 To Me.tabCtlIndex.Pages.Count - 1
    Me.tabCtlIndex.Pages(i).Visible = False
  Next i
  Me.TxtFocus.SetFocus
  Me.subFrmPartner.Visible = False
End Sub

Public Sub LoadPages(FirmName As String)
  Dim RS As DAO.Recordset
  Dim tc As TabControl
  Dim PartnerCount As Integer
  Set tc = Me.tabCtlIndex
 
  Set RS = CurrentDb.OpenRecordset("Select * from TblPartners where FirmName = '" & FirmName & "'")
  If Not RS.EOF And Not RS.BOF Then
    RS.MoveLast
    RS.MoveFirst
    PartnerCount = RS.RecordCount
   ' AddNames
  End If
  Me.Painting = False
  Me.TxtFocus.SetFocus
  AddNames RS
  HidePages 0
  ShowPages PartnerCount
  Me.subFrmPartner.Visible = True
  Me.Painting = True
End Sub

Public Sub HidePages(PartnerCount As Integer)
  Dim i As Integer
  For i = PartnerCount To Me.tabCtlIndex.Pages.Count - 1
    Me.tabCtlIndex.Pages(i).Visible = False
  Next i
End Sub
Public Sub ShowPages(PartnerCount As Integer)
  Dim i As Integer
  For i = 0 To PartnerCount - 1
    Me.tabCtlIndex.Pages(i).Visible = True
  Next i
End Sub
Public Sub AddNames(RS As DAO.Recordset)
  Dim PartnerName As String
  Dim i As Integer
  Do While Not RS.EOF
     PartnerName = RS!FullName
     With Me.tabCtlIndex.Pages(i)
       .Caption = PartnerName
       .Tag = RS!PartnerID
     End With
     i = i + 1
     RS.MoveNext
  Loop
End Sub

Private Sub tabCtlIndex_Change()
  LinkSubForm
End Sub
Private Sub LinkSubForm()
  Dim PartnerID As Long
  Dim strID As String
  strID = Me.tabCtlIndex.Pages(Me.tabCtlIndex.Value).Tag
  If strID = "" Then strID = "0"
  PartnerID = CLng(strID)
  Me.subFrmPartner.Form.Recordset.FindFirst "PartnerID = " & CLng(PartnerID)
End Sub

I set the main form unbounded and picked the parent Firm from a combo. However if the main form was bound the current event would still be a simple pull of the firm name from the current record instead of the combobox.

 

[The_Doc_Man]

I DO NOT for a moment doubt that you have made something work that is very impressive and I have NO DOUBT as to your skills. However, your example actually demonstrates the point I was making. Because everything has to be unbound, NONE of the automated Access form-field management features can be made to work for you. You have to roll your own feature handler for every feature you want to implement including navigation to load things.

Perhaps it is a difference in viewpoint over the meaning of the word "simple" as applied to users who are less experienced than you or I. There is a nasty little habit that experienced people develop that causes them to think that because they know how to do X, Y, or Z it must be simple for anyone else. It almost grieves me to say it, but Adam would agree that some of us develop a blind spot to just how easy or how hard something is when dealing with people who lack our level of experience. Therefore, please do not take my comments as disparaging your code in any way.

 

[MajP]

We will have to agree to disagree. I do not take any offense, I just do not see this one as overly complex. If I post something that is varsity level I will usually caveat that it is doable, but really hard and not for faint of heart. This one I would consider intermediate and is easily modifiable. You said the controls are unbound, but alll are bound. In the example I did not bind the main form, but could easily have. See updated version that binds the main form.
You mentioned extremely complex SQL but there is no complex SQL and almost no written SQL, The only SQL is:
""Select * from TblPartners where Firmid_FK = " & FirmID"
You mentioned that controls would not be editable, but all controls are editable.
The funny thing it took you 538 words to write how complicated this would be to do, but the whole code is only 238 words. Not suggesting this is a great approach, but it just is not as hard as you make it out if the user wants to attempt it.

 

[The_Doc_Man]

We will have to agree to disagree on the viewpoint of simplicity. Again, I have ABSOLUTELY NO DOUBT that your code works like a champ. At no time was my criticism aimed at the code itself.

 

[Galaxiom]

The funny thing it took you 538 words to write how complicated this would be to do, but the whole code is only 238 words. Not suggesting this is a great approach, but it just is not as hard as you make it out if the user wants to attempt it.

Innovators are usually those who don't realise that their goal is impossible.