asked on Unbound subform will not requery correctly
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...
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
Microsoft Access
Last Comment
Loki
pDog ... oddly enough both
Me.sub1.Requery and sub1.Form.Requery work ... although you would *think* that only the 2nd way would work (with Form).
mx
Me.sub1.Requery and sub1.Form.Requery work ... although you would *think* that only the 2nd way would work (with Form).
mx
Wait a minute ... "Unbound subform" ??
mx
mx
ASKER
I've tried every different combination of:
me.sub1.requery
&
Me.sub1.Form.Requery
& even
Forms!frm_Generate!sub1.Se
MsgBox "Sub has focus"
Forms!frm_Generate!sub1.Fo
I don't think the problem is in the requery command, but I think that the table data is slow on updating. The screen scrape happens in a split second and I'm sure the records are written as quickly, but if I do a requery, it's hit and miss as to whether that data will be displayed in the subform. If it doesn't display, then I hit the design form button and go back to view form and the subform is filled properly. Like I said earlier...if I use the short pause before requerying, then it works right 9 times out of 10. So I don't think there's anything wrong with my requery method. I've just never had this timing issue before. I can't be sure if it's not Access 2007 related or what.
me.sub1.requery
&
Me.sub1.Form.Requery
& even
Forms!frm_Generate!sub1.Se
MsgBox "Sub has focus"
Forms!frm_Generate!sub1.Fo
I don't think the problem is in the requery command, but I think that the table data is slow on updating. The screen scrape happens in a split second and I'm sure the records are written as quickly, but if I do a requery, it's hit and miss as to whether that data will be displayed in the subform. If it doesn't display, then I hit the design form button and go back to view form and the subform is filled properly. Like I said earlier...if I use the short pause before requerying, then it works right 9 times out of 10. So I don't think there's anything wrong with my requery method. I've just never had this timing issue before. I can't be sure if it's not Access 2007 related or what.
Hi Mx,
It is odd that they both work. I assume that Loki meant that he is using code in lieu of the master/child link provided by Access.
PDB
It is odd that they both work. I assume that Loki meant that he is using code in lieu of the master/child link provided by Access.
PDB
ASKER
I have a master child link:
txtInvoiceID (an unbound text box that gets filled in when the form first opens) to InvoiceID in the subform.
txtInvoiceID (an unbound text box that gets filled in when the form first opens) to InvoiceID in the subform.
Loki,
Try turning screen painting off (Me.Painting = False) and on (Me.Painting = True) where your code is generating the heaviest screen activity and see if that helps the timing.
PDB
Try turning screen painting off (Me.Painting = False) and on (Me.Painting = True) where your code is generating the heaviest screen activity and see if that helps the timing.
PDB
Is there a way you can check to be sure the last record had actually been added in the back end ... before you do the Requery, maybe using the LastModified property ... although not sure that works with ADO. Also, why are you using ADO instead of DAO ?
mx
mx
ASKER
I'll try the repaint.
I'm using ADO because it is more curent and I'm also looking to eventually write the data to a SQL table and ADO is better for that. I can check that the record has been written, but these are tiny records and I'm writting two of them...not even to a back end, but to a table within the database. There is no way that this should take as long as it does.
I'm using ADO because it is more curent and I'm also looking to eventually write the data to a SQL table and ADO is better for that. I can check that the record has been written, but these are tiny records and I'm writting two of them...not even to a back end, but to a table within the database. There is no way that this should take as long as it does.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I stuck a query on the recordsource to check if the data has been written and my confirmation comes up immedeately But it's still taking forever (about 5 seconds) to show up and sometimes it doesn't show up at all. I may try re-writing it in DAO tomorrow to see if that makes a difference.
ASKER
O.K...I re-wrote the code in DAO and now the requery works perfectly. I don't know why this is. I have several other databases (wrtitten in Access XP) that are ADO and I don't have this problem. For some reason, Access 2007 seems to be taking an inordinately long time to make the conversion between ADO and DAO. I hope this gets fixed in the future.
I'll give the points to DbMX for pointing me down the right path. Thanks for all of your input guys.
I'll give the points to DbMX for pointing me down the right path. Thanks for all of your input guys.
Me.sub1.Form.Requery