Issue with query results on combo box (1 Viewer)

setis · Nov 20, 2017

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 :

Code:

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 · Nov 20, 2017

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

Code:

 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 · Nov 20, 2017

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 · Nov 20, 2017

Thanks both for the answers

arnelgp said:
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

Code:

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 · Nov 20, 2017

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 · Nov 20, 2017

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 · Nov 20, 2017

arnelgp said:
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 · Nov 20, 2017

ridders said:
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.

Issue with query results on combo box (1 Viewer)

setis

Registered User.

isladogs

MVP / VIP

arnelgp

..forever waiting... waiting for jellybean!

setis

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

isladogs

MVP / VIP

setis

Registered User.

setis

Registered User.

Similar threads

Users who are viewing this thread