Not Sure How To Do This

[Afrosheen via AccessMonster.com]

First of all thanks for reading this post. Everyone has been a great help
answering my questions.

Here's my situations. I have one form. The different element are:
1} A list box with information within.
2} A group box that has the months of the year.
3} Another group box that has the different departments I need.
4} A combo box showing the supervisors selected from the Department group box

Now, here's where it gets complicated. I think. When I select information
from each one of the elements and display them they display correctly. I
build the string from all the elements which after selecting the supervisor I
can see them.

Since this is set up to try and email from the selection, I believe I have to
build some kind of on the fly query that will email a report to that
supervisor showing all his employees and information from the list box and
the month box. I hope.

I'm using 3 tables: 2 of them have a common factor. Supervisor. The third one
and one of the others are common with the staffid. {Relationship}

I tried building the query by design and it seemed to work. I copied the sql
in to the form and came up with a bunch of errors.

I hope I've explained it clearly. Thanks again for your help. This one is a
challenge for me.

 

[Jack Leach]

I think that the SQL View of a query design cannot be Copy&Pasted to a
VBAable sql string without some minor modifications to the syntax.

If you post the string you are trying to base the report off of we should be
able to get it straightened around, especially if this already works in the
access query builder.

Another alternative may be to set up a saved parameter query.

I copied the sql in to the form and came up with a bunch of errors.

I'm not sure where you would put this in a form... it would need to be used
as the source for a record.

A little more info to go any further please.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Afrosheen via AccessMonster.com]

Thanks for your reply Jack. Here is the sql:

'SELECT tblTaps.[WorkPlan], tblMain.[Location], tblSupervisor.[Supervisor],
tblSupervisor. & _
'FROM (tblMain INNER JOIN tblSupervisor ON tblMain.Supervisor = tblSupervisor.
Supervisor) INNER JOIN tblTaps ON tblMain.StaffId = tblTaps.StaffId & _
'WHERE ((tblTaps.WorkPlan)= strcnt AND ((tblSupervisor.Supervisor)=cboloc))

You see, I need the operator to select different options then have the
program build a query based on those options and then either print a report
or send a report by email. I did the sql to see if it would build the query.

Thanks


[QUOTE="Jack"]
I think that the SQL View of a query design cannot be Copy&Pasted to a
VBAable sql string without some minor modifications to the syntax.

If you post the string you are trying to base the report off of we should be
able to get it straightened around, especially if this already works in the
access query builder.

Another alternative may be to set up a saved parameter query.
[QUOTE]
I copied the sql in to the form and came up with a bunch of errors.[/QUOTE]

I'm not sure where you would put this in a form... it would need to be used
as the source for a record.

A little more info to go any further please.
[QUOTE]
First of all thanks for reading this post. Everyone has been a great help
answering my questions.[/QUOTE]
[quoted text clipped - 23 lines][QUOTE]
I hope I've explained it clearly. Thanks again for your help. This one is a
challenge for me.[/QUOTE][/QUOTE]

 

[Afrosheen via AccessMonster.com]

Thanks again Jack for taking up this challenge. The end results is:

1} I have a list box with 5 items in it.
2} I have a group box with the months
3} I have another group box with the departments.
4} I have a combo box with supervisors picked from the department group box.

So really I'm working with 3 types of boxes #1, 2, & 4

The table fields I have is setup as:
Workplan
Workplan Late
IntDue
IntLate
FinalDue
FinalLate.

The list box has the above fields in it.

What I'm trying to do but don't know how is to:
1-Select the field from the list box
2-Select the month from the group box grpMonth
3-Select the supervisor from the combo box

Have it all come together to create a report so I can either email it or
print it.

I can build the string because I've seen it. I just don't know how to build
the query because it is an on the fly query.

I'm lost. Maybe it can't be done. Who knows. That's why I'm here and I
appreciate any help I can get.

Thanks again.

Thanks for your reply Jack. Here is the sql:

'SELECT tblTaps.[WorkPlan], tblMain.[Location], tblSupervisor.[Supervisor],
tblSupervisor. & _
'FROM (tblMain INNER JOIN tblSupervisor ON tblMain.Supervisor = tblSupervisor.
Supervisor) INNER JOIN tblTaps ON tblMain.StaffId = tblTaps.StaffId & _
'WHERE ((tblTaps.WorkPlan)= strcnt AND ((tblSupervisor.Supervisor)=cboloc))

You see, I need the operator to select different options then have the
program build a query based on those options and then either print a report
or send a report by email. I did the sql to see if it would build the query.

Thanks
[QUOTE]
I think that the SQL View of a query design cannot be Copy&Pasted to a
VBAable sql string without some minor modifications to the syntax.[/QUOTE]
[quoted text clipped - 17 lines][QUOTE][/QUOTE][/QUOTE]

 

[Jack Leach]

Sorry, didn't mean to ignore you. In fact I could have swore I posted
another reply to here yesterday. Anyway...

(Assuming this form is bound to a table or query)
Getting the information from the form to a report shouldn't really be a big
deal, but you should have a 'template report' made already to accept this.
The template report should be pretty straightforward. Basically, you make
the report with the same recordsource and fields as your form has. When you
are doing this the report will contain all of the records of the form, which
is not what you want your end result to be, but we're going to filter the
report when it prints in runtime from your form.

So you should have: An underlying table/query, a form for the user to edit
the info, and a report that has all of the records that the form does. And
you'll also need some way to have the user launch this report (presumably a
command button). This also assumes that you have a primary key field in the
records that we can use to identify which record to filter to.

At that point it becomes very easy... in the click event of the button, open
the report with a criteria statement that identifies the current form record:

Private Sub cmdRunReport_Click()
Dim strWhere As String

'Save the data
Me.Dirty = False

strWhere = "[IDField] = " & Me![IDField]

DoCmd.OpenReport "yourreport", , , strWhere

End Sub


This should filter open your report, showing only the record that the user
is currently on in the form, with the same data that they entered into the
form. No on the fly queries required. This assumes that your primary key
field is a number and is called IDField... you'll have to change that as
necessary.


Hopefully that will get you on the right track for getting the result you
want to a report. Past that you will need somebody elses help as I've never
worked with sending emails via Access. However, if you do get the report
running good and want some information on email it, I'm sure I can dig up a
couple of good resources for information on accomplishing that.


hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Thanks again Jack for taking up this challenge. The end results is:

1} I have a list box with 5 items in it.
2} I have a group box with the months
3} I have another group box with the departments.
4} I have a combo box with supervisors picked from the department group box.

So really I'm working with 3 types of boxes #1, 2, & 4

The table fields I have is setup as:
Workplan
Workplan Late
IntDue
IntLate
FinalDue
FinalLate.

The list box has the above fields in it.

What I'm trying to do but don't know how is to:
1-Select the field from the list box
2-Select the month from the group box grpMonth
3-Select the supervisor from the combo box

Have it all come together to create a report so I can either email it or
print it.

I can build the string because I've seen it. I just don't know how to build
the query because it is an on the fly query.

I'm lost. Maybe it can't be done. Who knows. That's why I'm here and I
appreciate any help I can get.

Thanks again.

Thanks for your reply Jack. Here is the sql:

'SELECT tblTaps.[WorkPlan], tblMain.[Location], tblSupervisor.[Supervisor],
tblSupervisor. & _
'FROM (tblMain INNER JOIN tblSupervisor ON tblMain.Supervisor = tblSupervisor.
Supervisor) INNER JOIN tblTaps ON tblMain.StaffId = tblTaps.StaffId & _
'WHERE ((tblTaps.WorkPlan)= strcnt AND ((tblSupervisor.Supervisor)=cboloc))

You see, I need the operator to select different options then have the
program build a query based on those options and then either print a report
or send a report by email. I did the sql to see if it would build the query.

Thanks
[QUOTE]
I think that the SQL View of a query design cannot be Copy&Pasted to a
VBAable sql string without some minor modifications to the syntax.[/QUOTE]
[quoted text clipped - 17 lines][QUOTE]
I hope I've explained it clearly. Thanks again for your help. This one is a
challenge for me.[/QUOTE][/QUOTE]
[/QUOTE]

 

[Jack Leach]

Though it may not be necessary in this case, building an 'on the fly query'
isn't impossible by any means. Consider a form with three controls: two
numbers and a string datatype for this example.

Private Sub SomeButton_Click()
Dim strSQL As String

Me.Dirty = False

strSQL = "SELECT * FROM YourTable " _
& "WHERE (numberfield1 = " & Me![numberfield1] & ") AND " _
& (numberfield2 = " & Me![numberfield2] & ") AND " _
& (stringfield = """ & Me![stringfield] & """)"

Debug.Print strSQL
End Sub

If you were to run this and the field values of the current record are 5, 10
and ThisString, respectively, you're Debug.Print statement should read:

"SELECT * FROM YourTable WHERE (numberfield1 = 5) AND (numberfield2 = 10)
AND (stringfield = "ThisString")


What you do with this 'dynamic sql string' from here is a different story.
In the past I've opened a report and passed an sql string like this as an
OpenArg (2002 or later for report openargs, I think), and in the OnOpen
statement set the form's recordsource property to that sql string:

Private Sub ReportName_Open(Cancel As Integer)
If Len(Me.OpenArgs) <> 0 Then
Me.Recordsource = Me.OpenArgs
End If
End Sub

Although my other post that utilizes the Where criteria on the
DoCmd.OpenReport method is a much cleaner way to do exactly the same thing.

This may or may not help you with the issue at hand, but hopefully will give
some insight on how to build an 'on the fly' sql.

Here's a few other notes on working with this type of situation:

Me![numberfield1] refers to the Field in the underlying table/query.
Me.NumberControl1 would refer to the Control on the form (though in most
cases this value should not differ from the field).

String datatypes need to be enclosed in their own quotes for use in an sql
string. VBA reads two quotes in a row as a single quote inside a string:

"stringfield = """ & Me.ctlStringControl & """"

would read as:

stringfield = "StringValueFromTheControl"


Action queries can be run this way after building the string by using either
the CurrentDb.Execute method, or the DoCmd.RunSQL method.

Complex queries should try and be avoided 'on the fly' like this. When you
have a saved query it gets processed faster due to the fact that access saves
the Jet optimization for it as well... with an 'on the fly' query this needs
to be rebuilt each time jet uses it. This requires no input from you, but if
you have a large complex query, it will be much faster if its saved.


hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Thanks for your reply Jack. Here is the sql:

'SELECT tblTaps.[WorkPlan], tblMain.[Location], tblSupervisor.[Supervisor],
tblSupervisor. & _
'FROM (tblMain INNER JOIN tblSupervisor ON tblMain.Supervisor = tblSupervisor.
Supervisor) INNER JOIN tblTaps ON tblMain.StaffId = tblTaps.StaffId & _
'WHERE ((tblTaps.WorkPlan)= strcnt AND ((tblSupervisor.Supervisor)=cboloc))

You see, I need the operator to select different options then have the
program build a query based on those options and then either print a report
or send a report by email. I did the sql to see if it would build the query.

Thanks


[QUOTE="Jack"]
I think that the SQL View of a query design cannot be Copy&Pasted to a
VBAable sql string without some minor modifications to the syntax.

If you post the string you are trying to base the report off of we should be
able to get it straightened around, especially if this already works in the
access query builder.

Another alternative may be to set up a saved parameter query.
[QUOTE]
I copied the sql in to the form and came up with a bunch of errors.[/QUOTE]

I'm not sure where you would put this in a form... it would need to be used
as the source for a record.

A little more info to go any further please.
[QUOTE]
First of all thanks for reading this post. Everyone has been a great help
answering my questions.[/QUOTE]
[quoted text clipped - 23 lines][QUOTE]
I hope I've explained it clearly. Thanks again for your help. This one is a
challenge for me.[/QUOTE][/QUOTE]
[/QUOTE]

 

[Afrosheen via AccessMonster.com]

Sorry Jack, I just need some clarification.

This is my information

strSQL = "SELECT * FROM tblTaps " _

& "WHERE (listbox = " & Me![listbox] & ") AND " _
& (month = " & Me![month] & ") AND " _
& (stringfield = """ & Me![supervisor] & """)"

Now as I understand it. When I select from the listbox "Work Plan" this is
the name of the field. This is where the listbox = "& me![listbox] and in
that field is the month that I would select from the grpMonth like "January".
The string field I understand. It's just getting the other two correct in my
mind.

Sorry about the frustration. Thanks for your help. I really appreciate it.

Though it may not be necessary in this case, building an 'on the fly query'
isn't impossible by any means. Consider a form with three controls: two
numbers and a string datatype for this example.

Private Sub SomeButton_Click()
Dim strSQL As String

Me.Dirty = False

strSQL = "SELECT * FROM YourTable " _
& "WHERE (numberfield1 = " & Me![numberfield1] & ") AND " _
& (numberfield2 = " & Me![numberfield2] & ") AND " _
& (stringfield = """ & Me![stringfield] & """)"

Debug.Print strSQL
End Sub

If you were to run this and the field values of the current record are 5, 10
and ThisString, respectively, you're Debug.Print statement should read:

"SELECT * FROM YourTable WHERE (numberfield1 = 5) AND (numberfield2 = 10)
AND (stringfield = "ThisString")

What you do with this 'dynamic sql string' from here is a different story.
In the past I've opened a report and passed an sql string like this as an
OpenArg (2002 or later for report openargs, I think), and in the OnOpen
statement set the form's recordsource property to that sql string:

Private Sub ReportName_Open(Cancel As Integer)
If Len(Me.OpenArgs) <> 0 Then
Me.Recordsource = Me.OpenArgs
End If
End Sub

Although my other post that utilizes the Where criteria on the
DoCmd.OpenReport method is a much cleaner way to do exactly the same thing.

This may or may not help you with the issue at hand, but hopefully will give
some insight on how to build an 'on the fly' sql.

Here's a few other notes on working with this type of situation:

Me![numberfield1] refers to the Field in the underlying table/query.
Me.NumberControl1 would refer to the Control on the form (though in most
cases this value should not differ from the field).

String datatypes need to be enclosed in their own quotes for use in an sql
string. VBA reads two quotes in a row as a single quote inside a string:

"stringfield = """ & Me.ctlStringControl & """"

would read as:

stringfield = "StringValueFromTheControl"

Action queries can be run this way after building the string by using either
the CurrentDb.Execute method, or the DoCmd.RunSQL method.

Complex queries should try and be avoided 'on the fly' like this. When you
have a saved query it gets processed faster due to the fact that access saves
the Jet optimization for it as well... with an 'on the fly' query this needs
to be rebuilt each time jet uses it. This requires no input from you, but if
you have a large complex query, it will be much faster if its saved.

hth

Thanks for your reply Jack. Here is the sql:

[quoted text clipped - 31 lines]

 

[Afrosheen via AccessMonster.com]

Hi again Jack, this is what I've tried and my explanation.


strSQL = "select from tbltaps" & "where sel1 =" & sel2 And (Me.Supervisor =
""" & sel5&""")

sel1 is the listbox field that I've selected. Ex: WorkPlan or any one of 5
other choices.
sel2 is the information in the field listbox selected from the grpMonth. Ex:
January
sel5 is the information in the supervisor field. Ex: Tom

What I'm getting is a error 13: Type mismatch.

Thanks.

PS: Once I get the report working then the email shouldn't be a problem
because it's based on the same information.

Sorry Jack, I just need some clarification.

This is my information

.strSQL = "SELECT * FROM tblTaps " _

& "WHERE (listbox = " & Me![listbox] & ") AND " _
& (month = " & Me![month] & ") AND " _
& (stringfield = """ & Me![supervisor] & """)"

Now as I understand it. When I select from the listbox "Work Plan" this is
the name of the field. This is where the listbox = "& me![listbox] and in
that field is the month that I would select from the grpMonth like "January".
The string field I understand. It's just getting the other two correct in my
mind.

Sorry about the frustration. Thanks for your help. I really appreciate it.

Though it may not be necessary in this case, building an 'on the fly query'
isn't impossible by any means. Consider a form with three controls: two

[quoted text clipped - 67 lines]

 

[Jack Leach]

strSQL = "select from tbltaps" & "where sel1 =" & sel2 And (Me.Supervisor =
""" & sel5&""")

?? If you debug.print this statement you would see something like

select from tbltapswhere sel1 = <and then more stuff that doesn't work>

Aside from some spacing and quoting issues, the WHERE clauses don't have the
correct elements.

The first value after WHERE needs to be a field from the table that you are
building the sql from, and the = <whatever> is the value that selects the
records. If I understand correctly, you are trying to tell it to select
where a control = another control, which will not work. You MUST include the
field names that correspond to the values of the controls, and they must be
on the right side of the "=" sign, with the value on the left.

So fix the spacing issue between tblTaps and Where and include the Field
names from tblTaps in the Where clause. I'm not sure what your field names
are (they might be in one of these posts, I haven't gone searching for them
though). And adding some capitalization and line breaks will make it a lot
easier to read. You should wind up with

strSQL = "SELECT * FROM tblTaps WHERE " _
& "([tblTaps].[fieldname1] = """ & Me.sel1 & """) AND " _
& "([tblTaps].[fieldname2] = """ & Me.sel2 & """) AND " _
& "([tblTaps].[fieldname3] = """ & Me.Supervisor & """)"

You need to also verify what datatypes are being stored in the list and
combo boxes. Generally when I use either type of control a string is
displayed but the bound column is a number, so make sure nothing's getting
confused there. You will have to refer to the bound column no matter what
the user is seeing in the control. The above SQL string assumes that all
bound column datatypes are strings. You will need to adjust accordingly.
Hopefully you can see how the field needs to be on the left, and the value to
match on the right.


If you Debug.Print the statements, you should see this if its all string
datatypes...

SELECT * FROM tblTaps WHERE ([tblTaps].[fieldname1] = "value1") AND
([tblTaps].[fieldname2] = "value2") AND ([tblTaps].[fieldname3] = "value3")


You should be able to, after setting a breakpoint and F8ing (stepping)
through the procedure, hover the mouse over the Me.sel1 and Me.sel2 and
Me.Supervisor lines and have their respective values show. This may help you
determine that the value you think is bound actually is.

hth


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Hi again Jack, this is what I've tried and my explanation.


strSQL = "select from tbltaps" & "where sel1 =" & sel2 And (Me.Supervisor =
""" & sel5&""")

sel1 is the listbox field that I've selected. Ex: WorkPlan or any one of 5
other choices.
sel2 is the information in the field listbox selected from the grpMonth. Ex:
January
sel5 is the information in the supervisor field. Ex: Tom

What I'm getting is a error 13: Type mismatch.

Thanks.

PS: Once I get the report working then the email shouldn't be a problem
because it's based on the same information.

Sorry Jack, I just need some clarification.

This is my information

.strSQL = "SELECT * FROM tblTaps " _

& "WHERE (listbox = " & Me![listbox] & ") AND " _
& (month = " & Me![month] & ") AND " _
& (stringfield = """ & Me![supervisor] & """)"

Now as I understand it. When I select from the listbox "Work Plan" this is
the name of the field. This is where the listbox = "& me![listbox] and in
that field is the month that I would select from the grpMonth like "January".
The string field I understand. It's just getting the other two correct in my
mind.

Sorry about the frustration. Thanks for your help. I really appreciate it.

Though it may not be necessary in this case, building an 'on the fly query'
isn't impossible by any means. Consider a form with three controls: two

[quoted text clipped - 67 lines]

I hope I've explained it clearly. Thanks again for your help. This one is a
challenge for me.

 

[Afrosheen via AccessMonster.com]

Morning Jack.

Here is what's happening. I took your suggestions and did some rewrite. Here
is the code.

strSQL = "SELECT * FROM tblTaps WHERE" _
& "([tblTaps].[workplan] = """ & Me.Combo149 & """) AND" _
& "([tblTaps].[Supervisor] = """ & Me.cboloc & """)"

The debug.Print showed the correct information and if i do a msgbox combo149
and cboloc they show up correctly.

The problem now happening is that when I do the report it prompts me for the
supervisor name. I've tried to "Pass" the parameter of cboloc and that's
where it prompts me,

DoCmd.OpenReport stDocName, acPreview, strSQL, "[supervisor] = cboloc", ,
List55

I've set my filters on in the report but is shows all information. Not the
"Workplan" for January. That would be the combo149

Thanks gain for your help. It's getting there.

strSQL = "select from tbltaps" & "where sel1 =" & sel2 And (Me.Supervisor =

""" & sel5&""")

?? If you debug.print this statement you would see something like

select from tbltapswhere sel1 = <and then more stuff that doesn't work>

Aside from some spacing and quoting issues, the WHERE clauses don't have the
correct elements.

The first value after WHERE needs to be a field from the table that you are
building the sql from, and the = <whatever> is the value that selects the
records. If I understand correctly, you are trying to tell it to select
where a control = another control, which will not work. You MUST include the
field names that correspond to the values of the controls, and they must be
on the right side of the "=" sign, with the value on the left.

So fix the spacing issue between tblTaps and Where and include the Field
names from tblTaps in the Where clause. I'm not sure what your field names
are (they might be in one of these posts, I haven't gone searching for them
though). And adding some capitalization and line breaks will make it a lot
easier to read. You should wind up with

strSQL = "SELECT * FROM tblTaps WHERE " _
& "([tblTaps].[fieldname1] = """ & Me.sel1 & """) AND " _
& "([tblTaps].[fieldname2] = """ & Me.sel2 & """) AND " _
& "([tblTaps].[fieldname3] = """ & Me.Supervisor & """)"

You need to also verify what datatypes are being stored in the list and
combo boxes. Generally when I use either type of control a string is
displayed but the bound column is a number, so make sure nothing's getting
confused there. You will have to refer to the bound column no matter what
the user is seeing in the control. The above SQL string assumes that all
bound column datatypes are strings. You will need to adjust accordingly.
Hopefully you can see how the field needs to be on the left, and the value to
match on the right.

If you Debug.Print the statements, you should see this if its all string
datatypes...

SELECT * FROM tblTaps WHERE ([tblTaps].[fieldname1] = "value1") AND
([tblTaps].[fieldname2] = "value2") AND ([tblTaps].[fieldname3] = "value3")

You should be able to, after setting a breakpoint and F8ing (stepping)
through the procedure, hover the mouse over the Me.sel1 and Me.sel2 and
Me.Supervisor lines and have their respective values show. This may help you
determine that the value you think is bound actually is.

hth

Hi again Jack, this is what I've tried and my explanation.

[quoted text clipped - 36 lines]

 

[Jack Leach]

Hello again.

Lets take a look at the arguments you are opening with...

DoCmd.OpenReport stDocName, acPreview, strSQL, "[supervisor] = cboloc", , List55

and the argument structure (for 2003 at least...)

DoCmd.OpenReport report, view, filter, where, window, openargs


Report: You have stDocName, which is hopefully a string that contains the
name of the report to open, so that should be right.

acPreview: Good to go there

Filter: you have your SQL string passing as a filter, which is not exactly
correct (see below). The way I was planning on doing this, the filter
argument would be left blank.

WhereCondition: "[supervisor] = cboloc" You have the control cboloc
enclosed in the parentheses. You will need something along the lines of
"[supervisor] = """ & Me.cboloc & """"
More on this one below as well, because the where condition will end up
being a bit more than that.

WindowMode is empty, which is fine

OpenArgs: I'm guessing List55 is a listbox? If it's a variable, you'll be
ok, but you can't pass a list box in this fashion. Also, what do you need
the openargs for? Is all the data required not in the underlying
recordsource of the report? Just curious... most cases only call for one of
the three methods (filters, where's or openargs) to accomplish the task of
opening the report to a specified record(s).



I've never really used filters, so I can't give much advice there. I
suspect that a filter merely filters the active recordsource (making it
easily changeable through the open instance of the form/report), where the
SQL sets the actual recordsource (which can probably then be further filtered
using a filter).

I do know that filters can be applied in a procedure by Me.FilterOn and
Me.FilterOff (or maybe it's just Me.Filter), but I don't know how the
form/report reacts when opened with a passed filter.



As far as a WhereCondition goes, assuming that the report has the underlying
recordsource as I described earlier (a 'template' report with all the fields
and records you ever need), you can take everything after the "WHERE" in your
SQL string and put it here. This should be the only argument you need in
order to have the report filtered to the record(s) intended.

Considering everything above, we should be able to take:

strSQL = "SELECT * FROM tblTaps WHERE" _
& "([tblTaps].[workplan] = """ & Me.Combo149 & """) AND" _
& "([tblTaps].[Supervisor] = """ & Me.cboloc & """)"

DoCmd.OpenReport stDocName, acPreview, strSQL, "[supervisor] = cboloc", , List55

and turn it into:

strSQL = "([tblTaps].[workplan] = """ & Mr.Combo149 & """) AND " _
& "([tblTaps].[Supervisor] = """ & Me.cboloc & """)"

DoCmd.OpenReport stDocName, acPreview, , strSQL

(if you were to change the variable name from strSQL to strWhere it would be
a little easier to follow at this point).

Let me know where this puts you.



As a side note, you may want to give some serious consideration to
developing a naming scheme and utilizing puncuation in code. It is amazing
how much easier it makes your life programming. It takes a little bit to
find one that works for you and adapt to it, but it makes everything
everything 100x easier. Take the extra five minutes to set the names of your
controls to reflect their purposes. (I have a small amount of trouble
deciphering your code due to this... not a big deal really, it certainly
would make it easier for me <grin> but more importantly, this code won't be
fresh in your mind for very long after you finish it, and when you have to go
back to it a few months down the road (and we always do) you'll be thoroughly
perplexed as to whats what). This is probably the most important aspect of
efficient development, and I can't stress it enough. Google "reddick's
naming conventions" for a start, and try to adapt at least half of it... it's
a very popular standard for vba across many countries.

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Morning Jack.

Here is what's happening. I took your suggestions and did some rewrite. Here
is the code.

strSQL = "SELECT * FROM tblTaps WHERE" _
& "([tblTaps].[workplan] = """ & Me.Combo149 & """) AND" _
& "([tblTaps].[Supervisor] = """ & Me.cboloc & """)"

The debug.Print showed the correct information and if i do a msgbox combo149
and cboloc they show up correctly.

The problem now happening is that when I do the report it prompts me for the
supervisor name. I've tried to "Pass" the parameter of cboloc and that's
where it prompts me,

DoCmd.OpenReport stDocName, acPreview, strSQL, "[supervisor] = cboloc", ,
List55

I've set my filters on in the report but is shows all information. Not the
"Workplan" for January. That would be the combo149

Thanks gain for your help. It's getting there.

strSQL = "select from tbltaps" & "where sel1 =" & sel2 And (Me.Supervisor =

""" & sel5&""")

?? If you debug.print this statement you would see something like

select from tbltapswhere sel1 = <and then more stuff that doesn't work>

Aside from some spacing and quoting issues, the WHERE clauses don't have the
correct elements.

The first value after WHERE needs to be a field from the table that you are
building the sql from, and the = <whatever> is the value that selects the
records. If I understand correctly, you are trying to tell it to select
where a control = another control, which will not work. You MUST include the
field names that correspond to the values of the controls, and they must be
on the right side of the "=" sign, with the value on the left.

So fix the spacing issue between tblTaps and Where and include the Field
names from tblTaps in the Where clause. I'm not sure what your field names
are (they might be in one of these posts, I haven't gone searching for them
though). And adding some capitalization and line breaks will make it a lot
easier to read. You should wind up with

strSQL = "SELECT * FROM tblTaps WHERE " _
& "([tblTaps].[fieldname1] = """ & Me.sel1 & """) AND " _
& "([tblTaps].[fieldname2] = """ & Me.sel2 & """) AND " _
& "([tblTaps].[fieldname3] = """ & Me.Supervisor & """)"

You need to also verify what datatypes are being stored in the list and
combo boxes. Generally when I use either type of control a string is
displayed but the bound column is a number, so make sure nothing's getting
confused there. You will have to refer to the bound column no matter what
the user is seeing in the control. The above SQL string assumes that all
bound column datatypes are strings. You will need to adjust accordingly.
Hopefully you can see how the field needs to be on the left, and the value to
match on the right.

If you Debug.Print the statements, you should see this if its all string
datatypes...

SELECT * FROM tblTaps WHERE ([tblTaps].[fieldname1] = "value1") AND
([tblTaps].[fieldname2] = "value2") AND ([tblTaps].[fieldname3] = "value3")

You should be able to, after setting a breakpoint and F8ing (stepping)
through the procedure, hover the mouse over the Me.sel1 and Me.sel2 and
Me.Supervisor lines and have their respective values show. This may help you
determine that the value you think is bound actually is.

hth

Hi again Jack, this is what I've tried and my explanation.

[quoted text clipped - 36 lines]

I hope I've explained it clearly. Thanks again for your help. This one is a
challenge for me.

 

[Afrosheen via AccessMonster.com]

Thanks Jack for the suggestions. I did put a break in the open report command
and hovered over the strSQL and all it said was: strSQL"" and that's it. What
that's telling me is that strSQL is not being passed. am I correct in this?

Hello again.

Lets take a look at the arguments you are opening with...

DoCmd.OpenReport stDocName, acPreview, strSQL, "[supervisor] = cboloc", , List55

and the argument structure (for 2003 at least...)

DoCmd.OpenReport report, view, filter, where, window, openargs

Report: You have stDocName, which is hopefully a string that contains the
name of the report to open, so that should be right.

acPreview: Good to go there

Filter: you have your SQL string passing as a filter, which is not exactly
correct (see below). The way I was planning on doing this, the filter
argument would be left blank.

WhereCondition: "[supervisor] = cboloc" You have the control cboloc
enclosed in the parentheses. You will need something along the lines of
"[supervisor] = """ & Me.cboloc & """"
More on this one below as well, because the where condition will end up
being a bit more than that.

WindowMode is empty, which is fine

OpenArgs: I'm guessing List55 is a listbox? If it's a variable, you'll be
ok, but you can't pass a list box in this fashion. Also, what do you need
the openargs for? Is all the data required not in the underlying
recordsource of the report? Just curious... most cases only call for one of
the three methods (filters, where's or openargs) to accomplish the task of
opening the report to a specified record(s).

I've never really used filters, so I can't give much advice there. I
suspect that a filter merely filters the active recordsource (making it
easily changeable through the open instance of the form/report), where the
SQL sets the actual recordsource (which can probably then be further filtered
using a filter).

I do know that filters can be applied in a procedure by Me.FilterOn and
Me.FilterOff (or maybe it's just Me.Filter), but I don't know how the
form/report reacts when opened with a passed filter.

As far as a WhereCondition goes, assuming that the report has the underlying
recordsource as I described earlier (a 'template' report with all the fields
and records you ever need), you can take everything after the "WHERE" in your
SQL string and put it here. This should be the only argument you need in
order to have the report filtered to the record(s) intended.

Considering everything above, we should be able to take:

strSQL = "SELECT * FROM tblTaps WHERE" _
& "([tblTaps].[workplan] = """ & Me.Combo149 & """) AND" _
& "([tblTaps].[Supervisor] = """ & Me.cboloc & """)"

DoCmd.OpenReport stDocName, acPreview, strSQL, "[supervisor] = cboloc", , List55

and turn it into:

strSQL = "([tblTaps].[workplan] = """ & Mr.Combo149 & """) AND " _
& "([tblTaps].[Supervisor] = """ & Me.cboloc & """)"

DoCmd.OpenReport stDocName, acPreview, , strSQL

(if you were to change the variable name from strSQL to strWhere it would be
a little easier to follow at this point).

Let me know where this puts you.

As a side note, you may want to give some serious consideration to
developing a naming scheme and utilizing puncuation in code. It is amazing
how much easier it makes your life programming. It takes a little bit to
find one that works for you and adapt to it, but it makes everything
everything 100x easier. Take the extra five minutes to set the names of your
controls to reflect their purposes. (I have a small amount of trouble
deciphering your code due to this... not a big deal really, it certainly
would make it easier for me <grin> but more importantly, this code won't be
fresh in your mind for very long after you finish it, and when you have to go
back to it a few months down the road (and we always do) you'll be thoroughly
perplexed as to whats what). This is probably the most important aspect of
efficient development, and I can't stress it enough. Google "reddick's
naming conventions" for a start, and try to adapt at least half of it... it's
a very popular standard for vba across many countries.

hth

Morning Jack.

[quoted text clipped - 75 lines]

 

[Jack Leach]

Yes, that is mostly correct. It will be passed, but doesn't seem to contain
a value. You'll have to find out why.

If you could copy & paste your entire sub for the event you are calling from
I'd like to take a look at it. Maybe seeing it in it's entirety will help...
there seems to be something else going on thats mucking up the works here.

Jack

 

[Afrosheen via AccessMonster.com]

Thanks for helping me out Jack. I've only been at Access for about 1 1/2 yrs.
There no one here that I can chat with so this board has been a tremendous
help.

Here's the code with the strSQL: I've set it up on a Command button The funny
thing is that I use basically the same routine in another program and it
works.


Private Sub cmdFilter_Click()
Dim strSQL As String
Me.Dirty = False

strSQL = "([tblTaps].[workplan] = """ & Me.Combo149 & """) AND " _
& "([tblTaps].[Supervisor] = """ & Me.cboloc & """)"


MsgBox strSQL
'MsgBox cboloc
Debug.Print strSQL

End Sub

---------------------------------------------------------------------
Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim stDocName, strSQL As String

stDocName = "rptTapsCover"

DoCmd.OpenReport stDocName, acPreview, strSQL, , , List55

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

The list55 is equal to a string. So it could be anything.

Once everything is working correctly then it's supposed to get the supervisor
and all subordinates who has a work plan scheduled for "January" The
subordinates are displayed on a subform from the main form.

When I test it through the actual query, it works. That's so weird.

 

[Jack Leach]

I don't see where you need a cmdFilter button... the sql string you build in
that procedure is not visible in the Print button's procedure, which is why
hovering over the mouse on strSQL in the Print sub is showing ""

Do you have an Option Explicit at the top of this module? That should be at
the top of every module, right there with the Option Compare Database
statement.

Anyway, I'd remove the filter button and leave the print button, and then
arrange your code in the cmdPrint_Click event like so:

'---------------------------------------------------------------------
Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim stDocName As String 'Report Name
Dim strWhere As String 'Where clause for opening the report

stDocName = "rptTapsCover"

strWhere = "([tblTaps].[workplan] = """ & Me.Combo149 & """) AND " _
& "([tblTaps].[Supervisor] = """ & Me.cboloc & """)"

DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdPrint_Click:
Exit Sub
Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click
End Sub
'---------------------------------------------------------------------



That should be all you need

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Thanks for helping me out Jack. I've only been at Access for about 1 1/2 yrs.
There no one here that I can chat with so this board has been a tremendous
help.

Here's the code with the strSQL: I've set it up on a Command button The funny
thing is that I use basically the same routine in another program and it
works.


Private Sub cmdFilter_Click()
Dim strSQL As String
Me.Dirty = False

strSQL = "([tblTaps].[workplan] = """ & Me.Combo149 & """) AND " _
& "([tblTaps].[Supervisor] = """ & Me.cboloc & """)"


MsgBox strSQL
'MsgBox cboloc
Debug.Print strSQL

End Sub

---------------------------------------------------------------------
Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim stDocName, strSQL As String

stDocName = "rptTapsCover"

DoCmd.OpenReport stDocName, acPreview, strSQL, , , List55

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

The list55 is equal to a string. So it could be anything.

Once everything is working correctly then it's supposed to get the supervisor
and all subordinates who has a work plan scheduled for "January" The
subordinates are displayed on a subform from the main form.

When I test it through the actual query, it works. That's so weird.

Yes, that is mostly correct. It will be passed, but doesn't seem to contain
a value. You'll have to find out why.

If you could copy & paste your entire sub for the event you are calling from
I'd like to take a look at it. Maybe seeing it in it's entirety will help...
there seems to be something else going on thats mucking up the works here.

Jack

 

[Afrosheen via AccessMonster.com]

Thanks again for replying. The filter/query is showing up on the strWhere.
Now the problem is I'm using 1 form and a sub form and it doesn't show any of
the information is showing up on the report. What the report is supposed to
do is have it display the supervisor name on the main form and those that
work for him/her that are due a "Work Plan" in a given month. That month
being the combo149.

After I click on the print button, it keeps asking me for the name of the
supervisor and the month. This is getting crazy. I've been trying to get this
done for the past couple of weeks. If I had any hair left it would be gone.

Thanks for your help. I really appreciate it. Sorry to keep you on it so long.

I don't see where you need a cmdFilter button... the sql string you build in
that procedure is not visible in the Print button's procedure, which is why
hovering over the mouse on strSQL in the Print sub is showing ""

Do you have an Option Explicit at the top of this module? That should be at
the top of every module, right there with the Option Compare Database
statement.

Anyway, I'd remove the filter button and leave the print button, and then
arrange your code in the cmdPrint_Click event like so:

'---------------------------------------------------------------------
Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim stDocName As String 'Report Name
Dim strWhere As String 'Where clause for opening the report

stDocName = "rptTapsCover"

strWhere = "([tblTaps].[workplan] = """ & Me.Combo149 & """) AND " _
& "([tblTaps].[Supervisor] = """ & Me.cboloc & """)"

DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdPrint_Click:
Exit Sub
Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click
End Sub
'---------------------------------------------------------------------

That should be all you need

Thanks for helping me out Jack. I've only been at Access for about 1 1/2 yrs.
There no one here that I can chat with so this board has been a tremendous

[quoted text clipped - 59 lines]

 

[Afrosheen via AccessMonster.com]

Just to let you know Jack. I think I've got the supervisor part problem
solved. Now when it display the information in the sub form instead of show
those for the month of January, it shows them all.

So that problem is still not solved.

Thanks again for replying. The filter/query is showing up on the strWhere.
Now the problem is I'm using 1 form and a sub form and it doesn't show any of
the information is showing up on the report. What the report is supposed to
do is have it display the supervisor name on the main form and those that
work for him/her that are due a "Work Plan" in a given month. That month
being the combo149.

After I click on the print button, it keeps asking me for the name of the
supervisor and the month. This is getting crazy. I've been trying to get this
done for the past couple of weeks. If I had any hair left it would be gone.

Thanks for your help. I really appreciate it. Sorry to keep you on it so long.

I don't see where you need a cmdFilter button... the sql string you build in
that procedure is not visible in the Print button's procedure, which is why

[quoted text clipped - 36 lines]

 

[Jack Leach]

Send me an email so I have your address and I'll reply within a few days with
a zipped mdb (2003) containing 2 tables, 2 forms, 2 reports, and some step by
step instructions on how to set up the relationships and subform controls so
access does all the work for you.

dymondjack at hotmail dot com

Otherwise I think you'll have to find some help elsewhere... we don't seem
to be making much progress here.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Just to let you know Jack. I think I've got the supervisor part problem
solved. Now when it display the information in the sub form instead of show
those for the month of January, it shows them all.

So that problem is still not solved.

Thanks again for replying. The filter/query is showing up on the strWhere.
Now the problem is I'm using 1 form and a sub form and it doesn't show any of
the information is showing up on the report. What the report is supposed to
do is have it display the supervisor name on the main form and those that
work for him/her that are due a "Work Plan" in a given month. That month
being the combo149.

After I click on the print button, it keeps asking me for the name of the
supervisor and the month. This is getting crazy. I've been trying to get this
done for the past couple of weeks. If I had any hair left it would be gone.

Thanks for your help. I really appreciate it. Sorry to keep you on it so long.

I don't see where you need a cmdFilter button... the sql string you build in
that procedure is not visible in the Print button's procedure, which is why

[quoted text clipped - 36 lines]

and hovered over the strSQL and all it said was: strSQL"" and that's it. What
that's telling me is that strSQL is not being passed. am I correct in this?