New to Access and need a little help getting started

[Steven Davidson]

I'm playing around with Access 2007 and I'm starting it off with creating a
password database. I created two tables. One table is a client table that
has only two fields. The first field is an index field. The second field is
ClientName. The other table is a password table. This table has several
fields and a relationship to the client table. What I want to be able to do
is have a view that allows me to view the records and filter by client so
that I can see only those records which belong to the client I'm interested
in viewing. I figured that I'd have to create a report for that. For the
life of me, I cannot figure out how I'm going to get it so that I have a
combo box which lists all of the clients and then, when I select a client
from the list, the report would refresh and show only those records which
belong to that client. I tried to add a combobox to the report I created but
it didn't have a dropdown arrow and whatever I typed into it would disappear
as soon as I tabbed off the control. Is a report the way to do this?

 

[Philip Herlihy]

I'm playing around with Access 2007 and I'm starting it off with creating a
password database. I created two tables. One table is a client table that
has only two fields. The first field is an index field. The second field is
ClientName. The other table is a password table. This table has several
fields and a relationship to the client table. What I want to be able to do
is have a view that allows me to view the records and filter by client so
that I can see only those records which belong to the client I'm interested
in viewing. I figured that I'd have to create a report for that. For the
life of me, I cannot figure out how I'm going to get it so that I have a
combo box which lists all of the clients and then, when I select a client
from the list, the report would refresh and show only those records which
belong to that client. I tried to add a combobox to the report I created but
it didn't have a dropdown arrow and whatever I typed into it would disappear
as soon as I tabbed off the control. Is a report the way to do this?

I have something similar, but I'm using a form instead of a report.
Combo boxes only work in forms. (Access does allow "lookup" fields in
tables, but that's generally seen as a Bad Idea.) Reports are for
printing; forms are for on-screen stuff.

I've run out of time looking for a good reference for what you need to
do (pizza just arrived!) but you might find this useful:

http://files.lynda.com/files/marketing/microsoft/07_07_AC_sortfilter.mov

Phil, London

 

[John W. Vinson]

What I want to be able to do
is have a view that allows me to view the records and filter by client so
that I can see only those records which belong to the client I'm interested
in viewing.

Use a Form (not a report, reports are for printing, forms for viewing and
interacting with data) based on the Clients table, with a Subform based on the
password table; use the ClientID (or linking field) as the subform's
master/child link field. This will let you see each client's information in
conjunction with that client's name.

I would suggest using a few more fields in your clients table: LastName and
FirstName rather than storing both names in one field, probably static contact
information such as address, etc. can all go in the parent table.

 

[KenSheridan via AccessMonster.com]

To use a report put the combo box, cboClient in this example, in a separate
dialogue form and open the report filtered to the selected value. You can do
this either in the combo box's AfterUpdate event procedure, or in the Click
event procedure of a separate button on the form, e.g.

Const REPORTNAME = "YourReportNameGoeshere"
Dim strCriteria As String

' first close report if its already open
DoCmd.Close acReport, REPORTNAME

' set criterion for filtering report to selected client.
' if no client selected then all clients will be returned
If Not IsNull(me.cboClient) Then
strCriteria = "ClientID = " & Me.cboClient
End If

' open report in print preview
DoCmd.OpenReport REPORTNAME, _
View:=acViewPreview, _
WhereCondition:=strCriteria

In the above I've assumed that ClientID (or whatever your key is called) is a
number data type. If its text data type amend the code to:

strCriteria = "ClientID = """ & Me.cboClient & """"

The combo box would be set up as follows:

RowSource: SELECT ClientID, ClientName FROM Clients ORDER BY ClientName;

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

If your units of 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. Change the table and/or column names in the RowSource
property to your real ones as necessary.


If you just want to see the results on screen, however, and not have the
opportunity to print a report, then you can do it all in a single form.
Create a bound form in continuous form view, and this time put the combo box,
set up exactly as above, in the form header. Base the report on a query and
in query design view put the following in the 'criteria' row of the ClientID
column:

[Forms]![frmClients]![cboClient] Or [Forms]![frmClients]![cboClient] Is Null

Substituting your real form and combo box names as necessary in place of
frmClients and/or cboClient. You'll find that after saving the query Access
will move things around a little in design view, but it will work just the
same.

In form design view put the following in the cboClient control's AfterUpdate
event procedure:

Me.Requery

This requeries the form to show only the selected client's rows, or all rows
if no client is selected, so when you select a client in the combo box the
form will then redraw to show the rows for the selected client. If you
delete the selection in the combo box all rows will be returned.

If you are unfamiliar with entering code in event procedures, you first
select the control in form design view and open its properties sheet if its
not already open. Then select the relevant event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the event procedure with the first and last lines already in place.
Enter the lines of code between these two existing lines.

Ken Sheridan
Stafford, England

 

[Duane Hookom]

Why do you think you need to create a report to view records? You should be
able to use a continuous subform.

I would have a main form with the combo box (name it "cboClient").

Create a subform based on your password table. Add the subform to the main
form. Then set the Link properties on your subform control like:
Link Master: cboClient
Link Child: [related field with an unknown name]

 

[Philip Herlihy]

I have something similar, but I'm using a form instead of a report.
Combo boxes only work in forms. (Access does allow "lookup" fields in
tables, but that's generally seen as a Bad Idea.) Reports are for
printing; forms are for on-screen stuff.

I've run out of time looking for a good reference for what you need to
do (pizza just arrived!) but you might find this useful:

http://files.lynda.com/files/marketing/microsoft/07_07_AC_sortfilter.mov

Phil, London

Came back here to pick up my erratically incomplete answer, but I see
you've had considered replies from three of our most respected
contributors, so there's little I need add. I'll have to resist the
temptation to rush off an answer when there isn't time to be clear and
complete. Had I not paid attention to the pizza when it arrived I'd
quickly have found myself wearing it...

Phil

 

[Khusro]

Hi Steven

It is easy you just create another query with fields of your both
tables(combine both table in a query). Now go to the design view of this
query and on client name column at Criteria row define parameter by typing
[Enter Client Name] thats all. Save and exit.

Now open the query , a pop window will appear with message enter client name
, you just enter the name you wish and see the result.

You can use same query for your report to show desired record.