from list box to tmp table

[DomZ]

Hey everybody,
What I want to do is when a user selects a couple parts # from the list box, some of the information from TblPartsTracking, TblCients, TblParts will be put into a tmp table so that a report can use those...

I have been able to create a strSQL as follow:

strSQL = "INSERT INTO TblTmp1 ( CustomerID, CompanyName, PartNumber, Weight, TagNumber, Drums, Quantity, Fini, Épaisseur ) " & _
"SELECT TblPartsTracking.CustomerID, TblClients.CompanyName, TblPartsTracking.PartNumber, TblPartsTracking.Weight, TblPartsTracking.TagNumber, TblPartsTracking.Drums, TblPartsTracking.Quantity, TblParts.Fini, TblParts.Épaisseur " & _
"FROM (TblClients INNER JOIN TblPartsTracking ON TblClients.CustomerID = TblPartsTracking.CustomerID) INNER JOIN TblParts ON TblPartsTracking.PartNumber = TblParts.PartNumber "

So basically, when the user click on the preview button, it will DoCmd.OpenReport ''rptname'', acViewPreview
where rptname is bound to the tmp table..
Now, the thing is, is that everytime the user selects parts and clicks on preview, TblTmp1 needs to be cleared from it's content so that the new content can be stored...

Thanks

 

[bradcccs]

You will need to clear TblTmp1 prior to the user selecting from the list, otherwise you will clear the newly selected items.

You could use a delete query on the opening of the form in question.

I am assuming that the temp table is local, and not being used by multiple users, otherwise: Trouble Ahoy.

HTH

Brad.

 

[DomZ]

See below

 

[DomZ]

well, basically:
My list box has 3 columns: CustomerID, PartNumber and DateIn.
it takes this information from TblPartsTracking.
Now,
When the user selects some parts, lets say XZV-01892 and XZS-0092(Yes my list box has multiselect set to true) from the list box, which is the second column (#1 if the columns start at 0) in the list box, all the information which would be CustomerID, CompanyName, PartNumber, Weight, TagNumber, Drums, Quantity, Finish and Thickness, taken as seen in the SELECT part, of these parts, would be entered in the temp table named TblTmp1.

Here I have my strSQL statement.. Do I need a WHERE clause? what should it be?
"INSERT INTO TblTmp1 ( CustomerID, CompanyName, PartNumber, Weight, TagNumber, Drums, Quantity, Fini, Épaisseur ) " & _
"SELECT TblPartsTracking.CustomerID, TblClients.CompanyName, TblPartsTracking.PartNumber, TblPartsTracking.Weight, TblPartsTracking.TagNumber, TblPartsTracking.Drums, TblPartsTracking.Quantity, TblParts.Fini, TblParts.Épaisseur " & _
"FROM (TblClients INNER JOIN TblPartsTracking ON TblClients.CustomerID = TblPartsTracking.CustomerID) INNER JOIN TblParts ON TblPartsTracking.PartNumber = TblParts.PartNumber "

Now for the delete part. I added a Sub in the module section with the code as follow. Now when the user clicks the preview button, it will do that command right away and THEN what i need afterwards is the transfer of information into TblTmp1 as described above.

Sub DelTbl()
Dim DelStr As String
DoCmd.SetWarnings (False)
DelStr = "DELETE TblTmp1.* FROM TblTmp1;"
DoCmd.RunSQL DelStr
End Sub