code to pick a field(and retrieve it's value) from a record selected via list

[Duane Dibbley]

I have a query with the following structure;

aDate(pkey) cost1 cost2 cost3 calc1 calc2 calc3
01/012014 ,,,,,,,,,1,,,,,, 2,,,,,,, 3,,,,,, 1,,,,,,, 2,,,,, 3
01/02/2014 ,,,,,,,,1,,,,,, 2,,,,,,,, 3,,,,,, 2,,,,,, 4,,,,,, 6
01/03/2014,,,,,,, 1 ,,,,, 2,,,,,,, 3,,,,,,, 3,,,,,, 6 ,,,,, 9
01/04/2014 ,,,,,, 1,,,,,,,, 2,,,,,,, 3 ,,,,, 4,,,,,, 8,,,,,, 12

fields calc1,2,3 are running totals of cost1,2,3

I expect/hope to first calculate the sum of a cost field and then minus the value of its corresponding calc field from a specific record.

result = sum(cost1) - calc1 selected record value

I want to select the calc1 record from a drop down list of the primary key. Which cost field is in the equation will static/defined as I intend to make a textbox for each field.

i need to know the code to pick a field(and retrieve it's value) from a record selected via dropdown list.

 

[plog]

You've got a lot going wrong here. Mostly because of your improper table structure. 2 biggies:

1. You shouldn't store calculated values. Instead, you should calculate them in a query and reference that query when you need to use them.

2. Whenever you start numerating field names (cost1, cost2, etc.), it's a sure sign you need a new table. Look up normalization (http://en.wikipedia.org/wiki/Data_normalization) and read thru a few tutorials.

Additionally, I wouldn't store your psuedo date field as text ("Jan 2014", "Feb 2014"). Instead, I would make it a date field and store the first date of that month (1/1/2014, 2/1/2014). That way, you can easily sort your data properly (April would be first month of year when sorted as text, September the last) and you can perform Date calculations on the data (DateAdd, DateDiff, etc) which come in handy in figuring out next month, last month, 3 years ago, etc.

I'd focus on getting your table structure correct before moving forward with any form.

 

[smig]

if you use Combo you can put all the fields in there.
You can show all fields or only some. Use the NumberOfColumns and ColumnsWidths properties of the combo. Put 0 into the width of a column you want to hide.
In your case:
NumberOfColumns = 7
ColumnsWidths = 3, 0, 0, 0, 0, 0, 0 (Show first columns only)

Use this code to get any column you want:
Me.MyComboBox.Column(X) ' --- Columns start from 0

 

[Duane Dibbley]

The fields aren't actually numerated, i've just named them so in this post to give a better understanding of their relationship, as the actual names would be irrelevant gibberish to anyone reading.

The date field does contain actual date values.

I appreciate your effort, but if you don't know how to do the calculation I'm requesting help with, please refrain from replying as i don't want to fill the thread with irrelevant discussion that'll reduce my odds of someone answering the actual question

 

[Duane Dibbley]

if you use Combo you can put all the fields in there.
You can show all fields or only some. Use the NumberOfColumns and ColumnsWidths properties of the combo. Put 0 into the width of a column you want to hide.
In your case:
NumberOfColumns = 7
ColumnsWidths = 3, 0, 0, 0, 0, 0, 0 (Show first columns only)

Use this code to get any column you want:
Me.MyComboBox.Column(X) ' --- Columns start from 0

Gawd bless yah sah, will give this a go thank-you!