user to choose report, search for record using any field...

[misscrf]

Ok. I don't know if I am asking the wrong question to get the right answer, so please bear with me. I have a database, which I believe is in 3rd normal form. I have the forms for entry set up, and now I have the first report.

This is a hiring database, which tracks applicants, their applications, attaches to a job listing if they applied in reference to one, and all activities that pertain to each application ( calls, emails, interviews).

I have the report set up perfectly for a candidate report.

Now I want to add a way for the person using the app to be able to choose the candidate they want to see a report for. Chances are that they will only want to see one at a time.

My thought is to make a search form, have the user select the record and tie the pk parameter from the report to the form.

If you think there is a better way, I would love any ideas.

If that sounds like a good method, can someone help me with how I do it? lmao. I have the concept, but the search form and select is my fear. I have used a search form I found online once, but there is a limitation to it. It searches one table. I need to be able to have it search any field my user wants.

I have been told that a boss might say " hey can you pull up a report on that candidate that when to Brown university?" This means that they would need to search on the school field, which is in the tblcandidatetypeinfo table.

I wonder if a query might work, and I will probably try it out, but in the mean time, I thought I would throw this question out there, to see if anyone might have different thoughts. Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[HitechUser]

misscrf,

If I understand you correctly...

There is a simple (and somewhat good) example of what you need in the sample Northwind database (it has been a while..). I believe from the Nortwind main menu select reports (remember been a while). This should bring up a dialog with serveral options. One option is to select one or more products or leave blank for all. It has both print and print preview option.

Import this form into your database and then convert away. I have used this in the past and it fairly easy to modify.

I hope this helps.

Good Luck...

 

[HitechUser]

misscrf,

If I understand you correctly...

There is a simple (and somewhat good) example of what you need in the sample Northwind database (it has been a while..). I believe from the Nortwind main menu select reports (remember its been a while). This should bring up a dialog with serveral options. One option is to select one or more products or leave blank for all. It has both print and print preview options.

Import this form into your database and then convert away. I have used this in the past and it fairly easy to modify.

I hope this helps.

Good Luck...

 

[thefoxer]

sounds like you need to develop a search 'engine' - build a table of 'searchable' fields from the database and put together a UI which will allow the user to build QBE style SQL structures for report/shortlist output - i know this is of little help, but having been there and developed the t-shirt, the process is time consuming and labour intensive and there aren't many shortcuts avilable ...

 

[misscrf]

I found a search form example online and adapted it to my needs. If anyone wants the link, I am sure I can dig it up.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[kysterathome]

Misscrf,

I would be VERY interested in the link, since I am facing a similar problem:
A contacts database and I want to create a form that lets the user search by a number criteriae, e.g. any name that contains MAR (like Marks, for instance) - It should produce a list from which I can click to get to the adequate form/report.

I have been looking a lot online, but so far not successful... it seems such a typical thing to require, though...

Kysterathome

 

[misscrf]

Here is one that might work for you:

http://www.access-programmers.co.uk/forums/showthread.php?t=62853

I found that while trying to find this one

http://www.accessvba.com/showthread.php?s=&threadid=12147

read down the post, and find the zip file.

When you open the db, open the entry form, click find customers, then test it out. The post discusses choosing a record and bringing it back to the entry form. If you get lost there, let me know and I will try to help. I have a method for that.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[kysterathome]

misscrf - That looks stunning - That is exactly what I am looking for!! Now I just have to adapt it to my db.

Am very happy!!

Will experiment now

Kysterathome

 

[kysterathome]

misscrf,

It really works beautifully! I used the first thread (

http://www.access-programmers.co.uk/forums/showthread.php?t=62853)

But here is a question - in the list box where the search results are listed, is there a way to enlarge the columns individually, since I am using other criterae which don't fit into the column widths...

I really appreciate your help.
Regards,
Kysterathome

 

[misscrf]

I would have to look at it to be sure, but you can customize the form to fit your needs. Imagine this. You see the datasheet view. Now turn your form to single form view in design. Now line up the form fields in the detail in a horizantal line like a datahseet and put the headings in the form header.

Then, make the fields can grow and can shrink and apply scroll bars if you need. Then make your form continous and make the detail only as big as the line of fields. Then drag the bottom of the form box down so that when you preview it, the form show the records in succession but nicer than datasheet and show all the text in each record.

i will make a sample for you, but I have to figure out how to post it on my free geocities website. I will post back when I get a way to put it online for you.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

here you go:

http://www.geocities.com/misscrf/resultsformdesign.zip

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[kysterathome]

Thanks for your efforts!
My thoughts:
Like this I would have to link the SQL of the search to the form you suggest... my VBA knowledge is not that advanced, and then I would have to make one of the fields (e.g. Last Name) clickable, although I guess that should be fairly straight forward with the Existing code I have.

But, isn't there a way to just tell in the code how big the category widths should be?

i.e., right now, the last bit of code is as follows - somehow integrate the width?...

'Pass the SQL to the RowSource of the listbox

Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrder
End Sub
Private Sub Form_Open(Cancel As Integer)
End Sub

Private Sub lstCustInfo_DblClick(Cancel As Integer)
'Open frmCustomer based on the ID from lstCustInfo listbox

DoCmd.OpenForm "ContactsForm", , , "[ID] = " & Me.lstCustInfo, , acDialog

End Sub

You really provided a great start to the year so far though!! All the best to you too

Kysterathome

 

[misscrf]

You can, but there is more that you can do with the form that I gave you. You can add a select button in the header or footer, and have the record selectors. Then you can just use the select button to bring the record you want back to the form.

As for making the field size as big as the length of the text. That is a very good question. I would suggest giving it its own posting. Put that in the subject of the post.

If you get an answer, you might make an faq on it. I am sure it will be useful to many. Not my specialty though.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[kysterathome]

Thank you misscrf. I will run a thread on that.

So long,

Kysterathome