Multiple Questions from an Access and VB Newb

[ConfusedA]

Hi there, I am in the process of creating multiple databases but have little experience with Access and my visual basic coding knowledge is limited and rusty. I have searched through some forum topics looking for help for my problems but seem to be missing it. Anyways, I will start with the first project...

I am trying to set up a simple database to calculate multiple costs. Essentially what I have is:
-1 table (tblTruckvalues)...with the fields: ID (autonumber), Job (text value), JobHour (number), JobCost(number) Year (Limited sized text value), Month (text), and Week (number)
-a corrseponding form (FrmTruckInput) which has text boxes for the fields of Year, JobHour, List boxes for Month (Jan- Dec), Week (1-5) and Job.
For simplicities sake, Job has 3 current values.. Truck1plow, Truck1sand, Truck2plow.

It also has a button (cmdSaveCalc) which currently saves the record upon clicking

What I am trying to do is associate integer values with each of the "jobs" so that I can calculate a total cost based on the user entered JobHour value.

For example
Truck1plow is $22, Truck1sand is $15, Truck2plow is $25.

I figure this involves an "if" statement in VB, so here is what I have attempted

Option Compare Database
Private Sub cmdSaveCalc_Click()
Dim Job1 As Currency
Dim Cost As Currency
Job1 = 15.5
Cost = 0
If Forms!frmTruckInput!Job.Value = Truck1Plow Then
Job1 = 22
ElseIf Forms!frmTruckInput!Job.Value = Truck1Sand Then
Job1 = 15
ElseIf Forms!frmTruckInput!Job.Value = Truck2Plow Then
Job1 = 25
End If
Cost = [JobHours] * Job1
'Tables!tblTruckvalues!JobCost.Value = Cost
DoCmd.RunSQL "INSERT INTO tblTruckvalues (JobCost) VALUES (Cost)"
On Error GoTo Err_cmdSaveCalc_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_cmdSaveCalc_Click:
Exit Sub
Err_cmdSaveCalc_Click:
MsgBox Err.Description
Resume Exit_cmdSaveCalc_Click

End Sub

I am aware that my currently active line lets me input the cost total (but in to the next possible record, but what I would like to do is be able to have the form automatically calculate the cost and input it in the current record on the table when the button is clicked. I was wondering if I am on a better track with the inactive line above (see red).

Any help, advice, or links would be greatly appreciated.
-ConfusedA

 

[ConfusedA]

1 update, I accidentally changed JobHour to JobHours in this form, I have fixed that, it is not the problem.
Thanks again.

 

[twoplustwo]

Do you mean you want to calc some fields then write that to the table?

I would advise just calculating them at run time (on the fly!) rather than storing them.

 

[ConfusedA]

Thanks for taking the time to look at my problem. I hope this explains what I'm trying to do a bit better.

Essentially, I'm trying to get a weekly cost value for each set of actions
IE. If truck1 plows snow for 10 hours in a week at $22 an hour, i'd like to be able to access the price of $220 for that week. I would then like to be able to use this data to compile reports for weekly, monthly and yearly costs of the truck.

How could I just calculate it on the fly? like through a query?
Thanks again.

 

[twoplustwo]

Hey. That's what I thought.

In a query use your actions table and enter in a new column JobCost: [TruckColumnName]*[TruckPrice]

This should calculate the cost for the selected range and type of truck.

Give this a spin and get back to us.

 

[ConfusedA]

This solution makes sense for coming up with a cost value, my problem is I'm trying to associate each text value in my 'job' list menu with a numeric hourly value for the job (so that whoever is inputting the type of job is not necessarily aware of it's hourly cost) how would I be able to work this into the action table?

In the end I'm going to have approximately 70 different jobs split between about 40 trucks, and I don't want whoever will be inputting the data to have to look up the price of each job to submit it.

Is converting the string message to a numeric value even possible?
Thanks again for the prompt responses, it is much appreciated.

 

[twoplustwo]

I think your structure needs looking at. You should probably have another table (tblPrices) that contains a primary key, a field key (the ID of the truck) and the price. You don't want repeating data in a table. Have a look at "normalisation". This will avoid problems such as having many records of jobs with Truck 1 then the price of that truck changing. Rather than try and update all of those fields you can update 1 field in tblPrice which will cascade through the database.

You can then build quries etc. to return the data you want (and will subsequently be able to generate the solution I suggested).

There are several ways to generate a value based on text but I think this is the "proper" way forward and will save yuo a tonne of hassle in the long run.

 

[ConfusedA]

Wow, that will make my life, much easier. I'm going to recreate this database that way. Thank you so much for you help! I anticipate having more questions in the near future, but until then, I'd just like to say thanks again for all your help Twoplustwo.

 

[twoplustwo]

No problem.

See this link: http://datamodel.org/NormalizationRules.html#one for a good starting point!