You are here:

Using MS Access/Multiple Questions about Access DataBase :-D



I have built a database to collect news materials from around the Web using MS Access 2010. I'm pretty familiar with Access but have very little knowledge of coding.

I have a table with 10 columns on it, including title, date, type, and topic etc. I have a form to serve as my data entry interface for the table. There are 136 records so far. I have a query to search in any of the 10 fields and I have made the interface for the query a multi-field search form. I also have a third form which is serving as my switchboard.

When I run the search form, the results returned show up in a report. I also have a second report based on my only table to view all records.

I have a second query that searches for what I type in all fields and again the results show up in a separate report which makes the total 3 reports.
I have included a field for this query in the same multi-field search form I have.

So my DB consists of 1 Table, 3 Forms, 2 Queries, and 3 Reports.

My list of questions now :).

1. On all my reports I want to include a function such that when I press on a record in the Report I am taken to this record alone in form view such that I can edit it.

2.In my data entry form I have a combo box for the column "Type" such that I can for example choose newspaper or website etc. I want to make this combo box accept multiple entry. So when I get the drop down list and I choose something, and then I get the drop down list again and choose something else, it inserts a comma and adds it instead of replacing my initial choice.

3. On my Search Form I want the date search to be in the format of from/to. So I want two fields. How do I add this in my query and make it show up in my form ?

4. Lastly I think this is not possible but its worth asking. How can I search in the attachments of the records? Most attachments are MS word. Is it possible to search their text from within the database?

Thank you very much in advance. And sorry for the long list of questions. I greatly appreciate your time and help. :)

Kind regards,


ANSWER: 1. This is only possible in Report View. You can place buttons or other interactive controls on the report that will only be visible in Report View. I would recommend you use a form rather than a report. You can place the following in the ON click event of the form to open a form to the current record:

DoCmd.OpenForm "formname",,,"[keyfield] = " & Me.keycontrol

where keyfield is the name of your primary key and keycontrol the control on your form bound to the keyfield.

2. While this is possible, by setting the Multi-value property of the combo to Yes. I don't recommend it. This article talks about MVFs However, this is not the recommended way to track such data. Especially, if you want to filter or group records by type, using a MVF makes it more difficult. You have a many to many relationship here. One material can have multiple types and one type can apply to multiple materials. The way to model a many to many is to use a junction table:

MaterialID (FK)
TypeID (FK)

You should also have a lookup table for types to standardize input.

3.That depends on how you are managing the search. Generally you use a BETWEEN operator to define the date range.

4.No that's not possible. But what you can do is add a keywords table:
MaterialID (FK)

That will assign keywords to each material. You can then search on a keyword to find all articles associated with one.

Hope this helps,

---------- FOLLOW-UP ----------

QUESTION: Thank you very much for your clear and quick reply. I really appreciate it.
Points 3 and 4 are now clear to me.

However I want to ask about the first point. I realise that it's easier and better to do that on a form rather than a report. But my question is how do I create a form that has a similar look to a report and cannot be edited right away. So I want to make my View All report become a form that shows me all the records but that requires me to press on a certain record to go the form on which I can edit this record.

As for the second point it is still vague to me. Where I actually want to apply it is in the Topic field.
Right now what I'm doing is entering a topic followed by a comma followed by another topic followed by a comma etc. And so far it has showed me no problem in search since my query searches for any matching phrase in that field. What I want is to make a lookup table to standardise input into the field and then to be able to input different topics. And to do that I want a combo box rather than manual input field.
How exactly can I achieve this and why would it cause problems in searching ?

Thank you very much and apologies for the long essay :D

Kind Regards,


ANSWER: If you use Continuous Form mode you can make your form look more like a report. Showing multiple records, arranging controls etc. The only thing you can't really do is use grouping. You can Lock your controls so they don't allow editing. I'm not even sure you can do what you want, even in Report View, because I don't believe you can select the current record on a report so that Access knows what record to open.

The searching works, the way you have it now, probably because you are doing a "Like" search to find the phrase anywhere within the field. That works for searching, but it doesn't allow you to group by Topic. Again, what you have is a many to many relationship. So you create your lookup table of Topics similar to the keyword table I suggested above. Then you create a Junction table to model the many to many relationship. To add Topics, you would use a subform on your mainform bound to the Junction table and linked on the parent ID. Then have a combobox box to select a TopicID. You would be adding a record for each Topic associated with the parent record.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Brilliant !

Thank so much, your advice really helped me and made a difference. I really appreciate it :D

One last question. I have made a continuous form now and customised it in design view. I ve changed the field lengths and colours of the form as well as adding a few buttons. Now I want all my other continuous forms to have exactly the same look. Is there any way to cope the format and layout etc or do I have to customise each other form individually?

Thank you very much !

You can create your scheme as a Theme that can be applied to other forms. However, its not something I've worked with so I'm not sure of the best way to do it. You can research Themes for more info.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.