displaying Query results to subform

First--the disclaimer--I am in no way a programmer, but I am working
diligently to support my team in any way possible. With that said, I have
painstakingly created a simple build SQL string code in Access Visual Basic
Editor:

Private Sub Command8_Click()

Dim SQL As String

SQL = "SELECT * " & _
      "FROM [BCSG_CARDFILE] " & _
      "WHERE ((([BCSG_CARDFILE].[IMPORT_FILENAME]) Between " & _
      """" & _
      Forms![BCSG_CARDFILE_SEARCH]!cboDateFrom.Value & _
      """" & _
      " And " & _
      """" & _
      Forms![BCSG_CARDFILE_SEARCH]!cboDateTo.Value & _
      """" & _
      " And (([BCSG_CARDFILE].[EMPLOYER_GRP_NO])= " & _
      """" & _
      Forms![BCSG_CARDFILE_SEARCH]!cboGrpNo.Value & _
      """" & _
      ") " & _
      " And (([BCSG_CARDFILE].[CONT_CD])= " & _
      """" & _
      Forms![BCSG_CARDFILE_SEARCH]!cboContCd.Value & _
      """" & _
      " )) "

This basically creates the SQL query from the form I created. What I need to
do is display the results of this query in a subform below the form. I have
no clue where to go from here.

Any assistance/guidance would be greatly appreciated.

Thanx,

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

0
barefoot_traveler
6/25/2007 4:08:50 PM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
1173 Views

Similar Articles

[PageSpeed] 20

Barefoot,

Where you go, from where you are, depends on what you want to do.  What is 
it you want to do with the results of this query?  I've inserted a 
debug.print command at the end of the code below, to allow you to see, cut 
and paste, or do whatever else you might want to with the code.  Reality is 
that you probably want to make this RowSource for a form, listbox, or 
something else.  When you figure out what that is, post back and I can give 
you some help.

A couple of issues.  

1.  You look like you are comparing a field which contains a file name 
(IMPORT_FILENAME) against several date values.  This does not make any sense.

2.  Since the controls you are referring to are on the form that your code 
is in, you don't really need to put the complete form name 
(Forms![BCSG_CARDFILE]) in your reference to the controls.  I would replace 
that as shown below.

3.  When you are using dates (cboDateFrom, cboDateTo) in a query, you need 
to use the pound sign (#) to delimit them, not quotes, so that part of your 
qry might look like below.

4.  is your [Employer_GRP_No] field textual or numeric (I would expect that 
it is a long integer).  If it is numeric, you don't need to wrap that portion 
in quotes either.

5.  You need to change the names of the controls on your form.  Command8 
will mean nothing to you 6 months from now.  I prefer to add "txt_" as a 
prefix to my textboxes, "cbo_" as a prefix to all of my combo boxes, etc.  
You can google "VBA +naming +convention" to get more info on naming 
conventions. 

6.  When you are building your string in code, you don't have to wrap it in 
all of the parenthesis like Access does.  The exception to this is when you 
are joining multiple tables.

Private Sub cmd_DoSomething_Click

Dim strSQL as string

strSQL = "SELECT * " _
           & "FROM [BCSG_CARDFILE] " _
           & "WHERE me.[Import_FileName] BETWEEN #" & me.cboDateFrom & "# " _
                                                               & " AND #" & 
me.cboDateTo & "# " _
           & " AND me.[Employer_Grp_No] = " & me.cboGrpNo _
           & " AND me.[Cont_CD] = " & chr$(34) & me.cboCondCd & chr$(34)

debug.print strSQL
 
End Sub

HTH
Dale
-- 
Email address is not valid.
Please reply to newsgroup only.


"barefoot_traveler via AccessMonster.com" wrote:

> First--the disclaimer--I am in no way a programmer, but I am working
> diligently to support my team in any way possible. With that said, I have
> painstakingly created a simple build SQL string code in Access Visual Basic
> Editor:
> 
> Private Sub Command8_Click()
> 
> Dim SQL As String
> 
> SQL = "SELECT * " & _
>       "FROM [BCSG_CARDFILE] " & _
>       "WHERE ((([BCSG_CARDFILE].[IMPORT_FILENAME]) Between " & _
>       """" & _
>       Forms![BCSG_CARDFILE_SEARCH]!cboDateFrom.Value & _
>       """" & _
>       " And " & _
>       """" & _
>       Forms![BCSG_CARDFILE_SEARCH]!cboDateTo.Value & _
>       """" & _
>       " And (([BCSG_CARDFILE].[EMPLOYER_GRP_NO])= " & _
>       """" & _
>       Forms![BCSG_CARDFILE_SEARCH]!cboGrpNo.Value & _
>       """" & _
>       ") " & _
>       " And (([BCSG_CARDFILE].[CONT_CD])= " & _
>       """" & _
>       Forms![BCSG_CARDFILE_SEARCH]!cboContCd.Value & _
>       """" & _
>       " )) "
> 
> This basically creates the SQL query from the form I created. What I need to
> do is display the results of this query in a subform below the form. I have
> no clue where to go from here.
> 
> Any assistance/guidance would be greatly appreciated.
> 
> Thanx,
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1
> 
> 
0
Utf
6/25/2007 5:10:01 PM
screwed up the previous code example.  It should be:

Private Sub cmd_DoSomething_Click 

Dim strSQL as string 

strSQL = "SELECT * " _ 
& "FROM [BCSG_CARDFILE] " _ 
& "WHERE [Import_FileName] BETWEEN #" & me.cboDateFrom & "# " _ 
                                              & " AND #" & me.cboDateTo & "# 
" _ 
& " AND [Employer_Grp_No] = " & me.cboGrpNo _ 
& " AND [Cont_CD] = " & chr$(34) & me.cboCondCd & chr$(34) 

debug.print strSQL 

End Sub 


-- 
Email address is not valid.
Please reply to newsgroup only.


"barefoot_traveler via AccessMonster.com" wrote:

> First--the disclaimer--I am in no way a programmer, but I am working
> diligently to support my team in any way possible. With that said, I have
> painstakingly created a simple build SQL string code in Access Visual Basic
> Editor:
> 
> Private Sub Command8_Click()
> 
> Dim SQL As String
> 
> SQL = "SELECT * " & _
>       "FROM [BCSG_CARDFILE] " & _
>       "WHERE ((([BCSG_CARDFILE].[IMPORT_FILENAME]) Between " & _
>       """" & _
>       Forms![BCSG_CARDFILE_SEARCH]!cboDateFrom.Value & _
>       """" & _
>       " And " & _
>       """" & _
>       Forms![BCSG_CARDFILE_SEARCH]!cboDateTo.Value & _
>       """" & _
>       " And (([BCSG_CARDFILE].[EMPLOYER_GRP_NO])= " & _
>       """" & _
>       Forms![BCSG_CARDFILE_SEARCH]!cboGrpNo.Value & _
>       """" & _
>       ") " & _
>       " And (([BCSG_CARDFILE].[CONT_CD])= " & _
>       """" & _
>       Forms![BCSG_CARDFILE_SEARCH]!cboContCd.Value & _
>       """" & _
>       " )) "
> 
> This basically creates the SQL query from the form I created. What I need to
> do is display the results of this query in a subform below the form. I have
> no clue where to go from here.
> 
> Any assistance/guidance would be greatly appreciated.
> 
> Thanx,
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1
> 
> 
0
Utf
6/25/2007 5:17:01 PM
Dale,

Thank you for the input. I will go into the code and make the updates you
suggested. 

BTW, the "dates" aren't really date values, but numeric values listed as
'20070511'. No date formatting necessary to achieve the results of the query.

What I would like to do is view the data in a subform and probably create
another button to print the results if necessary. This is a request from a
team member and the requirements may change but the programming will be
essentially the same.

What I am missing is how to render the query results as a subform. I created
a MsgBox to ensure that the code is properly creating the SQL String but I
don't know how to run the query after it is created.

I tried the 

DoCmd.RunSQL

but I have received errors.

Dale Fye wrote:
>screwed up the previous code example.  It should be:
>
>Private Sub cmd_DoSomething_Click 
>
>Dim strSQL as string 
>
>strSQL = "SELECT * " _ 
>& "FROM [BCSG_CARDFILE] " _ 
>& "WHERE [Import_FileName] BETWEEN #" & me.cboDateFrom & "# " _ 
>                                              & " AND #" & me.cboDateTo & "# 
>" _ 
>& " AND [Employer_Grp_No] = " & me.cboGrpNo _ 
>& " AND [Cont_CD] = " & chr$(34) & me.cboCondCd & chr$(34) 
>
>debug.print strSQL 
>
>End Sub 
>
>> First--the disclaimer--I am in no way a programmer, but I am working
>> diligently to support my team in any way possible. With that said, I have
>[quoted text clipped - 33 lines]
>> 
>> Thanx,

-- 
Message posted via http://www.accessmonster.com

0
barefoot_traveler
6/25/2007 6:07:51 PM
Reply:

Similar Artilces:

RPC over HTTP/S on Exchange 2003
I have been configuring my single server with exchange to use RPC over https I have followed the instructions in MS guide and another simplified guide at http://www.petri.co.il/configure_rpc_over_https_on_a_single_server.htm Server spec is: Server 2003 standard SP1, Exchange 2003 SP1, XP client SP2 with outlook 2003 sp2 The bottom line is that when testing from the WAN, the outlook client will not connect and say that the exchange server is unavailable. I have a lot of experience configuring rpc over http/s with sbs2003 but this is the first time for server 2003 standard. I have outlook ...

Displaying MS Excel Chart control in ASP.NET Application
Hi, We are working on an ASP.NET (VB.NET) application. In one of th screens of our application, we need to show a graph and for this we ar using MS Excel Graph control. We have the graph prepared in Excel. Th application would feed in certain values into certain cells of th excel file and the graph would be drawn by the graph control based o these cells. We need some help on how exactly to display the excel graph contro directly on the screen of ASP.NET application. Thanks for the help oursm -- oursmp ...

Summing in A Query
Hello, I have a database which fuel records are stored in. The data is stored in two tables. The first records the daily logs that operators use each time they fuel up. It stores their name, the key they used (keylock fuel system - it's ancient) the unit number of the equipment using the fuel, and the amount of fuel they took. The second table stores the month end information retrieved from the key lock print out. It keeps a running total of the amount of fuel taken with each key, and the operator using that key. We have problems making sure all of the fuel is accounted for each mon...

Query fields
Is it possible to write a criteria where the value of an empty field is "0.00"? Background: I have three queries with different customer account groups. Not every salesperson has customer accounts in every accountgroup - so, he will not shown up in that query. But he has accounts included in another query. Now, I would like to get a sum of commission earned by each salesman calculated from all three queries together. Since the salesman has no record in one query the total sum of that specific salesman is not shown. Any idea how to solve that problem? Thanks Klaus On Wed, 29 A...

How to create an "and" rule in Query Based Distribution Groups
Hi, With Exchange 2003 Query Based Distribution groups, is it possible to create an "and" rule? ie, all users who are based in "London" "and" have the first name "John"? Thanks, Curtis. -- Please reply to news group only. Thank you. Sure. (&(attribute1=blah)(attribute2=blah)) http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp?frame=true -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Curtis Fray" <xxx@xxx.com> wrote in message news:OjVc...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

Query to hide duplicate records
I recall that this used to easy in previous versions (Unique values only ??), but in 2007, I can't get this to work at all. I have a table with our companie's job numbers in it. The job numbers show up multiple times because of different phases of the project: ProjNum ProjDescription 3077 Univ. of Vermont/UC/LEED 3077 Univ. of Vermont/University Commons: Building Fee 3077 Univ. of Vermont/University Commons: Excess Professiona... I need to jut have a single listing of each project number, otherwise, I get repeated records in the query that looks at this information (which ...

Simple Access counting queries
Hi, hoping someone can help a relative newbie with a pretty simple query. My database (Access 2007) has three tables: Customers Products Purchases (many-to-one links to both of the other tables, this is basically a linking table) I have two simple queries I'd like to get out of this database, but I'm a bit stuck on how to construct the SQL. Any direction you can give me would be helpful. 1. List of all customers who have purchased 2 or more products (or 3 or more products, or 4+, etc.) 2. List of all customers who have purchased both Product A and Product B (or A, B, and C, or B an...

Display Report Header/Footer
Can somebody please give me the telephone number of the smartass MS VP who came up with the bright idea for the Ribbon so that I can call her and have her tell me how to display the Report Header & Footer in a report? Obviously, I must be an idiot because I can't figure out how to do it with this simplied, intuitive, help-ya out m***** fu***** ribbon. Oh stupid me, its under 'ARRANGE'. How idiotic of me to think that it'd under 'DESIGN' since I'm 'designing' a report as opposed to 'arrangeing' a report. "microsoft"...

Subform question 04-09-10
I have a form (Form1) that contains a subform (Subform1). Within this subform I have a combo box which, depending on what is chosen, pops up another form (Popup1)for additional information. I need this additional information in the form that pops up to be 'linked' with the subform. The problem I am running into is that when the user enters information in Popup1, the table has not been populated witht he data that is in the subform so there is no record to 'link' to. What is the best way to force te esubform to pass its information to the table? Thanks i...

Problem with vba code to export query result in excel
Hi, I have a access report that exports to excel with click of a button after choosing parameters. This works well. However I have to modify couple of fields to utilize formula in the export module. I am not sure how to do this. I am writing the above code which seems to cause problem. I appreciate any help to resolve this issue. Thanks. Code: If lngColumn = 12 Then xlc.Offset(0, lngColumn).Value = =([UnitPrice]*[OriginalShippedQty])/1000 End If It seems the fields UnitPrice and OrigianalShippedQty are not being recognized here Jack wrote: >Hi, >I have a acces...

OWA Premium Client Not Displaying Correctly
Hello, I wonder whether anyone has seen this problem before because i would really appreciate any help on this one please. I have six Exchange 2003 servers in my organisation and there are two Exchange servers in three sites. Four of the Exchange servers are running Windows Server 2003 and Exchange Server 2003 [With Exchange SP1]. The front-end server is running Windows Server 2003 SP1 and Exchange Server 2003 SP2. Users with mailboxes on those four Back-End Exchange servers do not have any problems using OWA. The two Exchange servers on my site are using Windows Server 2003 SP1 with Exc...

Difficult query
Hi, I have a table called WT,contains the fields "Type of call","DateW" and "ID", this table is used to by users to add rows that determine type of calls received in a call center,I want to create a query with the following criteria: 1- To view number of calls received in each type per day. 2- To show the field "Type of call" in this query,even the type that wa not used,and to view number 0 in the count field. 3-Prcentage of each type of call . On Dec 11, 3:52 pm, Pietro <Pie...@discussions.microsoft.com> wrote: > Hi, > I have a tabl...

Report Query in RMS
Is there somewhere that I could get some information as to how the reports in RMS are composed. When you look at the various reports they all seem fairly simple in there basic structure. Are the any examples of how these reports are composed. The //--- Columns ---// iformation seems to be the same in most reports, same as the //--- Title Rows ---//. Setting up the flitters and the TablesQueried I need to understand. I have the schema for 1.2. How do I better understand the following or what do I need to learn. I wish I was in college again and had all the time in the world. //--- Repo...

VBA and SQL Query
hi, i use GP 8.0 and i want to make a custom form for a specific need. i cannot use Dexterity for this change so i really need to use VBA. however in the vba form i want to make a few sql select queries to pull client names, and call a table view, and eventually run a few update queries. is that possible to do with VBA in GP 8.0 ? Yes. You need to create an ADO connection and execute through that. Go to Customersource and search for ADO and VBA. Somewhere, I think in one of the demo links, is a file containing sample code that show you how to do this, including use of the retrievegl...

subform not refreshing
Hi there, I have a main form with a subform embedded to be able to scroll through a list of records. There is no linking field between the two. The subform displays all the records properly. One of the fields in the subform is a checkbox to 'close' the record indicating that the info is no longer current and shouldn't be displayed. This works fine. I should also mention that the subform is based on a query. I have a button on the main form which opens another modal form based on the same query as the subform that will let me uncheck the checkbox in case the record was 'clo...

pass through query variables
Hi Guys, I am looking for some help with a pass through query. I have a number of reports that allow users to select values from combo box's and want to know how I can pass this data in a pass through query. I have looked through and searched but seem to just get sites that I have to register to get some details, but do not wish to register elsewhere to get some info. If any has an example or links to any sites that would cover this I would appreciate it. An example of what I would want to pass is [forms]![frmReports].[txtsupplierID] [forms]![frmReports].[txtprod...

xsd.exe and the resulting cs classes.
I'm embarking on a c# based project in which I would like to make use of a lot of XML including database access and using hte xml and .net tools as a coding shortcut. What I'm trying to understand at the moment is how to use xsd.exe and result ..cs files to mange the xml. First question. My XSD includes <sequence>s which have > 1 value. the cs produced renders this as a single instance, though if I change this to an array (manually) the deserialization works as expected (nice!). How can I tell xsd it SHOULD be an array (I've tried setting maxOccurs, but this seems to...

MS Query
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C3998F.CE542560 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Currently I have a number of MS Queries connected to an Oracle DB = through the ODBC. We have had to change the user name and password for = accessing the database so I need to change the queries. How? The ODBC setup on the PC has been changed but each of the queries still = access via the original id, is there a way in the query editor to change = these details? --=20 Regards Dean=20 dkso@ntlworld.com...

display result of formula
How do I make a cell which contains a formula display the result of that calculation rather than the formula (it is formatted as "number" and I have tried "recalculating") Peter Peter Chadbund expressed precisely : > How do I make a cell which contains a formula display the result of that > calculation rather than the formula (it is formatted as "number" and I have > tried "recalculating") > Peter Change the cell format to 'General'. Redo the formula. Change the cell format to 'Number' and apply your display preferenc...

Display all messages?
Is there a way in Outlook 2002 to display all messages from all folders in one list? We have managed to end up with duplicate messages in multiple folders, and the best way to clear them out would be to see all messages together in one list sorted by subject or author. I can do that where I work with Lotus Notes, and I hope I can do that with Outlook, but I haven't found it. Thanks, Jack Jack B <jbruss@wi.rr.com> wrote: > Is there a way in Outlook 2002 to display all messages from all > folders in one list? We have managed to end up with duplicate > messages i...

How come an HTML image won't display?
The image in the following HTML paragraph will display a logo for most people her. But for one person, she just gets a red-x. This happens only very intermittently. She can usually see images OK. <p class=MsoNormal><font size=3 color="#1f497d" face="Times New Roman"><span style='font-size:12.0pt;color:#1F497D'><img width=275 height=92 id="Picture_x005f_x005f_x005f_x005f_x005f_x005f_x005f_x0020_1" src="cid:image001.jpg@01C99288.6B9B30B0" alt="KTB_usa_Megatron_logo_RGB"></span></font><span class...

Subforms won't refresh after new record 11-17-09
Access 2003, XP Pro I have a main form with two separate subforms. I view various records by clicking on some list boxes. To add a new record, I select Records, Data Entry. I then (1) Make a selection in a combo on the main form. (2) Make a selection in a combo and enter some text in a textbox on subform1 (3) Make a selection in a combo on subform2 Subform2 has some code on the After Update event that saves the new record and goes to a previously selected record in cbo1 on the main form (cbo2 has code on the After Update event to go to that record). Here's the cod...

Query Problems 07-20-07
Hi all,i am a new user of access,i am now using access 2007,i wanted to link the 3 tables together,to create a query that will returns records of company,part number,description,selling price and date of purchase.below are my tables in datasheet view; Customer details Products detail sales detail id id id Company part number company Address alternate part number Telephone model description ...

how do i query data from cobol to excel?
COBOL is a programming language--not a repository for data. If you're talking about looking at data that was created from a mainframe COBOL program, maybe just transferring the data from the mainframe to the PC, then importing it into excel (via file|open) would be enough??? barry wrote: -- Dave Peterson ...