Select fields for query

[garywood84]

I've made a form with list boxes on, the user's selections from which are taken as parameters for a query, created by VBA code.

I want to add a final list box to the form from which the user can select which of the available fields they want to see in the query output (i.e. has the same effect as checking/unchecking the "Show" checkbox for each field on the standard query design grid).

I have written the following code to try and do this, but it isn't working. I'm very new to VBA and wonder if anyone can help me out by showing me what's wrong?

'Build Field List
If Me!lstFieldList.ItemsSelected.Count > 0 Then
    For Each varItem In Me!lstFieldList.ItemsSelected
        strFieldList = strFieldList & "[" & Me!lstFieldList.ItemData(varItem) & "], "
    Next varItem
    strFieldList = Left(strCriteria, Len(strCriteria) - 2)
Else
    strFieldList = "'*'"
End If
     
'Create Query String
strSQL = "SELECT Centres." & strFieldList & " " & FROM Centres " & _
    "Where " & strCriteria & _
    " And " & strCriteriaCtr & strSortOrder & ";"

Running the above gives an error, and the section under "'Create Query String" is highlighted in debug mode so I guess the error must lie there? Either that, or the earlier part where strFieldList is being create is generating something which strSQL cannot interpret.

Many thanks in advance if you can help me.

Gary

 

[cuttsy]

When in Debug mode press Ctrl+G
This will bring up the immediate window.

In this window type this:
?strFieldList

press enter and what does it give you?

 

[garywood84]

Cuttsy,

The intermetidate window shows up at the bottom of the screen and I entered ?strListField and pressed enter.

However, all that happened was that the cursor moved onto a new line in the intermediate window!!

Did I do something wrong?

Gary

 

[cuttsy]

Type ?strFieldList not ?strListField.

What you are doing is asking what value is stored in your strFieldList variable.

 

[garywood84]

Sorry Cuttsy, misread your post.

However, typing ?strFieldList gives the same result... (i.e. the cursor just moves to the next line and nothing comes up...)

 

[cuttsy]

This means that your variable strFieldList contains nothing.

The 'Build Field List' section of your code is not working correctly.

 

[garywood84]

Cuttsy,

OK ... any ideas what's wrong with it?

Gary

 

[cuttsy]

underneath your 'Build Field List comment type Stop
then open your form and test it out.

When it hits the Stop line it will break into the code. now press F8 to move through the code this is a way of seeing exactly what your code is doing and with what.

At any time you are moving through your code you can hover your mouse over an expression or variable and it will show you what value it has.

This should highlight what is going wrong.

 

[garywood84]

In case it helps you to help me, here's the code I have:

'Build Field List
strFieldList = "Centres."
If Me!lstFieldList.ItemsSelected.Count > 0 Then
    For Each varItem In Me!lstFieldList.ItemsSelected
        strFieldList = strFieldList & "[" & Me!lstFieldList.ItemData(varItem) & "], "
    Next varItem
    strFieldList = Left(strCriteria, Len(strCriteria) - 2)
Else
    strFieldList = "'*'"
End If
     
strSQL = "SELECT " & strFieldList & " FROM Centres " & _
    "Where " & strCriteria & _
    " And " & strCriteriaCtr & strSortOrder & ";"

 

[cuttsy]

I think this line may be causing you a problem

strFieldList = Left(strCriteria, Len(strCriteria) - 2)

What is strCriteria?

You go to the trouble of looping through the selected items building your strFieldList string.

    For Each varItem In Me!lstFieldList.ItemsSelected
        strFieldList = strFieldList & "[" & Me!lstFieldList.ItemData(varItem) & "], "
    Next varItem

Only to overwrite the strFieldList string with strCriteria (minus the last 2 characters)

strFieldList = Left(strCriteria, Len(strCriteria) - 2)

 

[garywood84]

Thanks mate - you're absolutely right!

The code I was using is based on some other code which sets the query critiera, and strCriteria relates to that! I'd forgotten to change it to what it should be: strFieldList.

Editing that and changing Else part so that where no fields are selected * gets entered into the code, as opposed to '*' has made it work exactly the way I want.

Being new to VBA, this problem has been driving me mad - I hate knowing exactly what I want to do but not knowing how to do it! So, many thanks again for your help - much appreciated.

Cheers,

Gary

 

[cuttsy]

It's quite alright I used to be the same way. Now I almost sometimes know what I'm doing.

Can I give you a tip.

When you want to do something and you don't know how to do it. After you finally manage to figure it out write down what you wanted to do and how you managed to achive it, cos you will forget and it will save you piles of time later on.

Also try and fight the temptation to use code that you don't quite know how it works, I did it once and it turned into nothing short of a disaster. When you get something working, make sure you understand why it works before moving on.

 

[garywood84]

Thanks for this. Both of those are very valid points. Most of the solutions I've found so far have been proposed by forum members or various websites and I've been saving the information as PDFs so I can refer back to it.

As for knowing how code works - I found the following website invaluable in getting list boxes that could feed query parameters. It also has step-by-step walk-throughs of several other useful Access features:

http://www.fontstuff.com/access/