Create one record based on selections from multiple listboxes 3

[djfeldspiff]

Good afternoon!

I'm working on a database that is being used to cross reference pricing information on our products versus our competitors. In short, one table of our parts, one table of our competitors parts and a third table that houses a cross reference from our part to their part. I'm working on a user interface that would allow the user to browse our parts not having a record in the xref table and also browse our competitors parts not having a record in the xref table with the hope of marrying the data as a match is identified. The form I've created has two listboxes, one displaying each of the datasets I mentioned. I'd like the user to have the ability of selecting one or many of our parts to potentially one or many of their parts. After making the selections, I'd like the user to click on a command button that would write the record to the xref table. Each list box displays multiple columns to better identify the part number, but I only need one data element from our part list and two elements from their part list in order to make the cross reference successful. The data in question is: ourparts keyfield, competitoridnum, competitorpartnum. Can this be done via code?

 

[SnaveBelac]

Your XRef table should only store the unique IDs (primary keys) of each of the other tables (as well as a PK of its own). Any other data can be retreived from the relevant table as necessary. Presumably your competitoridnum and competitorpartnum are stored in the competitors part table.

Your XRef table provides the link for a Many-to-Many relationship between your parts and your competitors parts (as I am sure you already know)

Code to add multiple parts from each list and cross reference them all would require you too loop through one listbox and for each selected value loop through the other listbox and add those records to the XRef table..Perhaps like this...

Set ctl_OurParts = me!lstOurParts
Set ctl_TheirParts= me!lstTheirParts
For Each OurPart In ctl_OurParts.ItemsSelected
  varOurCode = ctl_OurParts.ItemData(OurPart)
  For Each TheirPart In ctl_TheirParts.ItemsSelected
    varTheirCode = ctl_TheirParts(TheirPart)
    strSQL = "INSERT INTO tbl_XRef (x_OurPart, x_TheirPart) VALUES(" & varOurPart & ", " & varTheirPart & ")"
    CurrentProject.Connection.Execute(strSQL)
  Next TheirPart
Next OurPart

I hope this makes sense and I have understood the question correctly. Let me know how you get on.

----------------------------
SnaveBelac - Adventurer

http://www.hostpipe.co.uk

 

[RickSpr]

It can certainly be done with code. In fact, it can only be done with code.

I need some clarification. You want the user to be able to select multiple items in each list box? And the effect is to create one row for each possible pairing of the selected items?

Assuming the answers to both questions are "Yes", you need to do the following in the command button's Click event:

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("[Xref Table]")
    For Each var1 In ListBox1.ItemsSelected
        For Each var2 In ListBox2.ItemsSelected
            rst.AddNew
            rst![ourparts keyfield] = ListBox1.Columns(0, var1)
            rst![Competitoridnum] = ListBox2.Columns(0, var2)
            rst![competitorpartnum] = ListBox2.Columns(1, var2)
            rst.Update
        Next var2
    Next var1
    rst.close
    Set rst = Nothing
    Set db = Nothing

You'll need to adjust the first parameter of the Columns() method calls in order to select the appropriate column from the list box.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein