calling an MS access sql query in a button click

[oly]

Dear expert;



Can you please paste the syntax on how to call an sql query in a button click event in MS access 2010 or can we use an embedded approach. if so, how. All help is appreciated.

[orange]

see the material here
http://www.fontstuff.com/access/acctut14.htm

[oly]

the link doesnt answer my question. Here is my questions again, I have a dropdown list contain colours and a button and I want a situation where if the button is clicked on, it shows a report comprising information about a colour. Does this have to be done in embedded macro or an event procedure, if so how. I am new to access but I have programmed in asp.net and asp before.

[orange]

You can use vba (event procedure) or a macro. I don't use macros.

In the On Click event of the button,
you would have code to Open the report in question, and you would pass the value in the combo(dropdown) as a parameter in the open report command.

There is a wizard in Access. When you add a dropdown(combobox in Access terms), an automated tool is invoked and asks you questions. Similarly when you add a button, a number of questions/steps are invoked.
You could say that you want to open a report when the button is clicked.
It will give you the framework for the code involved.

In very pseudo code, you would have something like

YourButton_Click()
Docmd.OpenReport "yourReportName",,, Me.ComboName.value

Here is actual code that has a list of reports in a combo box, and an option to print or preview; and a button to Open the selected report in the ViewOption chosen,

Private Sub Command2_Click()
10  On Error GoTo Err_Command2_Click
    Dim stDocName As String
    Dim iOption As Integer
20  stDocName = Me.Combo0.Value
    'frame optPreview = 1  (Preview)
    'frame optPrint   = 2  (Normal)
30  iOption = IIf(Me.Frame3 = 1, acViewPreview, acViewNormal)
40  'Debug.Print iOption 'for debugging

50  DoCmd.OpenReport stDocName, iOption
Exit_Command2_Click:
60  Exit Sub
Err_Command2_Click:
70  MsgBox Err.Description
80  Resume Exit_Command2_Click
End Sub

Good luck.

[oly]

I did that but i am getting an error message which says user-defined type not defined. see my syntax below. kindly note, I am using access 2010

Dim dbsA As Database
Set dbsA = CurrentDb
Dim strql As String


strql = "select * from stores"

[orange]

?? I'm a little confused.
Your last post said

Here is my questions again, I have a dropdown list contain colours and a button and I want a situation where if the button is clicked on, it shows a report comprising information about a colour.

You can create a query, or you can open a report and base that report on a value selected from a combobox.

What exactly do you want to do in plain English? and I will help.

[oly]

Hi this is my question once again, I have a dropdown list contain the following items: Red, Blue, Yellow. I want a situation where if the button is clicked on, it shows a report comprising information about the color.

For instance..here is my table information below. The table is called tbl_colour

ID Place
Yellow NewYork
Red Dallas
Blue Mexico
Yellow Toronto
Yellow China

So if yellow is picked from the dropdown list, it should show the following report below

ID Place
Yellow China
Yellow Toronto
Yellow NewYork.

This is the code I have so far below but it is not working though

Dim strql As String
Dim temp As String
temp = Me.Combo4.Value


strSQL = "select * from tbl_colour "
strSQL = strSQL & "where ID = '" & Me.Combo4.Value & "'"
strSQL = strSQL & "order by place"




Debug.Print strSQL
MsgBox strSQL


DoCmd.OpenReport "dbo_testreports", acViewPreview

The code is not working though unfortunately, it only displays one item...it is also complaining about the size of the report...how can I modify it

[orange]

Oly,

I am attaching a sample db that I created before I saw your last post.
It has 3 tables Animal info, AnimalCapture info and Sighting Info.
The query that is the data for the Report is based on the 3 tables.
It has a form with a combo and a button.
The combo lists the names of some Animals.
Select an Animal, then click the button to Open the Report that is constrained by the combo selection.

The report exists and you can open the report separately to see the format and content.

I created the database in 2010 and saved in 2003 format.

[oly]

one more question...I get this message "The section width is greater than the page width and there are no items in the additional space, so some pages may be blank"....if I want to include an and condition in the where section as well...how do I do so...for example if i want my select to be

strSQL = "select * from tbl_colour "
strSQL = strSQL & "where ID = '" & Me.Combo4.Value & "'"
strSQL = strSQL & "and Place != '" NewYork & "'"
strSQL = strSQL & "order by place"

thanks for your help so far

[orange]

That message is in reference to the width of your report -- and has been a pain for years.
Take you sample report an try to adjust column widths. Did you get a message during design that says in an option - to adjust sizes to fit on a page? I think I saw that when creating a report with 2010.

I always start my sql fragments with a space to avoid things running together (syntax errors)

strSQL = "select * from tbl_colour "
strSQL = strSQL & "where ID = '" & Me.Combo4.Value & "'"
strSQL = strSQL & "and Place != '" NewYork & "'"
strSQL = strSQL & "order by place"

strSQL = "select * from tbl_colour "
strSQL = strSQL & " where ID = '" & Me.Combo4.Value & "'"
strSQL = strSQL & " and Place != '"NewYork & "'"
strSQL = strSQL & " order by place"

You had a space before New York???

[oly]

yeah but what if I want to include the strSQL statement in the DoCmd.OpenReport. How do I do so...

[oly]

Also I am still getting a syntax error with the code...see code below


strSQL = "select * from tbl_colour"strSQL = strSQL & "where ID = '" & Me.Combo4.Value & "'"strSQL = strSQL & "and Place != '" & NewYork & "'"strSQL = strSQL & "order by place"

[orange]

yeah but what if I want to include the strSQL statement in the DoCmd.OpenReport. How do I do so...

What/where do you see that sql fitting? I did find a reference, but I haven't tried it.
Here's the link
http://en.allexperts.com/q/Using-MS-...cordsource.htm

When you post code snippets, you should use [ c o d e ] and [ / c o d e ] tags around the code ** no spaces**

[oly]

What/where do you see that sql fitting? I did find a reference, but I haven't tried it.
Here's the link
http://en.allexperts.com/q/Using-MS-...cordsource.htm

When you post code snippets, you should use [ c o d e ] and [ / c o d e ] tags around the code ** no spaces**

so what is wrong with this syntax below because I keep getting an error message and I dont know why

strSQL = "select * from tbl_colour"
strSQL = strSQL & "where ID = '" & Me.Combo4.Value & "'"
strSQL = strSQL & "and Place != '" & NewYork & "'"
strSQL = strSQL & "order by place"

[orange]

You could put a debug.print strSQL in your code. That will print the rendered SQL in the immediate window - excellent debugging technique. If you don't see anything obvious, you can copy the sql and paste it into the SQL View in the query designer. The syntax is good is the designer doesn't complain and the query executes.

If it does fail on syntax, an error will be given.

Is your field value "New York" or "NewYork"?

strSQL = "select * from tbl_colour" 
strSQL = strSQL & "Xwhere ID = '" & Me.Combo4.Value & "'" 
strSQL = strSQL & "Xand Place != 'NewYork'"
 strSQL = strSQL & "Xorder by place"

I think you need to ensure there is a space where the X appears. Also, your bracketing around New York needs to make New York a string.