displaying Query results to subform

  • Thread starter Thread starter barefoot_traveler via AccessMonster.com
  • Start date Start date
B

barefoot_traveler via AccessMonster.com

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,
 
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 said:
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,
Click to expand...
 
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
 
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 said:
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
Click to expand...
[quoted text clipped - 33 lines]
Thanx,
Click to expand...
Click to expand...
 
Back
Top