.FindFirst DAO

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

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.
 
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 ...
 
Back
Top