Issue with query results on combo box

[setis]

This is very basic but it bothers me a lot that I can figure out why it is happening.

I have 2 different combo boxes on my form that get the result from 2 different queries.

My problem is that for getting the result to show, I have to pick it up from the combo box. It is not showing automatically, despite it is the only result from the combo. I hope that this makes sense.

One of them is NrOfInvoices. When the field CaseNo is updated, the query on the combo counts the nr of invoices with that CaseNo.

I have tried :

Private Sub CaseNo_AfterUpdate()
Me.NrOfInvoices.Requery
End Sub

But I still have to choose the result from the combo. Same problem with the other combo that does a calculation..

Is it possible to get the result to show itself?

 

[isladogs]

Either set a variable equal to the combo box value and use that in your query criteria. Or just use as criteria

 Forms!MyFormName.Comboboxname

Also, is the required value the only or default column in the combo?
If not you'll also need to specify the column number by adding a suffix e.g. .Column(1).
Note that the first column is Column(0)

 

[arnelgp]

if there is only one Item in you combobox,
requery it first then get that first
item.

Me.combo.Requery
Me.combo = Me.combo.ItemData(0)

 

[setis]

Thanks both for the answers

if there is only one Item in you combobox,
requery it first then get that first
item.

Me.combo.Requery
Me.combo = Me.combo.ItemData(0)

The query results shows 2 values, the "CaseNo" and the "NrOfInvoices" in the query for the first example (NrOfInvoices), but it is still not working:

The code looks like this

Private Sub CaseNo_AfterUpdate()
Me.cmbNrOfInvoices.Requery
CaseTitle = CaseNo.Column(1)
Me.PolicyNo = Me.CaseNo.Column(2)
Me.MemberName = Me.CaseNo.Column(3)
Me.CountryofTreatment = Me.CaseNo.Column(7)
Me.cmbNrOfInvoices = Me.cmbNrOfInvoices.Column(1)
End Sub

The result is right there in the combo after clicking the arrow, I just don't get why it doesn't show up

Update: I don´t think that the requery is working. . After updating the CaseNo, when I click the combo, the result is blank, its only after I click on "refresh all" that the combo results show the actual NrOfClaims. Can that be the issue?

 

[arnelgp]

how many items are there in
cmbNrOfInvoices?
only one:


Private Sub CaseNo_AfterUpdate()
Me.cmbNrOfInvoices.Requery
Me.cmbNrOfInvoices = Me.cmbNrOfInvoices.ItemData(0)
CasetTitle = CaseNo.Column(1)
...
...
...

 

[isladogs]

In similar situations, I've occasionally resorted to having a hidden textbox displaying the combobox output and then reference that in my query.
That has always worked when the other method doesn't

 

[setis]

how many items are there in
cmbNrOfInvoices?
only one:


Private Sub CaseNo_AfterUpdate()
Me.cmbNrOfInvoices.Requery
Me.cmbNrOfInvoices = Me.cmbNrOfInvoices.ItemData(0)
CasetTitle = CaseNo.Column(1)
...
...
...

There are 2 items in the query results. The CaseNo and the NrOfInvoices, that's why I think that it should be:

Me.cmbNrOfInvoices = Me.cmbNrOfInvoices.ItemData(1)

 

[setis]

In similar situations, I've occasionally resorted to having a hidden textbox displaying the combobox output and then reference that in my query.
That has always worked when the other method doesn't

Actually I would prefer having a texbox instead of a combo, since the result is unique and users woudn't have to choose anything. The purpose is only to inform about the nr. of invoices with the same Case Number.