none
Can I display multiple records in unbound textboxes? RRS feed

  • Question

  • I asked this question earlier regarding a database I'm creating. I figured out the back end, but I'm having trouble making the front end appealing to my co-workers. So I'm trying to grab a template they have already been using and transforming my database around it. I need to display multiple records (contact information of employees), simultaneously in this layout. Is this possible?

    Here's the picture I posted:

    Tuesday, August 8, 2017 3:53 PM

Answers

  • Let me see if I understood correctly. So I make an unbound form (fully blank, nothing in it), then add a subform form it. This subform will grab data from a query or table, in this case all the contact information. I sort of follow so far... here's where I loose you, when you say ObjectSource are you talking about an unbound object frame?

    Hi Jaime,

    In the development stage you make a Main form, including the controls for the subforms ("grey bozes"). You have made a form (e.g. Person_form) that will contain the Person information and that fits in the "grey box". That form is used as SourceObject in the "grey box" control.

    If you want, you can already fill this value in the subform control.

    In the production version you can personalize the different grey boxes by assigning different RecordSources to the (same) subforms during the opening of the Main form:

        Me!Greybox1.Form.RecordSource = "SELECT * FROM Person_tbl WHERE Person_id = " & loop_set!Person_id
        loop_set.MoveNext
        Me!Greybox2.Form.RecordSource = "SELECT * FROM Person_tbl WHERE Person_id = " & loop_set!Person_id
        loop_set.MoveNext
        Me!Greybox3.Form.RecordSource = "SELECT * FROM Person_tbl WHERE Person_id = " & loop_set!Person_id

    I hope it is not too cryptic.

    Start small, with a few subform controls, and (to make it easy) on all controls the same person. As you master it, go to the next extension.

    Imb. 

    • Marked as answer by InnVis Wednesday, August 9, 2017 9:46 PM
    Wednesday, August 9, 2017 8:40 PM
  • I guess where I'm getting confused at is when I try to manually change the source object it changes the entire subform. Then when I go into the subform when I change the record source it takes out all the data to a different record. That all makes sense to me, the issue is I only see 1 record on this subform, and changing the record source changes the record. I need to view all 60 something records at the same time. I'll show you a screenshot

    Hi Jamie,

    That is right, per subform you see only ONE record. Therefore you need as many subforms as you need to see Persons.

    Make 2 or 3 subforms on the Main form, give then the same SourceObject, but modify the RecordSource of the underlying form. Do you see now the 2 or 3 different Persons, but displayed in the same way?

    Imb.

    • Marked as answer by InnVis Wednesday, August 9, 2017 9:45 PM
    Wednesday, August 9, 2017 9:31 PM

All replies

  • Do you want to display multiple records in each grey box? If so, you can concatenate records together as one string and place it in the box. Is this what you wanted? If not, can you please explain a bit more? Thanks.
    Tuesday, August 8, 2017 3:56 PM
  • Yes, I want to have each employee's contact info displayed per gray box; i.e.

    gray box 1:               gray box 2:                  .....

    employee 1               employee 2                  .....

    phone      1               phone      2                  .....

    email       1               email       2                  .....

    Tuesday, August 8, 2017 4:00 PM
  • Okay, thanks for the clarification. Then it should be easier than I first thought. You can loop through the recordset and display each record in a box. Perhaps you could take a look at some Appointment Calendar demos to see how to do it.

    Hope it helps...

    Tuesday, August 8, 2017 4:10 PM
  • Do you know where I can find some free demos of what you're talking about? I've been searching and can't find an Appointment Calendar database that is free and has access to developer options.
    Tuesday, August 8, 2017 4:39 PM
  • Hi,

    Have you looked at UtterAccess? Not sure if there's an exact one for you but maybe there's one enough to get you started. Good luck!

    • Marked as answer by InnVis Tuesday, August 8, 2017 4:51 PM
    • Unmarked as answer by InnVis Wednesday, August 9, 2017 3:26 PM
    Tuesday, August 8, 2017 4:45 PM
  • Thanks!
    Tuesday, August 8, 2017 4:51 PM
  • Hi,

    You're welcome. Let us know how it goes...

    Tuesday, August 8, 2017 5:13 PM
  • Having no luck >.< I found a database with an appointment calendar, but it's way more complicated to reverse engineer this.
    Tuesday, August 8, 2017 7:48 PM
  • Hi,

    Sorry to hear it. I'd say just focus on the part where the demo displays the records in each box and don't worry about how the boxes are dynamically created on the form.

    It should be something like looping through the records for each particular day and then adding each record in the box.

    Hope it helps...

    Tuesday, August 8, 2017 7:51 PM
  • The problem I'm having is that I need a dynamic roster of all staff members of a nursing ward per unit. Nurses use this as quick reference to call staff in. The issue is, each floor does it on either an excel or powerpoint in a similar format to what I showed above. Without that format, there will be no compliance with this database. It's imperative that I get all data in one database, because as it stands I need 1 person to go through all of the different floors and manually compile a master list. We're talking about thousands of records that are being imputed twice.
    • Proposed as answer by Imb-hb Tuesday, August 8, 2017 10:06 PM
    • Unproposed as answer by Imb-hb Tuesday, August 8, 2017 10:06 PM
    Tuesday, August 8, 2017 8:05 PM
  • Are you able to share your database with dummy data? It might be easier to help you if we can see what you're looking at.

    Just my 2 cents...

    Tuesday, August 8, 2017 8:23 PM
  • I emailed you a copy. I tried to reverse engineer an appointment Calendar, but holy hell the coding was so large that I couldn't read my way around the part I needed to dissect.
    • Edited by InnVis Tuesday, August 8, 2017 8:59 PM
    Tuesday, August 8, 2017 8:55 PM
  • Okay, thanks. I'll take a look and let you know.
    Tuesday, August 8, 2017 8:57 PM
  • The problem I'm having is that I need a dynamic roster of all staff members of a nursing ward per unit.

    Hi Jaime,

    I accidentally hit the "Propose as answer" function, so I did unpropose it again.

    What is the ordering in your roster? Do the columns represent the different floors? And the rows the days of week. Or is it something different. What is the meaning of the lifted most left and most right column, or is it just cosmetic?

    Is the number of columns or rows static or can it change depending on certain circumstances?

    Imb.

    Tuesday, August 8, 2017 10:21 PM
  • Hi J. Alexander Batista,

    you can try to dynamically generate the textboxes in the form.

    you can create the multiline textbox and display each field in new line. make the textbox disable for edit.

    then you can fetch one record and display in one textbox.

    then again move to next record in table , creating a new textbox for it and follow the same process till the end.

    below is the sample code to generate textbox dynamically.

    Private Sub Command0_Click()
    Const strForm = "formname"
        Const strCtl = "txtTest"
        Dim frm As Form
        Dim ctl As Control
        DoCmd.OpenForm FormName:=strForm, View:=acDesign
        Set frm = Forms(strForm)
        Set ctl = CreateControl(FormName:=strForm, ControlType:=acTextBox, _
            Left:=100, Top:=100, Width:=200, Height:=180)
        ctl.Name = strCtl
    
        RunCommand acCmdFormView
        Forms(strForm).Controls(strCtl).Value = "demo text"
    
    
    End Sub

    below is the code to loop through records in a table.

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Contacts")
    
    
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True
          
          
            sContactName = rs!FirstName & " " & rs!LastName
    
            'you need to create textbox here and assign the data
            
            rs.MoveNext
        Loop
    Else
       
    End If
    
    MsgBox "Done..."
    
    rs.Close
    Set rs = Nothing

    you can try to refer the code and make changes according to your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 9, 2017 1:27 AM
    Moderator
  • Everything is just cosmetic. I need to display at least 55-60 records all at once in a similar format to what I posted above. Having done a lot of research, I may have formatted my data incorrectly. I used a contact template where each row is a different record and each column is the specific data for that record. I feel that I may have to layout my database as I would a calendar and make multiple records all In a few number of rows and enter all data for each contact on 1 column at a time. Something that would be helpful is, what is the function for adding a textbox record in a column without deleting the current information, instead entering it below?
    Wednesday, August 9, 2017 10:28 AM
  • Everything is just cosmetic. I need to display at least 55-60 records all at once in a similar format to what I posted above.

    Hi Jaime,

    Of course all is just cosmetic, but what I meant is the lifting of the first column and the last column functional, or just because it is pretty to see.

    I made quite a lot of roster-type designs that are quite dynamical. It makes no difference what is displayed or how it is displayed: the user has all kind of choices. But for this you need quite a lot of routines to work with generalized forms, and I guess you don't have them.

    In my opinion you can succeed in your goal by making a form with the different "grey blocks" defined as subform controls, if necessary all invisible. The control names are systematics names.

    For the display of the actual data you make a single record unbound form. In the Open or Load event of the main form you assign an sql-string to the SourceObject of the subform control the identifies that one person, e.g.

           sql-string = "SELECT * FROM (Person_tbl INNER JOIN Other_tbl ON .....) WHERE Person_id = " & cur_set.Person_id

    In the Open or Load event you have selected a recordset that contains all Persons to be displayed. While looping through the RecordSet, you select a next subform control, and assign that specific string to the SourceObject.

    Imb.

    Wednesday, August 9, 2017 11:06 AM
  • There is no purpose to the lift, I did it that way to fill as much as possible and maintain the upper 3 as "leadership" in the chain of command. I don't know if this helps, but this is the layout I'm trying to mimic, but in an access database where each department can add their own and all the data is compiled into a master list for export in excel.

    Wednesday, August 9, 2017 11:39 AM
  • That's brilliant. You may have just given me an idea. It will take A LOT repetition during the building, but If I make an unbound form and add list boxes and query that list box to display only 1 record, I am able to create many list boxes to display all of the records I want in 1 form!!! Now the only issue is, how can I make a list box display the data in 1 column with multiple rows instead of 1 row with multiple columns?
    • Marked as answer by InnVis Wednesday, August 9, 2017 2:49 PM
    • Unmarked as answer by InnVis Wednesday, August 9, 2017 2:49 PM
    Wednesday, August 9, 2017 12:13 PM
  • That's brilliant. You may have just given me an idea. It will take A LOT repetition during the building, but If I make an unbound form and add list boxes and query that list box to display only 1 record, I am able to create many list boxes to display all of the records I want in 1 form!!! Now the only issue is, how can I make a list box display the data in 1 column with multiple rows instead of 1 row with multiple columns?

    Hi Jamie,

    I hope I did understand you in the right way.

    A list box is just a control, based on a recordset with multiple records. You can use such a recordset and loop through it, while choosing your subform controls ("grey boxes") in a vertical manner.

    Imb.

    PS: You have to do all this kind of definition in VBA. But nothing is wrong with that. Once you master it, you can do almost anything.

     
    • Edited by Imb-hb Wednesday, August 9, 2017 12:38 PM PS added
    Wednesday, August 9, 2017 12:33 PM
  • If I use the loop method, will I be able to view all records at once without having to click on each individual gray box?
    Wednesday, August 9, 2017 12:39 PM
  • If I use the loop method, will I be able to view all records at once without having to click on each individual gray box?

    Hi Jaime,

    In the Open or Load event of the main form you can loop through each record that you need. In this loop you also determine which "gray box"you will use, eventually set its position to the right place, set the ObjectSource of the subform control (that is the form used as subform), and set the RecordSource of that form using an one-record sql-string. In a previous reply I did not write this correct.

    Then all persons should be visible at the same time.

    Eventually you can make each "gray box" clickable to display far more information concerning the person in question.

    Imb.

    Wednesday, August 9, 2017 2:15 PM
  • Hi J. Alexander Batista,

    you can try to dynamically generate the textboxes in the form.

    you can create the multiline textbox and display each field in new line. make the textbox disable for edit.

    then you can fetch one record and display in one textbox.

    then again move to next record in table , creating a new textbox for it and follow the same process till the end.

    below is the sample code to generate textbox dynamically.

    Private Sub Command0_Click()
    Const strForm = "formname"
        Const strCtl = "txtTest"
        Dim frm As Form
        Dim ctl As Control
        DoCmd.OpenForm FormName:=strForm, View:=acDesign
        Set frm = Forms(strForm)
        Set ctl = CreateControl(FormName:=strForm, ControlType:=acTextBox, _
            Left:=100, Top:=100, Width:=200, Height:=180)
        ctl.Name = strCtl
    
        RunCommand acCmdFormView
        Forms(strForm).Controls(strCtl).Value = "demo text"
    
    
    End Sub

    below is the code to loop through records in a table.

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Contacts")
    
    
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True
          
          
            sContactName = rs!FirstName & " " & rs!LastName
    
            'you need to create textbox here and assign the data
            
            rs.MoveNext
        Loop
    Else
       
    End If
    
    MsgBox "Done..."
    
    rs.Close
    Set rs = Nothing

    you can try to refer the code and make changes according to your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hey Deepak, my apologies if this sounds like a dumb question, but what do you mean by you "you need to create a textbox here and assign the data." I went ahead and created a textbox in the form named txtName1 = sContactName but it's not functioning >.<
    Wednesday, August 9, 2017 3:29 PM
  • Imb, does this mean I need a separate subform for each "grey box?"
    Wednesday, August 9, 2017 7:06 PM
  • Imb, does this mean I need a separate subform for each "grey box?"

    Hi Jaime,

    On the main form you will have as many controls ("grey boxes") as you need to display, but all these controls have the same form as ObjectSource,

    In case you also want to display different information in such a control, you could give it a different form as ObjectSource.

    In my applications I use one Main form and one subform to make a million and one different "visible forms" just by changing the properties of the two forms.

    Imb.

    Wednesday, August 9, 2017 7:33 PM
  • Let me see if I understood correctly. So I make an unbound form (fully blank, nothing in it), then add a subform form it. This subform will grab data from a query or table, in this case all the contact information. I sort of follow so far... here's where I loose you, when you say ObjectSource are you talking about an unbound object frame?
    Wednesday, August 9, 2017 8:12 PM
  • Pardon me for jumping in but I think Imb is referring to the "SourceObject" property of the subform control.
    Wednesday, August 9, 2017 8:15 PM
  • So use the unbound form and add another unbound form to it in a form of a "subform." Then in the subform add how ever many hundreds of textboxes and program the change event of each box to display a record based on what my main form is telling them to display?
    Wednesday, August 9, 2017 8:22 PM
  • So use the unbound form and add another unbound form to it in a form of a "subform." Then in the subform add how ever many hundreds of textboxes and program the change event of each box to display a record based on what my main form is telling them to display?

    Nope, I don't think that's what he meant. If you go back to the appointment calendar demo you downloaded, you'll probably notice it only has one subform (copied multiple times) to represent all the days in a month. However, each subform displays a different date based on its position in the main form. This is done using code.
    Wednesday, August 9, 2017 8:26 PM
  • However, I will go ahead and say it here. Another option you might consider is to use Excel Automation. If the end goal is to export the data to Excel, then you can create an Excel template and just populate it with your records from Access. If so, you don't have to worry about creating a form in Access.

    Just a thought...

    Wednesday, August 9, 2017 8:28 PM
  • Let me see if I understood correctly. So I make an unbound form (fully blank, nothing in it), then add a subform form it. This subform will grab data from a query or table, in this case all the contact information. I sort of follow so far... here's where I loose you, when you say ObjectSource are you talking about an unbound object frame?

    Hi Jaime,

    In the development stage you make a Main form, including the controls for the subforms ("grey bozes"). You have made a form (e.g. Person_form) that will contain the Person information and that fits in the "grey box". That form is used as SourceObject in the "grey box" control.

    If you want, you can already fill this value in the subform control.

    In the production version you can personalize the different grey boxes by assigning different RecordSources to the (same) subforms during the opening of the Main form:

        Me!Greybox1.Form.RecordSource = "SELECT * FROM Person_tbl WHERE Person_id = " & loop_set!Person_id
        loop_set.MoveNext
        Me!Greybox2.Form.RecordSource = "SELECT * FROM Person_tbl WHERE Person_id = " & loop_set!Person_id
        loop_set.MoveNext
        Me!Greybox3.Form.RecordSource = "SELECT * FROM Person_tbl WHERE Person_id = " & loop_set!Person_id

    I hope it is not too cryptic.

    Start small, with a few subform controls, and (to make it easy) on all controls the same person. As you master it, go to the next extension.

    Imb. 

    • Marked as answer by InnVis Wednesday, August 9, 2017 9:46 PM
    Wednesday, August 9, 2017 8:40 PM
  • I think I understand now. Thank you guys for being patients. So I see that I can modify the SourceObject to change the subform, but the catch is I need to create an obscene amount of forms (1 for each record) so I can scroll through them by changing the SourceObject. Am I correct?
    Wednesday, August 9, 2017 8:56 PM
  • ... but the catch is I need to create an obscene amount of forms (1 for each record)...

    Hi,

    If you follow Imb's instructions, you'll only need to create two forms: the main form and the subform. The other records will just use a copy of the subform in separate subform controls on the main form. Again, the setup should be similar to an appointment calendar.

    Just my 2 cents...

    • Marked as answer by InnVis Wednesday, August 9, 2017 9:46 PM
    • Unmarked as answer by InnVis Wednesday, August 9, 2017 9:46 PM
    Wednesday, August 9, 2017 9:00 PM
  • I think I understand now. Thank you guys for being patients. So I see that I can modify the SourceObject to change the subform, but the catch is I need to create an obscene amount of forms (1 for each record) so I can scroll through them by changing the SourceObject. Am I correct?

    Hi Jamie,

    No, that is not correct.

    If you only want to display the same kind of information in the subform controls, you only need ONE form "Person_form", that is used for all the SourceObjects of the subform controls.

    The difference between the subforms is that they each get their own RecordSource. This can be done dynamically using a recordset with all te relevant persons, and modify the RecordSources accordingly during opening (loading) the main form.

    Imb.

    Wednesday, August 9, 2017 9:07 PM
  • I guess where I'm getting confused at is when I try to manually change the source object it changes the entire subform. Then when I go into the subform when I change the record source it takes out all the data to a different record. That all makes sense to me, the issue is I only see 1 record on this subform, and changing the record source changes the record. I need to view all 60 something records at the same time. I'll show you a screenshot

    • Marked as answer by InnVis Wednesday, August 9, 2017 9:45 PM
    • Unmarked as answer by InnVis Wednesday, August 9, 2017 9:45 PM
    Wednesday, August 9, 2017 9:17 PM
  • I guess where I'm getting confused at is when I try to manually change the source object it changes the entire subform. Then when I go into the subform when I change the record source it takes out all the data to a different record. That all makes sense to me, the issue is I only see 1 record on this subform, and changing the record source changes the record. I need to view all 60 something records at the same time. I'll show you a screenshot

    Hi Jamie,

    That is right, per subform you see only ONE record. Therefore you need as many subforms as you need to see Persons.

    Make 2 or 3 subforms on the Main form, give then the same SourceObject, but modify the RecordSource of the underlying form. Do you see now the 2 or 3 different Persons, but displayed in the same way?

    Imb.

    • Marked as answer by InnVis Wednesday, August 9, 2017 9:45 PM
    Wednesday, August 9, 2017 9:31 PM
  • You know what, what threw me off was the fact that when I created a new subform with the same SourceObject, the second forms look weird. Here's what I see:

    Wednesday, August 9, 2017 9:37 PM
  • Hi Alex,

    Yes, that is normal since you only need to see one actual copy of the subform in design view - the rest are like placeholders.

    Didn't you see the same thing when you examined the appointment calendar form?

    Wednesday, August 9, 2017 9:39 PM
  • No, the guy who created it did something extremely difficult with his tables. He had 3 tables that copied data to each other and his main table was horizontal instead of vertical. For his subform he used a multi item form and the data replicated itself vertically instead of needing 1 new subform for every record like me in this case.
    Wednesday, August 9, 2017 9:44 PM
  • No, the guy who created it did something extremely difficult with his tables. He had 3 tables that copied data to each other and his main table was horizontal instead of vertical. For his subform he used a multi item form and the data replicated itself vertically instead of needing 1 new subform for every record like me in this case.

    Ah, I guess you weren't looking at the same demo I saw before. Here's a screenshot from someone's commercial product:

    Hope it helps...

    Wednesday, August 9, 2017 9:54 PM
  • lol, had I see that before hand I would of saved myself 2 days of headache! Thanks again ImB and .theDBguy, I appreciate the help and patience.

    • Edited by InnVis Wednesday, August 9, 2017 10:10 PM
    Wednesday, August 9, 2017 9:58 PM
  • lol, had I see that before hand I would of saved myself 2 days of headache!
    Yea, sorry. I guess I should have clarified earlier which demo you found, so I could have shown you this sooner.
    Wednesday, August 9, 2017 10:08 PM
  • Oh.... my...... gosh, lol

    Wednesday, August 9, 2017 10:29 PM
  • Yay!
    Thursday, August 10, 2017 12:07 AM