Subform not repainting for first entry

[jmshipe]

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?

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, 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
Set sqlL = Nothing
Set sqlR = Nothing
Set sqlP = Nothing
Set sqlH = Nothing
Set objDB = Nothing


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 & "')"

Set sqlL = Nothing
Set objDB = Nothing

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

End Sub

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

End Sub

Private Sub Form_Load()
[Form_Log Form].aRxNumber.InputMask = "000000;; :"

End Sub

[zmbd]

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