Since I cannot post a reply in the original post I am posting a new posting (2 Viewers)

[Tom d]

Choosing an item from the first combo does automatically pick an item from the second combo. This is what I want it to do.
The posting is

How do i Set the Selected Field value In a 2nd combo box with a field in 1st combo box once a field is selected in the first combo box?​

 

[theDBguy]

Choosing an item from the first combo does automatically pick an item from the second combo. This is what I want to do

Is this a continuation of your other thread? If so, why not just post it there? Which item from the second combo should be automatically picked when you select from the first combo? Should it automatically pick the first item or something else?

 

[Tom d]

Is this a continuation of your other thread? If so, why not just post it there? Which item from the second combo should be automatically picked when you select from the first combo? Should it automatically pick the first item or something else?

Because I cannot post a reply in the original one. In the second Combo Box the selected record should be the selected record in the first combo box.

 

[DHookom]

Tom, it would be better to provide a descriptive thread title and answer all of theDBguys questions.

 

[Tom d]

Tom, it would be better to provide a descriptive thread title and answer all of theDBguys questions.

I have answered all the questions. A cascading combo box is not the answer.
In the second Combo Box the selected record should be the selected record in the first combo box.

 

[GPGeorge]

Because I cannot post a reply in the original one. In the second Combo Box the selected record should be the selected record in the first combo box.

You have two combo boxes. When a user selects a value in the first, you want the same value to be selected in the second.

To what end? What's the point? Why even have the second combo box if it's a duplicate of the first?

Please explain the "WHY" of the task. What would you gain in terms of the business rules?

 

[Tom d]

You have two combo boxes. When a user selects a value in the first, you want the same value to be selected in the second.

To what end? What's the point? Why even have the second combo box if it's a duplicate of the first?

Please explain the "WHY" of the task. What would you gain in terms of the business rules?

 

[Tom d]

The Combo boxes are loaded with two different tables . The fist one is load with the BookLibrary TBL and the second in load with the Authors TBL. The Key for the authors Combo box is held in the record from the Library Combo Box.

 

[DHookom]

Are you saying if 17 is the value of the BookLibrary combo box, then 17 will be the value in the Authors combo box?

 

[Gasman]

The Combo boxes are loaded with two different tables . The fist one is load with the BookLibrary TBL and the second in load with the Authors TBL. The Key for the authors Combo box is held in the record from the Library Combo Box.

Just bring it into the first combo then?, and use it from there?

 

[The_Doc_Man]

The answer to your question depends on the setup of the 2nd combo box. If you were to attempt to assert a value, you MIGHT encounter the effect of changing the value in the list that drives the combo, which would DEFINITELY be a bad thing.

Here is an older thread on the subject.

select row in combo box based on value in 2 columns

I have a combo box (cboContact) with multiple columns on a form: ContactID | Name | RegionID 15 | Alan | 1 15 | Alan | 2 16 | Bob | 1 16 | Bob | 2 ContactID is the bound...

www.access-programmers.co.uk

The short answer is loop through the records feeding the combo box until you find the one you want. HINT: The combo box's .Column function allows you to select both a column AND a row. If you find what you wanted, set that row index n cbo.ItemsSelected and you have made the selection. Just remember that combo boxes start numbering from 0. I looked and, as far as I can see, the combo box doesn't have an obvious search method. That means you can't just invoke a closed function / method. And that, in turn, means you have to manage your own search behind the scenes.

 

[GPGeorge]

The Combo boxes are loaded with two different tables . The fist one is load with the BookLibrary TBL and the second in load with the Authors TBL. The Key for the authors Combo box is held in the record from the Library Combo Box.

This is indeed exactly what a cascading combo box design does. The only difference is that you have designed your tables (IIRC from seeing the table design in a previous discussion) to support one author per book, rather than allowing for co-authors. That means the "downstream" combo box will be filtered to that one author record, but otherwise it's a bog standard Cascading ComboBox.

Here's an excellent article on the subject. https://www.fmsinc.com/MicrosoftAccess/Forms/combo-boxes/cascading.html

When you select a book in the books combo, the AfterUpdate event of that control fires. It can be implemented in more than one way, the essential function is that the AfterUpdate event requires the downstream combo box, in your case the authors combo box.

The rowsource for the authors combo box is of this pattern.

SELECT AuthorID, AuthorFirstName & " " & AuthorLastName
FROM tblYourAuthorTableNameGoesHere
WHERE AuthorID = Nz(Me.cboBook, 0)

When the AfterUpdate event of the book combo requeries the author combo, the where clause will filter the rowsource to that one matching Author.

Now, if you want to make this so that the Author is a default, but allow the user to change the assigned author, there are, again, different ways to implement that.

However, unless you want to go down that path as well, the above will do the trick of picking the author for each book for DISPLAY in the author combobox.

 

[Tom d]

Are you saying if 17 is the value of the BookLibrary combo box, then 17 will be the value in the Authors combo box?

Yes, but the authors name should show in the Authors combo Box. When the selection in the bookLibrary combo box changes then the authors combo box should reflect the change.

 

[DHookom]

I don't see why you want to do this but you could add some VBA in the after update event of the first combo box to set the value of the second combo box:

If Not IsNull(Me.cboFirst) Then
    Me.cboSecond = Me.cboFirst
End If

 

[Tom d]

I don't see why you want to do this but you could add some VBA in the after update event of the first combo box to set the value of the second combo box:

If Not IsNull(Me.cboFirst) Then
    Me.cboSecond = Me.cboFirst
End If

Code does nothing

 

[Tom d]

I don't see why you want to do this but you could add some VBA in the after update event of the first combo box to set the value of the second combo box:

If Not IsNull(Me.cboFirst) Then
    Me.cboSecond = Me.cboFirst
End If

Code does nothing

 

[theDBguy]

Code does nothing

With a slight modification, give this one a shot and let us know...

 

[Pat Hartman]

Tom, this a complete and total mis-use of the cascading combo concept.

You have books in combo1 and authors in combo2. When you choose the book in combo1. There would be one and only one item in combo2 if you implemented the cascade correctly because there is one and only one author for the book. This is just one of the many things that are wrong with the application. You have been given a good sample and lots of good advice - all of which has been ignored.

What is the PURPOSE of making the author of the book the selected item in the second combo? What do you think that is doing for you?

When you use authors as the source of combo1. You pick an author. Say "Mark Twain". Then the RowSource of the second combo is restricted to only those books where "one of the authors" is Mark Twain. You then choose the book that you are interested in. The first combo NEVER selects the item from the second combo. THAT is how a pair of cascading combos is intended to work.

Now, think about that last paragraph and digest it. Why are you doing this "backwards"? How are you intending to use the value you want to populate the second combo which you don't need to populate at all since you already know who the author is?

 

[Gasman]

I still think you copuld just use the logic in post #10?, no need for a second combo, due to the bad structure.

 

[Tom d]

With a slight modification, give this one a shot and let us know...

Code gives Error '424'
Objectrequired