By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,124 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Parameter query returning information in form view

P: n/a
Am something of a newbie at this, so please bear with any stupid
questions.

I have created a database to track shipments that we import. The
information is stored in a table and I have created a form which
displays the information from the table. The end users have access to
the form only - this is deliberate.

I have created some queries, including parameter queries, to aid them
which are accessible via buttons on the main form. These open in
datasheet view.

However, I want the parameter queries to return the information in the
main form. Essentially what I am trying to do is re-create the filter
by form effect but without using the built in filters.

Currently this query already exists but displays in datasheet mode,
which is quite useful as an overview, but it only shows 4 fields.
There are 42. Can this information be returned as a form, so the user
is merely presented with the first matching record but can scroll
through them using Pg Up / Dwn or using the arrows displayed by the
record nos at the bottom of the form view.

Alternatively, and perhaps preferably, is there a way in which the
user can glance down the worksheet produced by the query, see one they
want to edit, click into it and switch to form view so the information
is then returned showing all fields in the format to which they are
accustomed?

(Previously I worked with Lotus Approach and it was very easy to view
data in a report or datasheet, select one and switch back to form view
simply by clicking form tab - I've never found a way to do this in
Access)

Any help appreciated.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Simon Pleasants wrote:
Am something of a newbie at this, so please bear with any stupid
questions.

I have created a database to track shipments that we import. The
information is stored in a table and I have created a form which
displays the information from the table. The end users have access to
the form only - this is deliberate.

I have created some queries, including parameter queries, to aid them
which are accessible via buttons on the main form. These open in
datasheet view.

However, I want the parameter queries to return the information in the
main form. Essentially what I am trying to do is re-create the filter
by form effect but without using the built in filters.

Currently this query already exists but displays in datasheet mode,
which is quite useful as an overview, but it only shows 4 fields.
There are 42. Can this information be returned as a form, so the user
is merely presented with the first matching record but can scroll
through them using Pg Up / Dwn or using the arrows displayed by the
record nos at the bottom of the form view.

Alternatively, and perhaps preferably, is there a way in which the
user can glance down the worksheet produced by the query, see one they
want to edit, click into it and switch to form view so the information
is then returned showing all fields in the format to which they are
accustomed?

(Previously I worked with Lotus Approach and it was very easy to view
data in a report or datasheet, select one and switch back to form view
simply by clicking form tab - I've never found a way to do this in
Access)

Any help appreciated.


You've probably noticed you can't have headers/footers in datasheets.

You could create a main form (MF). Let's say you want to show all
records that are active or all records and also filter by code.

You now click on the datasheet form and make it a subform of MF. Let's
call it SF.

You could create a Checkbox (CH) for the active. And a combo (CB) for
the code list. In the afterupdate event for the checkbox and combobox
you could enter a command like
SetFilter
This will call the producture SetFilter

The SetFilter procedure could look like this
Private Sub SetFilter
Dim strFilter As STring
If CH Then strFilter = "[Active] = True And "
If Not Isnull(CB) Then
strFilter = strFilter & "[code] = " & Me.CB & " And "
Endif
'remove the word "And"
If strWHere > "" THen strWhere = Left(strWhere,Len(strWhere) -5)

'set the filter in the datasheet
Me.SF.Form.Filter = strWhere
Me.SF.Form.FilterOn = (strWhere > "")
End Sub

Note:*********
You refer to subforms with this syntax
Forms!MainForm!SubForm!ControlOnSubForm
or
Forms!MainForm!Subform.Form.(in this case Filter)
for methods/properties on a form.

Since you are in the mainform, you can simply use
Me!SubForm...whatever
**************

Open up the SF. Let's say you want to open SingleForm is the user
double clicks on the line. One of your fields is called ID.

Private Function GoToSingleRec()
'save record first if there has been a change in the datasheet
If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.ID) then
'show if exising rec
Docmd.OpenForm "SingleForm",,,"[Id] = " & Me.ID
Else
'add a new record
Docmd.OpenForm "SingleForm",,,,acAdd
Endif
End Function

Now click on all fields in SF. Open up the property sheet. Select the
Events tab. On the OnDblClick row enter
=GoToSingleRec()
now if you dbl-click on the fields, that function is called.


Nov 12 '05 #2

P: n/a
On Mon, 26 Apr 2004 17:44:44 GMT, Salad <oi*@vinegar.com> wrote:
You've probably noticed you can't have headers/footers in datasheets.

You could create a main form (MF). Let's say you want to show all
records that are active or all records and also filter by code.

You now click on the datasheet form and make it a subform of MF. Let's
call it SF.

You could create a Checkbox (CH) for the active. And a combo (CB) for
the code list. In the afterupdate event for the checkbox and combobox
you could enter a command like
SetFilter
This will call the producture SetFilter

The SetFilter procedure could look like this
Private Sub SetFilter
Dim strFilter As STring
If CH Then strFilter = "[Active] = True And "
If Not Isnull(CB) Then
strFilter = strFilter & "[code] = " & Me.CB & " And "
Endif
'remove the word "And"
If strWHere > "" THen strWhere = Left(strWhere,Len(strWhere) -5)

'set the filter in the datasheet
Me.SF.Form.Filter = strWhere
Me.SF.Form.FilterOn = (strWhere > "")
End Sub

Note:*********
You refer to subforms with this syntax
Forms!MainForm!SubForm!ControlOnSubForm
or
Forms!MainForm!Subform.Form.(in this case Filter)
for methods/properties on a form.

Since you are in the mainform, you can simply use
Me!SubForm...whatever
**************

Open up the SF. Let's say you want to open SingleForm is the user
double clicks on the line. One of your fields is called ID.

Private Function GoToSingleRec()
'save record first if there has been a change in the datasheet
If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.ID) then
'show if exising rec
Docmd.OpenForm "SingleForm",,,"[Id] = " & Me.ID
Else
'add a new record
Docmd.OpenForm "SingleForm",,,,acAdd
Endif
End Function

Now click on all fields in SF. Open up the property sheet. Select the
Events tab. On the OnDblClick row enter
=GoToSingleRec()
now if you dbl-click on the fields, that function is called.


Many thanks for this. Unfortunately, as I am quite new to this, most
of this went far over my head (anything that involves this sort of
scripting frequently does). I'll try to sit down and work my through
it when I am not going to get disturbed.

I also have a problem with a report I am trying to run. Perhaps you
can help with that? I have created a report, and customised it to the
enth degree, to display all active shipments grouped by arrival week
number and then ETA (date). This will then be faxed to the factory
who will make the various amendments which are required.

The problem is that the report works from the entire database and
therefore includes items which have already been delivered and are
therefore no longer relevent. It also, therefore, includes deliveries
from both factories and should.

I need to be able to narrow the report criteria down to ignore those
shipments which are completed (status field "delivered") and remove
reference to shipments from the other factory (order ref field
beginning "DM ").

I have read the help files but cannot work out how to limit the report
to this.

I'll post this second part as a new thread.
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.