Access Form - set default value for a form by using a lookup

[BarbaraT]

I'm creating a form to enter new data into a table. It's pretty simple -- you enter the product ID and the product price. Both of those pieces of data are stored in a table, along with the year (2011).

I have prices for the same products for 2010 already in the table. In many cases, the 2011 price is the same as the 2010 price, but it's not always the same, and the difference isn't consistent (can't get 2011 by adding a % to 2010 or anything).

I want to be able to enter a product ID in the form, and then have it go look up the 2010 price for that product, and pre-populate the "price" box on the form with the 2010 price. The user might be able to just leave the price as shown, or might have to change it, but by starting with the 2010 price in there, it will save a lot of data entry for the items whose price didn't change.

How do I make it pre-populate the price control? I've tried setting the default property to the table field, but that doesn't seem to work. I suspect that there's a way to do it using "on click" or "after update" events, but am not sure of the syntax.

If possible, I'd like to do this using the properties or macro builders features, rather than creating a VBA module. I haven't mastered that process, and I'd like to keep this as simple as possible, since someone else will probably inherit the database some day.

 

[BarbaraT]

Sorry to bug people, but I still haven't solved this, so am bumping the question.

Is there anyway to pre-populate a control in a form with a value looked up from a table, since MOST of the time that's the value we want?

Thanks.

 

[HiTechCoach]

If possible, I'd like to do this using the properties or macro builders features, rather than creating a VBA module.

Since most people would solve this with VBA code may be why you are not getting any response.

It may be possible to do without using VBA code. My guess from what you have described is that your design may make it very difficult to do with a Macro.

How easy this will be depends a lot on the design of your tables. This is tru for macro and vba code.

What is the table structure where you are storing the prices for each year?

Also, what version of Access are you using?

TIP: Access gets it real power form using VBA code. Macros are very limited. The main reasons I do not use macros is the lack of error handling and looping. Error handling is critical for any good application.

 

[Norie]

Barbara

Is this a 'one-off' update of prices?

Do you have the product IDs and the new prices in some sort of usable format?

eg a simple list with the ID and the new price

If you do then the whole thing might be able to be done with a single update query.