Problem on my code

[kkgusta]

Hi there,

I have created a multicolumn list box to allow user to select multi record and save them to the Project_script table. When the user click the cmdAllocateScript button the system will also pickup the record that already exist in the Project_script and only save the record that have not exist in the table. Below is the code that I am working on at the moment but it doesn't seen working well. Hope some of you can help me to find out what is the problem on my code.




Private Sub cmdAllocateScript_Click()

Dim rs As Recordset
Dim VarScriptID
Dim SelectScriptId As Integer

'variables for comparison
Dim PRojID, ScriptID As Integer
Dim StrPtojID As Integer
Dim found, displaymsg As Integer
Dim StrProjectname, StrScriptname, message As String

message = ""
displaymsg = 0

Set rs = CurrentDb.OpenRecordset("SELECT * FROM Project_Script")

For Each VarScriptID In lstSearchresult_projectscript.ItemsSelected

SelectScriptId = Me.lstSearchresult_projectscript.Column(0, VarScriptID)
StrProjectname = Me.cboProject_Name.Column(1)
StrPtojID = [txtAllocationProjID]
StrScriptname = Me.lstSearchresult_projectscript.Column(1, VarScriptID)
found = 0
'=======================================================================================

While Not rs.EOF

PRojID = rs.Fields("Project_ID")
ScriptID = rs.Fields("Script_ID")


If StrPtojID = PRojID And SelectScriptId = ScriptID Then
found = 1
displaymsg = 1
message = message & vbCr & StrScriptname

End If

rs.MoveNext
Wend


'=======================================================================================
If found = 0 Then

rs.AddNew
rs!Script_id = SelectScriptId
rs!Project_id = StrPtojID
rs!Date = Date
'rs.Update


End If

Next



rs.Close
Me.Refresh

If displaymsg = 1 Then

MsgBox (message & vbCr & "Already exist in the project :" & vbCr & StrProjectname)

End If

End Sub

 

[PHV]

From the 2nd occurrence of VarScriptID In lstSearchresult_projectscript.ItemsSelected rs.EOF is ALWAYS true.

You may try this:
...
For Each VarScriptID In lstSearchresult_projectscript.ItemsSelected
[!]rs.MoveFirst[/!]
SelectScriptId = Me.lstSearchresult_projectscript.Column(0, VarScriptID)
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kkgusta]

Got it thank you


CHeer...........