Query by Form: Output to Listbox?

Hi,

I have been asked to look at a database at work that has a query by form
where the user can select a table to query. There are three columns of boxes
on the form. The first column are drop-down combo boxes that allow the user
to select the fields depending upon the table selected above. The next column
of five are for the comparison operators (<,>, etc) and the third column of
five boxes are text boxes where the criteria values are typed. There is one
connector box that has two values: OR or AND.

The form works and displays the query in a table view. This is the code
behind the button that creates the output:

***** Begin Code Quote **********

On Error GoTo cmdRun_Click_Err
      
    Dim db As Database
    Dim qdf As QueryDef
    Dim strSQL As String
      
    Set db = CurrentDb
    
    strSQL = SQLBuild()
      
    If Len(strSQL) > 0 Then
    
        On Error Resume Next
        db.QueryDefs.Delete "tempQueryByForm"
        On Error GoTo cmdRun_Click_Err
        Set qdf = db.CreateQueryDef("tempQueryByForm", strSQL)
        DoCmd.Close
        DoCmd.OpenQuery "tempQueryByForm", acNormal, acReadOnly

    End If
  
cmdRun_Click_Exit:
    Exit Sub

cmdRun_Click_Err:
    MsgBox "Your Query could not run. Please check the SQL syntax and
criteria that you used.", vbOKOnly + vbCritical, "Help."
    Resume cmdRun_Click_Exit

***** End Code Quote ********************

Currently, when the Run button is clicked, the output is displayed in a table
(just like when a query is run). I have been asked two questions at work and
I don't know the answers:

1. Can the output be displayed on a listbox on the form? (I don't know why
they want that - but I'm interested to learn how it could be done)
2. Can the data be sent straight to a report when the user clicks another
button on the form? Would I need to create a report first or can you create a
report "on the fly"?

I tried to answer question 1 myself and created a list box called lstOutput
and thought maybe code similar to the following could work which is being
used elsewhere in their database: (have used general names below)

***** Begin Code Quote **********

    Dim strSQL As String
    Dim strItem As String
          
    strSQL = "SELECT Fieldname FROM " & Table_name
    Set db = CurrentDb
    Set RS = db.OpenRecordset(strSQL)
    Do Until RS.EOF
         strItem = RS.Fields(fieldname).value
         Me.ListBox.AddItem UCase(strItem) 
         RS.MoveNext
    Loop
    RS.Close
    Set RS = Nothing
    Set db = Nothing

***** EndCode Quote **********

Unfortunately, I cannot get the output displayed in the listbox. 

I would appreciate it if someone could give me some help regarding this
situation.

Thanks
Anthony

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200712/1

0
biganthony
12/31/2007 2:16:10 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
405 Views

Similar Articles

[PageSpeed] 33

Surely this is all you need to get the results displayed in a list box?

strSQL = "SELECT Fieldname FROM " & Table_name
Me.ListBox.RowSource = strSQL

"biganthony via AccessMonster.com" <u31673@uwe> wrote in message 
news:7d831e6d065d3@uwe...
> Hi,
>
> I have been asked to look at a database at work that has a query by form
> where the user can select a table to query. There are three columns of 
> boxes
> on the form. The first column are drop-down combo boxes that allow the 
> user
> to select the fields depending upon the table selected above. The next 
> column
> of five are for the comparison operators (<,>, etc) and the third column 
> of
> five boxes are text boxes where the criteria values are typed. There is 
> one
> connector box that has two values: OR or AND.
>
> The form works and displays the query in a table view. This is the code
> behind the button that creates the output:
>
> ***** Begin Code Quote **********
>
> On Error GoTo cmdRun_Click_Err
>
>    Dim db As Database
>    Dim qdf As QueryDef
>    Dim strSQL As String
>
>    Set db = CurrentDb
>
>    strSQL = SQLBuild()
>
>    If Len(strSQL) > 0 Then
>
>        On Error Resume Next
>        db.QueryDefs.Delete "tempQueryByForm"
>        On Error GoTo cmdRun_Click_Err
>        Set qdf = db.CreateQueryDef("tempQueryByForm", strSQL)
>        DoCmd.Close
>        DoCmd.OpenQuery "tempQueryByForm", acNormal, acReadOnly
>
>    End If
>
> cmdRun_Click_Exit:
>    Exit Sub
>
> cmdRun_Click_Err:
>    MsgBox "Your Query could not run. Please check the SQL syntax and
> criteria that you used.", vbOKOnly + vbCritical, "Help."
>    Resume cmdRun_Click_Exit
>
> ***** End Code Quote ********************
>
> Currently, when the Run button is clicked, the output is displayed in a 
> table
> (just like when a query is run). I have been asked two questions at work 
> and
> I don't know the answers:
>
> 1. Can the output be displayed on a listbox on the form? (I don't know why
> they want that - but I'm interested to learn how it could be done)
> 2. Can the data be sent straight to a report when the user clicks another
> button on the form? Would I need to create a report first or can you 
> create a
> report "on the fly"?
>
> I tried to answer question 1 myself and created a list box called 
> lstOutput
> and thought maybe code similar to the following could work which is being
> used elsewhere in their database: (have used general names below)
>
> ***** Begin Code Quote **********
>
>    Dim strSQL As String
>    Dim strItem As String
>
>    strSQL = "SELECT Fieldname FROM " & Table_name
>    Set db = CurrentDb
>    Set RS = db.OpenRecordset(strSQL)
>    Do Until RS.EOF
>         strItem = RS.Fields(fieldname).value
>         Me.ListBox.AddItem UCase(strItem)
>         RS.MoveNext
>    Loop
>    RS.Close
>    Set RS = Nothing
>    Set db = Nothing
>
> ***** EndCode Quote **********
>
> Unfortunately, I cannot get the output displayed in the listbox.
>
> I would appreciate it if someone could give me some help regarding this
> situation.
>
> Thanks
> Anthony
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200712/1
> 


0
Baz
12/31/2007 2:48:35 PM
Baz,

That was it - sometimes the simplest things are right in front of you. I also
had the listbox as a Value List. I set it as a Table/Query and the results
are displayed.

Thanks
Anthony


Baz wrote:
>Surely this is all you need to get the results displayed in a list box?
>
>strSQL = "SELECT Fieldname FROM " & Table_name
>Me.ListBox.RowSource = strSQL
>
>> Hi,
>>
>[quoted text clipped - 91 lines]
>> Thanks
>> Anthony

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200712/1

0
biganthony
12/31/2007 10:14:21 PM
I got this all working.

Anthony


biganthony wrote:
>Baz,
>
>That was it - sometimes the simplest things are right in front of you. I also
>had the listbox as a Value List. I set it as a Table/Query and the results
>are displayed.
>
>Thanks
>Anthony
>
>>Surely this is all you need to get the results displayed in a list box?
>>
>[quoted text clipped - 6 lines]
>>> Thanks
>>> Anthony

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1

0
biganthony
1/1/2008 2:10:13 AM
Good!  Happy New Year!

"biganthony via AccessMonster.com" <u31673@uwe> wrote in message 
news:7d895a7857b70@uwe...
>I got this all working.
>
> Anthony
>
>
> biganthony wrote:
>>Baz,
>>
>>That was it - sometimes the simplest things are right in front of you. I 
>>also
>>had the listbox as a Value List. I set it as a Table/Query and the results
>>are displayed.
>>
>>Thanks
>>Anthony
>>
>>>Surely this is all you need to get the results displayed in a list box?
>>>
>>[quoted text clipped - 6 lines]
>>>> Thanks
>>>> Anthony
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1
> 


0
Baz
1/1/2008 10:20:32 AM
Reply:

Similar Artilces:

Output to console
I am programming a replacement on-screen keyboard. This is a win32 program that needs to send messages to other windows. We use SendInput to send the keyboard messages to the window with the focus. This works for normal windows. It does not, however, work for sending output to a console window. Does anyone know how I can send characters to a console window? Dave ...

acess form: how can I have more than 2 people modify it?
someone told me about the bcm v2 - what is that? I've only found it for outlook... it should make 5 people open and modify the form I've created in Access, is this right? I'd like to know how I can have a shared folder with an Access Form be used by several people at the same time, not just one. Is this possible? If someone could help me on this I'd greatly appreciate it. Never heard of bcm v2, but the rest of your question is fairly common. With an Access application you want to be available for multiple users, here is the correct way to deploy it. First, let me say D...

Default value for a field in the create form
Hi all, It's possible to define a default value for a field, in the create form ? Thanks, Hugo You can default a picklist value with out of the box customization but that's it. >-----Original Message----- >Hi all, > >It's possible to define a default value for a field, in the create form ? > >Thanks, >Hugo > > >. > ...

MS Access Copy record to another table based on a form input 10-08-07
Hello. I have 6 Tables, all with identical fields. I want to copy a record from one table into another table, which table depends on what table is selected in a form. So if the table names are One, Two, Three, Four, Five, Six, and the record that I want to copy and insert into another table has been selected by a query in a form. I want the form to direct where to insert the table. So if the form has table Two selected (from the drop down list) then it should insert the record into table Two. If the form has table Four selected then it should insert it into table Four. The table the record is...

Help with debugging query (Rolling Average)
Hi I'm trying to do a rolling average on data that is ordered by a date time column. I'm after adivce about how best to do this, I've tried this - SELECT Time, ValueX, (HOUR(Time) * 3600 + Minute(Time) * 60 + Second(Time)) As TickTock (SELECT Avg(ValueX) FROM My_Table WHERE TickTock Between (TickTock) AND (TickTock) + 30) AS RollingAverage FROM My_Table; Basically my thinking was that it would be easier to work on this datetime data as Seconds, So that my rolling average can be based on 10, at 3 second intervals When I try this I get nothing in my RollingAverage box and also a...

Help with query-dont want to see dupes
I've made a query to find customers that have ordered a certain product between a period of time. Some customers order a lot so they'll appear multiple times but I only want to show each customer once. The field to compare is ContactID On Tue, 10 Apr 2007 10:02:51 -0700, "Rob" <nospam@nospam.com> wrote: >I've made a query to find customers that have ordered a certain product >between a period of time. Some customers order a lot so they'll appear >multiple times but I only want to show each customer once. > >The field to compare is Contac...

Remove sort and filters programmatically on form
I have a form based on a 2 table query. User will use shortcut menu to filter records down to what they want to work on and sort accordingly. I have a button to remove all filters but I also want to remove any sorts that were applied and I can't seem to get that part to work. I use the following to remove all the filters. I just need help with the code to get the form back to the original underlying query sort. On_Click event of button control Me.Filter = "" FilterOn = False Like I said, this brings back all records but the sort stays. I am using Access 200...

OPenign an Excel sheet form Access
Hello everyone, I would like to open a shared excel spreadsheet on our server by using a button from Access. Can I use the runApp macro for this? If so what do I need to do...I have little experience with macros. Thanks. Add a button to your form add an onclick event and then enter the following code application.followhyperlink "c:\...\...\...\excel.xls" obviously replace the "c:\...\...\...\excel.xls" with the path and filename of the excel file you wish to open. -- Hope this helps, Daniel Pineault "Amanda" wrote: > Hello everyone, > &g...

How do I Excel formula =LEFT(A2,LEN(A2)-3) in an Access Query
I need to delete the last 12 characters (phone number) from a field that contains names and phone numbers. Lou Are you asking how to do this in MS Access? If so, take a look in Access HELP re: Mid() function and Len() function. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. ...

Countdown a Query Duration
I would like to have Access show the user the wait time left for a query to finish. Most queries I've ran into in the past were rather fast (1 or 2 secs in duration), however, my database now has some queries that can take up to 20 seconds to process. I want to have a pop-up of some sort that shows the wait time remaining. Is this even possible? I don't think the actual time is computed. For long running queries, Access displays a status bar in the Access window footer. "Mercadogs" <Mercadogs@discussions.microsoft.com> wrote in message news:1652E7D8-940F-4559...

Query Criteria 02-10-10
I have a query in which I only want the top 10 stores for item velocity for multiple groups. I have used the top value and max criteria but it only brings back the top or max for the entire dataset, not for each group within the data. What am I missing??? Post your table and field names with datatype that you would use in the query along with sample data (need not be real but workable). -- Build a little, test a little. "gabbyccf" wrote: > I have a query in which I only want the top 10 stores for item velocity for > multiple groups. I have used the top...

Import external database query not working with other users?
Help! I set up an import external database query (ms access) for an excel report used on our network that works fine for me, but will not refresh the data for anyone else. It does not appear to be due to any difference in user access or mapping. I set everything up through folders and files that all users have access to. I can't figure out why it only works for me. Is the query set up by the wizzard somehow tied to the user? Thanks, Dave. -- devbox ------------------------------------------------------------------------ devbox's Profile: http://www.excelforum.com/member.php?...

sub-form dependent combo box
To all: I have a subform that has two fields (comboboxes). The first is a list of services. The second is a list of consultants who provide those services. Through the posts on this forum and others (thanks much!) made the list of consultants dependent on the type of service offered. BUT ... Once I open up the form (with the subform in it), the first combobox works fine, but the second one asks me to "Enter Parameter Value" and then gives the Criteria that the second combobox uses to reference the first one. Any thoughts? Thanks in advance! Hi, It would help if you post ...

Quickbase Exact Form
I followed the tutorial for creating an exact form and connecting it to my Quickbase data. However, when I try to save the template to Quickbase, I get an "automation error, Library not registered." There isn't any information about how to correct the error. Does anyone know the solution? Off topic here. Suggest that you take a look at http://community.intuit.com/categories/contents/10298 -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my services on a paid professional basis. "NKHunter&qu...

Quirky Query
The query below is giving me fits. I am using vba to pull in data from access. The query below blows up when I use the variable plnt in the having clause. & "HAVING (((zbrdist.Plnt)='" & plnt & "' I have several other queries that have the same clause and no problems. Any ideas? Here is the full string strsql = "SELECT zbrdist.Plnt, Sum(zbrdist.[Value TCur]) AS [SumOfValue TCur], zbrdist.Material, Controllers.Name, Controllers.Telephone, zbrdist.[WBS Element], zbrdist.[Grouping WBS el], DistributedAnalyst.Analyst, DistributedAna...

Criteria in Query
I have a multiuser query that prompts the user for criteria (sorry I do not know the technical terms). Is there a way that I can set this up where the user can type in part of the criteria and it return with all fields that have that part in it? So for example: If the field is state. The criteria in the query has [Enter the State]. I want the user to be able to type Ala and it return Alaska and Alabama. Is there a wild card? I tried entering * after Ala, but that did not work. Can this be done? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-...

Help with expression builder in a Query to retrieve wanted dates
I have 4 columns/fields of dates in a table: Field A, Field B, Field C & Field D. These fields can be either filled with a date or left blank. ABCD are all "Dates". I want to design a query or report to retrieve dates from Field A. The criteria is: Field A must be filled (i.e. there is a date, not a blank); and when dates in any field of B or C or D are not blank, A must be more recent than this/those date(s). How do I write it? I made "A is not null" in the first criteria. And then I think I should write something like when B or C or D are not b...

How to know if a form is loaded?
I saw this in my textbook, but I can't remember where and I haven't been able to find it (the texbook has more than 1000 pages). When I'm not looking for it, it will be right there in front of me, I'm sure. Maybe somebody knows this off the top of their heads? In Access 2000 and later, test: CurrentProject.AllForms("Form1").IsLoaded In any version: SysCmd(acSysCmdGetObjectState, acform, "Form1") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than all...

Setfocus on subform on tabbed form
Hi Access Gurus, I have a tabbed main form which has 2 subforms on one of the tabs where the subforms have been made to look like one form. My problem is that the user needs to be able to tab through the first subform into the next seemlessly but I can't get the setfocus syntax for that right in this setup. The Mainform is "MainMenu" the TabControl is "TabCtl0" the Page is "Addr" & the Subform is BusAddr. Can anyone help with the syntax? My intention is to place it in the Lostfocus Event of the last field on the first subform. Cheers for...

Form
I have a continuous FORM with 16 categories and numerous result rows corresponding to each category. I would like to page break the FORM so that the data shows separately by category. How do I accomplish that. Thanks. ...

Customizing Category and Subcategory fields in Activities Forms
Hi, I am trying to customize MS CRM 1.2 for my company sales team. I am looking at the forms customization for creating a picklist(or dropdown listbox) for the feilds category and subcategory feilds in any of the new activities(E-mail, Phone, Fax etc). The Data Migration Framework says that Activities is not Customizable. The Schema Manager did not allow me to create new schema fields in Activities entity. But I am in dire need to customize those forms with picklists. Any means to accomplish that? Your thoughts on this would be appreciated Thanks Lux May be possible...check your email... Mi...

Adding a lookup list to further filter form results
Created a form to list outstanding invoices, the datasource is a query called "invoices due" When I open that form it list all my outstanding invoices, so far so good. Now I want to add a drop down list control on the form to further filter the returned results to show only specific customers. The form (and query) allready have and display the customer name, so that data is allready there. 3 things are stumping me. 1) I tried to add a list box pulling the data from the customer name in the query, but that returned multiple copies of each name in the list box. ...

mechanism for form activation
If I add a form the form should come from the standard library. Correct? I accessed it by going from the calendar view selected Tools | Forms | Design a Form which opens the Design Form dialog box with the Standard Forms Library highlighted. In the large window Appointment is displayed. I can open the form and it appears "normal" I can use it and save the results to the calendar. I can also select Tools | Forms | Choose a form and select the appointment form and it works. To me this means the form is healthy, sorry for that nursing term. What doesn't work is opening the appoint...

How to stop Access re-arranging query criteria onto multiple criteria lines for OR condition
Hi All I have a form(cFrmDashboard) with text boxes for the user to enter criteria and a list box which displays a query that uses the values entered in the text boxes to search my main table. In my search query for each field that I have provided a text box I enter the following criteria (for example": Like "*" & Forms![cFrmDashboard]![txtType] & "*" Or Forms! [cFrmDashboard]![txtType] Is Null I enter these all on the 1st "Criteria" line and this is easy to read and change. This works fine until I want to add another field to my search query...

Query a Line Graph
I have an x,y scatter chart with trend line plotted through. I want to know exactly what is on the y axis for any given point on the x axis. i.e. i can pop up a dialog box asking for x axis input, it then follows this x axis point up to the trend line then across to the y axis and displays the result. Is this possible? Lets assume you have a linear trendline. But rather than use a trendline we will use the TREND function. Suppose you x-values are in A2:A101 and the y-values in B2:B101. In D1 put the x-value whose y equivalent is needed, in E1 enter =TREND(B2:B101,A2:A101,D1). This retur...