Combo box vba Not returning right value

[thechazm]

Ok I have ran across something interesting here and can't make sence of it.

I have a combo box that has 3 fields in it: [Shop],

, [Location]
 
When the user selects one of the entries it filters the form below it.
 
But when I have two [shops] with the same name but at different [Codes] and same [Locations] I get this.
 
First entry in combo box is:
[All], [950], [FMB]
 
Second is:
[All], [930], [FMB]
 
When I select the first one I use the On_Change event to filter the form with this:
 
Me.frmSetupVisualMatrixSchools.Form.Filter = "[Shop] = """ & Me.cmbSelectShop.Value & """ AND [Code] = """ & Me.cmbSelectShop.Column(1) & """ And [Location] = """ & Me.cmbSelectShop.Column(2) & """"
 
The value's here are correct at this point because I selected the first one.
 
When I select the second one it still returns
[All], [950], [FMB]
Instead of
[All], [930], [FMB]
 
Can anyone please shed some light on this for me,
 
Thanks
 
TheChazm

 

[boblarson]

Did you make sure to change the COLUMN COUNT property of the combo box to 3 from the default of 1?

 

[thechazm]

Yes the Column Count = 3. And I have on column heads that shows the three fields.

Other values are:
Bound Column = 1
Column Widths = 1";0.5";0.5"
List Width = 2.5"

 

[boblarson]

Have you also remembered to use the

Me.frmSetupVisualMatrixSchools.Form.FilterOn = True

and then reset it before doing it again

 

[thechazm]

Here is the full On_Change Event
Me.frmSetupVisualMatrixSchools.Form.Filter = "[Shop] = """ & Me.cmbSelectShop.Value & """ AND

 = """ & Me.cmbSelectShop.Column(1) & """ And [Location] = """ & Me.cmbSelectShop.Column(2) & """"
Me.frmSetupVisualMatrixSchools.Form.FilterOn = True
Me.chkAllCodes.Value = False

 

[boblarson]

Why are you using the On Change event? Normally you should use the After Update event unless you were using a text box for typing and having it search while typing. If you are using the combo's Change event then you can't use the combo's VALUE as it hasn't been set yet. You would need to refer to it using the .TEXT property instead.

 

[thechazm]

It really almost looks like the combobox is selecting the first one in its list based on the bound column.
Because when I select the combo box and I select [All], [930], [FMB]. Then if I select the combo box again right after that its highlighting [All], [950], [FMB]... Weird

 

[thechazm]

Ok I'll change it to see if it makes a difference. Thanks for the help

 

[thechazm]

Woot! You were right. The Combo box now returns the right value.

Its still a little weird that it still goes back to the first [All] entry in the combo box but the filter is working..

This is still going to cause me problems with my code so I dunno why its doing this.

 

[thechazm]

Should I ask about the combo box functionality in another spot in the forum or would you might have an idea why its still going to the first entry?

 

[boblarson]

Sounds like the form may be requerying somehow. Do you have any code on the parent form's On Current event or something?

 

[thechazm]

Maybe I should just capture the list index of the item and then have the combo box select it again during the after update? I don't have any me.requery statements or me.refresh in there. I dunno.

 

[thechazm]

I think it does when the subform applies the new filter.. I dunno

 

[thechazm]

Thanks for the help but even if I re-assign the index back to the combo box using:
Dim i As Long
i = Me.cmbSelectShop.ListIndex
Me.cmbSelectShop = Me.cmbSelectShop.Column(0, i + 1)

It still selects the first one. I apprecaite this but I believe this is now a question I should ask in the forms section.

Thanks again,

TheChazm

 

[ss6857]

Hi, I have a similar problem but I don't think its as complicated. I have checkboxes and when the checkbox is checked and a command button is pressed, I want it to perform an action. For example, I used a combo box so the user can pick the report. The report is displayed in a subform. when a checkbox is checked, I want to convert the report chosen to pdf and be saves as the report name (or the name that is in the combo box) Here is my code:

Private Sub Command93_Click()
Dim rn As String
Me.Combo7.SetFocus
rn = Me.Combo7.Text
If Me.Check72 = True Then
DoCmd.OutputTo acOutputReport, rn, "PDFFormat(*.pdf)", "S:\Reports\Pricing\" & "rn.pdf", False, "", , acExportQualityPrint
End If
End Sub

but instead of saving as the text that is displayed in the combo box, it saves as rn.
Thanks for any help.

 

[ss6857]

nvm! figured it out. for future reference:

Private Sub Command93_Click()
Dim rn As String
Dim vFilePath As String
Dim objn As String
Me.Combo7.SetFocus
rn = Me.Combo7.Text & ".pdf"
objn = Me.Combo7.Text
vFilePath = "S:\Reports\Pricing\"
If Me.Check72 = True Then
DoCmd.OutputTo acOutputReport, objn, "PDFFormat(*.pdf)", vFilePath & rn, False, "", , acExportQualityPrint
End If
End Sub