Multi Selection List Box Codes Again,

[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

 

[Beetle]

strSQL = "SELECT TBLMAIN.*, TBL_DEL_SITE_DETAILS.[CUSTOMER NAME]
FROM TBLMAIN INNER JOIN TBL_DEL_SITE_DETAILS ON
TBLMAIN.ID = TBL_DEL_SITE_DETAILS.ID "

strWhere = "WHERE TBLMAIN.ID IN( "

etc.

You'll need to adjust for the line wrap.

 

[CEL504]

Many thanks Beetle for you help.Can I impose on you a little more please as I
am still struggling a little with the code.

The listbox selection picks up recodes as chosen, from TBLMAIN ID and
displays them either indivually or all selected. However, the Del Cust Name,
is in a separate table called TBL_DEL_SITE_DETAILS. The TBLMAIN and
TBL_DEL_SITE_DETAILS are linked by Site No [TBLMAIN] and Del Site No
[TBL_DEL_SITE_DETAILS]. In the TBL_DEL_SITE_DETAILS is a heading called Del
Cust Name, which is the information I want to display in the query.

Any ideas on how this can be achieved? Many thanks for your help up to now

strSQL = "SELECT TBLMAIN.*, TBL_DEL_SITE_DETAILS.[CUSTOMER NAME]
FROM TBLMAIN INNER JOIN TBL_DEL_SITE_DETAILS ON
TBLMAIN.ID = TBL_DEL_SITE_DETAILS.ID "

strWhere = "WHERE TBLMAIN.ID IN( "

etc.

You'll need to adjust for the line wrap.
--
_________

Sean Bailey

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