Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    One last thing.
    You need to use + " " & in this example.



    Just using + either side of the " " causes another issue.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  2. #17
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Hi All,

    WOW! There is a lot for me to understand... I will try to implement this and see if I can get it working.

    I appreciate the time you took to answer my question :-)

  3. #18
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Hi All,

    I was able to use the code provided and get it working; however, I noticed that whenever I open the form containing these cbo the second cbo shows all options vs being shortlisted. The shortlisted version only works when a choose a company from the first cbo. I guess this is because the code is called and short listing of the second cbo is performed. This doesnt happen when you close the form and open it again.

    Any suggestions?

  4. #19
    Join Date
    Apr 2017
    Posts
    1,673
    On forms Open event, you either read 1st combo entry (a default one) or determine it, and then edit the rowsource of 2nd combo, like in event for 1st combo. (Or you call AfterUpdate event for 1st combo.)

  5. #20
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Hi Arvil,

    Sorry, but that went completely over my head. What do you mean?

  6. #21
    Join Date
    Apr 2017
    Posts
    1,673
    Open your database in Edit mode;
    Open your form in edit mode. When at right of Project window Property Sheet is displayed, then go to next, otherwiswe right-click on left-top corner of form, and select Properties from drop-down menu;
    In Property Sheet, activate tab Event;
    Locate OnOpen event. (When there is not "[Event Procedure]" displayed in 2nd column click on button <v> at right and select "[Event Procedure].) Open procedure, clicking on <...> button at right.
    You see a code like
    Code:
    Private Sub Form_Open(Cancel As Integer)
    ...
    End Sub
    Enter somewhere between top and bottom rows, but above any rows like "SomeName:" when such exists, and outside of any cycles or IF...Endif clauses, rows like
    Code:
    Private Sub Form_Open(Cancel As Integer)
    ...
            Me.cbbYourCombo2.RowSource = "SELECT ...;"
            Me.cbbYourCombo2.Requery
    ...
    End Sub
    where Select String is composed like you composed it in AfterUpdate event of your cbbYourCombo1;
    Close VBA editor and save database.

    Or Zip your database and put it into your next post as attachment. Probably someone here edits/adds the event and posts database back.

  7. #22
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Thank you for the details answer. Here's what the code looks like. Now when the form opens, cbo2 is empty and when clicked to see possible choices, the list too is empty.

    Private Sub Form_Open(Cancel As Integer)
    Me.CboContact.RowSource = "SELECT [tblContacts].[ID]," & _
    " [tblContacts].[CompanyID]," & _
    " [tblContacts].[FirstName]," & _
    " [tblContacts].[LastName] " & _
    "FROM tblContacts " & _
    "WHERE [CompanyID] = " & Me.CboCompany.Value
    Me.CboContact.RowSource = Contact
    Me.CboContact.Requery

    End Sub

  8. #23
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There's a nice example of cascading combo boxes here which you may find helpful:
    http://www.fmsinc.com/microsoftacces...cascading.html

    BTW What happened to concatenating the first & last names as in the early posts in this thread
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #24
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Sunny8760 View Post
    Private Sub Form_Open(Cancel As Integer)
    Me.CboContact.RowSource = "SELECT [tblContacts].[ID]," & _
    " [tblContacts].[CompanyID]," & _
    " [tblContacts].[FirstName]," & _
    " [tblContacts].[LastName] " & _
    "FROM tblContacts " & _
    "WHERE [CompanyID] = " & Me.CboCompany.Value
    No need for .Value
    "WHERE [CompanyID] = " & Me!CboCompany
    is enough.

    With next row of code you do overwrite the RowSource you just set with some variable??? Contact!
    What is the RowSource now?
    Quote Originally Posted by Sunny8760 View Post
    Me.CboContact.RowSource = Contact
    Me.CboContact.Requery
    End Sub
    Did you want to set default value for combo? Then this row must be (when Contact is variable which holds this default value)
    Me.CboContact = Contact
    Or did you want to set combo to value of table field [Contact]? Then the row must be
    Me.CboContact = Contact
    or
    Me.CboContact = me.Contact

  10. #25
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Move your code to the form Load event, the stored cbo values won't be there at form open.
    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 ↓↓

  11. #26
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Quote Originally Posted by Minty View Post
    Move your code to the form Load event, the stored cbo values won't be there at form open.
    Hi,

    I used the following code and now when I open the form a prompt box opens that is titled "Enter Parameter Value" with an entry box for "tblContacts.ContacName" and OK Cancel button.

    I believe i have made an error in the code below, but I'm unsure what...

    'To stop all names from appearing in CBO contact when form opens.
    Private Sub Form_Load()
    On Error Resume Next
    Me.CboContact.RowSource = "SELECT [tblContacts].[ID]," & _
    "[tblContacts].[ContactName] " & _
    "FROM tblContacts " & _
    "WHERE [CompanyID] = " & Me.CboCompany
    Me.CboContact = Me.ContactID
    Me.CboContact.Requery
    End Sub

  12. #27
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    At various points in this thread, you have
    1. Wanted to concatenate first and last name
    2. Used them separately
    3. Used ContactName

    The parameter request could be because contact name is a 'derived' field that hasn't previously had a value assigned.

    Reminder, you do not need to type .Value as that's the default
    Is CompanyID a number field? Is the bound column of cboCompany also a number field?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #28
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Quote Originally Posted by ridders52 View Post
    At various points in this thread, you have
    1. Wanted to concatenate first and last name
    2. Used them separately
    3. Used ContactName

    The parameter request could be because contact name is a 'derived' field that hasn't previously had a value assigned.

    Reminder, you do not need to type .Value as that's the default
    Is CompanyID a number field? Is the bound column of cboCompany also a number field?
    I hope I have not confused anyone. While trying to resolve the most recent issue, I tried going through this form in detail to see if I could resolve the problem myself. During this review I noticed I had not concatenated the fields, so I did that now.

    CompanyID is a number field and bound column of cbo is a number field.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Combo box limit to list doesnt limit
    By mcmcd99 in forum Access
    Replies: 6
    Last Post: 11-09-2017, 07:31 PM
  2. Replies: 0
    Last Post: 03-19-2017, 01:29 PM
  3. Cascading Combo / List Boxes
    By plowe in forum Programming
    Replies: 5
    Last Post: 09-07-2012, 10:55 AM
  4. Replies: 2
    Last Post: 05-27-2011, 08:12 AM
  5. List box update from combo box choice
    By allykid in forum Forms
    Replies: 1
    Last Post: 03-08-2011, 10:06 PM

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