SEARCHES

[kirstyschofield]

please help me, i'm doing a third yr project for uni and i've never used Access 2000 before and i need to be able to create a form for student details so that the user can type in text or a number on the form and then choose from a combo box (of Student Name, Student Number, Address) and then click a search button and the details for that particular student appear!!! i have no VB experience and i really need step by step instruction!!!

 

[DBL]

You can do it without needing a search button. In design view of the form use the combo box wizard. Select the third option (Find a record on a form based on the value I select in my combo box). Select the student number (or whatever) from the available list and follow the wizard through to the end. Then when you select a student number from the available list, it will bring the appropriate student's records up for view

HTH

 

[kirstyschofield]

hi i tried that but i'm a little confused....i just set up the combo box but all it does is give me a list of records...i want to create a form so the user can type into a text box, say a student name, ("Smith") and then be able to find just that record....thanks for your help...anymore would be greatly appreciated because i'm a bit out of my depth and my entire uni degree rests on passing this project!!!!

 

[Graham T]

Kirsty

I have a small example database showing this process.

If you drop me an e-mail I can let you have a copy of this. It is a zipped up Access 2000 example.

HTH

Graham

 

[DocManDobie]

Mr Graham, not wanting to be an arse but students have to learn and all the advice in the world is fine, as you have to understand it, but I reccon e-mailed examples are a no go as they can be copied and used as exam material with no effort put in.

Just my pennies worth!

 

[Accessable]

Did you use a wizard? On your toolbox make sure the magic wand tool is pressed. Then add a combo box to your form. This should make the process simple.

If you did not use the wizard, this may be a problem:

It is possible that your combo box is not pointing to the table where your data lies.

You see values in your combo box, but I wonder if your values are simply programmer-made strings of text. It is possible that the values you are seeing are not actual data from your table.

Open the properties of your combo box. Click data tab. Click Row Source field. Press [Shift]+F2. This is where you tie your combo box to your table. If you feel this is where the problem lies, then click the button on the Row Source field with three dots (...). Here you can use a query builder tool to add fields your combo box will contain. Navigate to your table, drag whichever fields you want to your combo box, and that should work.

Finally!
If your combo box IS tied to your table, and you are reading the data from the records of your table, but nothing is happening then:

Well, you have to make something happen. I would do this with code or macros (using event properties), you could also have a subform that links with the data in your combo box. There are tons of ways to do this stuff, though, and I think that may be for you to have to figure out.

Let me know if this was what you needed or not. Good luck,
-Mark

 

[DocManDobie]

first and the most important thing is to have either two forms, one as a search page and 1 to display the data. O the combo on the same form as the data.

All on one page. When you click on the combo box and put it onto your table, it will come up with a wizard option, then select find record option,

If you have a seperate page as the search option, then you could try this. Just have two fileds on your form, student name and student address, make sure the data comes from your table, then add a buton, in the wizard that comes up, choose record navigation and find record, this will bring up the familiar find bar, just enter the name of the student and it will find the record. NOW add a button again in the wizard choose the open form and choose the form with the students full records and link the NAME on each form, (easy with the wizard) or is you set us a primary key on the table ie Student ID link these two together.

Now when you goto the search page just click on the search button, enter the nam, the record will come up, then click on the new button you added and your other form should come up with the student records.

IF not give us a shout, this is the easy way of doing this, you could add boxes to type in the student name and have all the records narrow down till it gets to the correct record, but this gets very messy.

 

[expublish]

I am having a similar problem to the original one on this thread. Basically mine is simpler. I want a combo box that lists all records in 1 field of a table (say employee names). When the user selects the employee they want to view another form opens with all the employees details on.
I already have 2 separate forms for the search page and the results page. I have setup the combo box as explained about, using the wizard, but when I try to use the form the names are listed but upon selecting a name it doesn't go anywhere. The name is viewable in the combo box but nothing is actionned. Does it have to be linked to a macro? If so what actions does the macro need to include?
Please help!!!

 

[DocManDobie]

OK on the search form, did you make sure in the wizard to get its data from the same table as the other form. 2 Just add a combo box and tell it to find a record on the form. Next you will have to add some code for it to open the other form. On the combo box goto properties then on click, event proceedure, add code such as

On Error GoTo Err_Command127_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Details"

stLinkCriteria = "[Name]=" & Me![Name]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command127_Click:
Exit Sub

Err_Command127_Click:
MsgBox Err.Description
Resume Exit_Command127_Click

Command 127 change this to your combo box name

On this line stLinkCriteria = "[Name]=" & Me![Name], Name would be the field name, ie. Name or Surname.

stDocName = "Details" and on this change details to your form with the data to open

 

[ColinEssex]

Alternatively, you could use an input box and type the first few letters of surname (or whatever) then bookmark the selected name to the form.

I won't post all the code, but email me if you need it

Colin

 

[ColinEssex]

Alternatively, you could use an input box and type the first few letters of surname (or whatever) then bookmark the selected name to the form.

I won't post all the code, but email me if you need it

Colin

 

[DocManDobie]

expublish, i would go with the first method to begin with as it is easier, once you have learnt a bit more then have a go at the second, basically on the second you have a list of the names, a box above and as you type in the name the list gets smaller until it matches the name you typed in, then you click on the name an it opens the record, cool but not easy, it took me 3 months to suss that one out so have a go at the easier method first!

 

[DocManDobie]

If the above does not work follow this link
http://www.mvps.org/access/downloads/email.zip

It is an e-mail database BUT it has the exact criteria you need a list of clients when you click on it opens the record.

Good Luck, let me know if you need more help.

 

[kirstyschofield]

thank you ever so much for all the advice...i'm now going through it to see where i can apply it to my situation so i will let you all know if i make it work!!

i really appreciate all the help and i'm feeling a lot calmer about not having used access now!! but be warned i may post again in the future!!!!

kirsty x