Search form query

[Ehezve]

Hi everyone

I have created a very simple database that aims to capture all the correspondence we receive and provide the correspondence to the team through a search form. I have a Mail Record Table & a Mail Record Form which are my main datasources. My thought is to create a separate search form that the rest of the team uses to search for any correspondence using a wide range of fields. The search form is called Mail Record Search Form. I have run the following criteria in the Mail Record Query but it is not bringing up all the records when I run the query. This query is linked to the Search Command Button.
Like [Forms]![Mail Record Search Form].[DateReceived] & "*"

After struggling with this, I then tried another criteria in the Mail Record Query1 which is linked to the Run Query Command.
Like "*" & [Forms]![Mail Record Search Form]![Subject] & "*"
It is still not working properly.

I ended up using filters linked to a combo box in the header of the Mail Record Form. This seems to be working well.

Can someone please assist me to make this database to work? In a summary I need it;

1. for storing the record of correspondence
2. Linking the published correspondence to the sharepoint link
3. the end user to easily search for correspondence and retrieve it - Any suggestions on how best to achieve this are most welcome.
4. For it to be published onto sharepoint for easy access by users
5. In the long run to be able to task any actions to the officer responsible and them getting an email in regards to this action (This can be phase 2)

I have attached the database.

Thank you
Received & Send Mail Record1.accdb

[offie]

I just took a quick look and don't really understand what it is that you want... But what I can tell you is that you are making you life complicated by adding spaces in all of your field, query, and form names, because then you have you make sure you type it is [Word Word] or Word_Word depending on where you put it.
Also if you want to publish to sharepoint I would suggest using a navigation form.
To make viewing easier I would put the results query as a subform in the search form, or is that the brown one? I didn't really understand.
Also, for the Type, are you planning on typing it in for every record? it would be easier to make another table with all possible types and assign an easier value to it.
You might also want a way to get the user to add records, so you'll have to make a form for that, unless you are the only one who is going to put in data.

Start looking into those things, and if you can give me a simpler explanation of what you want to do I'll be happy to help.

Also Like [Forms]![Mail Record Search Form].[DateReceived] & "*" looks right, and I get an error with the code not the query

[Ehezve]

Thank you for the feedback. Sincerely appreciated.

I have started working on the issues you have mentioned. To further explain what the purpose of the database is.

1. A record of any correspondence received or sent will be enetred into the database by myself only - using the Mail Record Form
2. Everyone else in the team will access this database to view what we have received or sent. This will be via the Mail Record Search Form.
3. It is my intention that on opening the databse, the end user opens the search form automatically and allows them to search for a record they are after.
4. It is desired for the search results to display on the same page (if possible) e.g. as a list
5. The end user can then select a record of choice and opens it in a different form view.
6. This different form view can be very similar to Mail Record Form except with no functions to add a record as all they need to do is to view a record.
7. The user should be able to see the details of the record they have selected i.e. Type, Received from or send to, Date send or received, Subject, Actions, Correspondence and conditions.
8. After viewing this record, they can close the form and be taken to the search form to enable them to search for another record or close the form.

In addition to this, it would be good, to be able to assign the actions to team members. It is my intention to add a responsibility field after the action field. This will allow the database to show who has been assigned an action. It will also allow the system to send an email to the assigned team member notifying them that they have an action as a result of a correspondence received.

I hope its a bit clear now. In the meantime I have renamed all the fields in my forms so that there are no spaces. I am now looking into building a naviagtion form. Not really knowledgable but I will give it a try.

Any assistance will be sincerely appreciated.

Thank you

[offie]

You can do this with only 2 main forms a a couple subforms. The 2 main ones will be one where you enter data and the other where people view data.
The subforms will display the data (you make them like any regular form but then drage it into the main one).

You don't need to use navigation forms, you will only have one form for the viewers

Whenever you have a field in your main table that has information that repeat itself (ex: can only be a, b or c) make another table with only those values and an identification number/code, this way you are sure they are all the same and you won't misspell or miss anything

The main view form will be unbound, the subforms will be bound.
In the main view is where you will be searching
-First enter parameters to narrow results
-subform, in datatsheet view, shows keep information to then choose a single record (this one you dont have to make a form for, just make a query and drag that into the main form)
-Another search, using a definging factor (possibly ID) from the above subform
-Subform in form view (you will have to make it) that shows specific record

Set the forms and queries up, try to do the command buttons but I can help with the coding if you want (it's pretty simple though and just googling what you need will usually give you the answer)