Adding new info to an existing record

[Danielle17]

I have to add 5 columns of new info to an already existing record. I have 4 text boxes on a form each of which contain a dollar amount (Lodging, Meals, Gas, & Misc). Then I have another text box, Totals. I know that you can't calculate columns across and I also know that you can't do this in a table. So I've resorted to a form.

Now, on my form I have 4 text boxes one for each of the expense departments and another for the total. I'm not real good at using the expression builder so I'm unsure how to program the Totals text box to add the other 4 boxes. Before the user clicks the command button to add this new info they have to type in the JobID number in another text box. (I've never done anything like this before....)So that when the cmd btn is clicked the code will read something like, Go to record with this number (which is also the primary key), and add the info from the text boxes into the record that has that JobID.

I know how to say it in english, now I need a "computer person" to help me translate that into computer code

Here's what I have so far:

Private Sub cmdEnter_Click()

Dim db As Database
Dim rsExpense As Recordset

Set db = CurrentDb
Set rsExpense = db.OpenRecordset("zJobTable&quot

' put zeros in txt boxes so that it can be calculated if there is no number entered

If Me.txtLodging = "" Then
Me.txtLodging = 0
End If

If Me.txtMeals = "" Then
Me.txtMeals = 0
End If

If Me.txtGas = "" Then
Me.txtGas = 0
End If

If Me.txtMisc = "" Then
Me.txtMisc = 0
End If



rsExpense("LodgingCost&quot = Me.txtLodging
rsExpense("MealsCost&quot = Me.txtMeals
rsExpense("GasCost&quot = Me.txtGas
rsExpense("MiscCost&quot = Me.txtMisc
rsExpense("TotalCost&quot = Me.txtTotal


End Sub

I know it needs some help(right into the trash!) Thanks.

 

[TTThio]

Hi there,

I'm not sure if I quite understand the real problem, but let's see.

You might need to insert another field in the table named JOBID. Then in the form, create a txtbox with it as the control source. That way, everytime you store a new data, it will just be updated into the table.
If you're going to have one ID with a lot of records, than you'll need to use subform inside the main form.

About to add the 4 txtboxes value into the Total txtbox,
hold each txtbox value in variable, total = var1 + var 2 + var3 + var4.

Will it help?

Tin Tin

 

[Danielle17]

The JobID has a field in the main table as "ID". This is an autonumber and it will only have one record at all times. All the information for a specific job is entered before the technician goes on this job. After the Tech comes back he will then need to enter the expense amounts into that same record. I've tried using the recordsetclone and bookmark actions but so far nothing has worked. I've got to find a way so that access recognizes the number in the JobID text box as a number in the ID field in the table, then add the info in the other text boxes to the fields that they would go into (i.e. txtLodging would go into the field titled LodgingCost) I know how to add a new record I just can't figure out how to code so that it finds an existing record and then adds new info to that record.
I hope that helps clarify. When I get desperate I kinda ramble

 

[TTThio]

H Danielle,

in this case, you can try to have a combobox, control source = ID (table field)

Then on the cboID_afterupdate()
docmd.gotocontrol JOBID - the text box that hold the ID
docmd.find cboID

See if it works.