Try dropping the BetaData designators AND change the conjuction within the
criteria to AND versus the ampersand (&).
To test that this looks like a WHERE statement without the word where, you might
use a Debug.Print statement or a MsgBox to display the statement while you are
debugging. Once it looks right, then you can remove the debug.print or msgbox
statement.
vFinder = "[Channel] ='" & sChannel & "'"
vFinder = vFinder & " AND [Product]='" & sProduct & "'"
vFinder = vFinder & " AND [BankPship]='" & sBankPship & "'"
Debug.Print vFinder
Stop
The recordset will have the fieldname, but unless you have included two fields
with the same name from different tables via a query, the tablename WILL NOT be
part of the fieldname in the recordset. Since your source for the bRs is a
table (BetaData), there are no duplicate field names and therefore ...
Steve wrote:
>
> Hi EveryBody:
> I wrote this code, and its not working...give me
> error....
>
> betaData.Channel is not a valid field...eventhough its
> a field in the table... any help...
>
> code is here
>
> Option Compare Database
> Option Explicit
>
> Private db As DAO.Database
> Private bRs As DAO.Recordset
> Private cRs As DAO.Recordset
>
> Private sChannel As String
> Private sProduct As String
> Private sBankPship As String
> Private sFincProduct As String
> Private dCostCenter As Double
>
> Private vFinder As Variant
>
> Sub Assign_CostCenter()
>
> Set db = CurrentDb
> Set bRs = db.OpenRecordset("betaData", dbOpenDynaset)
> Set cRs = db.OpenRecordset("tblAssginCostCenter",
> dbOpenDynaset)
>
> On Error GoTo ErrorHandler
>
>
> With cRs
>
> If .RecordCount > 0 Then
> .MoveFirst
> Else
> Resume CloseSub
> End If
>
>
> While Not .EOF
>
> sChannel = !Channel
> sProduct = !Product
> sBankPship = !BankPship
> dCostCenter = !CostCenter
>
>
> vFinder = "([betaData].[Channel]
> ='" & sChannel & "' & "
> vFinder = vFinder & " [betaData].
> [Product]='" & sProduct & "' & "
> vFinder = vFinder & " [betaData].
> [BankPship]='" & sBankPship & "' )"
>
>
> With bRs
> If .RecordCount > 0 Then
> .MoveFirst
> Else
> Resume CloseSub
> End If
>
> While Not .EOF
>
> .FindFirst vFinder
>
> .Edit
> !CostCenter =
> dCostCenter
> .Update
>
> .MoveNext
>
> Wend
> End With
>
> .MoveFirst
> Wend
> End With
>
> CloseSub:
>
> cRs.Close
> bRs.Close
> db.Close
>
>
> Set cRs = Nothing
> Set bRs = Nothing
> Set db = Nothing
>
> Exit Sub
>
> ErrorHandler:
>
> MsgBox Err.Description, vbCritical, Err.Number
> Exit Sub
>
>
> End Sub
>
> Thanking you in advance for your help.
|
Similar Threads
