C
CEL504
Could any one help me with making an addtion to the following code please.
This is the code for being able to select several ID's from a listbox to run
a query. This works fine thanks to the help of Roger on here. However, I wish
to add another table to the query, where the name of the customers delivery
point is held. The name is not held in the TBLMAIN, just the site code. The
addtional table I need to incude is TBL_DEL_SITE_DETAILS and the field is
CUSTOMER NAME . How would I go about doing this? When I link this table in
the query, the table will be removed when the code is run.
As ever all your help is appreciated .
cel504.
Private Sub cmdChoose_Click()
On Error GoTo Err_cmdChoose_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 TBLMAIN.* FROM TBLMAIN "
strWhere = "Where ID IN( "
For i = 0 To ListBoxMulti.ListCount - 1
If ListBoxMulti.Selected(i) Then
strWhere = strWhere & ListBoxMulti.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 "qrySelect"
Set qdf = db.CreateQueryDef("qrySelect", strSQL)
'*** open the query
'DoCmd.OpenQuery "qrySelect", acNormal, acEdit
Dim stDocName As String
stDocName = "rptSelect"
DoCmd.OpenReport stDocName, acViewPreview
Exit_cmdChoose_Click:
Exit Sub
Err_cmdChoose_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_cmdChoose_Click
End If
End Sub
This is the code for being able to select several ID's from a listbox to run
a query. This works fine thanks to the help of Roger on here. However, I wish
to add another table to the query, where the name of the customers delivery
point is held. The name is not held in the TBLMAIN, just the site code. The
addtional table I need to incude is TBL_DEL_SITE_DETAILS and the field is
CUSTOMER NAME . How would I go about doing this? When I link this table in
the query, the table will be removed when the code is run.
As ever all your help is appreciated .
cel504.
Private Sub cmdChoose_Click()
On Error GoTo Err_cmdChoose_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 TBLMAIN.* FROM TBLMAIN "
strWhere = "Where ID IN( "
For i = 0 To ListBoxMulti.ListCount - 1
If ListBoxMulti.Selected(i) Then
strWhere = strWhere & ListBoxMulti.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 "qrySelect"
Set qdf = db.CreateQueryDef("qrySelect", strSQL)
'*** open the query
'DoCmd.OpenQuery "qrySelect", acNormal, acEdit
Dim stDocName As String
stDocName = "rptSelect"
DoCmd.OpenReport stDocName, acViewPreview
Exit_cmdChoose_Click:
Exit Sub
Err_cmdChoose_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_cmdChoose_Click
End If
End Sub