Search function in form

[chigley3]

Hello again,

Whilst I am at work I need to ask another question.

In my form, what is the best way to type in the a previous claim No to automatically retrieve all of its relative information.

Kind of like a search function to pull up all connected data?

Please help,

Thank you,

C.

 

[PHV]

You may try a linked subform.

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

 

[TheAceMan1]

How are ya chigley3 . . .

[blue] ... what is the best way to type in the a previous claim No to automatically retrieve all of its relative information.[/blue]

As a starting point ... say we have an unbound textbox named [blue]FindClaimNo[/blue]. In the [blue]AfterUpdate[/blue] event of [blue]FindClaimNo[/blue] we'd have something like:

[blue]   Me.Recordset.findFirst "[ClaimNo] = [red][b]'[/b][/red]" & Me.FindClaimNo & "[red][b]'[/b][/red]"[/blue]

Note: if ClaimNo is numeric, remove the single quotes [red]'[/red] in [red]red[/red]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[chigley3]

I tried that to now joy, is there anything else I should know about that,

Thanks by the way....please whereever i need to type the field name type in a different colour,

Im new to this stuff

 

[dhookom]

all of its relative information

This has little or no meaning to us. Can you use terms like records, fields, tables, queries? Is "all of its relative information" available in the current form's record source or do you need to open additional forms?

Duane
Hook'D on Access
MS Access MVP

 

[chigley3]

OK sorry about that,

If i type in the previous claim no I want dealer code, dealer name, dealer claim no, region, dealer customer, VIN, speedo, date of sale, date of repair....and so on to appear in the same form so that it can be looked at and edited if needed.

Thank you,

C.

 

[dhookom]

chigley3,
Do you expect us to understand where all of this information comes from? Again, are we to assume this information is all in the record source of the current form?

If the information is in the current form but you just need to navigate to a different Claim Number then use the combo box wizard that finds a record in the current form.


Duane
Hook'D on Access
MS Access MVP

 

[chigley3]

The information is on the form, which was linked to a table but because I wanted use maths I put expressions in the control source and now it is not linked in some fields

 

[dhookom]

I'm not sure what your title "Search function in form" has to do with changing the control sources and losing links to fields. Any control source that is not a simple field name will not update your table/record source.

Please tell us what you have and what you need.

Duane
Hook'D on Access
MS Access MVP

 

[chigley3]

Hello, I thought about doing it differently,

Can I use a FindRecord in a macro to search for the claim no? Will it then pull up all of the associated information to that claim number so i can see the whole of the record?

thank you

 

[dhookom]

Again, "If the information is in the current form but you just need to navigate to a different Claim Number then use the combo box wizard that finds a record in the current form."

You still haven't provided any information about your form recordsources or what you mean by "associated information". Changing your question from "relative information" to "associated information" doesn't provide any value.


Duane
Hook'D on Access
MS Access MVP

 

[chigley3]

All of the information in the form is linked to a table.

At my job I enter a claim no and then i can enter dealer name, dealer region, right up to labour cost and total paid.

When I do the search for example for claim no, I need all of that information (dealer name, dealer region.....) to appear. I want the information to appear in the current form.

I want to type a specific claim number into a box and then press a button to get that info.

Does that make sense?

If you can send code please make it obvious which is syntax and which is variable.

Thank you so much,

I am sorry, and I thank you for your time.

 

[dhookom]

Did you try my previous suggestion of "If the information is in the current form but you just need to navigate to a different Claim Number then use the combo box wizard that finds a record in the current form."

If you didn't try, then please do. If you did try but didn't get what you want, then please tell us about your lack of success.

Duane
Hook'D on Access
MS Access MVP

 

[chigley3]

How exactly do you set up the combo box?

 

[chigley3]

Ok, I can use the combo box to look at all the previous claim numbers but I want the ability to click on the number and then the form updates to bring up all of the information related to the claim number.

It's not that hard to understand is it?

If claim number 23A has dealer info, total amount due, etc then I need that information to be pulled up when selecting the claim no like a typical search function you would find on any stock database.

The form is linked to a table so all information can update in the table and the form.

Please help.

 

[dhookom]

chigley3,
Did you find the combo box wizard that has the option "Find a record on my form based on the value i selected in my combo box"?

If you didn't find it then try again. If you did find it but it doesn't work, please provide the combo boxes properties, it's after update code, and the record source SQL of your form.

Duane
Hook'D on Access
MS Access MVP

 

[chigley3]

duane,

I checked the combo box function and I got it working.

Thanks duane.