Results 1 to 10 of 10
  1. #1
    djclinton15 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    123

    Questing using Combo Boxes to pull the weekdays from within a sub form

    Hi. Althoughoften times I may think there must be an easy & simple solution to my inquiry, it never seems to work out that way for me. The reason being is, my database is probably improperly designed in many ways. Still, many members here are incredibly patient with me & try to help me at their best. So, I have a form that has a subform linked (or rather joined) together by 'WkDys' fld (the days of the week). In my main form I’m trying to create a combo box in the 'WkDy' fld on my main form to select that same weekday in my sub-form. No, I do not have the weekday field in 2-tables, however, for the weekday’s fields are from 2-different queries based on a date if the date field is ‘TodyDate’ then for the weekday field I have, Wkdy: Format [TodyDate], “ddd”}. I’m sure that it was unnecessary to tell you that, but I wanted you to know where the ‘weekdays’ field came from 2-different queries/tables. Already I feel like I’m talking out of my a**, but then again nobody needs to reply to my post if it sounds so senseless. If I may add, in the Row Source for the Combo Box, I thought by selecting the query from my sub-form & choosing the weekday field from that query. Then I thought I could pull up a particular weekday on my subform by selecting that same day of the week from my main form. I figured maybe, if I can link the 2-forms together based on the 'WkDy' fld from both forms than why can't I select any particular weekday from my main form & see the second form (sub-form) corresponding with the same weekday. When I tried to run the form & tried selecting weekday from my combo box of course nothing happened. When I click on the Combo box I do see a list of 7-days of theweek, but I cannot select any of them. I'm sure that I'm probably making any sense here at all. I do have a seperate table containing just the 7-days of the week & a sorting order fld. I have the days of the week set as the Primary Key. Here is an example of that table:

    WkdyID WkdySort
    Sun 1
    Mon 2
    Tue 3
    Wed 4
    Thu 5
    Fri 6
    Sat 7

    Usually, when I need to create queries involving weekdays I want them to run Sun through Sat. That is just my way of creating weekday-related queries; may it be right or wrong. I only mentioned this table called, 'WkDys_tbl' because although I knew that it wouldn't work, I tried just using this 'WkDys_tbl' as for my Row Source wondering if could pull up the corresponding weekday in the subform. As I said, I knew that idea wasn’t going to work for me. I’m including an image of my form which may provide you with a better understanding. Instead of having to go through all of the weekdays on my main form by selecting the Previous Record or the Next Record controls, I would like to have the option to pull up a certain weekday (on the subform) from the main form by using a Combo Box shown in my illustration3. I feel like an idiot, but I’m just trying to learn as I go along

    Any advice/suggestions would be greatly appreciated! Thanks in advance!

    Dammit , I'm unable to attached the image i wanted to present. Always have this trouble.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I usu make a query out of the table that includes extra fields,
    like table tData is a query qsData that has a DoW or Year only:
    DoW: format([dateFld],"ddd")


    then you can join this query or get date ranges for certain Days of Week.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Quite a long explanation you provided but I think you simply want a "searching" combo to take you to to the selected day in the main form (and therefore the subform as well if they are properly linked via WkDys field in the master\child relationship).
    The easiest way to do that is to use the combo wizard; open the form in design view, on the Design ribbon select the combo box control, click inside the form header and place the combo there. When the wizard shows up you should select Option 3 (Find a record....), just follow the prompts and you should have your combo working the way you want. Basically you want the combo to find the record (in the AfterUpdate event you will probably end up with an "embedded macro", but you can convert that to VBA to see how it works using the bookmark property of the form recordset).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    djclinton15 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    123
    Vlad, thanks a lot for your suggestion in using the Combo Box Wizard & selecting the third option. I wouldn't have considered trying the third opition. It worked me me the first time, but when I tried tweaking around with the query Well... I don't need to further "explain" do I?

    Honestly, I'm just being funny wiith you; I don't believe in sarcasm. Personally, I find it as sign of disrespctful & ignorance...

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    No worries, can you explain what kind of "tweaking" were you making to which query (the record source of the form or the row source of the query)? Maybe try to create a small sample db with just the objects needed to illustrate your issue and no sensitive\private data, few "dummy" records to allow us to see what you have.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    djclinton15 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    123
    If it's okay, I'll get back to you tomorow. My brain is fried for the night.

  7. #7
    djclinton15 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    123
    Hi Vlad. Sorry, I meant to get back to you yesterday, but I found myself sidetracked. I'm glad you were able to see my humor side & didn't think I was being smart with you. Anyways, I used the Combo Box Wizard (Option 3) in 2-separate forms & oddly enough I somehow got various results. Actually, the first time I create a Combo Box according to your steps it worked, but for some reason when I dropped down the Combo Box in the list the weekday "Sun" was listed twice. From there, I honestly don't remember what I did other than it took me at least a half a dozen times trying to get the Combo Box working. Yesterday I had to use this particular form to add data to the table & for the most part, the ‘DoB’ Combo Box worked for me fairly well. It may have hung up on me a few times, but hey, life can’t always be perfect, can it? Please give me time to think about making a smaller database; I wouldn’t know where to start. There is no sensitive/private data on this database. That is NOT an issue!

    I do have another question relating to combo boxes, but I’m not sure if you’ll feel I ought to create a new post. Some members here will only deal with one question at a time. I was explained that it’s more beneficial for the forum’s purposes. I’ll ask you anyway & see if you will respond. I have 2-combo boxes with data from a query. The data in both combo boxes (drop-down list) seems to be aligned to the right. In Design View & while selecting the combo box I tried searching in the Property Sheet under the ‘format’ tab to see if I could change the alignment & I didn’t see anything. Is there any way I can change the alignment of the text within a combo box list without having to recreate the combo boxes over?

    Vlad, I do have something else that I wanted to just run by you. I’m considering rebuilding my Music Library database over from scratch. I’ll still use the database I’ve been using for 10-years, but it is far-far from being normalized. I have 4-main tables which are sort of related by one common field. I simply cannot understand how to use Primary & Foreign keys & I have tried to teach myself for years. I wish I could understand, but I guess I’m just not that intelligent. This one common field in all 4-queries is set as the Primary Key. Except, (right or wrong) I have one Look Up field for 3-of the tables based on the fourth table which I’ll call the main table. I only did this to have consistency with the data in all 4-tables & yes, I have duplicate fields in more the one table which I know goes against Normalization. These 4-tables are linked only by a one-to-one relationship. There are a lot of entities within this database & I was thinking of just starting out creating a schema of my database. It would be a major project & one that I’m unsure if I want to start. If I did start out creating a schema might you (or anybody else here) be interested in taking a look at my schema & offer me some directions? If you don’t have the time I totally understand. I’m just throwing it out there seeing if I get any bites.

    Thanks for your time, dj

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi there dj,

    The combo should be simply be based on the tables listing the days of the week or a query based on that. Can't see how you would get Sun twice with that, can you copy the SQL for the row source and post it her? To make a sample db just start with a new Access file and import only the objects needed to illustrate the issue: the table(s), queries and the form(s). DOn't worry if you get any errors while opening the form because of missing objects, we can usually come up with a solution in that case.

    To change the alignment of individual columns in the combo's drop down list is not that easy but you might find some info here:
    https://www.pcreview.co.uk/threads/c...o-box.1138091/

    As for the last question I am sure you would find lots of great help and support here with your Music library database. I have never used lookup fields as I find them more troublesome than useful and I never really had a need to have a drop-down in a table or query as those are not meant for user-interaction.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    djclinton15 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    123
    I’m still having great difficulty in creating a weekday Combo Box within a different form. I tried using the Combo Box Wizard & using Option 3, however, when I tried using this Weekday Combo Box within the list I had 4-Sun, 3-Mon, 4-Tue, 3-Wed, etc. That is because in the Form (or query/Row Source) I have 4-Records which are on a Sun, 3-Records on a Mon, 4-Records on a Tue, 3-Records for Wed.

    Here is the SQL for the Combo Box:

    SELECT ArtistsiD_Entries_ThsWk_Yrly_qry.WkDys
    FROM ArtistsiD_Entries_ThsWk_Yrly_qry;
    I’ll give you the SQL for the Form itself:
    SELECT ArtistsID_Entries_Yrly_qry.AE_ID, ArtistsID_Entries_Yrly_qry.Mnths, ArtistsID_Entries_Yrly_qry.WkDys, ArtistsID_Entries_Yrly_qry.AEDate, ArtistsID_Entries_Yrly_qry.ArtistsID, ArtistsID_Entries_Yrly_qry.Duration, ArtistsID_Entries_Yrly_qry.DyEvn, ArtistsID_Entries_Yrly_qry.DNP, ArtistsID_Entries_Yrly_qry.[#AlbmnPlyd]
    FROM ArtistsID_Entries_Yrly_qry
    WHERE (((ArtistsID_Entries_Yrly_qry.AEDate) Between Date()-Weekday(Date())+1 And Date()+(7-Weekday(Date()))))
    ORDER BY ArtistsID_Entries_Yrly_qry.AEDate, ArtistsID_Entries_Yrly_qry.AE_ID;

    -----------------------------------------------------------

    This Form is a selection of music artists (ArtistID is the Primary Key) I’m going to listen to this week. The subform to this form is a list of albums relating or linked to each individual ArtistID. This subform is from a different table that holds all of the info for each individual Artists albums. In addition, on this subform with the Artist's albums, I keep track of the last time I played the album (Short Date) & another field I use to indicate the number of times I played an Artist’s album per year. Meaning, at the end of the year I delete the number of times I listened/played an album & start over.
    I have greatly digressed from my issue with my weekday Combo Box. I tried several things none of which worked. 1) I went into the Combo Box query (in Row Source & I just clicked on the ‘Show/Hide Totals Colum in the Query’ button & when I look at the query it only showed the weekdays once vs. several times depending on how many Record I have in that query. I closed the query & tried my weekday Combo Box & it still doesn’t work. The first SQL shown is in reference to the first SQL I then created a new query based on my main form & I took the weekdays fld & the ArtistsID fld for which I set to Count. So, for this week’s form, now for the DoW (I’ll use your label) I have one column with the weekday list & a second column showing the number of Artists I’m going to listen to on a given day. I normally listen to 3 or 4-artists every day I’ll try to send you an attached image of the Combo box, but the last time I tried to send an image I couldn’t get it to upload. But I will send you the SQL to query as well:

    SELECT [_ArtistsID_Entries_Wkdys_cmbobx].WkDys, [_ArtistsID_Entries_Wkdys_cmbobx].CountOfArtistsID, [_ArtistsID_Entries_Wkdys_cmbobx].WkDyNumID
    FROM _ArtistsID_Entries_Wkdys_cmbobx
    ORDER BY [_ArtistsID_Entries_Wkdys_cmbobx].WkDyNumID;

    -----------------------------------------------------------------------------

    Not to complicate things here Kald, for years I used Datasheet subforms whe3re all I had to do is click on each Record to bring down the subform. Except, now I’m trying to create & use actual “Forms,” but I’m not having much luck with these Combo Boxes. Please let me know fn you have any more suggestions. Thanks.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think you are over complicating things; if what you want the combo to do is to navigate the form to the specific week day you select in the combo you need to ensure:
    1. The combo is unbound (nothing in the control source property)
    2. The combo's row source type is set to Table\Query and the Row Source is something like this (adjust for your table name):
    "SELECT WkdySort,WkdyID from tblYourWeedDayLookupTable Order By WkdySort;"
    3. The combo should be set to have Bound column =1, Columns=2, Column Width=0";1" (the day number is hidden)
    4. In the combo's AfterUpdate event replace the [embedded macro] if you have one with the VBA code below (again, please adjust to match the control names):
    Code:
    Sub CboMoveTo_AfterUpdate ()
        Dim rs As DAO.Recordset
    
        If Not IsNull(Me._ArtistsID_Entries_Wkdys_cmbobx) Then
            'Save before move.
            If Me.Dirty Then
                Me.Dirty = False
            End If
            'Search in the clone set.
            Set rs = Me.RecordsetClone
            rs.FindFirst "[WkDys] = " & Me._ArtistsID_Entries_Wkdys_cmbobx
            If rs.NoMatch Then
                MsgBox "Not found: filtered?"
            Else
                'Display the found record in the form.
                Me.Bookmark = rs.Bookmark
            End If
            Set rs = Nothing
        End If
    End Sub
    The posted code is from http://allenbrowne.com/ser-03.html

    If you still have problems please try to create a sample, not much data needed, just a couple of dummy records. If you run a compact and repair and then zip it it should be small enough to upload here.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-22-2021, 09:00 AM
  2. Replies: 6
    Last Post: 10-08-2020, 04:31 PM
  3. combo box in form to pull report based on month
    By CRobledo in forum Programming
    Replies: 3
    Last Post: 12-07-2016, 11:49 AM
  4. Replies: 11
    Last Post: 08-06-2014, 09:47 AM
  5. Replies: 9
    Last Post: 06-04-2014, 10:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums