Access/SQL select view

[clive5]

I have a simple SQL 2000 database with an Access 2000 front end. There are 3 tables, CLIENT, CONSULTANT and WORK. The WORK table references Client_Name and Consultant_Name from the primary keys of the other tables. There is a WORK_INPUT FORM which displays all the WORK TABLE fields with Combo boxes for Client_Name and Consultant_Name. This all works fine for standard inputting of WORK data.

What I want to do is to give the user (using the Access 2000 front end) an option to select a Client_Name from a drop-down list and show (via the WORK_INPUT FORM) only those records in the WORK TABLE which contain that Client_Name - allowing the user to View/Add/Amend/Delete records for that Client_Name.

Can you suggest the easiest way to do this ?

Thanks, Clive.

 

[FuzzyBear9]

Hi,

If I understand your request, it is quite simple. Go into design view on your WORK_INPUT FORM. Cilck on the toolbox icon. Make sure that the 'control wizards' icon is selected (this is the magic wand icon in the top-right corner of the toolbox menu).

Select the Combo box icon, this will bring up the Combo box wizard. Select the third option 'Find a record on my form based on the value selected in my combo box'. Click Next. Access then asks you for the table or query that you wish to use. Select WORK TABLE (as you refer to it above). Click Next. Now Access asks you for the field which you want to search your records by. Select Client_Name. Click Next.

You should now see a preview of all the client names. (If Access displays numbers instead of names, you will need to modify the SQL statement, you can do this after you have created the combo box). Click Next. Access then allows you to opt for remembering the value for later use or storing the value in a field. Select the default 'Remember the value for later use.' Click Next. You will be prompted to name the combo box, ('Search' perhaps?). Now click on Finish.

You should now be able to View/Add/Amend/Delete records based on the Client name (Client_Name) you selected in the combo box.

Hope this helps you.

 

[clive5]

Thanks Fuzzy,
I've got to the point where I can select the ClientName in the ComboBox but the wizard did not ask me if I wanted to store the value of the selected clientname for later use so I can still see the records for all clientnames. Is it the SQL database that's causing the problem?

Clive.

 

[FuzzyBear9]

My experience is limited to Access. What choices (if any) does the wizard offer you?

 

[clive5]

I don't get any choices at all; I select the "Find a record on my form based on the value.....", then I select the field, then it asks me to label the Combo Box and select Finish (the Next button is greyed out).

Clive.

 

[FuzzyBear9]

Have you tried to use the combo box? What happens when you try to select a record from it? Does it display the correct record?

 

[clive5]

Yes, the Combo Box takes me to the first record with the selected ClientName but after that I can navigate all the other records from the form regardless of ClientName.

Clive.

 

[FuzzyBear9]

Hi Clive,

I understand now what you want to do. Try this, it is not perfect . The easiest way to filter for selected is to create a command button that allows the user to 'filter by selection'. (You will still need to use the combo box you have just created).

Use the toobox wizard again (making sure that the 'magic wand' is selected) and click on the 'command button' icon.

Select 'form operations' from the 'Categories' list and select 'Apply Form Filter' from the 'Actions' list. Click Next.

Select the 'filter by selection' picture for your button (or create a name that the button should display eg. filter). Click Next. Now give the button a label (eg. frmInputFilter) Click finish.

Place the button next to the Client_Name field. Click form view. You should be able to go to a record using the Combo box. Once you have selected the appropriate client, click in the Client_Name field and press the new filter button. Access will now display only those records that match the selected client.