VBA - Run a query... how to show it to the user 1

[neillovell]

What is the command to show my query results to a user?

Here is my code for a form with a button and a drop down menu, which the user selects a record identifier from e.g. to get all records that have AppName = ACROBAT, choose ACROBAT from the list.


Private Sub Command12_Click()
' Create variables
Dim AppName As String

Dim DatabaseConnection As ADODB.Connection
' Set the connection to the DB
Set DatabaseConnection = Application.CurrentProject.Connection
' Get the name of the person to add
AppName = AppNameChoiceComboBox.Value

If Name = Null Then
' Do nothing at all
End If

' Text between speech marks is copied in as-is, while that outside of it (i.e. & Name & and & Number &)
' has the value extracted from the variables.
' Be sure that single quotes, that must go around strings, are included as-is !
SQL = "SELECT * FROM TimingResultsTable WHERE AppName = '" & AppName & "' ORDER BY AppName"
DatabaseConnection.Execute SQL
End Sub

 

[KenReay]

Hi

You could dispense with all the ADO stuff and just set the recordsource of your form to the sql eg in the on open event of the form

Me.Recordsource = "SELECT * FROM TimingResultsTable WHERE AppName = '" & AppName & "' ORDER BY AppName"

or assuming you need to enter the parameter AppName on the 'main' form, have a datasheet view subform and set its recordsouce in the after update event of the AppName control so

Me.MySubFormControlName.FORM.RecordSource = "SELECT * FROM TimingResultsTable WHERE AppName = '" & AppName & "' ORDER BY AppName"
Me.MySubFormControlName.Requery



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[neillovell]

Thanks for responding, but I don't fully understand your reply.

I need the user to set the AppName parameter and they do this via the form, and you think I can either use the button or the onUpdate event to generate a datasheet subform...

I need to expand this form so users can enter other data, such as setting AppName then another field, so the OnUpdate method won't work. But how can I generate this datasheet subform from my code?

I'm not experienced enough in VBA but I imagine it would be something like (forgive the pseudo-C++)

//... code
MyDatasheet = new DataSheet(SQL);
MyDatasheet.Show()


What is the VBA for this?

 

[KenReay]

Hi

Are you saying that the content (ie column headings, not data) of the results are unknown until run time?, if yes, then the only way I can think of is to create an SQl string as you have already suggested,

but if the query structure is fixed in advance aI do not see any problem in prefining a datasheet style sub form as I suggested

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[neillovell]

No, the fields are all defined.
Each row has several fields, one of which is AppName.
The user opens the form, chooses from the list e.g. ACROBAT or WORD. and the code is supposed to return all records where AppName == ACROBAT for example.

What I cannot understand is how, using my code, I can display the results of the SQL query. This code would be fine for inputting into a table, as the user doesn;t have to see the table, but I require the query results to be shown.

 

[KenReay]

Hi

Well I thought I answered in my first post, but heer goes again

Make a query with criteria Forms!MyForm!AppName where MyForm is the name of your form and AppName is the name of the control where the user will enter the Application name

based on this query make a form (Data Sheet View)

On the main form, put a subform control (MySubFormControl) with the above datashhet form as the embedded form

On the main form in the after update event of the Control AppName, put MySubFormControl.Requery

If you (in the future) add further controls where the user may enter (filter) data, then extend the criteria of the above mentioned query, to include the new control(s)and put the requery code in the after update event of the new control(s)

Regards

Ken Reay



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[neillovell]

Ah, small problem.

When I change the combo box, I get the message
MSAccess can't find the macro ComboQuerySubform. It either doesn;t exist or it is not saved. It says when I put in the macrogroup.macroname syntax I must specify the macro's macrogroup it was saved under.

Funnily enough, if I go to design view then back to view mode, the subform is populated with the information.

 

[neillovell]

I got round it by creating a macro: action-> Requery control name -> MySubform and in the VBA code for a button I have
' Run this macro which performs a requery of the datasheet on the form.
Command12.OnClick = "RequeryMacro"


Thank you very much for your help on this, I'll jot this tip down.

 

[neillovell]

Ah, a problem.
If I have three combo boxes and I want one of them to have a choice of 'ALL' how can I do this?
I wrote the VBA code to do this complete with if then else statements, but I am back where I started - I can't show it to the user!

How can I present the results of an SQL statement made in VBA to the user?

 

[KenReay]

Hi

Assuming the source of the data for the combo is an SQL query, you van add an all option using a UNION query

eg

SELECT Id, strName FROM MyTable
UNION
SELECT "*" As A, "<All>" As B FROM MyTAble
ORDER BY strNAme;

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[neillovell]

No the source of the combo box is text typed in by me, i.e. I have written what options are available.

Is it not possible to write code in VBA that puts the result of a query on the screen? I can write the VBA code to get the values from the combo boxes and perform a query, but I cannot show it to the user without creating a new table and getting the user to double click on it in the control panel which seems very inefficient!

 

[KenReay]

Neil

We are going around in circles here,

I answered your question ref displaying the query results, by suggesting one possible option, ie the use of a subform, there are other possibilities (for example using a saved querydef and DoCmd.OpenQuery)

You then asked how to put an <All> option in a combo box, again I answered based on teh assumption that you were using a query to populate the combo, in fact you were using a typed list, in which case the answer is even more simple, just add the <all> option to the list!

Now you are back to asking the first question again, have you tried the solution offerd, was it unsatisfactory in some way?,

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[neillovell]

Sorry, I do not understand what you mean by adding an all option to the combobox Row Source. I have typed in <all> in different ways (with, without "", with/without <>) but nothing works - it treats it as a string.

If you can tell me how to do this I'd be very thankful - I am currently writing a managed C++ front end for the database as I cannot get this to work!

 

[KenReay]

Hi

Of course it treats it as a string, it is a string!

If you want to use (say) Like cboValue, then you need a two column combo box, first column with width zero, you put "*";"<All>";"1";"First Option"...etc in the list of the combo datasource

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[neillovell]

So when I create a combobox I have two columns,
the first is zero width, and has 1, 2, 3, *
the second is the one the user sees, and that has the app name e.g. WORD, NOTEPAD, POWERPOINT, ALL
like so...

1 WORD
2 NOTEPAD
3 POWERPOINT
* ALL

And so when the query fires it gets all records where AppName = 1 or AppName = *

But my records in the database are in text form, they say WORD or NOTEPAD. AppName = 1 won't return WORD will it?

I'm sorry if I seem a bit difficult, but once I learn this I can jot down an example and won't have trouble the next time!

 

[KenReay]

Hi

You misslead me by saying that "<All>" was treated as a string, implying your key waas not a string, if you are using the text name of the app and not a numeric key then

SELECT strName As A, strName FROM MyTable
UNION
SELECT "*" As A, "<All>" As B FROM MyTAble
ORDER BY strNAme;

should do it, using your own column names

There is also a FAQ on this subject on the forms forum

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[neillovell]

Using a column that has numbers in it, 1-5, I told the combobox wizard to use the first column as the identifier with the following

1 1
2 2
3 3
4 4
5 5
* ALL

And when I select the bottom option I get an "expression is too complex" error

Here is the Row Source
1;1;2;2;3;3;4;4;5;5;"*";"ALL

 

[KenReay]

Hi

Why do you keep changing track, first you say your data does not have numbers, now you are messing about with a number list, does it have number or not?

Try

Here is the Row Source
"1";"1";"2";"2";"3";"3";"4";"4";"5";"5";"*";"ALL"

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[neillovell]

"1";"1";"2";"2";"3";"3";"4";"4";"5";"5";"*";"ALL"
Selecting ALL returns the error message saying the expression is typed incorrectly or is too complex to be evaluated. Seelcting 1, 2 ,3 etc. is fine however.

The row source type is Value List, in case that is important.


Steps to reproduce:
1. Create a form with a combo box, two columns and six rows, with 1-5 and * in the left column and 1-5 and ALL in the right
2. Create an SQL Query, MyNumberQuery,
SELECT *
FROM MyTable
WHERE MyNumber=Forms!TESTRecordsByMyNumber!MyNumberChoiceComboBox
ORDER BY MyNumber;
3. Create a form using this query as its base.
4. Add a subform to the first form, selecting the second form as the basis of this subform. Choose datasheet view.
5. Create a macro to Requery the MyNumberQuery, and assign it to the subform (at the bottom of the macro design view)
6. In the AfterUpdate event of the combobox select the query.