Multiple cbobox retrival and subform copying 1

[CMooreJr]

Hey all! OK, let me set the scenerio...

I have a form that has a cbobox for the Retailer, called "cboCompany". When a company is selected, the address, etc is autofilled from the tblCompany table.

There is also a cbobox for the vendor, called "cboVendor". When the vendor is selected the address, etc is autofilled.

Now on this form there is a subform called "frmInventorySubform" This subform holds the following fields, "SKU", "Description", "NetCost", "MSRP", "Available","UnitsSold", "RetailSold", "CostSold", and "InventoryonHand"

Now the questions....

1) How can I have the subform bring up the data (inventory details) that only corresponds with the correct Retailer and the Correct Vendor? (I want them to select the two cboboxes and then the subform will fill accordingly. I want all other data filtered out.

2) Now, for the harder question. EACH WEEK there will be a record or records in this subform. When the next week comes up, these same records need to populate the subform also, but they MUST be a different record! The reason is the cost may change for a particular week, the quantity may change, etc. They may also need to add or remove one depending on if the retailer wants them for that particular week. Can I set up a button that will in essence "SELECT ALL" and "COPY" the records from the subform, and when they open a new record, have a button where they can "PASTE" the records in and change what is needed? I really dont see any other way to do it. If you guys have a better suggestion, I'd love to hear it.

Just in case I'm not clear, here is what the subform will look like for week 1:

SKU Desc. NetCost MSRP Available UnitsSold...etc....

123 Box $50.00 $67.99 1000 156
456 XXX $12.00 $17.99 250 12

WEEK 2 MAY be:

SKU Desc. NetCost MSRP Available UnitsSold...etc....

123 Box $45.00 $62.99 400 245
456 XXX $12.00 $17.99 50 0

As you can see, almost anything can change...your suggestions? THANKS!

 

[Buj123]

Hi,
I can answer your first question.

You can read the selections made in the two combo boxes and then pass that values selected in Combo boxes to a Sql statement and runs that Query. I will give you a small code explanation

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

In the above code I am retriving information from one table based on the selection made in Combo14 and Combo19. And i am sending that result to a Grid, in your case you can send it to Subform.

I think i didnot understand 2nd question.
A friendly Suggestion, Don't ask more than one question in one post, because if some body can answer for one question, then your replys will increase any not every body look at this post as there are so many replays, but that is only for one question.

Hope the above code makes you think.