Help Please - Access 2000 VBA

statto

Hi. I hope someone can help me as Access 2000 problem that's driving me mad. By the way I am not an Access or VB programmer I just dabble now and again - in fact I program Cobol on AS/400s. Anyway I was asked to do a database to help manage backup tapes. I have a form with to sub-forms on it. The first sub form shows available tapes. The users select a tape and click a button that will add a record to the second sub-form. At the end the user will click the confirm button to have the selected tapes ordered. The problem is that the second sub form is not been refreshed. It's only after selection a couple of records from the first sub form that the second sub form will display all the selected records. I am using the ADO methods to add the new records. I have tried refreshes; requeries, repaints all to no avail. In the end I have put in a loop to do the re-query 1000 times and eventually the new record appears on the second sub form - not very professional!!! I have searched various Access and VB websites with no luck. I remember last year I had a similar problem and I came across something the suggested that it was a buffer problem or a record commit problem. But that project was abandoned and I never resolved the issue. I am sure that my method of adding the record is causing the problem. So any suggestions or pointers would be very grateful. I have pasted the code below in case it helps.

Sorry about the rambling post.


Private Sub CmdRequest_Click()

If Me![TapeSub].Form.Status <> "Active" Then
MsgBox "Invalid Status - Tape not Ordered", 48, "Tape not Ordered"
Exit Sub
End If

If Me![TapeSub].Form.Location <> "Off Site" Then
MsgBox "Invalid Location - Tape not Ordered", 48, "Tape not Ordered"
Exit Sub

End If

Me![OrderSub].SetFocus

Dim CurConn As New ADODB.Connection
Dim rstTempOrderDet As New ADODB.Recordset



Dim strCnn As String
Dim booRecordAdded As Boolean

Set Curdb = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data source= " & Curdb.Name
.Open
End With

Set rstTempOrderDet = New ADODB.Recordset
rstTempOrderDet.CursorType = adOpenDynamic
rstTempOrderDet.LockType = adLockOptimistic
rstTempOrderDet.Open "[TempOrderDet]", CurConn, , , adCmdTable


With rstTempOrderDet

mysel = ("BarCode " & " = " & Me![TapeSub].Form.[BarCode])
If Not .EOF Then

.MoveFirst
.Find mysel
If Not .EOF Then
MsgBox "Tape Already Requested"
Exit Sub
End If
End If
.AddNew
![BarCode] = Me![TapeSub].Form.[BarCode]
![Rotation] = Me![TapeSub].Form.[Rotation]
![Description] = Me![TapeSub].Form.[Description]
![Status] = "Requested"
![Comment] = Null
.Update


End With


rstTempOrderDet.Save
'rstTempOrderDet.flush
rstTempOrderDet.Requery
'rstTempOrderDet.Resync
rstTempOrderDet.Close

CurConn.Close

Dim xcount As Double
xcount = 1
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
MySQL = "SELECT * FROM [TempOrderDet]"
MyRecordSource = MySQL
xcount = 1
While xcount < 1000
xcount = xcount + 1
'Me!OrderSub.SetFocus
'Set Me.OrderSub.Form.Recordset = MySQL
Me.[OrderSub].Requery
'Me![OrderSub].Resync
'Me!OrderSub.SetFocus
'Me!OrderSub.Requery
'Me.Refresh
'Me.Repaint
'Me.Requery
DoCmd.RepaintObject
Wend

End Sub