UNEXPLAINED ERROR IN VB CODE

[DIVINEDAR0956]

I have the following function in Access and can't seem to understand why I'm getting an error that I don't understand. The function is as follows:

Public Function EditMarkupPercentage()
Dim rst As ADODB.Recordset
Dim i As Integer
Dim strTblName As String
Dim strNewFormulaValue As Variant
Dim strNewMTLValue As Variant
Dim strNewLBRValue As Variant
Dim strNewREPValue As Variant
Dim strNewNEGValue As Variant

Set rst = New ADODB.Recordset


strNewFormulaValue = InputBox("Enter New Formula GM or MU&quot
strNewMTLValue = InputBox("Enter New Material Percentage&quot
strNewLBRValue = InputBox("Enter New Labor Percentage&quot
strNewREPValue = InputBox("Enter New Rep Fee Percentage&quot
strNewNEGValue = InputBox("Enter New Negotiation Fee Percentage&quot


' DoCmd.OpenQuery "DltMarkupPercentage", acViewNormal, acEdit

For i = 1 To 500

strTblName = Choose(i, "Markup&quot
rst.Open "Select [FORMULA] from " & strTblName & ";", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
While Not rst.EOF
rst![FORMULA] = strNewFormulaValue
rst.Update
rst.MoveNext
Wend
rst.Close

rst.Open "Select [MTLMUP] from " & strTblName & ";", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
While Not rst.EOF
rst![MTLMUP] = strNewMTLValue
rst.Update
rst.MoveNext
Wend
rst.Close

rst.Open "Select [LBRMUP] from " & strTblName & ";", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
While Not rst.EOF
rst![LBRMUP] = strNewLBRValue
rst.Update
rst.MoveNext
Wend
rst.Close

rst.Open "Select [REPFEEMUP] from " & strTblName & ";", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
While Not rst.EOF
rst![REPFEEMUP] = strNewREPValue
rst.Update
rst.MoveNext
Wend
rst.Close

rst.Open "Select [NEGOTFACTORMUP] from " & strTblName & ";", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
While Not rst.EOF
rst![NEGOTFACTORMUP] = strNewNEGValue
rst.Update
rst.MoveNext
Wend
rst.Close

Next i


End Function

The error is "INVALID USE OF NULL" and I can't seem to make heads or tails of it.

It stops at this line "strTblName = Choose(i, "Markup&quot". I checked the Markup table and I find no null spaces.

Could it be my "i" issue. There is only 43 records in the table and I have it checking up to 500. I not sure how that works yet either.

Someone please help. Darlene Sippio
dsippio@comtechsystems.com

 

[RobCPA]

It's happening because the variable strTblName is set to string rather than variant. A string variable cannot contain a null value while the variant variable can. You are right in that the problem is occurring because the return value for i greater than 43 is going to be null. You're solution would be to change the type string to variant, or better yet, change the 500 in the for loop to the number of records in the table.

Post again if you have further questions.

 

[DIVINEDAR0956]

My problem on changing the number of records in a set is that there is a possibility that the table could hold up to 500 records in one set. Is there a variable or something I can set for an unlimited number of records. I changed string to variant.

One more question for you. How do you set a variable to a table or a field in a table?

Thank you for your help. Darlene Sippio
dsippio@comtechsystems.com