Taking a string and converting it onthefly to a query? 1

[linsalad]

Hey all,

I have a little page set up where you can select one or more companies in a list box, click the "add" button, and pop them into the selected companies list box. (I know it's simply, but I'm new to this )

Anyways, what I want to do is compare said companies. Ideally by clicking a "compare" button that triggers a spreadsheet (is the proper term "dynasheet?&quot in a subform on that page.

...but I'm not sure how to take the stuff from the selected item box and create the query on the fly. I don't really even need it in the subform, although that would be nice. Is it possible? Can I generate queries like this on the fly? And if so, can someone either snip me some code, give me a general topic/heading I can use to look it up in my MS Access 2k Bible...or...well, anything constructive

Thank you!!!
Dave

 

[KyleS]

Hi Dave,

Yeah, creating the query at run-time (on the fly) is simple and so is populating the subform with the data. There are a couple options depending on what you're looking for.

I will need something of an example, and then I can give you the code. (To give me an example, create a query that looks like you want your output to look, same fields and all and click the View menu item and select SQL. Copy that text and paste it here so I can have a look at it)

First create a subform from your query and save it in "Datasheet" view (so the form when opened looks like a query).

Then we build the SQL string (the code behind the query) and set the recordsource of the subform to that SQL string and refresh the subform.

Might sound a little complicated, but give me an example and I'll send the code your way... Kyle

 

[linsalad]

Kyle - you rock!

Thanks! I'll try to get to it ASAP tomorrow morning - but (if it's ok) it might be more towards the afternoon, as I have a seminar from 9 to 5 (shudder).

Thanks

 

[linsalad]

Heya,

Here you go:
SELECT [Company Name], [YoY of CY2Q01 & CY2Q02], [Sequential of CY2Q02 & CY1Q02], [CY2Q02]
FROM [Q_Financial Calcs];

Pretty simple really - I even combined all the stuff I needed into one query, so I can pull the data just from one ...query

Anyways, this is just a sample of ALL the companies. I guess I just need to figure out how to select just the companies I want, from 1 up until the total # of companies (for loop? counter?...I'm desperately trying to apply my C++ and Java skills here and getting nailed on syntax.)

Thanks again!
Dave
p.s. have another all day meeting tomorrow, so might not be able to check the thread till monday :-/

 

[KyleS]

Dave, sorry for the delay, had a meeting Friday myself and got suckered into one today...

I don't know the name of your form or listbox, but try this (substituting for propernames)

Private Sub cmdGetData_Click()
Dim strSQL As String, i As Integer

strSQL = "SELECT [Company Name], [YoY of CY2Q01 & CY2Q02], [Sequential of CY2Q02 & CY1Q02], [CY2Q02]" & _
" FROM [Q_Financial Calcs]"

If Me!ListBox.ListCount > 0 Then
strSQL = strSQL & " WHERE [Company Name] = '" & Me!ListBox.ItemData(i) & "'"
If Me!ListBox.ListCount > 1 Then
For i = 2 To[/color] Me!ListBox.ListCount
strSQL = strSQL & " OR [Company Name] = '" & Me!ListBox.ItemData(i) & "'"
Next i
End If
End If

strSQL = strSQL & ";"

Me!SubFormName.RecordSource = strSQL
Me!SubFormName.Requery

End Sub Kyle

 

[linsalad]

Kyle - wow, thanks!

For some reason I didn't get an email notification, so after a day or so I stopped checking...and just checked now! I'll try out the code first thing tomorrow

Again, MAJOR thanks!

 

[KyleS]

Dave,
I just tried this on a form of mine and it didn't work as advertised...

Just one change. The code above HAS GOT to be on a button on the subform. Change it like this:

Private Sub cmdGetData_Click()
Dim strSQL As String, i As Integer

strSQL = "SELECT [Company Name], [YoY of CY2Q01 & CY2Q02], [Sequential of CY2Q02 & CY1Q02], [CY2Q02]" & _
" FROM [Q_Financial Calcs]"

If Forms!YourForm!ListBox.ListCount > 0 Then
strSQL = strSQL & " WHERE [Company Name] = '" & Forms!YourForm!ListBox.ItemData(0) & "'"
If Forms!YourForm!ListBox.ListCount > 1 Then
For i = 1 To Forms!YourForm!ListBox.ListCount - 1
strSQL = strSQL & " OR [Company Name] = '" & Forms!YourForm!ListBox.ItemData(i) & "'"
Next i
End If
End If

strSQL = strSQL & ";"

Me.RecordSource = strSQL

End Sub


So make these changes (or copy the code) and make absolutely sure the button that runs this code is on the subform and you'll be fine.



Sorry for any confusion there Dave, I just missed on the execution. Kyle

 

[linsalad]

Heya Kyle,

Well I had a chance today to play with it, but I'm a bit lost. I took care to associate the code with a button as well as to change the names (YourForm and Listbox), and when I click the button it runs...

But I'm confused about where I source the query to the refresh SQL string. I tried changing the "Me.RecordSource" to "[Name of my query].Recordsource" and got a "Compile error: Method or data member not found."

Sorry for what I assume is just a stupid mistake on my part...

 

[KyleS]

Actuall, you can leave the "Me" in there. Sorry for my assumtion. "Me" is a VB Keyword that represents the Database Object (Form, or Report) that the code is directly attached to. So Me.Recordsource will refer to the recordsource of the form on which the button resides (which is why it's important for the button to be on the form you're filtering).

Not at all a stupid question. Kyle

 

[linsalad]

Ahh...that makes sense. But if so, then the button has to be on the subform, correct? Not on the mainform? In other words, if I have the two listboxes on the mainform, I should move the button to the subform so that the proper recordsource is modified...

but wait - the subform is simply a query....

Ok, so to recap:
I made my form with the two listboxes, a select mechanism, and a button. I then made a query using the data (also from a query) that I am going to want to use, and I attached that as a subform to my mainform. Finally, I took your code, modified it, and put it on my button.

What is my missing step? The query is full of records at the start, and post-click doesn't update with the selected records.

Thanks!
Dave

 

[KyleS]

Actually, your subform is a form, in Datasheet view. The easiest option would be to do a little formatting and show your subform in Continuous Form View, and put your button in the Form Header. There are other ways I have accomplished what you're asking, but thay ain't pretty! Kyle

 

[linsalad]

Woot! Thanks again for your patience, it works!

Well, sort of. I can now scroll through the various selected records, but I do so sequentially, despite having it in continuous form view. Is there any way to organize it so that all of the selected forms come up in the same subform at the same time? i.e. in the same record...or at least, all visible simultaneously?

Thanks!
Dave

 

[KyleS]

Well, part way is better than we were when we started!

OK, so let's recap everything as it stands now.

You have a form with a button "Add" and 2 listboxes (let's call them "lstFirst" and "lstResults&quot "lstFirst" has ALL the companies in it, and when you click your "Add" button it copies the name of the selected company from "lstFirst" to "lstResults".

Than you have a subform (in continuous view) with a button (lets call this "cmdResults&quot when you click on cmdResults it changes the RecordSource of your subform to include only those records in lstResults. Does that sound right?

At which point does your subform show up with data and which company does it show for? (is it pulling from lstFirst or lst Results?) Kyle

 

[linsalad]

Hey Kyle,

Sorry for the delay - as usual, other projects came up marked as "urgent."

Recap:
As you said...:"You have a form with a button "Add" and 2 listboxes (let's call them "lstFirst" and "lstResults&quot "lstFirst" has ALL the companies in it, and when you click your "Add" button it copies the name of the selected company from "lstFirst" to "lstResults"."

Then I have subform, in continuous view, but the button ("cmdResults&quot resides on the MAIN form.

Here's what happens:
Load up the form - first two companies are already in the lstResults list (can't figure out how to remove them...I probably need to set the list to empty when the form loads). I select a group of named, add them in, and click done. When that happens, the /main/ form appears to update with more records, when I scroll through the /subform/ updates. But it is correctly using the lstResults data.

So everything works fine, and it's just the picky/tweaker part of me that would love to (if this is even possible) tweak the sheet such that all of the companies show up simultaneously in the subform, allowing for simultaneous comparison, versus trying to remember the data as I scroll back and forth from record to record.

Does that make sense?

Thanks again,
Dave

 

[KyleS]

Yeah, that makes sense.

So right now you get one record and have to scroll to the next one?

In your subform you're sure you've got the form view set to "Continuous Form" and NOT DataSheet or Single Form?

Continuous and Datasheet will give you the "list" view you're looking for...

Kyle

 

[linsalad]

Yup, positive I have the subform in Continuous Form.

 

[KyleS]

Ok,

Have you tried in Datasheet view since we changed it? I'm thinking the continuose Forms issue might be the size of the form, where as we won't have that issue if you put it back to datasheet view.

Is there any way I could get you to send me a copy of the forms and some empty tables (or if you could put some BS data in there that would be great) so I can see if I can't fix it?

kmsinaz11@yahoo.com Kyle

 

[linsalad]

Hey Kyle

I sent an email off to that address last week? Did it come through?

(no rush - just curious)

Thanks
Dave

 

[KyleS]

Yeah, I got it but I don't have A 2000 at work, so I forwarded it home (I have XP there) but I'm having some PC issues. I built a new PC and loaded some other software on my existing PC (so no office and not much of an option for that PC) but the HD I bought for my new PC was DOA, so I don't have office at home. I sent the HD back and it's replacement should be here any day now. I'll get it back to you ASAP! Kyle

 

[linsalad]

Wow - talk about your problems! I'm in the middle of building my next PC, since my old one is having IDE controller issues. blech.

Seriously - take your time! And would it help if I converted it down a version? (I don't know if that would cause problems once I converted it back up to 2000 though).