Results 1 to 13 of 13
  1. #1
    StuartR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Scotland
    Posts
    46

    Dynamic values in drop down lists

    Hi,



    I have a sub form which has a drop down combo box from which users can select from either "Subscription", "Donation", or "Other"

    I have a field on the main form (not the sub form) called [member_status]

    I want to show only two choices in certain circumstances ~ ie if [member_status] is = "Family (member)" I do not want to have a choice of "Subscription" available.

    What is the best way to achieve this ?

    Thoughts I have had are creating the drop down choices as a variable string, with the string being created as the main form is loaded, thus determining the member status at that time, but I'm open to other (and probably more efficient) solutions.

    Any guidance would be appreciated.

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you use the OnCurrent event to change the selection, it will change correctly every time you move between any records on the form.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    consider basing the combo rowsource on a query that references the main form control as a parameter and eliminates the values you don't want based on the main form data. You would have to deal with the potential that the main form control doesn't contain any data. Setting the values on form load won't help if you're navigating between records. Those notions make me think the combo and main form control are bound, which doesn't seem right. If it's possible to change the main form control data, the combo value can be out of sync. The idea of having to rebuild the combo rowsource every time the form navigates to a new record doesn't quite make sense to me either.

    Another method is to use a value list, and rebuild the list in code based on the main form control, but I wonder if you shouldn't explain more about what's going on.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    StuartR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Scotland
    Posts
    46

    Dynamic Values in Drop Down List

    Quote Originally Posted by Micron View Post
    consider basing the combo rowsource on a query that references the main form control as a parameter and eliminates the values you don't want based on the main form data. You would have to deal with the potential that the main form control doesn't contain any data. Setting the values on form load won't help if you're navigating between records. Those notions make me think the combo and main form control are bound, which doesn't seem right. If it's possible to change the main form control data, the combo value can be out of sync. The idea of having to rebuild the combo rowsource every time the form navigates to a new record doesn't quite make sense to me either.

    Another method is to use a value list, and rebuild the list in code based on the main form control, but I wonder if you shouldn't explain more about what's going on.
    Micron, Thanks for your suggestions. I'll try and explain a bit more about "what's going on" ...

    The Sub Form is created from a table which lists all income payments made into the database. This "Income" table is linked with a relationship to a field in the main "member_names" table. So when you open the form with it's sub form you get a member's details, and the sub-form shows you all the payments that that member has made. The Sub form is also where you can enter new payments made by that particular member, and that is where I want to restrict the type of payment that can be entered. In our membership, it is illogical that a member with the membership type "Family (Member)" can make a subscription, [we have a type "Family (Lead)" who pay the family subscriptions] therefore I do not want the operator(s) entering payment data for a "Family (Member)", to have that option available to them. They can however still make donations and some other types of payments ~ just not subscriptions.

    I do like the idea of basing the combo rowsource on a query, but not too sure how to achieve that. The concern that the main form would not have data, I think is irrelevant, as the form is only opened from a members record.

    Does this help you understand where I am coming from ???

    Thanks again for your input.

  5. #5
    StuartR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Scotland
    Posts
    46
    Quote Originally Posted by StuartR View Post
    Micron, Thanks for your suggestions. I'll try and explain a bit more about "what's going on" ...

    The Sub Form is created from a table which lists all income payments made into the database. This "Income" table is linked with a relationship to a field in the main "member_names" table. So when you open the form with it's sub form you get a member's details, and the sub-form shows you all the payments that that member has made. The Sub form is also where you can enter new payments made by that particular member, and that is where I want to restrict the type of payment that can be entered. In our membership, it is illogical that a member with the membership type "Family (Member)" can make a subscription, [we have a type "Family (Lead)" who pay the family subscriptions] therefore I do not want the operator(s) entering payment data for a "Family (Member)", to have that option available to them. They can however still make donations and some other types of payments ~ just not subscriptions.

    I do like the idea of basing the combo rowsource on a query, but not too sure how to achieve that. The concern that the main form would not have data, I think is irrelevant, as the form is only opened from a members record.

    Does this help you understand where I am coming from ???

    Thanks again for your input.
    PS the attached screenshot shows the Form & Sub Form. The top half being the main form, the lower half being the sub form showing all the income entries with the drop down box ready for the next line of entry. This client has a membership class of "Senior", but if he was a "Family (Member)" I do not want the drop down box to have the "Subscription" option.

    Click image for larger version. 

Name:	PaymentForm.jpg 
Views:	21 
Size:	169.5 KB 
ID:	32191

    Click image for larger version. 

Name:	PaymentForm.jpg 
Views:	21 
Size:	134.0 KB 
ID:	32192

  6. #6
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If your Payment types are stored in a table add a number field called PayOptions. Set it to 1 for every payment type except subscription, set it to 2.
    In your MembershipType table do the same so that only memebership types that can subscribe = 2 others =1.

    Unfortunately you can't refer to a combo column directly in a query, so add a hidden textboxfield (call it txtPayOpotion ? )that you set to MembershipType.Column(1) on the current event of the main form.
    Now in the criteria for the payment type combo query on the sub form refer to the txtbox and make the criteria for PayOptions < Forms!YourMainForm!txtPayOption
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Another possible solution:

    1. Create a table tblClassPaymentOptions: CPO_ID, MemberClass, PaymentOption, where are listed all available payment options for every membership class;

    2. Create OnCurrent event for Main form, and AfterUpdate event for cbbMembershipClass on Main form, where RowSource property of subforms cbbPaymentType is edited. On fly:
    Code:
    Me!Subform1.Form!cbbPaymentType.RowSource = "SELECT PaymentOption FROM tblClassPaymentOptions WHERE MemberClass = " & Me.cbbMembershipClass
    when membership class is numeric
    or
    Code:
    Me!Subform1.Form!cbbPaymentType.RowSource = "SELECT PaymentOption FROM tblClassPaymentOptions WHERE MemberClass = '" & Me.cbbMembershipClass & "'"
    when membership class is text.


    Edit: rereading my post, it occurred me, that there will be a problem, when the user changes membership class for existing user with some payment history - some earlier payments may be get "broken" (i.e. combo can't display the option, because conditions for it don't match, and #NA error is displayed instead).

    To avoid this:

    1. You add into table tblClassPaymentOptions a field for date, from which the option is available, and another one for date, from which is not available (leaving the second field empty means, that the option is currently available). And you have to design the query for cbbPaymentType RowSource parameter so, that it takes into account the payment date of entry in subform - which means you have to edit the combo's RowSource not from Main form events, but from subform - probably you can use subform's OnCurrent event for this.

    2. You add into payments table a field for membership class, and you add a hidden control where current membership class from Main form is stored, when a new record is saved. And again you write events for subform, where RowSource parameter for cbbPaymentType is edited, whenever a new payment is added, or when new record in subform is selected (you don't to bother about selecting another record in Main form - it results in new record in subform anyway).

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Still not entirely clear (must be my age). You use the term Membership Type in your description but I see Membership Class on the form. If they're not the same thing, then perhaps the proposed solutions are on the right track in that you're missing some sort of common link between the combo and the payment types allowed for a Membership Type. If not, then I don't see why it's not just a matter of a query that, as I think I mentioned, retrieves the payment types based on the membership - providing the required membership data is available from the main form. The combo rowsource would be based on that query, and would have to be requeried if a new main form record is loaded due to record navigation, or as I said, if the Membership Type/Class? value can change. Alternatively, you could accomplish that in code instead of a query if that's your preference.

    I'm still not sure the process is bullet proof from looking at the pictures. I see Avrilaanements is picking up on the possibility of invalid combinations. I mentioned this potential in my first post
    If it's possible to change the main form control data, the combo value can be out of sync.
    You would have to lock down the membership control, or since it appears you have a different form for edits, change it to a textbox. The possibility of the problem may still exist on that form.

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I have been using the following approach to deal with this:

    Private Sub Group_Enter()
    if me.MembershipType = "Family Member" then Me.YourComboBox.RowSource = "qryComboList_Restricted" 'simply create a query without "Subscription"
    End Sub


    Private Sub Group_Exit(Cancel As Integer)
    Me.Group.YourComboBox= "qryComboList_ALL" 'OnExit reset the list to avoid problems with displaying records as explained by Arvil

    End Sub

  10. #10
    StuartR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Scotland
    Posts
    46
    Quote Originally Posted by Gicu View Post
    I have been using the following approach to deal with this:

    Private Sub Group_Enter()
    if me.MembershipType = "Family Member" then Me.YourComboBox.RowSource = "qryComboList_Restricted" 'simply create a query without "Subscription"
    End Sub


    Private Sub Group_Exit(Cancel As Integer)
    Me.Group.YourComboBox= "qryComboList_ALL" 'OnExit reset the list to avoid problems with displaying records as explained by Arvil

    End Sub
    Gicu,

    Thanks for your time on this. This looks very close to the simple solution I am looking for.

    I have now created the two Query's (RESTRICTED & ALL) but I am not too sure where your lines of code should be entered.

    As an experiment, I changed the settings on the combo box in the sub form to populate the drop down from the restricted query, and this worked fine. But where do I put the code so that the drop down values change to the correct query relative to the Membership_Type ?

    Does it matter, that the field for the Membership_Type is in the main form ? The Sub Form has no reference to Membership Type other than the fact that it is reporting income from a specific member linked by [Member_ID] on the Members_Name Table and [Member_IDFK] on the Income Table.

    Thanks again for your help. Hopefully I am nearly there.

  11. #11
    Join Date
    Apr 2017
    Posts
    1,673
    It occurred to me, that the combo in subform (ccbPaymentType in my example) is probably a single-column combo. When so, you can set the LimitToList property to FALSE, which eliminates any possible errors (user can type in anything or select predefined choices).

    That your Main form is unbound or you use unbound controls instead of table fields in Main form to link subform is not definite - you have a subform linked to at least one control in main form or at least to one source field, then forms are linked. P.e. when you enter a new record into subform, it gets member id and payment date from main form automatically, unless you haven't established links at all.

    As you have 2 controls for making selections, and it looks like your Main form is bound one with members table as source, it will be best you use Main form's OnCurrent event for your code (as was adviced here earlier).

  12. #12
    Join Date
    Apr 2017
    Posts
    1,673
    It occurred to me, that the combo in subform (ccbPaymentType in my example) is probably a single-column combo. When so, you can set the LimitToList property to FALSE, which eliminates any possible errors (user can type in anything or select predefined choices).

    That your Main form is unbound or you use unbound controls instead of table fields in Main form to link subform is not definite - you have a subform linked to at least one control in main form or at least to one source field, then forms are linked. P.e. when you enter a new record into subform, it gets member id from main form automatically, unless you haven't established links at all.

    It will be best you use Main form's OnCurrent event for your code (as was adviced here earlier).


    Remark: There went something wrong with editing - I clicked on Preview, and after that I got a new post. A plea for some admin - ditch the previous one

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    StuartR,
    The code goes into the OnEnter and OnExit events of the payment combo box on the subform (the one you want to restrict). And thanks for pointing out that the member type is on the main form, you need to modify the If statement:

    if me.Parent.form.controls("MembershipType") = "Family Member" then Me.YourComboBox.RowSource = "qryComboList_Restricted" 'MembershipType is the name of the combo on the main form

    Cheers,
    Vlad

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

Similar Threads

  1. Fun with Drop-down lists.
    By againstevrythng in forum Access
    Replies: 2
    Last Post: 12-12-2014, 02:49 PM
  2. Replies: 2
    Last Post: 09-12-2014, 08:43 PM
  3. Replies: 5
    Last Post: 01-27-2014, 12:19 PM
  4. Replies: 1
    Last Post: 11-30-2013, 03:43 AM
  5. Replies: 1
    Last Post: 08-02-2011, 06:23 AM

Tags for this Thread

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