Creating a form that allows for value lookup

[sp3ctrecs]

Hi, I'll give you a little background info before I ask my question:

I have a table called "Company" with the following feilds:
Company_ID (PK), Ticker (Indexed, Unique), Company Name

I have (or at least want) a single record access form, and now for the problem:

I would like someone to be able to type a ticker into the ticker feild, and have it search for an existing feild with that ticker (it would automatically populate company name with the correct value). Now, if the ticker doesn't exist, I would like it to bring up a new record form so the user can type in that information. I know I've seen something like this before, but its been a while since I've used access, so I'm still kinda brushing up on this stuff. Can anyone help me out here? Thanks!

 

[Mile-O]

What is the purpose of the ticker field? It would seem, to me, redundant, since the primary key is unique and indexed.

 

[sp3ctrecs]

I suppose it is a bit redundant, so if it makes a difference you can treat ID and "Ticker" the same thing. But I dont really think it changes the question any...

 

[Jibbadiah]

Think most of what you want is in a combo box... specify that you want it to look up a record when the wizard pops up. If it isn't in the combo, then they can move to a new record manually.

 

[Mile-O]

Have a look for my thread called Combobox Examples in the Sample Databases forum. Should give you what you want. You'd also want to look at my FAQs on Adding Items to Combobox in the FAQ Forum.

 

[sp3ctrecs]

I'm not clear on what exactly you mean. I can't create a lookup to its own value can I? Maybe an example or two will help clarify what I'm asking for.

In Company I might have the following rows:
Ticker Company
AAA AAA Corp
ABC ABC Corp
BBC BBC Corp

Now by default my form looks like:
Ticker: _AAA_
Company: _AAA_Corp_

(the underlines are just a bad attempt at making it look like an input)
If I edit the ticker value, by default Access just changes the value for that row, so if I type ABC, it instead changes the AAA to ABC instead of jumping to ABC corp. So, what I want to happen when I type in ABC is to pull up the Company Name ABC Corp. Now, say I type in GRD in the ticker (that doesnt exist). I'd like the form to look like:

Ticker: _GRD_
Company: __________

It would jump to a blank form so I could fill in this new information.

I hope that clears things up a little.

 

[Jibbadiah]

Yeah... it will do that... create a new combo called 'Quick Lookup', that will allow you to find a record on the form. If it isn't in the Quick Lookup then they can create a new record. No need to over-complicate things.

 

[Mile-O]

Here it is

 

[sp3ctrecs]

Great! Thats exactly what I was looking for, thank you so much.