Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393

    Display Value in combo box onload or afterupdate

    Basically all I want to do is show or display the value that is filtered in a combo box when a form loads or after another combo box is updated.



    For example say I have 2 combo boxes

    1st combo code is
    SELECT tblCities.CityID, tblCities.CityName, tblCities.Postcode, tblCities.StateID
    FROM tblCities
    ORDER BY tblCities.CityName;

    So therefore first combo will give me list of all Cities and once a city is selected I can requery my second combo and whatever city is chosen only the respective state will be available in combo box as per following code

    SELECT DISTINCT tblStates.StateID, tblStates.StateName, tblCities.StateID
    FROM tblStates INNER JOIN tblCities ON tblStates.StateID = tblCities.StateID
    WHERE (((tblCities.CityID)=[forms]![frmContact]![cmbCities]))
    ORDER BY tblStates.StateName;


    This works great except you still need to select the value from second combo box.

    How can I automatically display this, something like set value row 1?

    This has got to be very easy but tried for an hour and can't find how to do this

    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Would this work for you?

    http://www.baldyweb.com/Autofill.htm

    You'd just have to tweak the first combo to include the state name, presuming that's what you want to display.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    OK not really getting the hang of this one at the moment I have tried

    form onload event procedure
    Me.cmbCities.Value = Me.cmbCities.Column(1)
    To attempt to display the city that was selected from previous form and also have tried the afterupdate event procedure from city combo box with -
    Me.cmbStates.Value = Me.cmbStates.Column(1)
    to try and display the state after update of the city combo box however neither of these display the value, you still need to select it form the combo box

    I know I can use text box to set value but I want the freedom to use a combo box for this I just want it to display the filtered value without having to select it manually

    Thanks

  4. #4
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    I thought it would maybe have something to do with somehow displaying row 1 of the combo box instead of row zero

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Let's clarify; are you just trying to display the state associated with the chosen city, or does the state need to be a combo so the user can change it?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Hi pbaldy, I know how to do this in a text box, essentially I want to be able to display the value in a combo box?

    I can filter the value so it is the only thing that exists in the combo box but is not visible until I click on the combo box

    Thanks

  7. #7
    stmoong is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Sep 2011
    Location
    Singapore
    Posts
    108
    You can do something like this.

    Code:
    With Me.cmbStates
        .RowSource = "SELECT ......"
        .Value = .ItemData(0)   'Select the first item
        .Requery
    End With

  8. #8
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    OK having quite a bit of trouble with this still, I have tried to combine it with my code for selecting what is in the combo box but keep getting errors

    Private Sub Form_Load()
    With Me.cmbStates
    .RowSource = "SELECT tblCities.CityID, tblCities.CityName, tblCities.Postcode, tblCities.StateID" FROM tblCities WHERE (((tblCities.CityID) = [Forms]![frmContact]![CityID])) Or ((([Forms]![frmContact]![CityID]) Is Null))ORDER BY tblCities.CityName;"
    .Value = .ItemData(0) 'Select the first item
    .Requery
    End With

    End Sub

    I have also tried just using .Value = .ItemData(0) 'Select the first item and many combinations of setvalue, etc and still can't get this to work

    Thanks to everyone for input

  9. #9
    stmoong is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Sep 2011
    Location
    Singapore
    Posts
    108
    Try this query instead?

    Code:
    "SELECT tblCities.CityID, tblCities.CityName, tblCities.Postcode, tblCities.StateID FROM tblCities WHERE tblCities.CityID = '" & Me.CityID & "' ORDER BY tblCities.CityName;"
    Also, check that you have set the following properties for your combo box in the property sheet:

    Column Count: 4
    Column Width: 0";x";0";0"
    -> x is the width of to display the value, so you are showing only CityName for selection

    Bound Column: 1
    -> assuming you want to get CityID from Me.cmbStates

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    And if you're still stuck, perhaps you can post the db here, or a representative sample?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Thanks guys, nope still no success, I have attached a minimal version of my database so you can see what I am trying to do

    Attachment 4620

    So as I said I have everything working so that when in main form I choose a client and then choose a contact I can then click edit client button.

    This opens the form contact and as you can see every text box is populated with the current contact's data, except the combo box is not displaying it. It is the only item in the drop down list however I want the combo box to automatically display it.

    (notably clicking the add new client will open the contact form with all fields empty and a full list of cities in cmbClient, this obviously does not require this functionality)

    Furthermore if the city is chosen from the first combo box i want the state combo box to not only filter to provide the option for the state the city is in I want the combo box to automatically select/ display it (therefore if the edit button is selected it should display both combo boxes)

    Thanks

  12. #12
    stmoong is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Sep 2011
    Location
    Singapore
    Posts
    108
    There seems to be something wrong with the query for getting all the contacts for a specific client. If one client has many contact details, then you would remove the DISTINCT from the query for contact details.

    There is also an error in the query, the ClientID is queried twice, and you can't select the client contact from the combo box, since it is bound to the column 1, which is ClientID previously.

    I change it to something like this, and use the Click event instead.

    Code:
        With Me.cmbClientsContact
            .RowSource = _
                "SELECT [tblContact].ContactID, [tblContact].ContactName, [tblContact].ContactSurName FROM tblClients" & _
                " RIGHT JOIN tblContact ON tblClients.ClientID=[tblContact].ClientID" & _
                " WHERE [tblContact].ClientID=" & Me.cmbClients & " ORDER BY [tblContact].ContactName;"
            .Requery
            .Value = .ItemData(0)
            .Visible = True
        End With
    I think you need to clean up your test data as well, and perhaps create some kind of relationship usnig the Database Tools -> Relationships to enforce data integrity. Fix your database design on paper first, before you create in Access. It will save you alot of programming time and trouble debugging later on.

  13. #13
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    STMoong, cheers for your quick reply. I hadn't really bothered to notice why contacts drop down was not working properly as all my other ones were

    and yes you are correct sorry about the lack of design/ structure I included I only exported these tables and forms for this question. I forgot to set relationships

    I actually have a full database with many more tables with relationships set as can be seen through my other threads in differing levels

    from your answer I have managed to almost answer one of my questions as where I would like to implement this startegy is not in frmMainMenu but actually in frmContact, therefore I have implemented this code in the AfterUpdate field of frmContact.cmbCities

    With Me.cmbStates
    .RowSource = _
    "SELECT [tblStates].StateID, [tblStates].StateName, [tblCities].StateID FROM tblCities" & _
    " RIGHT JOIN tblStates ON tblCities.StateID=[tblStates].StateID" & _
    " WHERE [tblStates].StateID=" & Me.cmbCities & " ORDER BY [tblStates].StateName;"
    .Requery
    .Value = .ItemData(0)
    .Visible = True
    End With
    End Sub

    This does indeed populate the combo box however it gives it the wrong value???

    After I get this sorted the more difficult one is populating both of them as as per the values from the Mainform

    Can you see what is wrong with my code?

    Thanks

  14. #14
    stmoong is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Sep 2011
    Location
    Singapore
    Posts
    108
    Wrong values could be due to either data error or the error with the query.

    You can run the query from the query designer, substituting with real value in place of the form control value. This will give you an idea, perhaps, you need to modify your query. Also, you might want to test with correct set of data - either with the relationships set up properly, or the data themselves follow the reference integrity.

    We don't want to work with the case that the relationship of the data is wrong, because those should be taken care of, either through your update logic or proper relationships set up for the tables.

  15. #15
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Thanks again and rest-assured I am only working with my main database with all relationships intact, my problem is that I have not done any sql queries in vb code before and my original sql statement

    SELECT DISTINCT tblStates.StateID, tblStates.StateName, tblCities.StateID
    FROM tblStates INNER JOIN tblCities ON tblStates.StateID = tblCities.StateID
    WHERE (((tblCities.CityID)=[forms]![frmContact]![cmbCities]))
    ORDER BY tblStates.StateName;

    This works correctly and populates state combo with only correct state however when i try and convert to vb it doesn't work properly it gives me the wrong state with this code

    With Me.cmbStates
    .RowSource = _
    "SELECT [tblStates].StateID, [tblStates].StateName, [tblCities].StateID FROM tblCities" & _
    " RIGHT JOIN tblStates ON tblCities.StateID=[tblStates].StateID" & _
    " WHERE [tblStates].StateID=" & Me.cmbCities & " ORDER BY [tblStates].StateName;"
    .Requery
    .Value = .ItemData(0)
    .Visible = True
    End With
    End Sub

    Thanks

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Display 2 Columns in Combo Box?
    By 10 Gauge in forum Forms
    Replies: 3
    Last Post: 04-12-2011, 10:24 AM
  2. Combo box dafault display value !!!
    By Kazim in forum Access
    Replies: 1
    Last Post: 02-28-2011, 09:31 AM
  3. Replies: 7
    Last Post: 02-07-2011, 10:11 PM
  4. Combo Box List display HELP!
    By ehabo in forum Access
    Replies: 11
    Last Post: 01-04-2011, 12:55 PM
  5. Combo Box Display
    By ssaucedo in forum Reports
    Replies: 17
    Last Post: 08-10-2009, 05:52 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