Multiple cbobox filter to a subform

[CMooreJr]

Hey all! I have a quick question.....I have 2 combo boxes on a form labeled "frmInventory"...

cboRetailer
cboVendor

I also have a subform "frmInventorySubform". I have the cboboxes pull addresses etc from the retailer and vendor on the form which is great, however, I wish the user to select the retailer and vendor from the cbo boxes and the SUBFORM populate with the correct data for those matches. They should also be able to enter new data if necessary. The subform pulls from the table labeled "tblInventory".

I got some great advice earlier (see below), but I'm sorry to say I'm a beginner at VBA, so I need a little more instruction cause I just cant get it!

Thanks for your help...


--CODE GIVEN TO ME TO TRY---I CANT GET IT TO WORK!---

Dim Sql As String
Dim Ctrl As Control
Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset


Sql = "SELECT tblCampaign.CampaignID, tblCampaign.CampaignVersion, tblCampaign.CampaignName FROM tblCampaign where "

If (Combo14 <> &quot;&quot Then
Sql = Sql & &quot;tblCampaign.CampaignStatus IN(&quot;

Sql = Sql & &quot;&quot;&quot;&quot; & Combo14.Value & &quot;&quot;&quot;&quot; & &quot&quot; & &quot; And &quot;
End If

If (Combo19 <> &quot;&quot Then

Sql = Sql & &quot;tblCampaign.ProgramName IN(&quot;
Sql = Sql & &quot;&quot;&quot;&quot; & Combo19.Value & &quot;&quot;&quot;&quot; & &quot&quot;

End If

rs.Open Sql, cnn, adOpenKeyset

MSHFlexGrid5.SetFocus
Set MSHFlexGrid5.DataSource = rs


Set rs = Nothing
Set cnn = Nothing

 

[Buj123]

At what place you are getting error, because this code is working for me

 

[CMooreJr]

Hey Buj! I think It's because I don't understand some of the code or where exactly to place it...here is MY code...(??? marks are beside the lines I have no idea what they are for)



Dim Sql As String
Dim Ctrl As Control
Dim rs As ADODB.Recordset (???)
Dim cnn As ADODB.Connection (???)

Set cnn = CurrentProject.Connection (???)
Set rs = New ADODB.Recordset (???)


Sql = &quot;SELECT tblInventoryListing.SKU, tblInventoryListing.SKU, tblInventoryListing.SKU,tblInventoryListing.Description,tblInventoryListing.CCInvoice,tblInventoryListing.MSRP,tblInventoryListing.Available,tblInventoryListing.UnitsSold,tblInventoryListing.CostSold,tblInventoryListing.RetailSold,tblInventoryListing.InventoryonHand FROM tblInventoryListing where &quot;

If (cboCompany <> &quot;&quot Then
Sql = Sql & &quot;tblInventoryListing.Company IN(&quot;

Sql = Sql & &quot;&quot;&quot;&quot; & cboCompany.Value & &quot;&quot;&quot;&quot; & &quot&quot; & &quot; And &quot;
End If

If (cboVendor <> &quot;&quot Then

Sql = Sql & &quot;tblInventoryListing.Vendor IN(&quot;
Sql = Sql & &quot;&quot;&quot;&quot; & cboVendor.Value & &quot;&quot;&quot;&quot; & &quot&quot;

End If

rs.Open Sql, cnn, adOpenKeyset (???)

MSHFlexGrid5.SetFocus (???)
Set MSHFlexGrid5.DataSource = rs (???)


Set rs = Nothing (???)
Set cnn = Nothing (???)


Thanks for your help!

 

[Buj123]

Ok, From your post what i understood is you want to pull and show some fields from &quot;tblInventory&quot;. If so, what i tried to write is to pull the data from the table based on the selections made in the two combo boxes will happen when you run a query to pull the data. Normally we can just write a query in database and open it from VBA code, but as you want to make changes to query conditions based on Combo boxes you do need to build a Sql statement and pass the selection values made in the combo boxes to the Sql statement. After that it nneds to run to get the result. To do that through Access we should have an ActiveConnection, and a Recordset to hold the result of the query. Now
Dim rs As ADODB.Recordset (???)
Dim cnn As ADODB.Connection (???)

Set cnn = CurrentProject.Connection (???)
Set rs = New ADODB.Recordset (???)

This is Declaring the Recordset(rs) and Connection(cnn).
And i am allocating memory to the &quot;rs&quot; by
set rs = new adodb.recordset

And Telling the Access that Cnn is the active connection, that is because i am pulling the data from the table which is in my current database.

So, finally when you Query has been built we should run that. To run that i am using

rs.Open Sql, cnn, adOpenKeyset

Now
MSHFlexGrid5.SetFocus (???)
Set MSHFlexGrid5.DataSource = rs (???)

The above Flexgrid is a example that i tried to give you, because it will show the result in the Flexgrid that is on your Form (If you keep it).

Set rs = Nothing (???)
Set cnn = Nothing (???)
And this is once the Recordset and Connection has bben declared it needs to be closed at the end.

But now, what i want to suggest is create another table with all the field names that you want to retrive from SQL and then pass that rs data to the table. Once if that data is in the table you can load that data to yur subform, in Subforms &quot;onload&quot; event.

If you want any help in how to feed the table with recordset data, let me know.
Thanks.
So now the result in Recordset (rs).

N