Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Help Please - Access 2000 VBA

Options
  • 05-10-2005 2:15pm
    #1
    statto
    Closed Accounts Posts: 92 ✭✭


    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


Advertisement