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

Having trouble with requery

P: 9
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
  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
  12. Public Sub aRxNumber_AfterUpdate()
  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))
  21. Dim sqlR, sqlP, sqlH, sqlD1, sqlD2, sqlD3, sqlL As DAO.Recordset
  22. Dim strDB, objDB
  23. strDB = CurrentProject.FullName
  24. Set objDB = OpenDatabase(strDB)
  26. Set sqlL = objDB.OpenRecordset("SELECT [UserID] FROM [USERLIST] WHERE [Logged In] = True")
  27. loggedBy = sqlL![UserID]
  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
  38. patId = sqlR![PatientID]
  39. Set sqlP = objDB.OpenRecordset("SELECT [Patient], [HouseID] FROM [PATLIST] WHERE [PatientID] = " & patId)
  41. patName = sqlP![Patient]
  42. patName = Trim(Replace(patName, vbTab, " "))
  43. [Form_Log Form].aPatientName.Value = patName
  45. homeId = sqlP![HouseID]
  46. Set sqlH = objDB.OpenRecordset("SELECT [Home] FROM [HOMELIST] WHERE [HouseID] = " & homeId)
  48. theHome = sqlH![Home]
  49. [Form_Log Form].aHomeName.Value = theHome
  50. End Sub
  51. Private Sub bLogItem_Click()
  52. theQuantity = Val(Nz([Form_Log Form].aQuantity.Value, 0))
  53. daySupply = Val(Nz([Form_Log Form].aDaySupply.Value, 0))
  54. LoanedMeds = Val(Nz([Form_Log Form].cLoanedMed.Value, False))
  55. [Form_Log Form].cLoanedMed.Value = False
  56. rxNum = Val(Nz([Form_Log Form].aRxNumber.Value, 0))
  57. If rxNum = 0 Or theQuantity = 0 Or daySupply = 0 Then
  58.     a = MsgBox("Please enter a non-zero value for both the Rx Number, Day Supply and the Quantity.", vbOKOnly, "Error")
  59.     Exit Sub
  60. End If
  62. Dim CurDate As Long
  63. CurDate = Date
  65. Dim strDB, objDB
  66. strDB = CurrentProject.FullName
  67. Set objDB = OpenDatabase(strDB)
  68. Set sqlL = objDB.OpenRecordset("SELECT [UserID] FROM [USERLIST] WHERE [Logged In] = True")
  69. loggedBy = sqlL![UserID]
  71. objDB.Execute "INSERT INTO [LOGLIST] (" & _
  72.     "[Log Date], [Rx Number], [Quantity], " & _
  73.     "[Day Supply], [Loaned Med?], [Logged By]) VALUES (" & _
  74.     CurDate & ", " & _
  75.     rxNum & ", " & _
  76.     theQuantity & ", " & _
  77.     daySupply & ", " & _
  78.     LoanedMeds & ", '" & _
  79.     loggedBy & "')"
  81. Call clearForm
  82. [Form_Log Form].aRxNumber.SetFocus
  83. Call UpdaterxQuery
  84. End Sub
  86. Private Sub UpdaterxQuery()
  87. [Form_Log Form].rxQuery.Requery
  88. [Form_Log Form].Repaint
  90. End Sub
Aug 6 '12 #1
Share this Question
Share on Google+
9 Replies

Expert Mod 2.5K+
P: 3,284

Just before your call to update the query, try refreshing the form:

Expand|Select|Wrap|Line Numbers
  1. Me.Refresh
  2. Call UpdaterxQuery 
This will ensure that all values residing in the controls on the form are saved to the data tables. This may explain why when you enter data in one control it is not in the query, but a second entry shows the first entry.

Please let me know how this works.
Aug 6 '12 #2

P: 9
Thank You for the quick response, but no it didn't change anything.
Aug 6 '12 #3

P: 9
Okay so I added some code and improved the results, but It's still not quite right.
Expand|Select|Wrap|Line Numbers
  1. Set sqlL = Nothing
  2. Set objDB = Nothing
I wasn't closing the object Database. Now that I am the first record I try to add to the subform does not update correctly, but all subsequent records and fine. Any reason for the first one not working right? It's only the first record I try to add right after I opened the form.
Aug 6 '12 #4

Expert Mod 2.5K+
P: 3,284
I've been trying to work through some of your code, and although I may not have an answer for your specific question, I may be able to give some advice on some of your VBA coding practices that may make it a bit easier for you and easier to follow for others who review it.

First, I notice whenever you refer to controls on your form, you are using the form name and then the value property. To save you some time, this can be shortened with the "Me" keyword. For example the two lines of code below:

Expand|Select|Wrap|Line Numbers
  1. [Form_Log Form].aRxNumber.Value = ""
  2. Me.aRxNumber = ""
Render the exact same result. Your method is not wrong, but this may save some key strokes.

Second, I notice you do not use the Option Explicit statement at the top of your module. I always recommend you include that statement, right after your Option Compare Database statement. This forces you to declare any variables you use before you use them. This is just a safeguard for your coding.

Speaking of variables, I also notice you group your variables by type. In older versions of VBA, you were allowed to do this with no problems. However, from what I understand, with newer versions of VBA, doing this will just set aside memory for the variables, but not declare the actual type until a value is assigned to it. Just the last variable would get assigned a specific variable type, all others would be Variants. I wish we could still do it the old way.

However, the "better" way is to declare each variable individually, setting its data type. So, again, the code below demonstrates this:

Expand|Select|Wrap|Line Numbers
  1. Dim patName, theHome, loggedBy As String
  3. 'Should be:
  4. Dim patName as String
  5. Dim theHome as String
  6. Dim loggedBy as String
This method does NOT save you keystrokes, but ensures your variables are properly declared.....

Next, I notice your overuse of the Val() function. Val() should be reserved for converting string numbers into numbers (i.e. '123' and 123 mean different things to the DB). You also use the Nz() function, often when referring to things that shouldn't ever be Null (such as a Yes/No field--unless you use it in triple state, which is not very common). These extraneous functions are really just a waste of processor power and, in the case of very large databases and intense code, could really slow things down. So, the code below demonstrates more streamlining:

Expand|Select|Wrap|Line Numbers
  1. Dim LoanedMeds As Boolean
  2. LoanedMeds = Val(Nz([Form_Log Form].cLoanedMed.Value, False))
  3. [Form_Log Form].cLoanedMed.Value = False
  5. 'Could be shortened to:
  6. Dim LoanedMeds As Boolean
  7. LoanedMeds = Me.cLoanedMed
  8. Me.cLoanedMed = False
One minor point has to do with the use of the MsgBox as a variable. See below:

Expand|Select|Wrap|Line Numbers
  1. a = MsgBox("The Rx Number you entered does not exist in the database. ", vbOKOnly, "Error")
I would ercommend not assigning the result of a Message Box to a variable unless you are going to do something with that variable. This can easily be done by removing the parentheses from around the function, like so:

Expand|Select|Wrap|Line Numbers
  1. MsgBox "The Rx Number you entered does not exist in the database. ", vbOKOnly, "Error"
This produces the same result--a message box pops up--but, since the result of the message box doesn't drive any other actions, you don't have to assign the value to a variable.

Finally, I notice many unconventional naming conventions in your code. Again, there is nothing "wrong" with calling tables, fields and variables whatever you choose. However, to assist others who may have to one day look at your database to troubleshoot or analyze what you are doing, you may want to come more in line with certain standards that have become more recognized in the community. One such naming convention can be found here:

There are many others, just do an internet search for database naming conventions. Do some research and choose a style that suits you. The reason I bring this up is you have several variables declared as DAO.Recordsets. However, the variables are called sqlR, sqlP, sqlH, sqlD1, sqlD2, sqlD3, sqlL, which to the average person doesn't mean anything, and may imply that the variable actually holds a string SQL statement.

In general, we name recordsets rstXxxx and strings as strXxxx. A common string you will find in VBA recordset coding is strSQL which is always understood as the string holding a SQL statement (i.e. "SELECT * FROM tblPatients;").

Again, nothing "wrong" with the way you're doing things, just a bit confusing at first glance.

Hope this doesn't overwhelm, and certainly hope you don't take any offense to my advice. This forum is for making us all better DB programmers, and some of these tips may pay off in the long run for you.

Hope this is useful.

Aug 7 '12 #5

P: 9
Thank you very much, I will go ahead and make your suggested changes.
Aug 7 '12 #6

Expert Mod 5K+
P: 5,397

Twinnyfo is correct with the declaration change in VB; however I do not know if this went thru to VBA.

However, I'm very old school so I tend to like to group things logically so I do things like this:

Expand|Select|Wrap|Line Numbers
  1. Dim dbs As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
  2. Dim rs_1 As DAO.Recordset, s_rs1_field1 As String, l_rs1_field2 As Long
  3. Dim rs_2 As DAO.Recordset, s_rs2_field1 As String
  4. Dim rs_3 As DAO.Recordset, s_rs3_field1 As String
Thus, I get the logical grouping which helps me ensure that I have the variables I need and yet also ensure that they are explicitly declared... and I only type that Dim four times instead of 10 times

It also sounds like you have something not set correctly in your parent/child link... could upload a database with just the form and associated code?

Aug 7 '12 #7

Expert Mod 2.5K+
P: 3,284

Thanks for the clarification and the tip. I am sure that sooner or later one of us will be able to figure out why your subform query does not work properly.
Aug 7 '12 #8

P: 9
Turns out all I needed was:
I'm not sure why "requery" isn't sufficient
Aug 8 '12 #9

Expert Mod 5K+
P: 5,397
Vacation Brain and Been on vacation:

In OP first code block... requery and repaint is only happening for the "Form_Log Form." You were not doing anything to the subforms. I still suspect you have some issue with the parent/child field linking.

The refresh you're doing over the QueryDefs collection is hitting all of your defined queries so anything using those queries should also refresh too... Note that the Refresh doesn't actually requery your data... it doesn't show changes or deletions of records in the just takes the current stuff and reshows it...

Aug 8 '12 #10

Post your reply

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