Conver a Aquery to VBA string.

[Falcon88]

i have a Query , i try to convert it to VBA string but fails.
this is my query "

SELECT concatenate1("SELECT tblEmploees.EmploeeName
FROM tblEmploees INNER JOIN tblShiftEmploDetils ON tblEmploees.EmploeeID = tblShiftEmploDetils.EmploeeNo
WHERE (((tblShiftEmploDetils.DetailID) in(" & strSet & ")))") AS MyEmpX
FROM TblShifts
WHERE (((TblShifts.ShiftID)=[Forms]![MainFrm]![SubFrmShiftsMorning].[Form]![ShiftID]));

 

[pbaldy]

It would help to see the code, but this should help:

http://www.baldyweb.com/ImmediateWindow.htm

It would also help to know what "it fails" means exactly. Depending on how you use the string, you may need to concatenate the form value into it.

 

[Falcon88]

It would help to see the code, but this should help:

http://www.baldyweb.com/ImmediateWindow.htm

It would also help to know what "it fails" means exactly. Depending on how you use the string, you may need to concatenate the form value into it.

i try that but not works good .

 

[pbaldy]

What SQL did it produce? What is your code? "not works good" is no more helpful than "it fails" was.

 

[Falcon88]

Dim vItem       As Variant
    Dim strSet      As String
    Dim i           As Long
Dim strSQL As String
Dim strSQL1 As String
Dim varItm As Variant
    strSet = ""
'make sure a selection has been made
  If Me.DeleteList.ItemsSelected.Count = 0 Then
    MsgBox "Please Select At least one emploee"
    Exit Sub
    End If
    With Me.DeleteList
        For Each vItem In .ItemsSelected
            If Not IsNull(vItem) Then
                strSet = strSet & "," & .ItemData(vItem)
            End If
        Next
    End With

    'Remove the first comma
    strSet = Trim(Right(strSet, Len(strSet) - 1))
'strSQL1 = "SELECT concatenate1(""SELECT tblEmploees.EmploeeName FROM tblEmploees INNER JOIN tblShiftEmploDetils ON tblEmploees.EmploeeID = tblShiftEmploDetils.EmploeeNo WHERE (((tblShiftEmploDetils.DetailID) In ("" & strSet & ""))); "") AS MyEmpX FROM TblShifts WHERE (((TblShifts.ShiftID)=[Forms]![MainFrm]![SubFrmShiftsMorning].[Form]![ShiftID]));"
   ' Depug.Print .strSQL1
    
    If MsgBox("you will delete an Emploee " & vbCrLf & _
                "Are you Sure?" & vbCrLf & _
               strSQL1, vbCrLf & _
               vbYesNo + vbMsgBoxRtlReading + vbMsgBoxRight + vbQuestion, "Delete ?? ") = vbNo Then Exit Sub

    strSQL = "DELETE FROM tblShiftEmploDetils WHERE DetailID IN (" & strSet & ")"

    CurrentDb.Execute strSQL
 
    For i = 0 To DeleteList.ListCount - 1
        DeleteList.Selected(i) = False
    Next
For Each vItem In Me!DeleteList.ItemsSelected
    Me.DeleteList.Selected(varItm) = False
  Next vItem

i want the name(s) of the Emploee(s) that will be deleted from a list, show in the massage .

and i have a concatenate fun. as :

Function Concatenate1(pstrSQL As String, Optional pstrDelim As String = " And ") As String
  Dim rs As New ADODB.Recordset
    
    rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    Dim strConcat As String
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                   strConcat = strConcat & .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
        Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate1 = strConcat
End Function

 

[pbaldy]

What is the result of using Debug.Print on strSQL after it's built?

 

[JHB]

One problem is, you can't refer to a form in a SQL-string, you've to get the value instead.

"WHERE (((TblShifts.ShiftID)=[Forms]![MainFrm]![SubFrmShiftsMorning].[Form]![ShiftID]));"

Something like below:

"WHERE TblShifts.ShiftID=" & [Forms]![MainFrm]![SubFrmShiftsMorning].[Form]![ShiftID] & ";"

 

[pbaldy]

Already mentioned that:

Depending on how you use the string, you may need to concatenate the form value into it.

and "can't" is strong. As I mentioned, it depends on how the string is used.