Multi Select ListBox as Source

[straydog]

Can someone help me out with this?

I have a database to track Artwork. The artwork filenames are named e.g. 714329-00A.zip, 714329-00B.zip where the letter is the revision of said artwork. In the revision table I have the following fields:

RevisionID, ArtworkID, Revision, RevisionStatus_ID, Path

I have a Continous form that conatains listbox that performs a search of the network for all files based on users input e.g. D:\Artwork\Approved\714329-00A.zip, or D:\Artwork\Pending\714329-00B.zip.

Also on the form I have a control button that when I click on it I want it to lookup the values in a MultiSelect listbox (i.e. 714329-00A.zip, etc.) and search for matching fields in the ArtworkID field of the table/form. If a match is found I want to update the RevisionStatus_ID field of that row.

Below is the Code I have so far. The troubl I'm having is that it is updating all the rows in the table and not those selected in the listbox only.

Thanks for taking a look at this for me.


the Code:


Private Sub SetStatus_Click()
Dim rst As DAO.Recordset
Dim dbs As Database
Dim strLookup As String
Dim ctlSource As Control
Dim intCurrentRow As Integer
Dim strSql As String

Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset("Revision", dbOpenDynaset)
Set ctlSource = Me.lstResults

strSql = "Update Revision Set [RevisionStatus_ID] = '" & 1 & "' Where 'ArtworkID.Column(1)' = " & strLookup

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strLookup = strLookup & Left(ctlSource.Column(0, intCurrentRow), 9) & " Or "
End If


Next intCurrentRow
strSql = strSql & strLookup
strSql = Left(strSql, Len(strSql) - 3)
dbs.Execute strSql


Me.Requery


End Sub

 

[WildHare]

Stray, I'm not exactly sure what you're trying to do here ( I got lost with all that code about revisionStatusIDs and ArtworkIDs and so one..LOL) but if you want to send me a stripped down copy of the database maybe I can look at it later today and figure you a solution...

My email can be found on my website in the following signature lines...

Jim

How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[BlackKnight]

Hi,

I think you can try this. This will update all records' Revision_StatusID to for the selected artwork. According to your code it is not taking into account the revision (A or B) but just the first 9 characters of the filename (714329-00)

Private Sub SetStatus_Click()
Dim rst As DAO.Recordset
Dim dbs As Database
Dim strLookup As String, strSql As String, FName As String
Dim ctlSource As Control
Dim intCurrentRow As Integer

Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset("Revision", dbOpenDynaset)
Set ctlSource = Me.lstResults

'Instead of using a bunch of ORs I am using IN
strSql = "Update Revision Set [Revision_StatusID] = '1' Where [ArtWorkID] IN ([ArtList]);"

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
FName = Left(ctlSource.Column(0, intCurrentRow), 9)
strLookup = strLookup & "'" & FName & "',"
End If
Next intCurrentRow

'Remove last comma.
strLookup = Left$(strLookup, Len(strLookup) - 1)
strSql = Replace(strSql, "[ArtList]", strLookup)
dbs.Execute strSql
Me.Requery
End Sub

Have a good one!
BK

 

[straydog]

Thanks for the tips and replies back. After working all night until 5:30am I finally figured it out. Now if I can only figure out how do include the file name at the end of the path string to update the Path field.

Once again thanks so mmuch for the help.

Here's the working code thus far.

Private Sub SetStatus_Click()
Dim rst As DAO.Recordset
Dim dbs As Database
Dim strLookup As String, strSql As String, strMatch As String, strQoute As String
Dim strPath As String, strWhere As String
Dim ctlSource As Control
Dim intCurrentRow As Integer, intAnswer As Integer

Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset("Revision", dbOpenDynaset)
Set ctlSource = Me.lstResults

strMatch = "Artwork.ArtworkNumber"
strQoute = Chr$(34)

strPath = strPath & Me.txtSearchPath
strWhere = strWhere & strQoute & "Retrieve#" & strPath & strQoute

strSql = "UPDATE Revision INNER JOIN Artwork ON Revision.ArtworkID = Artwork.ArtworkID Set Revision.RevisionStatus_ID = 1, [Path] = " & strWhere & " Where " & strLookup

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strLookup = strLookup & strMatch & " = " & strQoute & Left(ctlSource.Column(0, intCurrentRow), 9) & strQoute & " Or "
End If


Next intCurrentRow
strSql = strSql & strLookup
strSql = Left(strSql, Len(strSql) - 3)

intAnswer = MsgBox(strSql, vbOK)

dbs.Execute strSql

Me.Requery


End Sub