Parameter Query Dialog Box Msg

[tbrogdon]

I have a parameter query that takes 3 criteria and thus opens 3
dialog boxes in succession to get the data.

How can I change the message in the dialog box from Forms!
frmProdReview!txtProdDate to "What date would you like?"

Thanks,

Tim

 

[Ken Sheridan]

Tim:

You simply change the parameter in the criteria row of the relevant column
in query design view to:

[What date would you like?]

However it sounds like you already have a form, frmProdReview, which is
being referenced for the three parameters, so a far better way of opening the
query, or better still a report or form based on it, is to open the form,
enter the values in the controls and then open the query/form/report with a
button on the from.

BTW, as one of the fields at least seems to be of date/time data type its
best that the parameter be declared in the query as this data type.
Otherwise a date entered as the parameter value in short date format could be
interpreted as an arithmetical expression rather than a date. This will not
raise an error, but simply give the wrong result. The parameter can be
declared in design view or by adding the declaration to the start of the
query in SQL view:

PARAMETERS frmProdReview!txtProdDate DATETIME;
SELECT etc……..

Ken Sheridan
Stafford, England

 

[tbrogdon]

As always Ken you give me great info!

Thanks,

Tim

 

[John Spencer]

A better solution would be to open the form frmProdReview and fill in the
controls on the form. Then with the form open, execute the query (or
report)

Otherwise replace the entire parameter
[Forms]![ frmProdReview]![txtProdDate]
with
[What Date would you like?]
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[tbrogdon]

You simply change the parameter in the criteria row of the relevant column
in query design view to:

[What date would you like?]

However it sounds like you already have a form, frmProdReview, which is
being referenced for the three parameters, so a far better way of opening the
query, or better still a report or form based on it, is to open the form,
enter the values in the controls and then open the query/form/report with a
button on the from.

PARAMETERS frmProdReview!txtProdDate DATETIME;
SELECT etc........

Hi Ken,

I actually already have my query set up the way you suggest (i.e.,
opening the query from another form that contains the 3 criteria by
utilizing the PARAMETERS statement in my sql and that works great. It
does return the recordset in datasheet view. No problems. But I have 1
more question..

Is it possible to have the recordset returned in Continuous form view
instead OR is it possible to have the datasheet view returned and
somehow allow the user to select a record from the datasheet view and
open it in Single Form view?

My reason for this is that, given the number of fields (15) and the
potential for needing to edit the data later, I would like to present
the user with an aesthetically pleasing alternative to editing a
single record that won't fit on the screen.

I have been investigating potential solutions with multiple forms,
utilizing OpenArgs, etc. but frankly my head is spinning.

Thanks again,

Tim

 

[tbrogdon]

Hi John,

I actually tried this but got lost.

What I did was this:

I have another form frmProductionOperation that is a data entry form
that is identical to frmProdOpReview EXCEPT for the record source
which is qryProdOpReview. I created frmProdOpReview by copying/pasting
frmproductionOperation and changed the record source to
qryProdOpReview.

But when I open frmProdOpReview it opens the parameter dialog bozes in
succession rather than taking me straight to the form which also has
fields on it for the parameter input.

I've played with the On Open event to workaround but to no success.

I would love to do it the way you suggest.

Thanks,

Tim

 

[Ken Sheridan]

Tim:

Create a continuous view form based on the query (you can simply create an
autoform to save time, and then modify its design, or use the form wizard for
more control). Then from the button on your existing form open the form
rather than the query directly. If you haven't already done so make sure the
query is sorted on an appropriate column or columns so that the form presents
the records in a logical order as you navigate through them.

You could then create another form, this time in single form view, based on
the underlying table or set of tables in another query; this query doesn't
need any parameters or need to be sorted. You can then add a 'View Details'
button to the continuous form's footer or header to open the single view form
at the record currently selected in the continuous form. Lets say the record
can be identified by the value of a numeric column called MyID, the code for
the button's Click event procedure would be like this:

Dim strCriteria As String

strCriteria = "[MyID] = " & Me.[MyID]

' first ensure current record is saved
RunCommand acCmdSaveRecord
' open single form view in dialogue mode
' at currently selected record
DoCmd.OpenForm "YourSingleViewForm", _
WhereCondition:=strCriteria, _
WindowMode:=acDialog

By opening the single view form in dialogue mode the user can't set focus
back to the original form, or anything else for that matter, until they close
the single view form. This eliminates the possibility of their leaving it
open with unsaved changes to a record.

Ken Sheridan
Stafford, England

You simply change the parameter in the criteria row of the relevant column
in query design view to:

[What date would you like?]

However it sounds like you already have a form, frmProdReview, which is
being referenced for the three parameters, so a far better way of opening the
query, or better still a report or form based on it, is to open the form,
enter the values in the controls and then open the query/form/report with a
button on the from.

PARAMETERS frmProdReview!txtProdDate DATETIME;
SELECT etc........

Hi Ken,

I actually already have my query set up the way you suggest (i.e.,
opening the query from another form that contains the 3 criteria by
utilizing the PARAMETERS statement in my sql and that works great. It
does return the recordset in datasheet view. No problems. But I have 1
more question..

Is it possible to have the recordset returned in Continuous form view
instead OR is it possible to have the datasheet view returned and
somehow allow the user to select a record from the datasheet view and
open it in Single Form view?

My reason for this is that, given the number of fields (15) and the
potential for needing to edit the data later, I would like to present
the user with an aesthetically pleasing alternative to editing a
single record that won't fit on the screen.

I have been investigating potential solutions with multiple forms,
utilizing OpenArgs, etc. but frankly my head is spinning.

Thanks again,

Tim

 

[tbrogdon]

Create a continuous view form based on the query...........

Hi Ken,

I've tried it the way you suggested and I like it a lot. I've also
tried creating an unbound dialog box that accepts my 3 criteria and
when the user clicks a command button on this form it does
DoCmd.OpenQuery. This is the method I was trying to get away
from...however....now that you've introduced me to some of the finer
points of the Query Desgn interface I think it might actually be
advantageous to go that route. The design interface and the datasheet
view are much more flexible format-wise than I had originally thought.
I am stuck on one thing however.

I have three fields in tblProductionOperation that are all linked to
tblEmployees.EmployeeID (autonumber PK). The three fields are
SetupPerson, Operator1, and Operator2 (When I setup my relationships I
simply dragged an instance of EmployeeID onto each field in the other
table). When my query runs these three fields each return a numeric
value (EmployeeID). I would like to view the employee's name
concatenated rather than a number - and I am sure this is simple but I
can't figure it out.

I was looking at the Northwind Orders Query trying to figure out how
they concatenated the Employee names in that query to no avail. I
tried looking at all of the Properties pages I could find. I've read
the SQL behind the query, etc. I can't find anything that looks like
[Last Name]&", "&[First Name] which is how it appears they
accomplished this but I just can't find it. I've read that that last
expression could be used to define a new field but I do not see how
they accomplished that in the Northwind db. I've got the books out
but ................

Thank you again,

Tim

 

[Ken Sheridan]

Tim:

To return the names in a query you need to join the tblProductionOperation
to three instances of the tblEmployees table in the query. The joins are on
EmployeeID and SetupPerson, Operator1, and Operator2 respectively. You can
then return columns which show the names for each. Lets assume that the
employees table includes columns FirstName and LastName, and that the first
instance of the employees table you added to the query is joined on the
SetupPerson column, in query design view for the setup person you'd enter
this in the 'field' row of a blank column in the design grid:

[Setup Person]:[tblEmployees].[FirstName] & " " & [tblEmployees].[LastName]

When you add more than one instance of a table to a query Access gives each
instance a _1, _2 etc suffix, so assuming the second instance is joined on
Operator1 the expression for another computed column would be:

[Operator 1]:[tblEmployees_1].[FirstName] & " " & [tblEmployees_1].[LastName]

and for that joined onOperator2:

[Operator 2]:[tblEmployees_2].[FirstName] & " " & [tblEmployees_2].[LastName]

In a form you'd do it in a different way. Ther is no need to join the
tables in a query; the form would be based on the tblProductionOperation
table or a query based solely on that table, and in the form the controls for
the SetupPerson, Operator1, and Operator2 fields would be combo boxes. These
would be set up with properties like so:

RowSource: SELECT EmployeeID, FirstName & " " & LastName AS FullName
FROM tblEmployees ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units are measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box. You'll see the names ordered alphabetically by last
name then first name, but the values of the combo boxes will be the hidden
first columns.

Having said that, there is in fact a design flaw in your table. Having
multiple columns refereeing the employees table is what's known as 'encoding
data as column headings', whereas in a relational database data should only
be stored as values at column positions in tables; what's called the
'information principle'. What you have here is a many-to-many relationship
between tblProductionOperation and tblEmployees. The correct way to model
such a relationship type is by a third table. This would have two foreign
key, one referencing the primary key of tblProductionOperation and the other
the primary key of tblEmployees. It would also have a column, e.g.
EmmployeeRole which would contain values "Set up person" or "Operator" as
appropriate. Consequently ther would be three rows in this table for each
row in tblProductionOperation, assuming one set up person and two operators.
You could of course add more operators per row in tblProductionOperation if
you wished simply by inserting more rows into the table. Data input for
tables like this which model a many-to-many relationship is normally done via
a subform; the order details subform in Northwind is an example of this.

Whether you decide to change your design is you to you of course, but even
if you don't, be aware that it is faulty and could give rise to problems
querying the database at some stage; not insurmountable ones, but which could
require a bit of ingenuity to work around the flaw in the logical model.

Ken Sheridan
Stafford, England

Create a continuous view form based on the query...........

Hi Ken,

I've tried it the way you suggested and I like it a lot. I've also
tried creating an unbound dialog box that accepts my 3 criteria and
when the user clicks a command button on this form it does
DoCmd.OpenQuery. This is the method I was trying to get away
from...however....now that you've introduced me to some of the finer
points of the Query Desgn interface I think it might actually be
advantageous to go that route. The design interface and the datasheet
view are much more flexible format-wise than I had originally thought.
I am stuck on one thing however.

I have three fields in tblProductionOperation that are all linked to
tblEmployees.EmployeeID (autonumber PK). The three fields are
SetupPerson, Operator1, and Operator2 (When I setup my relationships I
simply dragged an instance of EmployeeID onto each field in the other
table). When my query runs these three fields each return a numeric
value (EmployeeID). I would like to view the employee's name
concatenated rather than a number - and I am sure this is simple but I
can't figure it out.

I was looking at the Northwind Orders Query trying to figure out how
they concatenated the Employee names in that query to no avail. I
tried looking at all of the Properties pages I could find. I've read
the SQL behind the query, etc. I can't find anything that looks like
[Last Name]&", "&[First Name] which is how it appears they
accomplished this but I just can't find it. I've read that that last
expression could be used to define a new field but I do not see how
they accomplished that in the Northwind db. I've got the books out
but ................

Thank you again,

Tim

 

[tbrogdon]

Hi Ken,

Firstly, thank you for answering my first question re: concatenating
the names. After I sent my last post, I did finally figure out
thatNorthwind also had a junction table where the concatenation
happened.

Secondly, I very much appreciate your input re: my design - it is very
important to me to have as robust a design as possible. So, thank you.
I am going to implement your suggestion and I realized while reading
your last post that may be yet another spot where I could apply the
same design feature.

Thank you again - your time and input is very welcome...

Tim

 

[chrisho.chris]

Hi Ken,

Firstly, thank you for answering my first question re: concatenating
the names. After I sent my last post, I did finally figure out
thatNorthwind also had a junction table where the concatenation
happened.

Secondly, I very much appreciate your input re: my design - it is very
important to me to have as robust a design as possible. So, thank you.
I am going to implement your suggestion and I realized while reading
your last post that may be yet another spot where I could apply the
same design feature.

Thank you again - your time and input is very welcome...

Tim




- Show quoted text -

Hi Tim,

My name is Chris. I believe I am having the similar issue as you, but
my access skills are not as advence as you. When I look through your
conversations with Ken, I am kind of lost.
I was wondering you can walk me through this process to create a
countious form and query return the original form.

I did created a form, but the query return is database table which not
what I wanted it. Please help me out.

Thanks,
Chris