INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...I have been a grateful member of this site for several years. I love this site and refer everyone to it!..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Microsoft: Access Queries and JET SQL Forum

Use list box to edit query in code

CODE

Dim stDocName As String
Dim itm As Variant
Dim itm2 As Variant
Dim strtype As String
Dim strtype2 As String
Dim strSql As String
Dim qdf As DAO.QueryDef
DoCmd.SetWarnings False

For Each itm In Me.lstJobs.ItemsSelected
    strtype = strtype & """,""" & Me.lstJobs.Column(0, itm)
Next


'Set up SQL string
strSql = "SELECT dbo_Job.szCustId_tr INTO ChosenJobs " _
& "FROM dbo_Job " _
& "GROUP BY dbo_Job.szCustId_tr " _
& "HAVING (((dbo_Job.szCustId_tr) In ("
If Trim(strtype & "") <> "" Then
strSql = strSql & "AND dbo_Job.szCustId_tr IN (" & Mid(strtype, 3) & """)"
End If

strSql = strSql

'Check if a query called query1 exists
'If it does not exist, create it.
'If it does exist, permanently change it
   If DLookup("Name", "MSysObjects", "Name= 'qryChosenJobs'") <> "" Then
        Set qdf = CurrentDb.QueryDefs("qryChosenJobs")
        qdf.SQL = strSql
    Else
        Set qdf = CurrentDb.CreateQueryDef("qryChosenJobs", strSql)
    End If

MICKI0220 (IS/IT--Management)

29 Jun 12 15:30

I have edited the code as follows and cannot figure out why I keeping getting a syntax error in my FROM clause...It is a simple select statement.

CODE

Private Sub Command10_Click()
Dim stDocName As String
Dim itm As Variant
Dim strtype As String
Dim strsql As String
Dim qdef As DAO.QueryDef
DoCmd.SetWarnings False
For Each itm In Me.lstJobs.ItemsSelected
    strtype = strtype & """,""" & Me.lstJobs.Column(0, itm)
Next

'Set up SQL string

strsql = "SELECT dbo_Job.szCustId_tr FROM dbo_Job"

If Trim(strtype & "") <> "" Then
  strsql = strsql & " AND dbo_Job.szCustID IN (" & Mid(strtype, 3) & """)"
End If

strsql = strsql
'& Mid(strtype, 3) & """)"

'Check if a query called qryChosenJob exists
'If it does not exist, create it.
'If it does exist, permanently change it
   If DLookup("Name", "MSysObjects", "Name= 'qryChosenJob'") <> "" Then
        Set qdef = CurrentDb.QueryDefs("qryChosenJob")
        qdef.SQL = strsql
    Else
        Set qdef = CurrentDb.CreateQueryDef("qryChosenJob", strsql)
    End If

        

End Sub

dhookom (Programmer)

29 Jun 12 17:13

Troubleshoot. Step through your code to see what is going on as well as using debug.print
Try add

CODE

debug.print "strsql: " & strsql

Duane
Hook'D on Access
MS Access MVP

PHV (MIS)

29 Jun 12 18:11

Replace this:
strsql = strsql & " AND dbo_Job.szCustID IN (" & Mid(strtype, 3) & """)"
with this:
strsql = strsql & " WHERE dbo_Job.szCustID IN (" & Mid(strtype, 3) & """)"

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

MICKI0220 (IS/IT--Management)

9 Jul 12 9:19

PHV that worked, however I discovered something new. The code below puts the selected items from the list box into a textbox called txtselected. If How would I refer to it instead of the listbox. Thanks again for your help.

CODE

Private Sub lstJobs_AfterUpdate()
    
    ' Print the list of selected items to the text
    ' box txtSelected.
    Dim varItem As Variant
    Dim strList As String

    If False Then
       
        Dim intI As Integer
        
    End If
   
    With lstJobs
        If .MultiSelect = 0 Then
            txtSelected = .Value
        Else
            'varItem returns a row number.
            'It 's up to you to retrieve the
            'data you want from that row
            'in the list box.
            For Each varItem In .ItemsSelected
                strList = strList & .Column(0, varItem) & vbCrLf
            Next varItem
            txtSelected = strList
        End If
    End With
End Sub

PHV (MIS)

9 Jul 12 9:54

PHV that worked
So, why not use it ?

MICKI0220 (IS/IT--Management)

9 Jul 12 13:51

I realized that if the list box did not have the jobs highlighted anymore, even though they were in the txtselected box, then it did not run the query correctly. If the list box items were not highlighted anymore it sees it as nothing chosen with the original coding. For me it would not be a problem, just make sure it has the focus, but my users always find any bug and then complain about it.

SkipVought (Programmer)

9 Jul 12 13:53

So you code for that eventuality, and give the user a message that they have not chosen ANYTHING.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Queries and JET SQL

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

Use list box to edit query in code

CODE

CODE

CODE

CODE

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!