Synchronized Combo Box Solution Unable to Display Saved Values 2

[scm21st]

In a form in Access 2007, I have synchronized two combo boxes using the code below in a AfterUpdate event procedure. It works as it should....

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

Private Sub basinID_AfterUpdate()
' Update the row source of the playID combo box
' when the user makes a selection in the basinID
' combo box.
Me.playID.RowSource = "SELECT playID, playName FROM" & _
" tblPlays WHERE basinID = " & _
Me.basinID & _
" ORDER BY playName"

Me.playID = Me.playID.ItemData(0)
End Sub

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

However, if I navigate to a new record and then return to previously entered records, the combo box values are no longer properly displayed.

The previously entered values do exist as they should in the underlying table, but they do not display correctly in the form.

Does anyone know how to alter the above code so that it will properly display existing values when the user returns to previously entered records?

Thanks very much for any assistance.

Steve

 

[dhookom]

I expect the issue is with setting the value of PlayID. You could check for a new record or maybe:

Private Sub basinID_AfterUpdate()
    ' Update the row source of the playID combo box
    ' when the user makes a selection in the basinID
    ' combo box.
    Me.playID.RowSource = "SELECT playID, playName FROM" & _
                               " tblPlays WHERE basinID = " & _
                               Me.basinID & _
                               " ORDER BY playName"
    If IsNull(Me.PlayID) Then
        Me.playID = Me.playID.ItemData(0)
    End If
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[scm21st]

Duane,

Thanks for the help. It still looks like only the last record entered is displaying the play value correctly (i.e the play is displayed instead of a blank field).

I'll keep digging. If i find a solution I'll post it.

Steve

 

[dhookom]

You probably need code in the On Current similar to your After Update code.


Duane
Hook'D on Access
MS Access MVP

 

[scm21st]

Ok, thank you. I'll look into that.

 

[TheAceMan1]

How are ya scm21st . . .

This is a typical problem that happens when the [blue]RowSource[/blue] of a [blue]bound[/blue] combobox is changed. Keep in mind that combobox is for all records and is just repeated, and so is its recordsource. The recordsource is the same for all instances of the combo.

Typically the recordsource returns all values that can be viewed. In your case you changing what can be viewed. Say the ID's returned by a recordsource are Apple, Orange. In several records we select Apple or Orange from the combo. Now we change the rowsource to return [blue]Peach only![/blue] Since Apple & Orange are not returned in the rowsource ... how then is access to display them? Access doesn't, its just displays a blank.

Get the picture?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[scm21st]

That helps clear it up a bit. Thanks, AceMan.

 

[dhookom]

Good catch on the continuous form TheAcMan1. In continuous forms, I will not filter the records but put them in an order that the appropriate selections are at the to of the selection list. I will also add a column that identifies values that are not appropriate for selection.


Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

dhookom . . .

Roger That! I see were both synchronized on the problem. However ... single or continuous the problem lies in changing the [blue]rowsource[/blue] of the [blue]bound[/blue] combo. Just not gonna work!

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

scm21st . . .

An answer to why ... doesn't solve your problem. What are your thoughts at this point? .... What is it you whish to do now?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

To All! . . .

My post on [blue]5 Apr 11 12:38[/blue] should've read:

[blue]How are ya scm21st . . .

This is a typical problem that happens when the RowSource of a bound combobox is changed. Keep in mind that combobox is for all records and is just repeated, and so is its [purple]rowsource[/purple]. The [purple]rowsource[/purple] is the same for all instances of the combo.

Typically the [purple]rowsource[/purple] returns all values that can be viewed. In your case you changing what can be viewed. Say the ID's returned by a [purple]rowsource[/purple] are Apple, Orange. In several records we select Apple or Orange from 0the combo. Now we change the [purple]rowsource[/purple] to return Peach only! Since Apple & Orange are not returned in the [purple]rowsource[/purple] ... h0ow then is access to display them? Access doesn't, it just displays a blank.

Get the picture?[/blue]

Sorry for the mess!

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

I would use code something like:

Private Sub basinID_AfterUpdate()
    ' Update the row source of the playID combo box
    ' when the user makes a selection in the basinID
    ' combo box.
    Me.playID.RowSource = "SELECT playID, playName, BasinID = " & Me.BasinID & _
                               " FROM tblPlays " & _
                               " ORDER BY 3,2 "
    If IsNull(Me.PlayID) Then
        Me.playID = Me.playID.ItemData(0)
    End If
End Sub

This will display the appropriate selections first. You can actually display something more descriptive in the 3rd column. Use the After Update event of this combo box to test the value of the 3rd column and possibly cancel if not appropriate.

Duane
Hook'D on Access
MS Access MVP

 

[scm21st]

Thanks very much for the feedback, guys. I've got a clear picture of what's going on now. I've been pulled onto another project (gotta love our new PMO) and will have to test this out down he road. But having a clear understanding of the process will help a great deal in future.

 

[scm21st]

My apologies, do I need to take further action and close this thread?

 

[dhookom]

There is no "closing" process. If your question has been answered successfully, you should click the "thank..." link.

Duane
Hook'D on Access
MS Access MVP

 

[scm21st]

Done, thanks.