Multiple Selections in Listbox Code Error

[CEL504]

I have written a code to allow multiple selections in a listbox. I keep
getting an error message which states " You have cancelled the previous
operation". Does anyone have any idea what this may be? I have checked the
code many times and compared it to one which does work and it appears to be
the same, but just can't get my head around this particular error message.

Any advice would be greatly appreciated as always.

Cel504.

 

[John W. Vinson]

I have written a code to allow multiple selections in a listbox. I keep
getting an error message which states " You have cancelled the previous
operation". Does anyone have any idea what this may be? I have checked the
code many times and compared it to one which does work and it appears to be
the same, but just can't get my head around this particular error message.

Any advice would be greatly appreciated as always.

Cel504.

Care to post the code?

 

[CEL504]

John,

Please find code as advised.

Many thanks for the quick response.

Cel504





Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String, strWhere As String
Dim i As Integer

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT CarTable.* FROM CarTable "
strWhere = "Where ID IN( "
For i = 0 To lstID.ListCount - 1
If lstID.Selected(i) Then
strWhere = strWhere & "'" & lstID.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere
MsgBox strSQL
'*** delete the previous query
db.QueryDefs.Delete "qryCarTable"
Set qdf = db.CreateQueryDef("qryCarTable", strSQL)

'*** open the query
DoCmd.OpenQuery "qryCarTable", acNormal, acEdit

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on the
next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdRunQuery_Click
End If
End Sub

 

[Roger Carlson]

The code looks fine to me. The question is, how are you trying to use it?
It appears to be an adaptation of code from my sample: CreateQueries2.mdb.
This code is meant to create and display a query to the user by pushing a
button on a form. Is that what you are trying to do with it?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[CEL504]

Roger, the code is one of yours and I visited your website after writing to
you. I followed your instructions and advice, but can't get past this error
message. I have included a button on the from and when pressed the items
selected are displayed, but then an error message appears as discribed. If I
copy yours exactly it will work, but when I substitute my objects and columns
I have this problem.

Roger / John, many thanks for you help and advice up to now.

 

[CEL504]

Roger / John.

With reference to the code, I have sent the complete code from the simple
database to see if you can see any errors in the other part of it.

Cel504.


Option Compare Database

Option Explicit
Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String, strWhere As String
Dim i As Integer

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT CarTable.* FROM CarTable "
strWhere = "Where ID IN( "
For i = 0 To lstID.ListCount - 1
If lstID.Selected(i) Then
strWhere = strWhere & "'" & lstID.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere
MsgBox strSQL
'*** delete the previous query
db.QueryDefs.Delete "qryCarTable"
Set qdf = db.CreateQueryDef("qryCarTable", strSQL)

'*** open the query
DoCmd.OpenQuery "qryCarTable", acNormal, acEdit

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on the
next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdRunQuery_Click
End If
End Sub
Private Sub cmdExit_Click()
DoCmd.Close

Exit_cmdExit_click:
ExitSub

Err_cmdExit_Click:
MsgBox Err.Description
Resume Exit_cmdExit

End Sub

Private Sub Detail_Click()

End Sub

Private Sub Form_Load()

End Sub

Private Sub lstID_BeforeUpdate(Cancel As Integer)

End Sub

 

[Roger Carlson]

Try this. Replace

MsgBox strSQL

with

Debug.Print strSQL

and put a break point on the Delete line below it. Run the form and push
the button. It will stop before it deletes the query and display the SQL
string in the Immediate Window. Copy the result and Paste it here.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger, the code is one of yours and I visited your website after writing
to
you. I followed your instructions and advice, but can't get past this
error
message. I have included a button on the from and when pressed the items
selected are displayed, but then an error message appears as discribed. If
I
copy yours exactly it will work, but when I substitute my objects and
columns
I have this problem.

Roger / John, many thanks for you help and advice up to now.

 

[CEL504]

Roger, I have followed your instructions, please find results as requested.

Cel504


Debug.Print strSQL
'*** delete the previous query

db.QueryDefs.Delete "qryCarTable"
Set qdf = db.CreateQueryDef("qryCarTable", strSQL)
'*** open the query
DoCmd.OpenQuery "qryCarTable", acNormal, acEdit

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on the
next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdRunQuery_Click
End If

 

[Roger Carlson]

Sorry, I meant print out what appears in the Immediate Window so I can see
what the built query looks like.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[CEL504]

Roger,

I think I have found the problem, in your code, you could choose from the
list box (lstCostCenter) a value of the CostCenter in the GLTable. The input
expected was set to text in the table. In the one I am trying to do, it was
set to ID and was an automated number as the record is entered into the
database. When set to text it works fine, when set to autonumber, the "you
cancelled the previous operations" error message appears.

Any ideas on how to allow the autonumber value to be accepted in the listbox
to run the query?

Many thanks for your support on this.