troubleshooting Question

Unbound subform will not requery correctly

Avatar of Loki
LokiFlag for United States of America asked on
Microsoft Access
12 Comments1 Solution1089 ViewsLast Modified:
I've searched through all of the solutions so far and haven't found one for my particular situation.

I have a database that I'm using to scrape invoice information from a mainframe screen and then write some of it to the main form and some of it to records, which are then displayed in a subform.  I'm not having any problems except for getting the subform to requery correctly.  Most times, the form stays blank after I send a me.sub1.requery command.  Sometimes it will work though.  I played around with it and figured out that it was a timing issue.  So I paused the code a bit before requery and it seems to work better, but it's not 100%.  Sometimes it still doesn't requery.  I never had a problem doing this kinda' thing on Access XP, but I recently got a new PC and it came with Access 2007.  Ever since, the littlest things have been messing me up.

Here is the code I'm using...

'Write Invoice Data records
    Set rst1 = New ADODB.Recordset
    rst1.Open "tbl_InvoiceData", conn, , adLockOptimistic
    rst1.AddNew
    rst1("InvoiceID") = strInvoiceID
    rst1("SalesOrderNumber") = Me.txtSONumber
    rst1("ReleaseNumber") = Me.txtRelNumber
    strPO = Trim(sess0.Screen.getstring(3, 41, 30))
    StripString (strPO)
    strPO = strHoldString
    rst1("CustomerPONumber") = strPO
    rst1("LineNumber") = ""
    rst1("Qty") = Trim(sess0.Screen.getstring(23, 16, 7))
    rst1("ItemNumber") = Trim(sess0.Screen.getstring(20, 9, 25))
    rst1("ItemDescription") = ""
    rst1("UnitValue") = Trim(sess0.Screen.getstring(20, 40, 13))
    rst1("ExtendedPrice") = Trim(sess0.Screen.getstring(21, 70, 11))
    'Get external data
    'Call ExternalData
    rst1("HarmonicCode") = ""
    rst1("HarmonicDescription") = ""
    rst1("CountryofOrigin") = ""
    rst1.Update
       
    'Check for "MORE" invoice data records
    strTest = Trim(sess0.Screen.getstring(2, 62, 4))
    If strTest = "MORE" Then
        intMore = 1
        w = 1
200
        X = 7
        sess0.Screen.SendKeys ("<pf8>")
        sess0.Screen.WaitForCursor 2, 14
        sess0.Screen.SendKeys (" ")
        sess0.Screen.WaitForCursor 2, 15
        For i = 1 To 3
            'Add new item
            strTest = Trim(sess0.Screen.getstring(X, 9, 25))
            'If IsNull(strTest) Then
            If strTest = "" Then
                Exit For
            End If
            rst1.AddNew
            rst1("InvoiceID") = strInvoiceID
            rst1("SalesOrderNumber") = Me.txtSONumber
            rst1("ReleaseNumber") = Me.txtRelNumber
            rst1("CustomerPONumber") = strPO
            rst1("LineNumber") = ""
            rst1("Qty") = Trim(sess0.Screen.getstring((X + 3), 16, 7))
            rst1("ItemNumber") = Trim(sess0.Screen.getstring(X, 9, 25))
            rst1("ItemDescription") = ""
            rst1("UnitValue") = Trim(sess0.Screen.getstring(X, 40, 13))
            rst1("ExtendedPrice") = Trim(sess0.Screen.getstring((X + 1), 70, 11))
            'Get external data
            'Call ExternalData
            rst1("HarmonicCode") = ""
            rst1("HarmonicDescription") = ""
            rst1("CountryofOrigin") = ""
            rst1.Update
            
            X = X + 5
        Next i
        strTest = Trim(sess0.Screen.getstring(2, 62, 4))
        If strTest = "MORE" Then
            GoTo 200
        End If
    End If
Else
    MsgBox "There is no data to scrape." & vbCrLf & " " & vbCrLf & "Please check to see if you've entered the correct sales order info."
End If
 
sess0.Screen.SendKeys ("<pf3>")
 
'Pause and requery
For intWait = 1 To 3000
    DoEvents
Next intWait
Me.sub1.Requery
 
rst1.Close
conn.Close
ASKER CERTIFIED SOLUTION
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Database Architect / Application Developer
Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros