Microsoft Access Search query

im creating a database for work to track staff laptops and ips assigned to them, but i cant remember how to make a query, so that a dialog box opens and you tipe in the persons name, and it takes you to that record (not in table format) and not the search button eith god im picky aint I

I know it can be done because a bloke at my last place of work did it, but i cant remember what he used as the criteria????

thanks in advance

djdohboy

It sounds like you're trying to create a form to do that. If you wanted it to be in table format, you would type something like:

SELECT * FROM tablename WHERE PName = PErsonName
As long as PersonName isn't defined anywhere, a dialog box will appear asking you to specify what value PersonName has.

If you're doing it in a form, use the form building wizard based on your query. Then, where patient name is, add a button. Edit the code behind the button after you name the text field "txtName" or something, and put:

dim rs as New ADODB.Recordset
rs.open "SELECT * FROM Person WHERE PName = '" [note the tick -- ' ] & txtName & "'", [not sure what goes here in Access. It should prompt you, but this is where you put what connection you want to use in VB. Not sure about VBA]

Then you would populate your text fields:

txtLoaned = rs!Loaned
txtType = rs!LaptopType
etc.

Note that it is the field name that follows the rs!, and it is case sensitive. If this doesn't work or you have more questions, I'll be around. I'm subscribed to this thread now

You could just do a Query by Example and in the criteria put the question you wish to pose in square brackets:

[Please enter staff name as it would appear in the database]

would go in the criteria bit (or similar the bit inside the brackets is customisable). However, this will output it as a table. From this saved query you can use the form design wizard to base a form upon this query and customise this as appropriate.

Nothing on JungleJake but this is a lot easier than getting into the code. Just depends on how involved and how flexible you want to get.

What can I say, I'm a coder and no fan of Microsoft Access As a result, I don't know the GUI functionality very well, and just talked about what I know.

Definately take Infidellic's advise, it is far simpler and sounds like it will do exactly what you want done.

Originally posted by junglejake

What can I say, I'm a coder and no fan of Microsoft Access

As a result, I don't know the GUI functionality very well, and just talked about what I know.

Definately take Infidellic's advise, it is far simpler and sounds like it will do exactly what you want done.

I code in VB myself using the archaic DAO (I really must get into ADO!) but for purposes like this I find that easier, but I do agree that Access can be a pain in the arse (Access ->