switchboard / forms question...

  • Thread starter Thread starter aaron
  • Start date Start date
A

aaron

Hi,
I have a database I'm working on..
It has a table AFStable

I have 2 queries made up that pull different things

What I want the user to only see is a switchboard
So as it is now
I have a switchboard with several buttons that allows them
to:

Add a new customer
Edit a Customer
Run a Report to view a certain caseload's records
Run a Report to view a certain casenumber's records

when the users click on say the first Run option, a window
pops up (via a macro) and asks them to enter a caseload #,
they do that.. then a report appears showing just that
caseload

same with the second Run option, a window pops up if they
click on it and this window asks them to enter a
casenumber... then a report appears showing that casenumber

well.... so bascially these users have 2 options (or
queries) that they can do on this table RUN1 and RUN2

they would like to be able to do a query that they would
like to define on the fly... that would give them more
flexibility than just querying by a casenumber or by a
caseload

is there a way to to this... by just giving them the
option to click on a switchboard button then be prompted
to enter what their query criteria is

(similiar to how I did the RUN1 and RUN2 mentioned above)

they want this to be as "dummy" proof as possible.
that's why i'd like them to only be able to use the
switchboard.. and have it prompt them for what they'd like
to see.

Any help would be greatly appreciated
Thanks :)
Aaron
(e-mail address removed)
 
It may be the simplest to use a parameter query as the record source for
the report. This is achived by using a bracketed message in the WHERE clause:

SELECT * FROM MyTable WHERE CaseNumber = [Please enter case number:]

If you need more flexibility, you can build SQL strings in VBA, then
either put them into record sources of your reports or put them into the
SQL properties of your stored queries, then use those stored queries for
your reports.
Options for defining and executing SQL on the fly are unlimited. I think
the most important thing is to design the user interface in the most
intuitive way so that the users don't have to take a course to know what
to enter in which text box. Creating SQL from the user entries in VBA is
nothing more than making sure the SQL contains no typos.
I noticed you said so far the DB uses macros. Don't be intimidated by
VBA (or offended if you're an expert in it) - if you can type in macros,
you can type in VBA code, too.
If you want an example of how to do this, show us a piece of your data structure.
Good luck,
Pavel
 
Hi,
thanks for your reply... i'm new to all this.. so some
of that makes sense.

i think that's how i did what i currently have... using
a "parameter" query...

here's code from just one of my 2 queries

SELECT [AFS table].worker_id, [AFS table].app_date, [AFS
table].case_type, [AFS table].case_number, [AFS
table].case_lastname, [AFS table].case_firstname, [AFS
table].disposition, [AFS table].program_type, [AFS
table].authorization_date, [app_date]+20 AS 20_days,
[app_date]+30 AS 30_days
FROM [AFS table]
WHERE ((([AFS table].case_number)=[Forms]!
[specific_case_number]![Text0]));



so.. how it works...
the user clicks on the Run Report for a specific case
number (on the switchboard)

a pop up form appears and asks for the case number
so they enter the casenumber into this form, and click ok
(clicking ok on this form popup window starts the macro to
set the value of the casenumber.. and it goes to the
query... as a parameter)

then a report is created from that query

does this make any sense?
sorry if not... let me know.. and i'll try to provide a
better explanation

my users want to be able to define their OWN queries..
not just the two "predefined" ones that I did
ie) by casenumber or by caseload

they just want to be able to click on something... (on the
switchboard) and then be prompted as to What would you
like to query on ? by name, casenumber, address,
caseload ID, etc.. etc....

this may not even be possible... but thought i'd check
with the pros :)

-Aaron
(e-mail address removed)
-----Original Message-----
It may be the simplest to use a parameter query as the record source for
the report. This is achived by using a bracketed message in the WHERE clause:

SELECT * FROM MyTable WHERE CaseNumber = [Please enter case number:]

If you need more flexibility, you can build SQL strings in VBA, then
either put them into record sources of your reports or put them into the
SQL properties of your stored queries, then use those stored queries for
your reports.
Options for defining and executing SQL on the fly are unlimited. I think
the most important thing is to design the user interface in the most
intuitive way so that the users don't have to take a course to know what
to enter in which text box. Creating SQL from the user entries in VBA is
nothing more than making sure the SQL contains no typos.
I noticed you said so far the DB uses macros. Don't be intimidated by
VBA (or offended if you're an expert in it) - if you can type in macros,
you can type in VBA code, too.
If you want an example of how to do this, show us a piece of your data structure.
Good luck,
Pavel
Hi,
I have a database I'm working on..
It has a table AFStable

I have 2 queries made up that pull different things

What I want the user to only see is a switchboard
So as it is now
I have a switchboard with several buttons that allows them
to:

Add a new customer
Edit a Customer
Run a Report to view a certain caseload's records
Run a Report to view a certain casenumber's records

when the users click on say the first Run option, a window
pops up (via a macro) and asks them to enter a caseload #,
they do that.. then a report appears showing just that
caseload

same with the second Run option, a window pops up if they
click on it and this window asks them to enter a
casenumber... then a report appears showing that casenumber

well.... so bascially these users have 2 options (or
queries) that they can do on this table RUN1 and RUN2

they would like to be able to do a query that they would
like to define on the fly... that would give them more
flexibility than just querying by a casenumber or by a
caseload

is there a way to to this... by just giving them the
option to click on a switchboard button then be prompted
to enter what their query criteria is

(similiar to how I did the RUN1 and RUN2 mentioned above)

they want this to be as "dummy" proof as possible.
that's why i'd like them to only be able to use the
switchboard.. and have it prompt them for what they'd like
to see.

Any help would be greatly appreciated
Thanks :)
Aaron
(e-mail address removed)
Click to expand...
.
Click to expand...
 
Aaron,
What you described makes perfect sense, and what you want to do is not dificult.
First, make a note of the query name that the report is based on. Say,
it is qryReportData, and report name is MyReport.
On your popup form, create a combo box named cbFieldList that will
select the Field List as the row source from Table [AFS table].
On the Events tab, select After Update event, select [Event procedure]
and click the [...] button to go to the VBA editor.
Paste into it:

Dim strWhere as String, fullSQLstring as String
Dim Qdef as DAO.Querydef. Db as DAO.Database
strWhere = " WHERE [AFS table]." & Me.cbFieldList " = '" & Me.Text0 & "'"
fullSQLstring = "SELECT [AFS table].worker_id, [AFS table].app_date,
[AFS " & _
"table].case_type, [AFS table].case_number, [AFS " & _
"table].case_lastname, [AFS table].case_firstname, [AFS " & _
"table].disposition, [AFS table].program_type, [AFS " & _
"table].authorization_date, [app_date]+20 AS 20_days, " & _
"[app_date]+30 AS 30_days" & _
"FROM [AFS table]" & strWhere
Set Db = CurrentDB()
Set Qdef = Db.QueryDefs("qryReportData")
Qdef.SQL = fullSQLstring
DoCmd.OpenReport, "MyReport"
Qdef.Close
Set Qdef = Nothing
Db.Close
Set Db = Nothing

This should do it. But please note that it will be up to you to make
sure that only text data fields are present in the data source. For
example, if they enter a date to search app_date field, the SQL will
fail, because dates need to be enclosed in ## instead of ''.
Cheers,
Pavel
Hi,
thanks for your reply... i'm new to all this.. so some
of that makes sense.

i think that's how i did what i currently have... using
a "parameter" query...

here's code from just one of my 2 queries

SELECT [AFS table].worker_id, [AFS table].app_date, [AFS
table].case_type, [AFS table].case_number, [AFS
table].case_lastname, [AFS table].case_firstname, [AFS
table].disposition, [AFS table].program_type, [AFS
table].authorization_date, [app_date]+20 AS 20_days,
[app_date]+30 AS 30_days
FROM [AFS table]
WHERE ((([AFS table].case_number)=[Forms]!
[specific_case_number]![Text0]));

so.. how it works...
the user clicks on the Run Report for a specific case
number (on the switchboard)

a pop up form appears and asks for the case number
so they enter the casenumber into this form, and click ok
(clicking ok on this form popup window starts the macro to
set the value of the casenumber.. and it goes to the
query... as a parameter)

then a report is created from that query

does this make any sense?
sorry if not... let me know.. and i'll try to provide a
better explanation

my users want to be able to define their OWN queries..
not just the two "predefined" ones that I did
ie) by casenumber or by caseload

they just want to be able to click on something... (on the
switchboard) and then be prompted as to What would you
like to query on ? by name, casenumber, address,
caseload ID, etc.. etc....

this may not even be possible... but thought i'd check
with the pros :)

-Aaron
(e-mail address removed)
-----Original Message-----
It may be the simplest to use a parameter query as the record source for
the report. This is achived by using a bracketed message in the WHERE clause:

SELECT * FROM MyTable WHERE CaseNumber = [Please enter case number:]

If you need more flexibility, you can build SQL strings in VBA, then
either put them into record sources of your reports or put them into the
SQL properties of your stored queries, then use those stored queries for
your reports.
Options for defining and executing SQL on the fly are unlimited. I think
the most important thing is to design the user interface in the most
intuitive way so that the users don't have to take a course to know what
to enter in which text box. Creating SQL from the user entries in VBA is
nothing more than making sure the SQL contains no typos.
I noticed you said so far the DB uses macros. Don't be intimidated by
VBA (or offended if you're an expert in it) - if you can type in macros,
you can type in VBA code, too.
If you want an example of how to do this, show us a piece of your data structure.
Good luck,
Pavel
Hi,
I have a database I'm working on..
It has a table AFStable

I have 2 queries made up that pull different things

What I want the user to only see is a switchboard
So as it is now
I have a switchboard with several buttons that allows them
to:

Add a new customer
Edit a Customer
Run a Report to view a certain caseload's records
Run a Report to view a certain casenumber's records

when the users click on say the first Run option, a window
pops up (via a macro) and asks them to enter a caseload #,
they do that.. then a report appears showing just that
caseload

same with the second Run option, a window pops up if they
click on it and this window asks them to enter a
casenumber... then a report appears showing that casenumber

well.... so bascially these users have 2 options (or
queries) that they can do on this table RUN1 and RUN2

they would like to be able to do a query that they would
like to define on the fly... that would give them more
flexibility than just querying by a casenumber or by a
caseload

is there a way to to this... by just giving them the
option to click on a switchboard button then be prompted
to enter what their query criteria is

(similiar to how I did the RUN1 and RUN2 mentioned above)

they want this to be as "dummy" proof as possible.
that's why i'd like them to only be able to use the
switchboard.. and have it prompt them for what they'd like
to see.

Any help would be greatly appreciated
Thanks :)
Aaron
(e-mail address removed)
Click to expand...
.
Click to expand...
Click to expand...
 
coooool
i'll mess with this and see what happens
thanks much for replies :)
-aaron
-----Original Message-----
Aaron,
What you described makes perfect sense, and what you want to do is not dificult.
First, make a note of the query name that the report is based on. Say,
it is qryReportData, and report name is MyReport.
On your popup form, create a combo box named cbFieldList that will
select the Field List as the row source from Table [AFS table].
On the Events tab, select After Update event, select [Event procedure]
and click the [...] button to go to the VBA editor.
Paste into it:

Dim strWhere as String, fullSQLstring as String
Dim Qdef as DAO.Querydef. Db as DAO.Database
strWhere = " WHERE [AFS table]." & Me.cbFieldList " = '" & Me.Text0 & "'"
fullSQLstring = "SELECT [AFS table].worker_id, [AFS table].app_date,
[AFS " & _
"table].case_type, [AFS table].case_number, [AFS " & _
"table].case_lastname, [AFS table].case_firstname, [AFS " & _
"table].disposition, [AFS table].program_type, [AFS " & _
"table].authorization_date, [app_date]+20 AS 20_days, " & _
"[app_date]+30 AS 30_days" & _
"FROM [AFS table]" & strWhere
Set Db = CurrentDB()
Set Qdef = Db.QueryDefs("qryReportData")
Qdef.SQL = fullSQLstring
DoCmd.OpenReport, "MyReport"
Qdef.Close
Set Qdef = Nothing
Db.Close
Set Db = Nothing

This should do it. But please note that it will be up to you to make
sure that only text data fields are present in the data source. For
example, if they enter a date to search app_date field, the SQL will
fail, because dates need to be enclosed in ## instead of ''.
Cheers,
Pavel
Hi,
thanks for your reply... i'm new to all this.. so some
of that makes sense.

i think that's how i did what i currently have... using
a "parameter" query...

here's code from just one of my 2 queries

SELECT [AFS table].worker_id, [AFS table].app_date, [AFS
table].case_type, [AFS table].case_number, [AFS
table].case_lastname, [AFS table].case_firstname, [AFS
table].disposition, [AFS table].program_type, [AFS
table].authorization_date, [app_date]+20 AS 20_days,
[app_date]+30 AS 30_days
FROM [AFS table]
WHERE ((([AFS table].case_number)=[Forms]!
[specific_case_number]![Text0]));

so.. how it works...
the user clicks on the Run Report for a specific case
number (on the switchboard)

a pop up form appears and asks for the case number
so they enter the casenumber into this form, and click ok
(clicking ok on this form popup window starts the macro to
set the value of the casenumber.. and it goes to the
query... as a parameter)

then a report is created from that query

does this make any sense?
sorry if not... let me know.. and i'll try to provide a
better explanation

my users want to be able to define their OWN queries..
not just the two "predefined" ones that I did
ie) by casenumber or by caseload

they just want to be able to click on something... (on the
switchboard) and then be prompted as to What would you
like to query on ? by name, casenumber, address,
caseload ID, etc.. etc....

this may not even be possible... but thought i'd check
with the pros :)

-Aaron
(e-mail address removed)
-----Original Message-----
It may be the simplest to use a parameter query as the record source for
the report. This is achived by using a bracketed
Click to expand...
message
in the WHERE clause:
SELECT * FROM MyTable WHERE CaseNumber = [Please enter case number:]

If you need more flexibility, you can build SQL strings in VBA, then
either put them into record sources of your reports or put them into the
SQL properties of your stored queries, then use those stored queries for
your reports.
Options for defining and executing SQL on the fly are unlimited. I think
the most important thing is to design the user
Click to expand...
interface
in the most
intuitive way so that the users don't have to take a course to know what
to enter in which text box. Creating SQL from the user entries in VBA is
nothing more than making sure the SQL contains no typos.
I noticed you said so far the DB uses macros. Don't be intimidated by
VBA (or offended if you're an expert in it) - if you
Click to expand...
can
type in macros,
you can type in VBA code, too.
If you want an example of how to do this, show us a
Click to expand...
piece
of your data structure.
Good luck,
Pavel

aaron wrote:

Hi,
I have a database I'm working on..
It has a table AFStable

I have 2 queries made up that pull different things

What I want the user to only see is a switchboard
So as it is now
I have a switchboard with several buttons that allows them
to:

Add a new customer
Edit a Customer
Run a Report to view a certain caseload's records
Run a Report to view a certain casenumber's records

when the users click on say the first Run option, a window
pops up (via a macro) and asks them to enter a
Click to expand...
caseload
#,
they do that.. then a report appears showing just that
caseload

same with the second Run option, a window pops up if they
click on it and this window asks them to enter a
casenumber... then a report appears showing that casenumber

well.... so bascially these users have 2 options (or
queries) that they can do on this table RUN1 and RUN2

they would like to be able to do a query that they would
like to define on the fly... that would give them more
flexibility than just querying by a casenumber or by a
caseload

is there a way to to this... by just giving them the
option to click on a switchboard button then be prompted
to enter what their query criteria is

(similiar to how I did the RUN1 and RUN2 mentioned above)

they want this to be as "dummy" proof as possible.
that's why i'd like them to only be able to use the
switchboard.. and have it prompt them for what they'd like
to see.

Any help would be greatly appreciated
Thanks :)
Aaron
(e-mail address removed)
.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top