Link to home
Start Free TrialLog in
Avatar of Samantha Deane
Samantha DeaneFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Problem creating enquiry log database

Hi all

My Access skills are Very rusty and I'm struggling to do what I know should be relatively simple.

I want to create an enquiries/tasks database for my team to use, and I'm basing it on the in-built Tasks database that comes with Access 2010.  That's fine as far as it goes, but I want to be able to customise it to record the customer who has made the enquiry (which will then be assigned as a task to a team member).

I want a facility whereby the person recording the enquiry can select a contact name.  All the contact details (name, email, organisation etc) are in linked tables from another Access database.  I have put together a query to pull all these details into one place, so I can see that the natural thing to do would be to have a combo box from which to choose the contact details.

I've managed to create the combo box, and managed to ensure the ID for the contact is stored against the enquiry so that we can pull back records in future of who has asked what etc.  What I want to be able to do, though, is to enable a search facility (via a parameter query on surname or similar), display all contacts that match the criteria, have the user select the person they want, then display the details of the selected contact on the enquiry form so that when users scroll through enquiries they can see who has asked what.

If it were up to me I'd store all this data in tables and have done with it, but I'm trying to pull this together for colleagues who have limited IT knowledge and so I want to make it as easy as possible to a) input the data and b) view it in future!

So to summarise:

- How do I enable users to query the list of all contacts (I could just have them scroll through, but there are over 2100)
- how do I display the (four column) results of the selection of a combo box in such a way as is meaningful for an end user?

Thanks in advance!
Avatar of mbizup
mbizup
Flag of Kazakhstan image

For querying the data, try creating a Filter Form.  There are plenty of sample databases out there with filter forms, including Helen Fedemma's "Fancy Filters" download:
http://www.helenfeddema.com/Access%20Archon.htm

Regarding displaying the other columns for the selection in a combo box with multiple columns, you can add textboxes adjacent to the combo box, and set their control source properties to the additional columns in the combobox.  Note that column index is zero-based, so for columns 2-4 in a 4 column combobox:

= Forms!MyFormName!MyComboName.Column(1)
= Forms!MyFormName!MyComboName.Column(2)
= Forms!MyFormName!MyComboName.Column(3)
- How do I enable users to query the list of all contacts (I could just have them scroll through, but there are over 2100)
- how do I display the (four column) results of the selection of a combo box in such a way as is meaningful for an end user?
1. What are they searching on?  If it is a name or a number, you would sort your combo' RowSource on this field and set the colums so that the search field would be the first visible field.  This is normal for a combo to hide the "key" field by setting it's width to 0.  If the search is more complex, you might be able to use cascading combos or you can go full blown search using the form recommended by mbizup.
2. The combo columns (except for the first) are only visible when the combo list is showing.  To get the fields to show on your form permanently, there are at least three different options.  My personal choice is to use  a query as the RowSource of the main form that joins to the contact table using a left join.  The left join is necessary just in case you don't initially have a contact.  If contact is required, then you can use an inner join.  Having the data in the form's recordSource allows you to simply bind controls to the fields.  As you change the selection from the combo, Access auto-magically populates the lookup values.  Using this method, it is best to set the Locked property of these controls to yes to prevent accidental updates.
FillFormFields.zip
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.