Having trouble with requery

[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:

Private Sub UpdaterxQuery()
[Form_Log Form].rxQuery.Requery
[Form_Log Form].Repaint
End Sub

_______________ _______________ ___________

The whole form code is:

Option Compare Database

Public Sub clearForm()
[Form_Log Form].aRxNumber.Value = ""
[Form_Log Form].aQuantity.Value = ""
[Form_Log Form].aDaySupply.Value = ""
[Form_Log Form].cLoanedMed.Value = ""
[Form_Log Form].aPatientName.Value = ""
[Form_Log Form].aHomeName.Value = ""
End Sub

Public Sub aRxNumber_AfterUpdate()

Dim patName, theHome, loggedBy As String
Dim rxNum, patId, homeId, theQuantity, daySupply As Long
Dim LoanedMeds As Boolean
LoanedMeds = Val(Nz([Form_Log Form].cLoanedMed.Value, False))
[Form_Log Form].cLoanedMed.Value = False
rxNum = Val(Nz([Form_Log Form].aRxNumber.Value, 0))

Dim sqlR, sqlP, sqlH, sqlD1, sqlD2, sqlD3, sqlL As DAO.Recordset
Dim strDB, objDB
strDB = CurrentProject.FullName
Set objDB = OpenDatabase(strDB)

Set sqlL = objDB.OpenRecordset("SELECT [UserID] FROM [USERLIST] WHERE [Logged In] = True")
loggedBy = sqlL![UserID]

Set sqlR = objDB.OpenRecordset("SELECT [PatientID] FROM [SCRIPTLIST] WHERE [RXID] = " & rxNum)
If sqlR.EOF And sqlR.BOF Then
    Call clearForm
    a = MsgBox("The Rx Number you entered does not exist in the database. ", vbOKOnly, "Error")
    Set sqlR = Nothing
    Set objDB = Nothing
    Exit Sub
End If

patId = sqlR![PatientID]
Set sqlP = objDB.OpenRecordset("SELECT [Patient], [HouseID] FROM [PATLIST] WHERE [PatientID] = " & patId)

patName = sqlP![Patient]
patName = Trim(Replace(patName, vbTab, " "))
[Form_Log Form].aPatientName.Value = patName

homeId = sqlP![HouseID]
Set sqlH = objDB.OpenRecordset("SELECT [Home] FROM [HOMELIST] WHERE [HouseID] = " & homeId)

theHome = sqlH![Home]
[Form_Log Form].aHomeName.Value = theHome
End Sub
Private Sub bLogItem_Click()
theQuantity = Val(Nz([Form_Log Form].aQuantity.Value, 0))
daySupply = Val(Nz([Form_Log Form].aDaySupply.Value, 0))
LoanedMeds = Val(Nz([Form_Log Form].cLoanedMed.Value, False))
[Form_Log Form].cLoanedMed.Value = False
rxNum = Val(Nz([Form_Log Form].aRxNumber.Value, 0))
If rxNum = 0 Or theQuantity = 0 Or daySupply = 0 Then
    a = MsgBox("Please enter a non-zero value for both the Rx Number, Day Supply and the Quantity.", vbOKOnly, "Error")
    Exit Sub
End If

Dim CurDate As Long
CurDate = Date

Dim strDB, objDB
strDB = CurrentProject.FullName
Set objDB = OpenDatabase(strDB)
Set sqlL = objDB.OpenRecordset("SELECT [UserID] FROM [USERLIST] WHERE [Logged In] = True")
loggedBy = sqlL![UserID]

objDB.Execute "INSERT INTO [LOGLIST] (" & _
    "[Log Date], [Rx Number], [Quantity], " & _
    "[Day Supply], [Loaned Med?], [Logged By]) VALUES (" & _
    CurDate & ", " & _
    rxNum & ", " & _
    theQuantity & ", " & _
    daySupply & ", " & _
    LoanedMeds & ", '" & _
    loggedBy & "')"

Call clearForm
[Form_Log Form].aRxNumber.SetFocus
Call UpdaterxQuery
End Sub

Private Sub UpdaterxQuery()
[Form_Log Form].rxQuery.Requery
[Form_Log Form].Repaint

End Sub

[twinnyfo]

jmshipe,

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

Me.Refresh
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.

[jmshipe]

Thank You for the quick response, but no it didn't change anything.

[jmshipe]

Okay so I added some code and improved the results, but It's still not quite right.

Set sqlL = Nothing
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.

[twinnyfo]

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:

[Form_Log Form].aRxNumber.Value = ""
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:

Dim patName, theHome, loggedBy As String

'Should be:
Dim patName as String
Dim theHome as String
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:

Dim LoanedMeds As Boolean
LoanedMeds = Val(Nz([Form_Log Form].cLoanedMed.Value, False))
[Form_Log Form].cLoanedMed.Value = False

'Could be shortened to:
Dim LoanedMeds As Boolean
LoanedMeds = Me.cLoanedMed
Me.cLoanedMed = False

One minor point has to do with the use of the MsgBox as a variable. See below:

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:

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: http://en.wikipedia.org/wiki/Leszyns...ing_convention.

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.

Cheers!

[jmshipe]

Thank you very much, I will go ahead and make your suggested changes.

[zmbd]

JmShipe:

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

http://support.microsoft.com/kb/311331

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

Dim dbs As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
Dim rs_1 As DAO.Recordset, s_rs1_field1 As String, l_rs1_field2 As Long
Dim rs_2 As DAO.Recordset, s_rs2_field1 As String
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?

-z

[twinnyfo]

zmbd,

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.

[jmshipe]

Turns out all I needed was:
objDB.QueryDefs .Refresh
I'm not sure why "requery" isn't sufficient

[zmbd]

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 recordset...it just takes the current stuff and reshows it... http://office.microsoft.com/en-us/ac...010256400.aspx

-z