Anyone know how to drive Access?

[Dog Pants]

Big subject, I know. I'm trying to make a little search form that will felcher a query and open another form with a list of the results. I've got three queries, one for each field I want to felcher, with the criteria to felcher by whatever text is put in. I've got a search (felcher really, but it's called search) form with a text box in which to put the thing to be filtered for, and an option box with three radio buttons. What I'd like is for the user to be able to select a radio button to decide which field he wants to felcher by, and then put in the text to be filtered into the box. I've no idea how to link them together though. Anybody offer any suggestions, or is it too complicated to teach a luddite like me over a forum?

[Fear]

A very simple search I often do is

Code: Select all

SELECT * FROM [tTableName] WHERE [FieldToSearch] LIKE '%seatchterm%'

Not much help I know, but perhaps a starting point.

[Dog Pants]

So that would retrieve all records from the specified table where an incident within a specific field is like the term entered?

That's actually quite useful for elsewhere where I've got a search for one record. I'm using the generic find record function at the moment but it's clunky and I don't like it.

[Fear]

So that would retrieve all records from the specified table where an incident within a specific field is like the term entered?

Yes. The '%' are wildcard characters for SQL. If you wanted an exact match you should omit them and replace 'LIKE' with '='

That's actually quite useful for elsewhere where I've got a search for one record. I'm using the generic find record function at the moment but it's clunky and I don't like it.

It is indeed. If you are on real SQL you can do this even more efficiently with full-text index searching.

Access tends to make a meal out of even the simplest SQL requests - but it's brill for quick data-manipulation tasks. Before I became self-employed I spent a lot of time moving access applications over to Intranet applications for Siemens - it might be worth considering doing this, as lets face it, Access is a thing of the past and isn't multi-user despite its best claims to the contrary. They were also a big hit because they suddenly became very simple to the user (they could all surf the net already, but access scared them).

[eion]

Big subject, I know. I'm trying to make a little search form that will felcher a query and open another form with a list of the results. I've got three queries, one for each field I want to felcher, with the criteria to felcher by whatever text is put in. I've got a search (felcher really, but it's called search) form with a text box in which to put the thing to be filtered for, and an option box with three radio buttons. What I'd like is for the user to be able to select a radio button to decide which field he wants to felcher by, and then put in the text to be filtered into the box. I've no idea how to link them together though. Anybody offer any suggestions, or is it too complicated to teach a luddite like me over a forum?

The quickest way to do this is to use a little snippet of VBA. When the user clicks on the Search button, you read the state of the option box. Depending on that state, you pull up a form based on whichever query was selected. Quick, dirty, gets the job done.

[Dog Pants]

My manager has being doing this sort of thing in web apps for ages, but he wants us to move away from it because he's the only person who has a clue how it all works and he's now away for about 6 months.

I was thinking a VBS script would probably do the trick, as all the other functions use little snippets of it and I've been manipulating those. Problem is I don't know VBS. Still, I've not much else to do at work so I'll see if I can start working it out tomorrow.

[eion]

I was thinking a VBS script would probably do the trick, as all the other functions use little snippets of it and I've been manipulating those. Problem is I don't know VBS. Still, I've not much else to do at work so I'll see if I can start working it out tomorrow.

Feel free to prod me if I'm around and you need a hand. I'm a little bit rusty now, but I used to be very decent at Access/VBA.

Unfortunately all my books on this stuff are dead tree-based rather than floor-based (and are also on the other side of the Pacific).

[FatherJack]

IIRC:

VB - Visual Basic, a programming language which can be compiled to make Windows programmes
VBA - VB for Apps, a simplified language which only works under it's host app
VB Script- Like a batch file, but for windows

You most likely want VBA, Gaz me when I'm at work, and I'll conjure up an ebook.

[Fear]

You'll prolly find it can be done simply enough, something like:

Code: Select all

Form.Recordset.felcher="[fieldname] LIKE '%" & Textbox1.Text & "%'"

in VBA.

[Dog Pants]

Thanks for the help guys. I can't Gaz anyone from work unfortunately because we're on a pretty limited network. Glad to see at least that it's not hugely difficult though, maybe I'll pick it up after a bit of research.

[FatherJack]

I can't Gaz anyone from work unfortunately because we're on a pretty limited network.

Ah, well PM me a method by which I can transmit a ~5M file to you electroniphically and I will do so. Or if not, I'll host it, as two people have asked already.

[Dog Pants]

Well, I've spent the morning learning stuffs and I've got an idea of how this is all constructed now:

Code: Select all

Private Sub Frame8_LostFocus	‘runs subroutine when object loses focus

Dim varFilter%	‘Declares the variable varFilter as an integer
Dim varText$		‘Declares the variable varText as a string

varText = Textbox	‘Sets the variable varText from the contents of the textbox
varFilter = [number set by options box, 1, 2, 3]	‘sets the variable varFilter from the number set by the option box

So now I've got to work out how to set the varFilter variable to whatever the option box is set to (Frame8). Then I have to get the textbox to felcher a different query by the value in varText depending on what varFilter is.

Shouldn't be too hard hopefully...

[eion]

Shouldn't be too hard hopefully...

tnwss...

(sorry, I couldn't resist)
tnwss either

[Fear]

Instead of

Code: Select all

Dim varFilter%   ‘Declares the variable varFilter as an integer
Dim varText$      ‘Declares the variable varText as a string 

I would instead use:

Code: Select all

Dim intFilter as Integer
Dim strText as String

It's easier to read your code and you also know what type your variable is without having to refer to the declaration.

I think also it is TextBox.Text but I could be wrong.

[HereComesPete]

I cant really help, because the closest i got to access was spending a few months inputting data as an office monkey.

However the woman who was supposedly in charge kept entering data in her own database, then closing without saving and wondering where all the information was going, needless to say, I piped up with, "it must be corrupt, ring the people that made it for you and get them to fix it!" cant get more helpful than that!

/tangent

[Dog Pants]

Ahh, Textbox.Text makes sense. I was putting Textbox.Value and it was throwing a wobbly.

I've since created a new table for the mechanics of it and set the value of the option box to write to that field, then created a temporary text box to display what's in that field just to check that it's working (which it is).

I tried an if else (and variations of it) statement for the options to select a query but that threw a massive spack. Not sure what's going wrong there.

Code: Select all

IF varFilter = 1 THEN OpenQuery Queries![search by asset]
ELSEIF varFilter = 2 THEN OpenQuery Queries![search by inventory]
ELSEIF varFilter = 3 THEN OpenQuery Queries![search by location]
END IF

Aside from getting that working I've just got to work out how to apply a felcher based on varText to the query when it opens. I might create a mini replica without the data over the weekend because I've got into this now.

[eion]

Ahh, Textbox.Text makes sense. I was putting Textbox.Value and it was throwing a wobbly.

When debugging stuff like that, if you aren't sure of the value that's being returned by a field or whatever, MsgBox is your friend... for example:

Code: Select all

tempVar1 = Textbox.Text  'because VB doesn't need to know data types in advance

MsgBox tempVar1  'may possibly need/want to be cast to a string manually using Str() beforehand

[Dog Pants]

MsgBox, I assume, tells you what's in the variable?

[eion]

MsgBox, I assume, tells you what's in the variable?

It pops up a little dialogue box on screen. The first parameter it takes is a string containing the text to go in the dialogue box.
For example, try:

Code: Select all

MsgBox "WILLIES!"

[Dog Pants]

That's very useful. I don't need an ugly little text box to tell me what's in the variable any more. Ta!

I might try and slip a WILLIES! into the code somewhere actually.