By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,952 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Subform not repainting for first entry

P: 9
I have a form with a subform. The form has values that add a record to a table. The subform is populated by a query that draws from the table. I need the subform to update when you add a new record.

The problem that I'm having is that, when I open the form, the first added record won't show up on the subform. On the second entry it shows the first and second records.

How do I get the refresh/repaint method to work for the first entry?

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public Sub clearForm()
  4. [Form_Log Form].aRxNumber.Value = ""
  5. [Form_Log Form].aQuantity.Value = ""
  6. [Form_Log Form].aDaySupply.Value = ""
  7. [Form_Log Form].cLoanedMed.Value = ""
  8. [Form_Log Form].aPatientName.Value = ""
  9. [Form_Log Form].aHomeName.Value = ""
  10. End Sub
  11.  
  12. Public Sub aRxNumber_AfterUpdate()
  13.  
  14. Dim patName, theHome, loggedBy As String
  15. Dim rxNum, patId, homeId, theQuantity, daySupply As Long
  16. Dim LoanedMeds As Boolean
  17. LoanedMeds = Val(Nz([Form_Log Form].cLoanedMed.Value, False))
  18. [Form_Log Form].cLoanedMed.Value = False
  19. rxNum = Val(Nz([Form_Log Form].aRxNumber.Value, 0))
  20.  
  21. Dim sqlR, sqlP, sqlH, sqlL As DAO.Recordset
  22. Dim strDB, objDB
  23. strDB = CurrentProject.FullName
  24. Set objDB = OpenDatabase(strDB)
  25.  
  26. Set sqlL = objDB.OpenRecordset("SELECT [UserID] FROM [USERLIST] WHERE [Logged In] = True")
  27. loggedBy = sqlL![UserID]
  28.  
  29. Set sqlR = objDB.OpenRecordset("SELECT [PatientID] FROM [SCRIPTLIST] WHERE [RXID] = " & rxNum)
  30. If sqlR.EOF And sqlR.BOF Then
  31.     Call clearForm
  32.     a = MsgBox("The Rx Number you entered does not exist in the database. ", vbOKOnly, "Error")
  33.     Set sqlR = Nothing
  34.     Set objDB = Nothing
  35.     Exit Sub
  36. End If
  37.  
  38. patId = sqlR![PatientID]
  39. Set sqlP = objDB.OpenRecordset("SELECT [Patient], [HouseID] FROM [PATLIST] WHERE [PatientID] = " & patId)
  40.  
  41. patName = sqlP![Patient]
  42. patName = Trim(Replace(patName, vbTab, " "))
  43. [Form_Log Form].aPatientName.Value = patName
  44.  
  45. homeId = sqlP![HouseID]
  46. Set sqlH = objDB.OpenRecordset("SELECT [Home] FROM [HOMELIST] WHERE [HouseID] = " & homeId)
  47. theHome = sqlH![Home]
  48. [Form_Log Form].aHomeName.Value = theHome
  49. Set sqlL = Nothing
  50. Set sqlR = Nothing
  51. Set sqlP = Nothing
  52. Set sqlH = Nothing
  53. Set objDB = Nothing
  54.  
  55.  
  56. End Sub
  57. Private Sub bLogItem_Click()
  58. theQuantity = Val(Nz([Form_Log Form].aQuantity.Value, 0))
  59. daySupply = Val(Nz([Form_Log Form].aDaySupply.Value, 0))
  60. LoanedMeds = Val(Nz([Form_Log Form].cLoanedMed.Value, False))
  61. [Form_Log Form].cLoanedMed.Value = False
  62. rxNum = Val(Nz([Form_Log Form].aRxNumber.Value, 0))
  63. If rxNum = 0 Or theQuantity = 0 Or daySupply = 0 Then
  64.     a = MsgBox("Please enter a non-zero value for both the Rx Number, Day Supply and the Quantity.", vbOKOnly, "Error")
  65.     Exit Sub
  66. End If
  67.  
  68. Dim CurDate As Long
  69. CurDate = Date
  70.  
  71. Dim strDB, objDB
  72. strDB = CurrentProject.FullName
  73. Set objDB = OpenDatabase(strDB)
  74. Set sqlL = objDB.OpenRecordset("SELECT [UserID] FROM [USERLIST] WHERE [Logged In] = True")
  75. loggedBy = sqlL![UserID]
  76.  
  77. objDB.Execute "INSERT INTO [LOGLIST] (" & _
  78.     "[Log Date], [Rx Number], [Quantity], " & _
  79.     "[Day Supply], [Loaned Med?], [Logged By]) VALUES (" & _
  80.     CurDate & ", " & _
  81.     rxNum & ", " & _
  82.     theQuantity & ", " & _
  83.     daySupply & ", " & _
  84.     LoanedMeds & ", '" & _
  85.     loggedBy & "')"
  86.  
  87. Set sqlL = Nothing
  88. Set objDB = Nothing
  89.  
  90. Call clearForm
  91. Call UpdaterxQuery
  92. [Form_Log Form].aRxNumber.SetFocus
  93.  
  94. End Sub
  95.  
  96. Private Sub UpdaterxQuery()
  97. [Form_Log Form].rxQuery.Requery
  98. [Form_Log Form].Repaint
  99.  
  100. End Sub
  101.  
  102. Private Sub Form_Load()
  103. [Form_Log Form].aRxNumber.InputMask = "000000;; :"
  104.  
  105. End Sub
  106.  
Aug 7 '12 #1
Share this Question
Share on Google+
1 Reply


zmbd
Expert Mod 5K+
P: 5,285
JmShipe:
It sounds as if your parent/child links are not setup correctly. If there are related records when you first open the form, then these should show in the subform. However, from what information you've given it is difficult to tell.

You may find the following useful:
Form-Subform Filtering

By looking over Neopa's tutorial it may give you some leads with your issue.

-z
Aug 7 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.