473,320 Members | 1,920 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Subform not repainting for first entry

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
1 1449
zmbd
5,501 Expert Mod 4TB
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

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

Similar topics

1
by: Richard L Rosenheim | last post by:
I'm trying to create a style sheet to generate HTML output, based upon some XML data. I'm grouping some data, and I'm trying to get the very first entry either just passed as a parameter, or...
6
by: Paul H | last post by:
Hi I have a problem with certain users of my ASP.NET application. The user logs into the site, and they are logged into the application. Then when they select a link, they are presented with a...
2
by: christophe.leroquais | last post by:
Hi, I have a HashTable that can have the same keys. For example: Key: myKey1, myKey2, myKey3, myKey1 Value: "value1" , "value2" , "value3", "value4" That's ok and I'm happy with that....
3
by: bosmatthews | last post by:
I have a main form with a subform and a second subform nested to the first subform. The data entry property for all three forms (main, subform and sub-subform) is set to "yes" because I am intending...
0
by: Presto | last post by:
I have a Main form with a subform named Payments. I have a button on the main form named NewPaymentEntry that opens a small popup form to enter a new payment. There is a "Save" , "Cancel", and...
14
TheSmileyCoder
by: TheSmileyCoder | last post by:
I have a subform "frm_Obs" on my main form "frm_Main" If there is no records, I want the user to be able to add one. If there allready is one, I want the user to be able to change it, but not add...
7
by: bbobely | last post by:
Hi, I've built a sequence that works thusly: User chooses a value from a combo box. This causes a query to run displaying records with a field value matching the combo box in subform 1. Then, the...
3
by: Syed Khaleel Ahamed | last post by:
Dear sir, I am doing a project of employee attendance management system using jsp, servlets. In that project it will capture the date and time of that employee when he entered...
2
by: cambar | last post by:
When Access db is first opened and the first record is entered, tabbing on subform works properly. However, when subsequent records are entered, the subform retains memory of the last field filled in...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.