Solved

No Current record Error message on Multi-Select Listbox

Posted on 2007-04-04
36
325 Views
Last Modified: 2013-11-27
the current code works find on a single selection in the listbox, but I get an error"No Current Record" with a multi-selection.  

Private Sub cmdDeleteSelect_Click()
    Dim db As DAO.Database
    Dim rs, rs1, rs2 As DAO.Recordset
    Dim strSQL, strSQL_1, strSQL2, strSQL3, nJG As String
    Dim lngCount, lngLoop, lngLoop1, lngID, lngID2, nSRCtr As Long
    Dim fld As DAO.Field
    Dim varNumber As Variant

On Error GoTo Err_cmdDeleteSelect_Click

Select Case MsgBox("ARE YOU SURE YOU WANT TO DELETE THE SELECTED RECORDS?", _
    vbYesNoCancel Or vbExclamation Or vbDefaultButton1, _
    "Deleting the Selected Records")

    Case vbYes
        If lstSRTemp.ItemsSelected.Count = 0 Then
            MsgBox "You must select at least one SR item to be Deleted."
                Exit Sub
        Else:
            varNumber = lstSRTemp.ItemsSelected.Count
            nSRCtr = 0
            Set db = CurrentDb
            Set rs = db.OpenRecordset("TA_SRArchiveData", dbOpenDynaset)
               For varNumber = IIf(Me.lstSRTemp.ColumnHeads, 1, 0) To Me.lstSRTemp.ListCount - 1
                    If nSRCtr = lstSRTemp.Column(0) Then
                        GoTo cont2:
                    Else
                        nSRCtr = lstSRTemp.Column(0)
                        nJG = lstSRTemp.Column(1)
                           strSQL = "SELECT top 1 * FROM TA_SR where Job_Group =" & Chr(34) & nJG & Chr(34) & ""
                           strSQL_1 = "SELECT Max(Job_Group) AS MaxOfJob_Group" & _
                                " FROM TA_SRArchiveData " & _
                                " ORDER BY Max(Job_Group) DESC"
                            Set rs1 = CurrentDb.OpenRecordset(strSQL_1)
                            Set rs2 = CurrentDb.OpenRecordset(strSQL)
                            lngID2 = rs1.Fields("MaxOfJob_Group").Value
                            With rs2
                                If rs2.Fields("SubmittedSR").Value = True Then
                                    Select Case _
                                        MsgBox("You CANNOT delete this record, due to being previously submitted.  Do you wish to create a copy of this record for a new SR?", _
                                        vbYesNo Or vbExclamation Or _
                                        vbDefaultButton1, _
                                        "Deleting Previously Submitted")
                                   
                                        Case vbYes
                                            cmdCopyNewRec_Click
                                        Case vbNo
                                            GoTo Exit_cmdDeleteSelect_Click:
                                    End Select
                                End If
                                If rs2.Fields("Job_Group").Value = lngID2 Then
                                    lngID = 0
                                End If
                                    lngCount = .RecordCount
                                    If lngCount > 0 Then
                                    While .EOF = False
                                        For lngLoop = 1 To lngCount
                                            With rs
                                                .AddNew
                                                   If lngID = 0 Then
                                                       lngID = rs2.Fields("SRctr").Value
                                                   End If
                                                    For Each fld In rs.Fields
                                                        With fld
                                                            If .Attributes And dbAutoIncrField Then
                                                                GoTo NextCont:
                                                            End If
                                                                rs.Fields(.Name).Value = rs2.Fields(.Name).Value
                                                        End With
NextCont:                                            Next
                                                .Update
                                                End With
                                         If lngLoop = lngCount Then
                                                'GoTo cont1:
                                            End If
                                        Next
                                        .MoveNext
                                    Wend
                                    End If
                            End With
                        strSQL2 = "SELECT * FROM tblEquipListingPerJobGroup where Job_Group =" & Chr(34) & nJG & Chr(34) & ""
                        strSQL3 = "SELECT * FROM TBL_EquipList_ARCHIVE"
                        Set rs1 = CurrentDb.OpenRecordset(strSQL2)
                        Set rs2 = CurrentDb.OpenRecordset(strSQL3)
                        rs1.MoveLast
                           lngCount = rs1.RecordCount
                        rs1.MoveFirst
                           With rs1
                                If rs1.Fields("Job_Group").Value = lngID2 Then
                                    lngID = 0
                                End If
                                    If lngCount > 0 Then
                                        Do Until rs1.EOF
                                            With rs2
                                                .AddNew
                                                    For Each fld In rs1.Fields
                                                        With fld
                                                            If .Attributes And dbAutoIncrField Then
                                                                GoTo NextCont1:
                                                            End If
                                                                rs2.Fields(.Name).Value = rs1.Fields(.Name).Value
                                                        End With
NextCont1:                                           Next
                                                .Update
                                                End With
                                               lngLoop1 = lngLoop1 + 1
                                            If lngLoop1 = lngCount Then
                                                GoTo cont1:
                                            End If
                                         rs1.MoveNext
                                        Loop
cont1:                              End If
                            End With
                            strSQL = "Select * from TA_SR WHERE Job_Group = " & Chr(34) & nJG & Chr(34) & ""
                                Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
                                   If Not rs.NoMatch Then
                                      rs.Delete
                                   End If
                            strSQL = "Select * from tblEquipListingPerJobGroup WHERE Job_Group = " & Chr(34) & nJG & Chr(34) & ""
                                Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
                                   If Not rs.NoMatch Then
                                    rs.MoveLast
                                        lngCount = rs.RecordCount
                                    rs.MoveFirst
                                    With rs
                                        If rs.Fields("Job_Group").Value = lngID2 Then
                                           lngID = 0
                                        End If
                                        If lngCount > 0 Then
                                            Do Until rs.EOF
                                                rs.Delete
                                                lngLoop1 = lngLoop1 + 1
                                                If lngLoop1 = lngCount Then
                                                    GoTo cont2:
                                                End If
                                            rs.MoveNext
                                            Loop
                                        End If
cont2:                                End With
                                    End If
                            strSQL = "Select * from tblSRListTemp WHERE Job_Group = " & Chr(34) & nJG & Chr(34) & ""
                                Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
                                   If Not rs.NoMatch Then
                                    rs.MoveLast
                                        lngCount = rs.RecordCount
                                    rs.MoveFirst
                                    With rs
                                        If rs.Fields("Job_Group").Value = lngID2 Then
                                           lngID = 0
                                        End If
                                        If lngCount > 0 Then
                                            Do Until rs.EOF
                                                rs.Delete
                                                lngLoop1 = lngLoop1 + 1
                                                If lngLoop1 = lngCount Then
                                                    GoTo cont2:
                                                End If
                                            rs.MoveNext
                                            Loop
                                        End If
                                    End With
                                    End If
                            rs.Close
                            db.Close
                    End If
                Next varNumber
Set db = Nothing
Set rs = Nothing
Set rs1 = Nothing
Set rs2 = Nothing
           Me.lstSRTemp.Requery
           Me.FilterSub.Requery
        End If
    Case vbNo
        GoTo Exit_cmdDeleteSelect_Click:

    Case vbCancel
        GoTo Exit_cmdDeleteSelect_Click:

End Select

Exit_cmdDeleteSelect_Click:
    Exit Sub

Err_cmdDeleteSelect_Click:
    MsgBox Err.Description
    Resume Exit_cmdDeleteSelect_Click
   
End Sub
0
Comment
Question by:Karen Schaefer
  • 16
  • 16
  • 2
  • +2
36 Comments
 
LVL 9

Expert Comment

by:TheSloath
Comment Utility
Which line?
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
Is this returning a valid number when you step through?
        Else:
            varNumber = lstSRTemp.ItemsSelected.Count

Also, in your declarations, you have to declare each variable's type when do more than one with each Dim statement, i.e.
Dim rs, rs1, rs2 As DAO.Recordset
should be
Dim rs As DAO.Recordset, rs1 As DAO.Recordset, rs2 As DAO.Recordset

As it is now, rs & rs1 are set to Variant instead of DAO.Recordset
I don't think this has anything to do with your question -- just wanted to point it out

OM Gang
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
lol ... sorta code overload ...

yes ... which line ...

Sloath ... what time is it in NZ?  right now it's 15:17 PST (04-04-2007) ... just curious ...

mx
0
 
LVL 9

Expert Comment

by:TheSloath
Comment Utility
A very civilised 10:18, rather than the 01:30 this morning. Productive time then though...  :-)
0
 

Author Comment

by:Karen Schaefer
Comment Utility
If you can suggest how to simplified it I would appreciated greatly.  I am trying to delete records from 2 tables, but prior to deleteion I want to archive the data.  Keep in mind that table "TA_SR" and its archive counterpart shoudl be 1 record, however the equipment table can contain multiple records for each job_group.

When I step thru the code it does not pass the next SRCTR from the list and so it wants to quit via goto cont2: in the code.

How do i get it to gotot and pass the next selected item in the listbox once the first item has been deleted and archived.
0
 

Author Comment

by:Karen Schaefer
Comment Utility
Is there a way to copy both records listed in the list box at the same time from the same table then move on to the equipment tables and delete all pertient records - but remember to archive before deletion.?
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
Hi Karen...

a few things...and yes...I want to help you reduce this code...we all do.

goto's syntax should read
GoTo NextCont without the :

selecting multiple items, you can actually iterate through the listbox an create a list that can be used in an IN("text","text") statement, that can then be used in a delete SQL or InsertSQL, rather than the whole recordset idea.

a small loop like so would make it a one step process

With Me.MyListBox
        For Each varItem In .ItemsSelected
            myString = myString & ", " & varItem.Selected
        Next
    End With
     mystring = left(myString, len(myString)-1) ' To get rid of the comma
    strSQL = "select * from mytable where field in(" & myString & ");"

you can re-use the myString at this point to pull your recordsets together.

More to follow....
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
this we can make a variable since it returns only 1 field value

                           strSQL_1 = "SELECT Max(Job_Group) AS MaxOfJob_Group" & _
                                " FROM TA_SRArchiveData " & _
                                " ORDER BY Max(Job_Group) DESC"

MaxJG = dmax("Job_Group", "TA_SRArchiveData")

0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
actually...this gets replaced

lngID2 = rs1.Fields("MaxOfJob_Group").Value

with

lngID2 = dmax("Job_Group", "TA_SRArchiveData")
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
Comment Utility
ok..revision.

this creats a string that can be used to select multiple records from a table using a numeric ID field in the SQL

'for numeric fields
    Dim varItem As Variant
    Dim myString As String
    With Me.MyListBox
        For I = 0 To .ItemsSelected.count - 1
            myString = .Column(0, I) & ", " & myString
        Next I
    End With
    myString = Left(myString, Len(myString) - 2)
strSQL = "Select * from tblTest where ID in(" & myString & ");"


This one, uses a text field...instead of a numeric one.

'for text fields
    Dim varItem As Variant
    Dim myString As String
    With Me.MyListBox
        For I = 0 To .ItemsSelected.count - 1
            myString = Chr(39) & .Column(0, I) & Chr(39) & ", " & myString
        Next I
    End With
    myString = Left(myString, Len(myString) - 2)
strSQL = "Select * from tblTest where ID in(" & myString & ");"
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
Do you understand how to use these Karen? Where before, you were looping through 1 time for each item selected, you can now collect ALL the items selected at once, and apply the list of items to your select statements for your RS's.

So you aren't looping, you're going through once....archiving, then deleting all in one pass.
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
is your column(1) text or numeric?
0
 

Author Comment

by:Karen Schaefer
Comment Utility
text!

k
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
IF I create your SQL where I'm grabbing all the records using all values from the list box...how then would that affect this line?

If rs1.Fields("Job_Group").Value = lngID2 Then
0
 

Author Comment

by:Karen Schaefer
Comment Utility
                           If rs1.Fields("Job_Group").Value = lngID2 Then
                                lngID = 0
                            End If

this part is check to see if it already exists in the archive table. Not sure how it affects it at this time.

Also I should keep the loop statements that actual update the tables correct?

K
0
 

Author Comment

by:Karen Schaefer
Comment Utility
Am I on the right track:

Private Sub cmdRecDelete_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset, rs1 As DAO.Recordset, rs2 As DAO.Recordset
    Dim myString As String, strSQL As String, strSQL_1 As String, strSQL2 As String, strSQL3 As String, nJG As String
    Dim lngCount, lngLoop, lngLoop1, lngID, lngID2, nSRCtr As Long
    Dim fld As DAO.Field
    Dim varItem As Variant
    Dim myString As String


Select Case MsgBox("ARE YOU SURE YOU WANT TO DELETE THE SELECTED RECORDS?", _
    vbYesNoCancel Or vbExclamation Or vbDefaultButton1, _
    "Deleting the Selected Records")

    Case vbYes
        If lstSRTemp.ItemsSelected.Count = 0 Then
            MsgBox "You must select at least one SR item to be Deleted."
                Exit Sub
        Else:
            Set db = CurrentDb
            Set rs = db.OpenRecordset("TA_SRArchiveData", dbOpenDynaset)
            With Me.lstSRTemp
                For i = 0 To .ItemsSelected.Count - 1
                    myString = Chr(39) & .Column(0, i) & Chr(39) & ", " & myString
                Next i
            End With
            myString = Left(myString, Len(myString) - 2)
            strSQL = "Select * from TA_SR where Job_group in(" & myString & ")"
            Debug.Print strSQL
                With Me.lstSRTemp
                    For Each varItem In .ItemsSelected
                        myString = myString & ", " & varItem.Selected
                    Next
                End With
                    Set rs1 = CurrentDb.OpenRecordset(strSQL)
                        lngID2 = DMax("Job_Group", "TA_SRArchiveData")
                        With rs1
                            If rs1.Fields("SubmittedSR").Value = True Then
                                Select Case _
                                    MsgBox("You CANNOT delete this record, due to being previously submitted.  Do you wish to create a copy of this record for a new SR?", _
                                    vbYesNo Or vbExclamation Or _
                                    vbDefaultButton1, _
                                    "Deleting Previously Submitted")
                                Case vbYes
                                     cmdCopyNewRec_Click
                                Case vbNo
                                    ' GoTo Exit_cmdDeleteSelect_Click
                                End Select
                            End If
                            If rs1.Fields("Job_Group").Value = lngID2 Then
                                lngID = 0
                            End If
                                lngCount = .RecordCount
                                If lngCount > 0 Then
                                While .EOF = False
                                    For lngLoop = 1 To lngCount
                                        With rs
                                            .AddNew
                                               If lngID = 0 Then
                                                   lngID = rs1.Fields("SRctr").Value
                                               End If
                                                For Each fld In rs.Fields
                                                    With fld
                                                        If .Attributes And dbAutoIncrField Then
                                                            GoTo NextCont
                                                        End If
                                                            rs.Fields(.Name).Value = rs1.Fields(.Name).Value
                                                    End With
                                                Next
                                            .Update
                                            End With
                                     If lngLoop = lngCount Then
                                            'GoTo cont1:
                                        End If
                                    Next
                                    .MoveNext
                                Wend
0
 

Author Comment

by:Karen Schaefer
Comment Utility
do I need to move the
   For Each varItem In .ItemsSelected
                        myString = myString & ", " & varItem.Selected
  '>>>>>                  Next
 '>>>>>                                 End With
to the end of the code after the Wend?
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
the first loop looks ok

ok           With Me.lstSRTemp
.                For i = 0 To .ItemsSelected.Count - 1
.                    myString = Chr(39) & .Column(0, i) & Chr(39) & ", " & myString
.                Next i
.            End With
.            myString = Left(myString, Len(myString) - 2)
.           strSQL = "Select * from TA_SR where Job_group in(" & myString & ")"
.            Debug.Print strSQL

but why the second loop below?
                With Me.lstSRTemp
                    For Each varItem In .ItemsSelected
                        myString = myString & ", " & varItem.Selected
                    Next
                End With

say MyString from the top loop ends up looking like this

select * from TA_SR where Job_Group in (1,2,3);

When you open that up in a recordset, you'll get 3 records returned if the Job_Group record IDs match.

Given that the MyString contains the ID numbers that you need to process, you can do an insert statement into your archive table and a delete statement for your other tables and call it a day.

If you have an index in your archive table that doesn't allow duplicates, then you don't have to worry about checking the Job_Group value.

This should really only involve 1 insert statement, and 2 delete statements. I don't think you have to loop for anything except the Listbox.

J

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
something like this for the Archive insert

INSERT INTO TA_SRArchiveData ( Job_Group )
SELECT TA_SR.Job_Group, TA_SR.*
FROM TA_SR
WHERE TA_SR.Job_Group in ( " & MyString & ");"
0
 

Author Comment

by:Karen Schaefer
Comment Utility
why is the results only 1 record when Ihave two selected?

Select * from TA_SR where Job_group in('7571041', 'Job_Group')

Note i changed the column(0) to column(1) - this is the key filtering field
Do I need to move the next i to end of code?

k
0
 

Author Comment

by:Karen Schaefer
Comment Utility
Don't I need to be concerned about the pk field in the archive table - no duplicates?
K
0
 

Author Comment

by:Karen Schaefer
Comment Utility
So you are suggesting that I replace the loop statements that update the records with the simple
strsql =INSERT INTO TA_SRArchiveData ( Job_Group )
SELECT TA_SR.Job_Group, TA_SR.*
FROM TA_SR
WHERE TA_SR.Job_Group in ( " & MyString & ");"
CurrentDb.Execute strsql

etc. for each update and delete queries?
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
when you're building MyString, you're pulling ID numbers right? Above, I pasted two different options for pulling the listbox items....one was IF you were pulling so that you can use text in your IN(  statement, the other if you were using numbers..

textual in statements look like this   IN('bob', 'sam', 'ted')
numeric in statements look like this  IN(1, 2, 3)

so depending on the field that you're using, you need to use the right code to pull the values.

Also, you are using a loop that I didn't give you to get the listbox values.
J
0
 

Author Comment

by:Karen Schaefer
Comment Utility
Could I do a

currentdb.execute (qryname, where job_group = mystring)

k
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
yes

An in your archive table...create an index that would include all the fields so that duplicates aren't going to be put into the table.

That way, when you run the insert SQL, it will simply NOT write in any records that already exist in the table....based on your index.

J
0
 

Author Comment

by:Karen Schaefer
Comment Utility
That way, when you run the insert SQL, it will simply NOT write in any records that already exist in the table....based on your index

Not sure what you are saying Here?
0
 

Author Comment

by:Karen Schaefer
Comment Utility
Also I need the value (text) from the second column of the list box - because the equp detail is linked to the sr detail via the Job_group(second column value).
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
>>Could I do a

currentdb.execute (qryname, where job_group = mystring)

I've never tried that syntax...not sure if that would work

but you're getting it.

if you know the syntax works like that then

qryName can be either an insert query...OR a delete query...myString will apply to both, given that what is in the string matches.



0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
>>That way, when you run the insert SQL, it will simply NOT write in any records that already exist in the table....based on your index

>>Not sure what you are saying Here?


say your archive table has the following (for example sake)

ID     field1   field2   field3
1       abc      123     xyz
2       def       234    fgh
3       bbb      345    xxx

and you want to insert a record into this table using your code.

if you have an index on field1, field2 and field3

you cannot insert the following values again

       abc      123     xyz

because it would violate the index



0
 

Author Comment

by:Karen Schaefer
Comment Utility
the current where statement only retruns 1 job_group in the IN

 WHERE TA_SR.Job_Group in ( '7571041', 'Job_Group') and two items where selected in the lisbox.

Private Sub cmdRecDelete_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset, rs1 As DAO.Recordset, rs2 As DAO.Recordset
    Dim strSQL As String, strSQL1 As String
    Dim lngCount, lngLoop, lngLoop1, lngID, lngID2, nSRCtr As Long
    Dim fld As DAO.Field
    Dim varItem As Variant
    Dim myString As String


Select Case MsgBox("ARE YOU SURE YOU WANT TO DELETE THE SELECTED RECORDS?", _
    vbYesNoCancel Or vbExclamation Or vbDefaultButton1, _
    "Deleting the Selected Records")

    Case vbYes
        If lstSRTemp.ItemsSelected.Count = 0 Then
            MsgBox "You must select at least one SR item to be Deleted."
                Exit Sub
        Else:
            With Me.lstSRTemp
                For i = 0 To .ItemsSelected.Count - 1
                    myString = Chr(39) & .Column(1, i) & Chr(39) & ", " & myString
                Next i
            End With
            myString = Left(myString, Len(myString) - 2)
            strSQL1 = "Select * from TA_SR WHERE TA_SR.Job_Group in ( " & myString & ")"
                   Set rs = CurrentDb.OpenRecordset(strSQL1)
                   With rs
                        If .Fields("SubmittedSR").Value = True Then
                            Select Case _
                                MsgBox("You CANNOT delete this record, due to being previously submitted.  Do you wish to create a copy of this record for a new SR?", _
                                vbYesNo Or vbExclamation Or _
                                vbDefaultButton1, _
                                "Deleting Previously Submitted")
                            Case vbYes
                                 cmdCopyNewRec_Click
                            Case vbNo
                                ' GoTo Exit_cmdDeleteSelect_Click
                            End Select
                        End If
                    End With
 strSQL = "INSERT INTO TA_SRArchiveData ( SRCtr, SubmittedSR, Job_Group, Project, Priority, FTEMNomenclature," & _
                    " NomenclatureModel, Complete_By_Date, Requestor_ID, Calibration, Repair, Modification, Acceptance, Evaluation, Maintenance," & _
                    " Support, Cmis_Lab, Service_Lab, Work_Code, Charge_Number, Disposition, Input_Range_Min, Input_Range_Max, Input_Units," & _
                    " Output_Range_Min, Output_Range_Max, Output_Units, Gain, Cutoff_Freq, Input_Freq, Ref_Freq, Ref_Voltage, Excit_Voltage," & _
                    " Excit_Enabled, FTIR_Accuracy, Offset, Offset_Enabled, REQ_EMO1, REQ_EMO2, REQ_EMO3, REQ_EMO4, REQ_EMO5, REQ_EMO6, SpareCode," & _
                    " CALIBRATION_ID, CalDueDt, EdDt, RequestorComments, PROCESSED_IND )" & _
                " SELECT SRCtr, SubmittedSR, Job_Group, Project, Priority, FTEMNomenclature, NomenclatureModel, Complete_By_Date, Requestor_ID," & _
                    " Calibration, Repair, Modification, Acceptance, Evaluation, Maintenance, Support, Cmis_Lab, Service_Lab, Work_Code, Charge_Number," & _
                    " Disposition, Input_Range_Min, Input_Range_Max, Input_Units, Output_Range_Min, Output_Range_Max, Output_Units, Gain, Cutoff_Freq," & _
                    " Input_Freq, Ref_Freq, Ref_Voltage, Excit_Voltage, Excit_Enabled, FTIR_Accuracy, Offset, Offset_Enabled, REQ_EMO1, REQ_EMO2, REQ_EMO3," & _
                    " REQ_EMO4, REQ_EMO5, REQ_EMO6, SpareCode, CALIBRATION_ID, CalDueDt, EdDt, RequestorComments, PROCESSED_IND" & _
                " FROM tblSRListTemp INNER JOIN TA_SR ON (tblSRListTemp.SRCtr = SRCtr) AND (tblSRListTemp.Job_Group = Job_Group)" & _
                " WHERE TA_SR.Job_Group in ( " & myString & ")" & _
                " GROUP BY SRCtr, SubmittedSR, Job_Group, Project, Priority, FTEMNomenclature, NomenclatureModel, Complete_By_Date," & _
                    " Requestor_ID, Calibration, Repair, Modification, Acceptance, Evaluation, Maintenance, Support, Cmis_Lab, Service_Lab, Work_Code," & _
                    " Charge_Number, Disposition, Input_Range_Min, Input_Range_Max, Input_Units, Output_Range_Min, Output_Range_Max, Output_Units, Gain, Cutoff_Freq," & _
                    " Input_Freq, Ref_Freq, Ref_Voltage, Excit_Voltage, Excit_Enabled, FTIR_Accuracy, Offset, Offset_Enabled, REQ_EMO1, REQ_EMO2, REQ_EMO3," & _
                    " REQ_EMO4, REQ_EMO5, REQ_EMO6, SpareCode, CALIBRATION_ID, CalDueDt, EdDt, RequestorComments, PROCESSED_IND"
            Debug.Print strSQL
        CurrentDb.Execute strSQL
             End If
End Select

End Sub
0
 

Author Comment

by:Karen Schaefer
Comment Utility
after stepping thru the listbox code it only returns the second value, for some reason the first past only retuns
mystring = '', job_group

k
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
isn't Job_Group the name of the field? that doesn't make sense...

do you have column headings in your listbox?
0
 

Author Comment

by:Karen Schaefer
Comment Utility
yes to column headiings

It seems to work if I remove the -1 after .itemselected.count -1
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
With Me.MyListBox
        For I = 1 To .ItemsSelected.count
            myString = Chr(39) & .Column(1, I) & Chr(39) & ", " & myString
        Next I
    End With

for listboxes with column headings
0
 

Author Comment

by:Karen Schaefer
Comment Utility
Thanks Jeff for all your hard work - I think I am on the right track now.  I always try to do things the hard way.  Your way is so much simplier and more precise.

THANKS, THANKS, THANKS, VERY, VERRRRRRRRRRRRRRY MUCH.

I wish I could award you more than 500 points you earned it.

Karen
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
Happy to help anytime. It's your experience that makes helping you easy...not like noobs. Thanks for playing!
J
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now