Solved How to reference a unbound combo box column in Ms Access (1 Viewer)

[nector]

I have a challenge here I was trying to reference a column from unbond combobox it does not work from the query below any idea:

rivate Sub CboQuotation_Click()
Me.txtCboQtyControl = Me.CboQuotation.Column(2)
End Sub


Private Sub Form_Load()
    QSQOCCustomerIDLookup.Initialize Me.CustomerID, 3
    QSQOCCustomerIDLookup.UnfilteredRowSource = "SELECT CustomerID,Company FROM tblCustomers WHERE Company Like '**'ORDER BY Company;"
    QSQOCCustomerIDLookup.IsLazyLoading = True
    Me.CustomerID.Requery
AQSQOCCustomerIDLookup.Initialize Me.CboQuotation, 3
    AQSQOCCustomerIDLookup.UnfilteredRowSource = "SELECT QryQuotationLookup.Company, QryQuotationLookup.SalesQtyID FROM QryQuotationLookup WHERE Company Like '**'ORDER BY Company;"
    AQSQOCCustomerIDLookup.IsLazyLoading = True
    Me.CboQuotation.Requery
End Sub

My query looks strange somehow but it works terrific for SQL Server since it only pull one requested record at a time.

 

[CJ_London]

rather confusing since I don't see the rowsource to Me.CboQuotation.

and this does not make sense - 'since it only pull one requested record at a time' does not match ' Like '**'ORDER BY Company' which will pull all records

Are you trying to pull data from a web page or something else? A bit of background on what your code is supposed to be doing would be helpful

What is QSQOCCustomerIDLookup? a class object?

All I can see is your two queries pull two fields (in different orders which may or may not be relevant) and you are looking for column 2 - columns start from 0 so there is no column 2, it needs to be 0 or 1.

 

[cheekybuddha]

You give very little information.

What is "it does not work from the query below"? Error? Wrong result? ...?

How many columns in your combo?

What is the RowSource of your combo?

Where are objects AQSQOCCustomerIDLookup and QSQOCCustomerIDLookup declared and instantiated?

What does their Initialize() proc look like?

 

[GPGeorge]

You reference columns in both bound or unbound combo boxes the same way.

Columns are indexed from left to right, with a zero-based index.

The "first" column, therefore, is the one on the left. It's referenced by index 0 in VBA.
The "second" column, therefore, is the next one to the right of column 0. It's referenced by index 1 in VBA.

Additional columns are indexed and referenced in the same way.

The difference between bound and unbound combo boxes is that, in a bound combo box, one of the columns is linked directly to a field in the recordsource to which the form is bound. Normally--and by default--that will be either a Primary Key field or a Foreign Key field in that underlying table. By default, Access creates bound columns on the "first" column, i.e. Index 0.

However, you can override that. I can't say that I have done so more than one or two times ever, though.

 

[nector]

Sorry Im pulling data from cloud database the style is to use a lazy combo plus 4 class modules which does the job of a bound row source no wonder you cannot see the row source but this is an excellent way of pulling data faster over the internet because all combo boxes load empty the drop down list only built once the first 3 letters are entered.

 

[CJ_London]

So not much further forward then

 

[nector]

Here I had to redo the query , now all is fine

Private Sub CboQuotation_AfterUpdate()
Me.txtCboQtyControl = Me.CboQuotation.Column(1)
End Sub

Private Sub Form_Load()
    QSQOCCustomerIDLookup.Initialize Me.CustomerID, 3
    QSQOCCustomerIDLookup.UnfilteredRowSource = "SELECT CustomerID,Company FROM tblCustomers WHERE Company Like '**'ORDER BY Company;"
    QSQOCCustomerIDLookup.IsLazyLoading = True
    Me.CustomerID.Requery
AQSQOCCustomerIDLookup.Initialize Me.CboQuotation, 3
    AQSQOCCustomerIDLookup.UnfilteredRowSource = "SELECT tblSalesQuotation.CustomerName, tblSalesQuotation.SalesQtyID FROM tblSalesQuotation WHERE CustomerName Like '**'ORDER BY CustomerName;"
    AQSQOCCustomerIDLookup.IsLazyLoading = True
    Me.CboQuotation.Requery
End Sub

Without doing it this way and allowing the combo box load all details can be disaster in term of loading 3 million details, now it does not matter whether there 100 million in a combo box

 

[CJ_London]

Fail to see how any of your code prevents attempting to load 3m records - although combo’s are limited to 65k rows

and since you didn’t provide anything relevant to your question, looks like our guess regarding column numbers was correct.

we all very much appreciate your acknowledgement of our help in pointing you in the right direction

 

[nector]

Okay I see, in cloud database, the database tends to be slow if a lot of data is requested at one go Imagine requesting 65500 records in a combo box will take something like 120 or more minutes loading.

Then what next?

Here you clear the combo box row source so that the form loads empty and faster.

Now how about a retail business with 20 million product line how do you do it? well that is where you need to plan like below:

(1) The combo must initialize by a class module first
(2) Another class module must pull only the first 3 characters typed into the combo box
(3) The third-class module must build now a short drop-down list for only matching record typed characters above
(4) When you clear the combo box row source, the fourth-class module comes in to hold the built in short list in memory , this allows the users to select only the specific record they want and pull it over the internet or local area network!

That is how you can manage to search in over 100 million records because you will always have a short list of data option. Not doing this and allowing the combo box pull data straight from linked tables will wreck your software to very poor performance and I do not think users will be happy with that.

In addition to that, a combo box can only hold 65500 records, then what if your client has 100 millions of products lines? Will say no! my software cannot work on such a record? That is where the above technique comes into play.

 

[CJ_London]

OK - still don't see how you are pulling through no records since this sql

"SELECT QryQuotationLookup.Company, QryQuotationLookup.SalesQtyID FROM QryQuotationLookup WHERE Company Like '**'ORDER BY Company;"

pulls through all of them.

Personally I use two ado disconnected recordsets to minimise network traffic

1. first recordset returns only records which are currently required for display in the current form and is disconnected (assuming a continous form)
2. once 3 chars (whatever) are typed the relevant records are fetched to a second recordset, the recordset is disconnected and assigned to the combo
3. subsequent addition of characters then filters this ado recordset, avoiding further fetches
4. if user deletes or modifies one or more of the first three characters then the recordset either reverts to nothing or the initial recordset
5. Once an item has been selected, the first recordset is updated with the new item and assigned back to the combo

 

[Oviziest]

I have a challenge here I was trying to reference a column from unbond combobox it does not work from the query below any idea:

rivate Sub CboQuotation_Click()
Me.txtCboQtyControl = Me.CboQuotation.Column(2)
End Sub


Private Sub Form_Load()
    QSQOCCustomerIDLookup.Initialize Me.CustomerID, 3
    QSQOCCustomerIDLookup.UnfilteredRowSource = "SELECT CustomerID,Company FROM tblCustomers WHERE Company Like '**'ORDER BY Company;"
    QSQOCCustomerIDLookup.IsLazyLoading = True
    Me.CustomerID.Requery
AQSQOCCustomerIDLookup.Initialize Me.CboQuotation, 3
    AQSQOCCustomerIDLookup.UnfilteredRowSource = "SELECT QryQuotationLookup.Company, QryQuotationLookup.SalesQtyID FROM QryQuotationLookup WHERE Company Like '**'ORDER BY Company;"
    AQSQOCCustomerIDLookup.IsLazyLoading = True
    Me.CboQuotation.Requery
End Sub

My query looks strange somehow but it works terrific for SQL Server since it only pull one requested record at a time.

you're referencing the column in your code for the unbound combo box. Have you tried checking the index number you're using for the column in Me.CboQuotation.Column(2)? Sometimes it might start from 0 or a different number. Double-checking that might help fix the issue you're facin

 

[nector]

Hi Oviziest!

Here I'm talking about a cloud database where users pull data back and forth from the internet, now can you imagine you allow a combo box load all the stock for the users to select from, that will be a disaster in terms of performance.

That is the reason why you use a combo box on a fly no row source, fortunately when it comes to column reference it works the same way like a bound combo box. But the four class modules that does the magic are very complex I had to seek assistance also to get right, I was here but though people tried to help, but solution, until I had to pay someone to assist me in coding the required VBA codes.

Now my software works super and the speed is like there is no cloud database. The other thing is that all my queries are delt with by the views in the cloud database itself.

I cannot cheat you the work to move to cloud is quite massive , for example I have the combo boxes which handles complex calculations see below :

SELECT DISTINCTROW tblProducts.ProductID, tblProducts.ProductName, tblProducts.BarCode, tblSalesTax.TaxClass, tblPricing.Prices, tblPricing.RRP, tblSalesTax.VatRate, tblSalesTax.Tourism, tblSalesTax.Insurance, tblSalesTax.TourismLevy, tblPricing.TaxInclusive, tblSalesTax.InsuranceRate, tblSalesTax.InsuranceRate AS Premium, IIf(([TaxClass]="D"),Round(IIf(([TaxClass]="D"),[Prices],([Prices]/1.16)),2),Round(IIf(([TaxClass]="C3"),[Prices],([Prices]/1.16)),2)) AS ExportPrice, tblPricing.NoTaxes, tblProducts.Sales, tblSalesTax.TurnoverTax, tblSalesTax.Bettings, tblSalesTax.Excise, tblSalesTax.ExciseRate
FROM tblSalesTax INNER JOIN (tblProducts INNER JOIN (tblcartergory INNER JOIN tblPricing ON tblcartergory.CartID = tblPricing.CartID) ON tblProducts.ProductID = tblPricing.ProductID) ON tblSalesTax.IDClass = tblPricing.IDClass
WHERE (((tblProducts.Sales)=Yes))
ORDER BY tblProducts.ProductID DESC;

As you can see the above combo box does all that calculation for the software, now to speed up the calculation I had to use a flying combo box as below:

Option Compare Database
Option Explicit

Private SalesProductIDLookup As New weComboLookup

Private Sub Form_Load()
    SalesProductIDLookup.Initialize Me.ProductID, 3
    SalesProductIDLookup.UnfilteredRowSource = "SELECT ProductID,ProductName,BarCode,TaxClass,Prices,RRP,VatRate,Tourism," & _
    "Insurance,TourismLevy,TaxInclusive,InsuranceRate,Premium,ExportPrice,NoTaxes," & _
    "Sales,TurnoverTax,Bettings,Excise,ExciseRate FROM ViewtblCustomerBiginvSelect WHERE Productname Like '**' ORDER BY ProductName;"
    SalesProductIDLookup.IsLazyLoading = True
    Me.ProductID.Requery
End Sub