Performing Calculations with Recordsets

[chims]

Hi,

I am currently trying to create a simple form that is dummy proof to update associates vacation time. However, the code is receiving a compile error: Sub or Function not defined when ran. I have 2 combo box's and one text box on the form.



Please any ideas!?

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Tbl_Associate", dbOpenDynaset)
rst.FindFirst "[NBKID] = '[Forms]![Frm_UpdateAssociatesTime]![comboNBK]'"
rst.Edit
If [Forms]![Frm_UpdateAssociatesTime]![Combo6] = "Vacation" Then
    
    rst![Current Vacation Available] = Sum(rst![Current Vacation Available] - [Forms]![Frm_UpdateAssociatesTime]![Text14])
    rst![Vacation Used] = Sum(rst![Vacation Used] + [Forms]![Frm_UpdateAssociatesTime]![Text14])
    rst.Update

End If
rst.Close

Set rst = Nothing

[ranman256]

You dont need code, nor recordsets to a combo data.
put the SQL in a query, and assign the query to the combo.rowsource.

IF assigning a data value to a textbox , use a query and give it =Dlookup() , or DCount() in field.

No code needed.

[chims]

Seems alot easier lol.

Will that still work if I am trying to do calucations depending on the selection of [Forms]![Frm_UpdateAssociatesTime]![Combo6]? There are a possible of 3 selections (Vacation, Sick, Personal) and each one has their own field totals on Tbl_Associate.

[ranman256]

1. you should name your controls so they are readily understandable.. not combo6 (default name), but in the control property, other tab, name,
give it say cboVacaType, or lstStates, etc.


2. Your query would pull data based on the selection of combo6.

select * from tbl_associate where [vacaType] = '" & [Forms]![Frm_UpdateAssociatesTime]![Combo6] & "'"