Copying selection to some sort of global variable

[BigDanUk]

Hi there, IV not touched VBA since my college days (im a netowrk guy! dont pretend to be a programmer ) but i have been asked to write a Quote Generation Program for a Company that builds industral generators,

I have set up different tables for each component, the first two are

TBL_ENGINE

TBL_ALTERNATOR

each with the fields

ID (autonumber)
Reference (text)
Manufacturer (value list)
GenSetSize (Value list)
Price (currency)
Notes (memo)

On the first form the user selects the Gensetsize they want to build and a list box only displays the compatible engines, Manufacturer, ID, and Reference.

On the second form they will select the Altenator and so on...

I some how need to copy this selection (engine record) to some sort of global variable so at the end of the program i can pull it up on a report with the prices of all the components added together, obviously in the end there will be about 20 components. Just starting with engine and alternator.

Is this possible? or am i barking up the worng tree with access?

 

[M8KWR]

how many different components are there/or would there be.

I think it is possible to do.

Are you going to be storing the quote for future reference.

If you are this would make it a little bit easier. If the quote reference and all components where stored, then you have to call the reference number, then this should bring in all the comoponents for the said quote number.

You could also save the price, in the same table, because depending on how long the price stays valid for, and if someonein the meantime updates the main price table, thent he quote value would be altered.

I hope I have understood what you require.

If there is any more help you need then please let me know.

 

[BigDanUk]

this is eaxtly what im trying to achive

i just thought saving the quotes would make it more complex, the prices dont change really, and were talking maybe 20 components absolute max.

Am i right having a speperate table for everything and then one for the actual quotes?

 

[M8KWR]

what i would do is to have a seperate table for each components, so engine, alternator etc. Then within this table you would need a link to only pull information that is relevant to the item previously selected. (That is where the fun starts, and how you do it).

You in theory try and put all the information into the 1 table, but i'm think for the quote side, when you are adding in the components it would be easier to be in seperate tables.

If you want to give me some example, because some engine, will be able to run different types of alternators etc and so on. Then i could devise you a standard of how to pull the correct information in.

 

[BigDanUk]

How iv done it is

Tbl_Engine

ID I Reference I Manufatcurer I Genset I Notes I Price I
1 I SCAN140 I SCANIA I 40 I I I
2 I rrrr I MTU I 80 I I I
3 I yyy I SCANIA I 80 I I I

Tbl_alternator is same. but with diffeent manufacturers (Fixed List) the GenSet is a fixed set of values. Iv built a form that allows me to choose my genset size then shows me different engines that are compatible and show them in a lsit box. thats as far as iv got becasue i take it i need to somehow write that records ID to a global variable?


Thankyou so much for your help

 

[M8KWR]

your global variable of your ID number is stored for you in your list box. How many list boxes are you going to end up with on your form.

Have you considered using subforms?

 

[BigDanUk]

Just one list box on each form, Like a step through system, at the end, so at the end i will have say 20 records for 20 tables that just need to be pulled into a report.

Iv had a readup on subforms and am slightly confused to how they can help?

Thanks again,

Dan

 

[M8KWR]

ummmm...

What you could do, which would look nice, instead of having 20 forms, have 1, with lots of subforms.

But you only display the one subform the end user requires. This way all the information is still available for the data side, and you have not got the issue of passing variable across forms, because they are all stored.

I think i need to show you what i mean, but could do with the tables from yourself with the information in them...

The only issue with this fourm is that you can not put email address into it, as they tell you off. So to get around this use "kevin dot redfern at gmail dot com