Microsft Access Error 94 when updating a row

[hbarracks]

thread702-1273482

I recently added a row in the CTRDistributionMap table and when I try to process the data I keep getting error 94. None of the definitions of the fields in the table are defined as not null only, those that are allow duplicates. It is as soon as it executes the .Update command that the application fails.

The actual data in the fields it was using when it got the error are as follows:

Category = Alcohol Manufacturer
SeqNbr = 1
type = Registration
Distribution = w
beer percent = 0
beer prog = null
winePercent = 1
wine prog = 07364
liquor percent = 0
liquor prog = null


LoadCTRDistributionMap = True ' default

Set db = CurrentDb

strSQL = "SELECT * FROM " & cCTRDistributionMap
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

With rs
rsAccess.MoveFirst
Do Until rsAccess.EOF ' from the table in ChartOfAccounts


strDistCategory = rsAccess!Category
iDistSeqNbr = rsAccess!SeqNbr
strDistType = rsAccess!Type
strDistDistribution = rsAccess!Distribution
sglDistBeerPercent = rsAccess!BeerPercent
strDistBeerProg = rsAccess!BeerProg
sglDistWinePercent = rsAccess!WinePercent
strDistWineProg = rsAccess!WineProg
sglDistLiquorPercent = rsAccess!LiquorPercent
strDistLiquorProg = rsAccess!LiquorProg

.AddNew ' to the table in this db

!Category = strDistCategory
!SeqNbr = iDistSeqNbr
!Type = strDistType
!Distribution = strDistDistribution
!BeerPercent = sglDistBeerPercent
!BeerProg = strDistBeerProg
!WinePercent = sglDistWinePercent
!WineProg = strDistWineProg
!LiquorPercent = sglDistLiquorPercent
!LiquorProg = strDistLiquorProg

.Update
rsAccess.MoveNext
Loop
End With

Exit_LoadCTRDistributionMap:
Exit Function

Err_LoadCTRDistributionMap:
MsgBox "Load CTR Distribution Map Error: " & Err.Number & vbCrLf & Err.Description
Resume Next
LoadCTRDistributionMap = False

End Function

 

[MajP]

So these fields are?
1) Not required
2) Not part of an index
3) Allow zero length if text.

How about just wrapping everything and providing a zero length string instead of a null?

strDistBeerProg = Nz(rsAccess!BeerProg,"")

 

[dhookom]

Is there a reason why you are using recordsets and not an append query?

You aren't providing your code that creates the variables or other recordset. I assume you have set Option Explicit and your code compiles.

Duane
Hook'D on Access
MS Access MVP