Rewrite to Add field values to existing record 1

[josephwc]

Hello,
In a Microsoft XP module
I currently have two procedures that are adding values to a table called "Project Function". The way these two procedures are currently designed two seperate records are being created to enter all field data into the table when I want these two functions to put the field values designated in the same record. Can anyone tell me how to modify the second procedure to place all the field values in the same record.

'********************************************************************************
' Procedure Name: AddFunctionData
' Description: Save Function Data (from Project) into a separate Table
' Input Parameters: pcode (string): Project Code
' fname (string): Function Name
' famount (string): Function Amount (include Zero values)
'
' Return Parameters: none
'********************************************************************************

Private Sub AddFunctionData(pCode As String, fcode As String, fvalue As String)
Dim db As DAO.Database
Dim rec1, rds As DAO.Recordset
Dim sQuery As String

On Error GoTo ErrorHandler

' Check if function is part of static data...
sQuery = "SELECT code FROM StaticData WHERE staticDataType = 'functionName' " & _
"AND code = '" & fcode & "' "

Set rds = CurrentDb.OpenRecordset(sQuery)

If rds.RecordCount = 1 Then

If fvalue <> "" Then
Set db = CurrentDb()
Set rec1 = db.OpenRecordset("ProjectFunction")

rec1.AddNew
rec1!internalProjectId = Val(pCode)
rec1!FunctionCode = fcode
rec1!FunctionValue = fvalue


rec1.Update

rec1.Close

End If
End If

rds.Close

ExitHere:
Exit Sub

ErrorHandler:
' Just attach the function name and keep raising the error
Err.Description = "AddFunctionData:" & Err.Description
Err.Raise (Err.Number)
End Sub
'********************************************************************************
' Procedure Name: AddFunctionDataForecast
' Description: Save Function Data (from Project) into a separate Table
' Input Parameters: pcode (string): Project Code
' fname (string): Function Name
'
' fforecast (string): Function Forecast
' Return Parameters: none
'********************************************************************************

Private Sub AddFunctionDataForecast(pCode As String, fcode As String, fforecast As String)
Dim db As DAO.Database
Dim rec1, rds As DAO.Recordset
Dim sQuery As String

On Error GoTo ErrorHandler

' Check if function is part of static data...
sQuery = "SELECT code FROM StaticData WHERE staticDataType = 'functionName' " & _
"AND code & ' Forecast' = '" & fcode & "' "

Set rds = CurrentDb.OpenRecordset(sQuery)

If rds.RecordCount = 1 Then

If fforecast <> "" Then
Set db = CurrentDb()
Set rec1 = db.OpenRecordset("ProjectFunction")

rec1.AddNew
rec1!internalProjectId = Val(pCode)
rec1!past = Left(fforecast, 3)
rec1!jan2006 = Mid(fforecast, 4, 3)
rec1!feb2006 = Mid(fforecast, 7, 3)
rec1!mar2006 = Mid(fforecast, 10, 3)
rec1!apr2006 = Mid(fforecast, 13, 3)
rec1!may2006 = Mid(fforecast, 16, 3)
rec1!jun2006 = Mid(fforecast, 19, 3)
rec1!jul2006 = Mid(fforecast, 22, 3)
rec1!aug2006 = Mid(fforecast, 25, 3)
rec1!sep2006 = Mid(fforecast, 28, 3)
rec1!oct2006 = Mid(fforecast, 31, 3)
rec1!nov2006 = Mid(fforecast, 34, 3)
rec1!dec2006 = Mid(fforecast, 37, 3)
rec1!jan2007 = Mid(fforecast, 40, 3)
rec1!feb2007 = Mid(fforecast, 43, 3)
rec1!mar2007 = Mid(fforecast, 46, 3)
rec1!apr2007 = Mid(fforecast, 49, 3)
rec1!may2007 = Mid(fforecast, 52, 3)
rec1!jun2007 = Mid(fforecast, 55, 3)
rec1!jul2007 = Mid(fforecast, 58, 3)
rec1!aug2007 = Mid(fforecast, 61, 3)
rec1!sep2007 = Mid(fforecast, 64, 3)
rec1!oct2007 = Mid(fforecast, 67, 3)
rec1!nov2007 = Mid(fforecast, 70, 3)
rec1!dec2007 = Mid(fforecast, 73, 3)
rec1!future = Right(fforecast, 3)


rec1.Update

rec1.Close

End If

End If

rds.Close

ExitHere:
Exit Sub

ErrorHandler:
' Just attach the function name and keep raising the error
Err.Description = "AddFunctionDataForecast:" & Err.Description
Err.Raise (Err.Number)
End Sub

 

[Zion7]

First, the WHERE criteria, must be the same in both recordsets, so the recordset cursor, points to the SAME record. It's not the case here...

WHERE staticDataType = 'functionName' " & _
"AND code & ' Forecast' = '" & fcode & "' "

WHERE staticDataType = 'functionName' " & _
"AND code = '" & fcode & "' "



Secondly, remove "rec1.AddNew" from 2nd Procedure, so that it UPDATES the Selected record(s), not INSERTS.

 

[josephwc]

I removed the rec1.AddNew and was prompted to have either this statement or "edit" back in. I tried .edit and got an error as well. Here is the code that these two procedures fit in. Maybe it will help.

Dim sFunctionValue As String
Dim sFunctionForecast As String

sFunctionValue = ""
sFunctionForecast = ""

' Get the custom fields
Set objCustomFieldList = pobjNode.selectSingleNode("customFields").childNodes

For i = 0 To objCustomFieldList.length - 1
Set objCustomField = objCustomFieldList.nextNode

Set objAttributes = objCustomField.Attributes
sCustomFieldName = objAttributes.getNamedItem("name").nodeValue

Select Case sCustomFieldName

Case "Tower Group Forecast"
sFunctionForecast = XML_GetChildNodeValue(objCustomField, "value")
Case "WCS EMS Forecast"
sFunctionForecast = XML_GetChildNodeValue(objCustomField, "value")
Case "WCS ENH Forecast"
sFunctionForecast = XML_GetChildNodeValue(objCustomField, "value")
Case "WCS Fee Srvcs Forecast"
sFunctionForecast = XML_GetChildNodeValue(objCustomField, "value")
Case Else
'Retrieve Function Custom Field Value...
sFunctionValue = XML_GetChildNodeValue(objCustomField, "value")
End Select

If Not IsNull(sFunctionValue) Or (sFunctionValue <> "") Then
Call AddFunctionData("9999999", sCustomFieldName, sFunctionValue)
sFunctionValue = ""
End If


If Not IsNull(sFunctionForecast) Or (sFunctionForecast <> "") Then
Call AddFunctionDataForecast("9999999", sCustomFieldName, sFunctionForecast)
sFunctionForecast = ""
End If
Next

 

[Zion7]

Sorry Joseph, I was thinking ADO.

Yes, you need ...

rec1.Edit
....
rec1.Update

But, you said EDIT, gives error.
What is the error?

 

[josephwc]

I don't remember the error and I found a work around in the mean time. I created a second table to temporarily store the forecast values. I then have an update query run which connects the two tables. Thanks for your help.

 

[Zion7]

Excuse my persistence but, this problem sounds very straightforward, & you're definately taking too many steps to achieve your objective.

I made a mistake, by refering to the wrong recordset, when I suggested you must sync, you recordset cursor.

...
strSQL = "SELECT * FROM ProjectFunction WHERE internalProjectId = " & Val(pCode)
.
If fforecast <> "" Then
Set db = CurrentDb()
Set rec1 = db.OpenRecordset(strSQL)
rec1.Edit
rec1!internalProjectId = Val(pCode)
rec1!past = Left(fforecast, 3)
rec1!jan2006 = Mid(fforecast, 4, 3)
...
rec1.Update


Small point when declaring variables on same line.
ie; Dim rec1, rds As DAO.Recordset

Default data type in VBA, is variant.
if you don't specify, your variable becomes a variant data type. WHETHER on the same line as another, or not!

secondly, why open a recordset to check a single value/count etc..
Use an aggregate function...

Dim varCode As Variant
' Check if function is part of static data...
varCode = DCount("code","StaticData","staticDataType = 'functionName' " & _
"AND code & ' Forecast' = '" & fcode & "' ")

If varcode = 1 Then

If fforecast <> "" Then

 

[josephwc]

that makes sense. Thanks alot!