Text box as Query with results displayed on form 1

[MSealy]

Hi, I hope someone can help me please.

On a filing database, I'm trying to set up a search text box on a form, which will show the one record it relates to on the same form.

To simplify, I have a table called INVOICES. Fields are BoxNo, InvFrom and InvTo. I've created a (think it's parameter) query using the 'Between' method in the Criteria and it prompts me for "Invoice No." On entering this, it will display the record with the BoxNo that contains the invoice. Great, so far so good...

But, what I'd like to do instead of running the query separately, is to have 4 text boxes on my main form. One as an input (search) box for the invoice number, which, when typed in will instantly show the query results in the other 3 text boxes (representing the fields) below it. It would also be good to have a command button next to my search field to 'clear' the boxes for another search.

If all this is possible, I would repeat this process for other tables, using my main form as a search-and-results screen (so I therefore wouldn't want to base this main form on any one query, or table).

Is it possible ?


Thanks and regards,

Mark.

 

[lespaul]

yes it's possible. check out FAQ701-6763

Leslie

Have you met Hardy Heron?

 

[MSealy]

Ok, thanks. How do I apply that - to the controls on the main form, or the actual query itself ?


Mark.

 

[lespaul]

I guess I don't understand the question? The controls on the form can control the values in the query....maybe this is a better option: faq701-2328



Leslie

Have you met Hardy Heron?

 

[MSealy]

Ok, thanks. I admit I'm a bit rusty with all this. I was wondering if it's possible to use the text boxes as a query instead. I tried using dlookup, but I can't get it to work.

My table called "tblINVOICES" has a field, "FILE REF:" which is the basically a box number for a box of invoices. Another field called "INCLUDES" (the first invoice in that box) and "TO" (the last invoice in that box).

On my main form "frmMAIN", I have an unbound text box called "txtInvSearch", another called "txtInvBoxNo".

So what I want to do is type an invoice number into "txtInvSearch" which will show the "FILE REF:" from tblINVOICES (if the number I type in is in the range of "INCLUDES" and "To") in the "txtInvBoxNo"

Is this possible to do just using the control source field of the text box (without an event)? So far, I've tried ..

=DLookUp("FILE REF:","tblINVOICES","INCLUDES:" & Forms!frmMAIN.txtInvSearch)

but I get an error. Any ideas ?

Thanks.

 

[lespaul]

so your query will be something like:

SELECT [FILE REF] FROM tblINVOICES WHERE Forms!frmMAIN.txtInvSearch BETWEEN INCLUDES and TO

and the control source for txtInvBoxNo will be the FILE REF field from the query.

HTH
Leslie

 

[MSealy]

Thanks. Well I was trying to get around this without a query but it sounds good. I've saved it as "qryINVTRANS"

Unfortunately, when I reference the query in "txtInvBoxNo" using..

=[qryINVTRANS]![FILE REF]

I just get #Name in that box - regardless of whether I type an invoice no. into the "txtInvSearch" box or not.

 

[lespaul]

how are you running the query? a button on the form or something?

 

[MSealy]

Oh, I see. This is why I'd rather not go down the road of queries.

I have 2 text boxes on a form, one where I enter the invoice number, the other where I'm hoping to see the box reference result.

I guess with a query, it won't auto-run when I type in the text box.

Surely this must be possible with dlookup or something similar in the text boxes - instead of running a query ??

Regards.

 

[lespaul]

maybe put as the control source of txtInvBoxNo:

DLookup ("File Ref", tblInvoices, Forms!frmMAIN.txtInvSearch Between Includes AND To )

and then some kind of refresh on the on exit event of txtInvSearch...somehow you have to trigger the function call whether it's in a query or a control event.

 

[lespaul]

just as an FYI, your previous DLOOKUP function:

=DLookUp("FILE REF:","tblINVOICES",[b]"INCLUDES:" & Forms!frmMAIN.txtInvSearch[/b])

doesn't have any comparison operator in the criteria section...so when this is evaluate at run time the criteria becomes:
[tt]INCLUDES:ValueFromForm[/tt]

there's no =, <, >, etc. so it's not a valid criteria.

leslie

 

[PHV]

what about this ?
=DLookUp("[FILE REF:]","tblINVOICES",Forms!frmMAIN!txtInvSearch & " Between [INCLUDES:] And [TO]")



Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MSealy]

Thanks.

The Dlookup looks fine, but produces #Error - even if I've already populated the txtInvSearch with a valid invoice no.

I'm not sure what's happening.

Regards.

 

[PHV]

What are the REAL name and data type of the relevant fields in tblINVOICES ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MSealy]

They are (within quotes) ...

"FILE REF:"
"INCLUDES:"
"to"

Regards.

 

[PHV]

Which data type ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MSealy]

Oh, yes, sorry. They are all text.

 

[MSealy]

Eureka - it works - that's where it was going wrong. I should've used number instead of text for tblINVOICES.

I said I was rusty

Thanks v much. Now all I need to do is a little button next to the text box to 'clear' the search.