Help with Adding Records to Table

[penndro]

I could use some help trying to determine why this code is not adding the records to the table:

When i run this I get null values... it should fill bx1, bx2, ect. based on the Nmbr field in the table.

Sub AddBalls()

Dim Bx1 As String
Dim Bx2 As String
Dim Bx3 As String
Dim Bx4 As String
Dim picNmbr As String
Dim DrType As String


Dim MyR As Recordset
Set MyR = CurrentDb.OpenRecordset("History")
MyR.MoveFirst

Do Until MyR.EOF


MyR.AddNew
MyR![Nmbr] = [picNmbr]
MyR![Draw] = [DrType]

Select Case Len(MyR![Nmbr])

Case 3
Bx1 = Trim(Left(myNumber, 1))
Bx2 = Trim(Right(Left(myNumber, 2), 1))
Bx3 = Trim(Right(myNumber, 1))

Case 4
Bx1 = Trim(Left(myNumber, 1))
Bx2 = Trim(Right(Left(myNumber, 2), 1))
Bx3 = Trim(Left(Right(myNumber, 2), 1))
Bx4 = Trim(Right(myNumber, 1))

End Select

'Fill in Missing Balls


Select Case MyR![Draw]

Case "P4"
MyR![B1] = Trim([Bx1])
MyR![B2] = [Bx2]
MyR![B3] = [Bx3]
MyR![B4] = Nz([Bx4])
Case "C3"
MyR![B1] = Trim([Bx1])
MyR![B2] = [Bx2]
MyR![B3] = [Bx3]

End Select

MyR.Update
MyR.MoveNext

Loop


End Sub

 

[jrbarnett]

Hello

You declare the variable PicNmbr at the top of your sub (Dim picNmbr As String), but don't assign it a value before using it after the .AddNew row to assign the (presumably) key values. This explains why your rows get null values added.

You should also be able to get a significant performance boost by rewriting the loop of .addnew rows as an SQL insert query.

John