Help me, please!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi guys, me again

I have 10 fields in a table, Field1 to Field10. Field1 has all the retail
prices of cars built in 1996, Field2 those of 1997, Field3 those of 1998 and
so on. On this form is also field called CarMake. Each carmake has its own
unique number which is in the first field on this form, called MMRefNumber

On a second form, I have a dropdown with a list of years, starting at 1970,
1971 and so on, up till 2005. On that same form, I have a textbox,
txtCurrentValue. And a textbox called txtMake and a textbox txtMMRefNo.

I want to enter a MMRefNo into this textbox and want Access to lookup that
Make and put that into txtMake.

Then, and this is where I need help, I want to click on a year in the
combobox and then want access to lookup the current value of that car in the
specific field on the first form and show that value in txtCurrentValue.

If I pick a year other than 1996 to 2005, I want txtCurrent Value to display
a Null value.

Is it possible that someone can help me sort out this one.

Regards

Peet
 
In the After Update event of the combo box:
If Me.MyYearCombo < "1996" Then
Me.txtCurrentValue = Null
Else
'Whatever you do if it is >= "1996:
End If
 
Hi guys, me again

I have 10 fields in a table, Field1 to Field10. Field1 has all the retail
prices of cars built in 1996, Field2 those of 1997, Field3 those of 1998 and
so on. On this form is also field called CarMake. Each carmake has its own
unique number which is in the first field on this form, called MMRefNumber
Click to expand...

Uggghhhh...

This is a classic case of a non-normalized "spreadsheet" design.
You're storing data - years - in fieldnames. Each year a new model
comes out, you'll need to CHANGE THE STRUCTURE of your table, your
queries, your forms, your reports to accommodate that years' models.

A MUCH better design would be to store data in fields, not in
fieldnames: for instance, fields MMRefNumber, ModelYear (joint
two-field Primary Key), Cost. This lets you add a new *record* for
each model year, and completely prevents your problem, which is
entirely due to the improper table design.

You can use a "Normalizing Union Query" to migrate data from this
spreadsheet into a proper table; post back for details if you're
interested.

John W. Vinson[MVP]
 
Back
Top