Repaint method not working on the first entered record

[jmshipe]

I have a form that has a subform in it. The subform gets populated by a query.

When you enter data into the form and click a button it is supposed to add it to the subform.

It works partially right now. It is refreshing the subform, but it is one click too late.

Example if you enter entry 1 nothing happens when you enter entry 2 the subform then shows entry 1.

The repaint sub routine is: Expand|Select|Wrap|Line Numbers

1. Private Sub UpdaterxQuery()
2. [Form_Log Form].rxQuery.Requery
3. [Form_Log Form].Repaint
4. End Sub

_________________________________________

The whole form code is:
Expand|Select|Wrap|Line Numbers

1. Option Compare Database
2. Public Sub clearForm()
3. [Form_Log Form].aRxNumber.Value = ""
4. [Form_Log Form].aQuantity.Value = ""
5. [Form_Log Form].aDaySupply.Value = ""
6. [Form_Log Form].cLoanedMed.Value = ""
7. [Form_Log Form].aPatientName.Value = ""
8. [Form_Log Form].aHomeName.Value = ""
9. End Sub
10. Public Sub aRxNumber_AfterUpdate()
11. Dim patName, theHome, loggedBy As String
12. Dim rxNum, patId, homeId, theQuantity, daySupply As Long
13. Dim LoanedMeds As Boolean
14. LoanedMeds = Val(Nz([Form_Log Form].cLoanedMed.Value, False))
15. [Form_Log Form].cLoanedMed.Value = False
16. rxNum = Val(Nz([Form_Log Form].aRxNumber.Value, 0))
17. Dim sqlR, sqlP, sqlH, sqlD1, sqlD2, sqlD3, sqlL As DAO.Recordset
18. Dim strDB, objDB
19. strDB = CurrentProject.FullName
20. Set objDB = OpenDatabase(strDB)
21. Set sqlL = objDB.OpenRecordset("SELECT [UserID] FROM [USERLIST] WHERE [Logged In] = True")
22. loggedBy = sqlL![UserID]
23. Set sqlR = objDB.OpenRecordset("SELECT [PatientID] FROM [SCRIPTLIST] WHERE [RXID] = " & rxNum)
24. If sqlR.EOF And sqlR.BOF Then
25. Call clearForm
26. a = MsgBox("The Rx Number you entered does not exist in the database. ", vbOKOnly, "Error")
27. Set sqlR = Nothing
28. Set objDB = Nothing
29. Exit Sub
30. End If
31. patId = sqlR![PatientID]
32. Set sqlP = objDB.OpenRecordset("SELECT [Patient], [HouseID] FROM [PATLIST] WHERE [PatientID] = " & patId)
33. patName = sqlP![Patient]
34. patName = Trim(Replace(patName, vbTab, " "))
35. [Form_Log Form].aPatientName.Value = patName
36. homeId = sqlP![HouseID]
37. Set sqlH = objDB.OpenRecordset("SELECT [Home] FROM [HOMELIST] WHERE [HouseID] = " & homeId)
38. theHome = sqlH![Home]
39. [Form_Log Form].aHomeName.Value = theHome
40. End Sub
41. Private Sub bLogItem_Click()
42. theQuantity = Val(Nz([Form_Log Form].aQuantity.Value, 0))
43. daySupply = Val(Nz([Form_Log Form].aDaySupply.Value, 0))
44. LoanedMeds = Val(Nz([Form_Log Form].cLoanedMed.Value, False))
45. [Form_Log Form].cLoanedMed.Value = False
46. rxNum = Val(Nz([Form_Log Form].aRxNumber.Value, 0))
47. If rxNum = 0 Or theQuantity = 0 Or daySupply = 0 Then
48. a = MsgBox("Please enter a non-zero value for both the Rx Number, Day Supply and the Quantity.", vbOKOnly, "Error")
49. Exit Sub
50. End If
51. Dim CurDate As Long
52. CurDate = Date
53. Dim strDB, objDB
54. strDB = CurrentProject.FullName
55. Set objDB = OpenDatabase(strDB)
56. Set sqlL = objDB.OpenRecordset("SELECT [UserID] FROM [USERLIST] WHERE [Logged In] = True")
57. loggedBy = sqlL![UserID]
58. objDB.Execute "INSERT INTO [LOGLIST] (" & _
59. "[Log Date], [Rx Number], [Quantity], " & _
60. "[Day Supply], [Loaned Med?], [Logged By]) VALUES (" & _
61. CurDate & ", " & _
62. rxNum & ", " & _
63. theQuantity & ", " & _
64. daySupply & ", " & _
65. LoanedMeds & ", '" & _
66. loggedBy & "')"
67. Call clearForm
68. [Form_Log Form].aRxNumber.SetFocus
69. Call UpdaterxQuery
70. End Sub
71. Private Sub UpdaterxQuery()
72. [Form_Log Form].rxQuery.Requery
73. [Form_Log Form].Repaint
74. End Sub

[pbaldy]

I find the code hard to read that way, but try adding

If Me.Dirty Then Me.Dirty = False

before the Requery line, to make sure the displayed record has been saved.

[jmshipe]

Okay so I figured it out. All I needed was:
objDB.QueryDefs.Refresh

I still don't understand why requery and repaint aren't sufficient, but oh well, it works.

[pbaldy]

Glad you sorted it out. For the record, with lines like this:

Dim sqlR, sqlP, sqlH, sqlD1, sqlD2, sqlD3, sqlL As DAO.Recordset

You've declared 6 Variants and 1 Recordset. To declare each as a recordset:

Dim sqlR As DAO.Recordset, sqlP As DAO.Recordset, sqlH As DAO.Recordset, sqlD1 As DAO.Recordset, sqlD2 As DAO.Recordset, sqlD3 As DAO.Recordset, sqlL As DAO.Recordset

[jmshipe]

Ah, thank you. I will go ahead and correct that

[pbaldy]

No problem. Your code will usually still work, since a Variant can be anything, but I prefer declaring them as what they're supposed to be.