Parameter Query on a Form

  • Thread starter Thread starter KKash
  • Start date Start date
K

KKash

I want to put a Parameter Query on a form. I currently have users entering
data into a form that is being compiled into the tables. However, they will
need to go back throughout the course of the year and find one of the records
that they entered to enter additional information as it comes available. The
easiest way I can think of them to do this (need something as simple as
possible) is for them to click a box on a switchboard to "Update Existing
Records" when they click this a Parameter Query box pops up for them to enter
in the ID of the record they need. How do I set up the Parameter Query for
the Form? Thanks!
 
You don't use a parameter query for this. When you want to make an existing
record the current record, you navigate to the record. This is typically
done with a combo box control.

The ID you will be using should be the primary key field of the table or it
can be any field that is unique to the record. In most cases, the ID field
is an autonumber field that has no meaning to a human, so there is often some
descriptive text field that will make it clear to the user which record they
are selecting.

First you have to set up a row source for your combo. It should be a query
based on the table or query you are using as the record source for your form.
I will use a client table as an example, so you will need to modify the
names to suit your situation. The row source will look something like this:

SELECT tblClient.ClientID, tblClient.MainName FROM tblClient;

Now, there are some properties to be set so the user will see only the name
and not the number. Set these properties:

Column Count: 2
Bound Column: 1
Column Widths: 0";2"
The 0" makes the first column (ClientID) hidden and the 2" sets the
width for the second column (MainName) It can be whatever width necessary to
see the entire field.

Now, when the user types in or clicks on a name, you use the After Update
event of the combo box to move to that record.

Private Sub cboSearch_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub
 
Thanks for the info! Is there no way that I can make a pop up box open up to
ask for the ID number, so once that they enter in which ID they want it
automatically navigates to that record?

Klatuu said:
You don't use a parameter query for this. When you want to make an existing
record the current record, you navigate to the record. This is typically
done with a combo box control.

The ID you will be using should be the primary key field of the table or it
can be any field that is unique to the record. In most cases, the ID field
is an autonumber field that has no meaning to a human, so there is often some
descriptive text field that will make it clear to the user which record they
are selecting.

First you have to set up a row source for your combo. It should be a query
based on the table or query you are using as the record source for your form.
I will use a client table as an example, so you will need to modify the
names to suit your situation. The row source will look something like this:

SELECT tblClient.ClientID, tblClient.MainName FROM tblClient;

Now, there are some properties to be set so the user will see only the name
and not the number. Set these properties:

Column Count: 2
Bound Column: 1
Column Widths: 0";2"
The 0" makes the first column (ClientID) hidden and the 2" sets the
width for the second column (MainName) It can be whatever width necessary to
see the entire field.

Now, when the user types in or clicks on a name, you use the After Update
event of the combo box to move to that record.

Private Sub cboSearch_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

--
Dave Hargis, Microsoft Access MVP


KKash said:
I want to put a Parameter Query on a form. I currently have users entering
data into a form that is being compiled into the tables. However, they will
need to go back throughout the course of the year and find one of the records
that they entered to enter additional information as it comes available. The
easiest way I can think of them to do this (need something as simple as
possible) is for them to click a box on a switchboard to "Update Existing
Records" when they click this a Parameter Query box pops up for them to enter
in the ID of the record they need. How do I set up the Parameter Query for
the Form? Thanks!
Click to expand...
Click to expand...
 
Why? It only complicate your code.
You could have a separate form that only gets the ID to search for and
passes the selection back to the main form, but what is the point?
--
Dave Hargis, Microsoft Access MVP


KKash said:
Thanks for the info! Is there no way that I can make a pop up box open up to
ask for the ID number, so once that they enter in which ID they want it
automatically navigates to that record?

Klatuu said:
You don't use a parameter query for this. When you want to make an existing
record the current record, you navigate to the record. This is typically
done with a combo box control.

The ID you will be using should be the primary key field of the table or it
can be any field that is unique to the record. In most cases, the ID field
is an autonumber field that has no meaning to a human, so there is often some
descriptive text field that will make it clear to the user which record they
are selecting.

First you have to set up a row source for your combo. It should be a query
based on the table or query you are using as the record source for your form.
I will use a client table as an example, so you will need to modify the
names to suit your situation. The row source will look something like this:

SELECT tblClient.ClientID, tblClient.MainName FROM tblClient;

Now, there are some properties to be set so the user will see only the name
and not the number. Set these properties:

Column Count: 2
Bound Column: 1
Column Widths: 0";2"
The 0" makes the first column (ClientID) hidden and the 2" sets the
width for the second column (MainName) It can be whatever width necessary to
see the entire field.

Now, when the user types in or clicks on a name, you use the After Update
event of the combo box to move to that record.

Private Sub cboSearch_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

--
Dave Hargis, Microsoft Access MVP


KKash said:
I want to put a Parameter Query on a form. I currently have users entering
data into a form that is being compiled into the tables. However, they will
need to go back throughout the course of the year and find one of the records
that they entered to enter additional information as it comes available. The
easiest way I can think of them to do this (need something as simple as
possible) is for them to click a box on a switchboard to "Update Existing
Records" when they click this a Parameter Query box pops up for them to enter
in the ID of the record they need. How do I set up the Parameter Query for
the Form? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
I agree however, I have Thousands of records and a group of users that are
not proficient in Access utilizing the database. I need to have a simplified
way for them to return to a particular record without having to search
through all of them.

Klatuu said:
Why? It only complicate your code.
You could have a separate form that only gets the ID to search for and
passes the selection back to the main form, but what is the point?
--
Dave Hargis, Microsoft Access MVP


KKash said:
Thanks for the info! Is there no way that I can make a pop up box open up to
ask for the ID number, so once that they enter in which ID they want it
automatically navigates to that record?

Klatuu said:
You don't use a parameter query for this. When you want to make an existing
record the current record, you navigate to the record. This is typically
done with a combo box control.

The ID you will be using should be the primary key field of the table or it
can be any field that is unique to the record. In most cases, the ID field
is an autonumber field that has no meaning to a human, so there is often some
descriptive text field that will make it clear to the user which record they
are selecting.

First you have to set up a row source for your combo. It should be a query
based on the table or query you are using as the record source for your form.
I will use a client table as an example, so you will need to modify the
names to suit your situation. The row source will look something like this:

SELECT tblClient.ClientID, tblClient.MainName FROM tblClient;

Now, there are some properties to be set so the user will see only the name
and not the number. Set these properties:

Column Count: 2
Bound Column: 1
Column Widths: 0";2"
The 0" makes the first column (ClientID) hidden and the 2" sets the
width for the second column (MainName) It can be whatever width necessary to
see the entire field.

Now, when the user types in or clicks on a name, you use the After Update
event of the combo box to move to that record.

Private Sub cboSearch_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

--
Dave Hargis, Microsoft Access MVP


:

I want to put a Parameter Query on a form. I currently have users entering
data into a form that is being compiled into the tables. However, they will
need to go back throughout the course of the year and find one of the records
that they entered to enter additional information as it comes available. The
easiest way I can think of them to do this (need something as simple as
possible) is for them to click a box on a switchboard to "Update Existing
Records" when they click this a Parameter Query box pops up for them to enter
in the ID of the record they need. How do I set up the Parameter Query for
the Form? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
What I described is as simple as possible. It is easier on you and easy for
the users to understand. Users should not have to be proficient in Access to
use your application. If they do, your app needs some work.

If the users have to be profiecient in Access, it would be like having to
have a pilot's license to be a passenger on an airplane.
--
Dave Hargis, Microsoft Access MVP


KKash said:
I agree however, I have Thousands of records and a group of users that are
not proficient in Access utilizing the database. I need to have a simplified
way for them to return to a particular record without having to search
through all of them.

Klatuu said:
Why? It only complicate your code.
You could have a separate form that only gets the ID to search for and
passes the selection back to the main form, but what is the point?
--
Dave Hargis, Microsoft Access MVP


KKash said:
Thanks for the info! Is there no way that I can make a pop up box open up to
ask for the ID number, so once that they enter in which ID they want it
automatically navigates to that record?

:

You don't use a parameter query for this. When you want to make an existing
record the current record, you navigate to the record. This is typically
done with a combo box control.

The ID you will be using should be the primary key field of the table or it
can be any field that is unique to the record. In most cases, the ID field
is an autonumber field that has no meaning to a human, so there is often some
descriptive text field that will make it clear to the user which record they
are selecting.

First you have to set up a row source for your combo. It should be a query
based on the table or query you are using as the record source for your form.
I will use a client table as an example, so you will need to modify the
names to suit your situation. The row source will look something like this:

SELECT tblClient.ClientID, tblClient.MainName FROM tblClient;

Now, there are some properties to be set so the user will see only the name
and not the number. Set these properties:

Column Count: 2
Bound Column: 1
Column Widths: 0";2"
The 0" makes the first column (ClientID) hidden and the 2" sets the
width for the second column (MainName) It can be whatever width necessary to
see the entire field.

Now, when the user types in or clicks on a name, you use the After Update
event of the combo box to move to that record.

Private Sub cboSearch_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

--
Dave Hargis, Microsoft Access MVP


:

I want to put a Parameter Query on a form. I currently have users entering
data into a form that is being compiled into the tables. However, they will
need to go back throughout the course of the year and find one of the records
that they entered to enter additional information as it comes available. The
easiest way I can think of them to do this (need something as simple as
possible) is for them to click a box on a switchboard to "Update Existing
Records" when they click this a Parameter Query box pops up for them to enter
in the ID of the record they need. How do I set up the Parameter Query for
the Form? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
I totally agree. So are you saying that putting a Parameter Query on a form
is completely impossible?

Klatuu said:
What I described is as simple as possible. It is easier on you and easy for
the users to understand. Users should not have to be proficient in Access to
use your application. If they do, your app needs some work.

If the users have to be profiecient in Access, it would be like having to
have a pilot's license to be a passenger on an airplane.
--
Dave Hargis, Microsoft Access MVP


KKash said:
I agree however, I have Thousands of records and a group of users that are
not proficient in Access utilizing the database. I need to have a simplified
way for them to return to a particular record without having to search
through all of them.

Klatuu said:
Why? It only complicate your code.
You could have a separate form that only gets the ID to search for and
passes the selection back to the main form, but what is the point?
--
Dave Hargis, Microsoft Access MVP


:

Thanks for the info! Is there no way that I can make a pop up box open up to
ask for the ID number, so once that they enter in which ID they want it
automatically navigates to that record?

:

You don't use a parameter query for this. When you want to make an existing
record the current record, you navigate to the record. This is typically
done with a combo box control.

The ID you will be using should be the primary key field of the table or it
can be any field that is unique to the record. In most cases, the ID field
is an autonumber field that has no meaning to a human, so there is often some
descriptive text field that will make it clear to the user which record they
are selecting.

First you have to set up a row source for your combo. It should be a query
based on the table or query you are using as the record source for your form.
I will use a client table as an example, so you will need to modify the
names to suit your situation. The row source will look something like this:

SELECT tblClient.ClientID, tblClient.MainName FROM tblClient;

Now, there are some properties to be set so the user will see only the name
and not the number. Set these properties:

Column Count: 2
Bound Column: 1
Column Widths: 0";2"
The 0" makes the first column (ClientID) hidden and the 2" sets the
width for the second column (MainName) It can be whatever width necessary to
see the entire field.

Now, when the user types in or clicks on a name, you use the After Update
event of the combo box to move to that record.

Private Sub cboSearch_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

--
Dave Hargis, Microsoft Access MVP


:

I want to put a Parameter Query on a form. I currently have users entering
data into a form that is being compiled into the tables. However, they will
need to go back throughout the course of the year and find one of the records
that they entered to enter additional information as it comes available. The
easiest way I can think of them to do this (need something as simple as
possible) is for them to click a box on a switchboard to "Update Existing
Records" when they click this a Parameter Query box pops up for them to enter
in the ID of the record they need. How do I set up the Parameter Query for
the Form? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
There is no such thing as putting a parameter query on a form. If what you
are saying is that you want to open a query from a command button on the form
and let the parameter prompt pop up from there, you can do that, but that is
not a good way to do it.

When you open a query and let the user work directly in it, you are sure to
get bad data entered. You never allow users direct access to tables or
updatable queries. Users should never see anything but forms and reports.
--
Dave Hargis, Microsoft Access MVP


KKash said:
I totally agree. So are you saying that putting a Parameter Query on a form
is completely impossible?

Klatuu said:
What I described is as simple as possible. It is easier on you and easy for
the users to understand. Users should not have to be proficient in Access to
use your application. If they do, your app needs some work.

If the users have to be profiecient in Access, it would be like having to
have a pilot's license to be a passenger on an airplane.
--
Dave Hargis, Microsoft Access MVP


KKash said:
I agree however, I have Thousands of records and a group of users that are
not proficient in Access utilizing the database. I need to have a simplified
way for them to return to a particular record without having to search
through all of them.

:

Why? It only complicate your code.
You could have a separate form that only gets the ID to search for and
passes the selection back to the main form, but what is the point?
--
Dave Hargis, Microsoft Access MVP


:

Thanks for the info! Is there no way that I can make a pop up box open up to
ask for the ID number, so once that they enter in which ID they want it
automatically navigates to that record?

:

You don't use a parameter query for this. When you want to make an existing
record the current record, you navigate to the record. This is typically
done with a combo box control.

The ID you will be using should be the primary key field of the table or it
can be any field that is unique to the record. In most cases, the ID field
is an autonumber field that has no meaning to a human, so there is often some
descriptive text field that will make it clear to the user which record they
are selecting.

First you have to set up a row source for your combo. It should be a query
based on the table or query you are using as the record source for your form.
I will use a client table as an example, so you will need to modify the
names to suit your situation. The row source will look something like this:

SELECT tblClient.ClientID, tblClient.MainName FROM tblClient;

Now, there are some properties to be set so the user will see only the name
and not the number. Set these properties:

Column Count: 2
Bound Column: 1
Column Widths: 0";2"
The 0" makes the first column (ClientID) hidden and the 2" sets the
width for the second column (MainName) It can be whatever width necessary to
see the entire field.

Now, when the user types in or clicks on a name, you use the After Update
event of the combo box to move to that record.

Private Sub cboSearch_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

--
Dave Hargis, Microsoft Access MVP


:

I want to put a Parameter Query on a form. I currently have users entering
data into a form that is being compiled into the tables. However, they will
need to go back throughout the course of the year and find one of the records
that they entered to enter additional information as it comes available. The
easiest way I can think of them to do this (need something as simple as
possible) is for them to click a box on a switchboard to "Update Existing
Records" when they click this a Parameter Query box pops up for them to enter
in the ID of the record they need. How do I set up the Parameter Query for
the Form? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Klatuu said:
There is no such thing as putting a parameter query on a form. If what you
are saying is that you want to open a query from a command button on the form
and let the parameter prompt pop up from there, you can do that, but that is
not a good way to do it.

When you open a query and let the user work directly in it, you are sure to
get bad data entered. You never allow users direct access to tables or
updatable queries. Users should never see anything but forms and reports.
Click to expand...

I beg to differ. It may be more accurate to say that one can use a
parameter query as a recordsource of the form so in that sense it's
possible to do so, and it need not involve opening the query directly.

However, I think the OP may be better served by googling for 'Query by
Form' technique. One such example would be this:
http://support.microsoft.com/kb/209645

but this isn't the only way neither is it the best way of implementing
the same technique (e.g. null checks shouldn't really be used due to
performance ramifications). But as I said, googling will yield many more
results that offers different use of the same technique. This is more
general than using a combobox to select a record and can be used to
return a set of results which may be what the OP is looking for.
 
Thanks for the info! Is there no way that I can make a pop up box open up to
ask for the ID number, so once that they enter in which ID they want it
automatically navigates to that record?
Click to expand...

What Dave is saying is *IT'S EVEN EASIER* than that.

The user can select the ID (or type it, if they prefer) from an unbound Combo
Box on the form, and have the form automatically navigate to the record for
that ID.

Opening a popup box, or (worse) a query datasheet, is an unnecessary
complexity.
 
Banana,

The terminolgy I used is correct. A query is neither a property nor a
method of a form. It can be indentified in the form's recordsource property
as the record source. Additionally, you should reread what the OP was
asking. The answer I provided is correct.
 
Back
Top