Access User Defined Function in Continuous Form
Please post the code you are using to populate the form.
I assume you've verified that the form is set for continuous-forms view.
Dirk Goldgar, MS Access MVP 2001-2015
Access tips: www.datagnostics.com/tips.html
Was this reply helpful?
What is the form's Default View set to? Continuous Forms?
Have you validated that no Filter is set?
Do you have any code running when the form launches, On Open, On Load, Current, ... that could be changing the recordset or filter?
--
Daniel Pineault
Microsoft MVP 2010-2021
Was this reply helpful?
I bet you have the Default View property of the form set to "Single Form". Change it to "Continuous Forms".
-Tom.
Microsoft Access MVP
Phoenix, AZ
Was this reply helpful?
Hello Dirk, First, I want to thank-you for taking your time to assist me with this. Yes, the form is set for Continuous. The code is very long. I copied an excerpt that includes the SQL call that populates the form's control values. The SQL code was copied directly from the code that was auto generated in the query design.
Private Sub cmdExecuteFilterChoices_Click()
Dim tempfilterArgs As String
'Call to tempOpenArgs function to create filter string
tempfilterArgs = tempOpenArgs
If (tempfilterArgs <> "") Then
Dim dbs As Database
Set dbs = CurrentDb
Dim rst As Recordset
Dim strSQL As String
'user selected filter options and clicked the filter button. 'qryIssueReview
strSQL = "SELECT tblRunType.RunType, tblAssayType.AssayType, tblSampleType.SampleType, tblRuns.RunID, tblRuns.InstConfigID, " & _
"tblRuns.TeamID, tblRuns.DateRunStarted, tblRuns.Location, tblRuns.InstSWVersion, tblRuns.RunNotes, " & _
"tblIssues.AffectedSubassy, tblIssues.AffectedConsumables, tblIssues.IssueNotes, tblInstrument.InstrumentSN, " & _
"tblInstrument.ConsumablesID, tblFRACAS.FINPFCode, tblFRACAS.FailureMode, tblFRACAS.IssueReviewed, " & _
"tblFRACAS.IssueCurrentInvestigatorID, tblFRACAS.InvestigationNotes, tblFRACAS.ClosedDate, tblFRACAS.IssueClosedByID " & _
"FROM tblSampleType INNER JOIN (tblRunType INNER JOIN ((tblInstrument INNER JOIN (tblAssayType INNER JOIN tblRuns ON " & _
"tblAssayType.AssayTypeID = tblRuns.AssayTypeID) ON tblInstrument.InstConfigID = tblRuns.InstConfigID) INNER JOIN " & _
"(tblIssues LEFT JOIN tblFRACAS ON tblIssues.IssueID = tblFRACAS.FRACASID) ON tblRuns.RunID = tblIssues.IssueID) ON " & _
"tblRunType.RunTypeID = tblRuns.RunTypeID) ON tblSampleType.SampleTypeID = tblRuns.SampleTypeID " & _
"WHERE "
'"WHERE tblInstrument.InstrumentSN ='EP2'" & ";" 'for testing purposes only
strSQL = strSQL & tempfilterArgs & ";"
Set rst = dbs.OpenRecordset(strSQL)
With rst
.MoveLast
If (.RecordCount = 0) Then
'No records found
ClearAllContents
DisplayFilterChoices (tempfilterArgs)
tempResponse = MsgBox("No records found matching this filter criteria", vbOKOnly, "Filter Warning")
Else
'Populate the form controls with the contents from the filters selected
On Error Resume Next
txtbxInstrumentSN.Value = !InstrumentSN
txtbxFailureMode.Value = !FailureMode
txtbxInstrumentSWVer.Value = !InstSWVersion
txtbxAffectedSubassys.Value = !AffectedSubassy
If (!FINPFCode = "F") Then
txtbxFailure_Intervention.Value = "Failure"
Else
If (!FINPFCode = "I") Then
txtbxFailure_Intervention.Value = "Intervention"
End If
End If
txtbxConsumablesAffected = !AffectedConsumables
txtbxAssayType = !AssayType
txtbxSampleType.Value = !SampleType
txtbxWhoDiscovered = DLookup("[tempName]", "tblTeam", "[TeamID] = " & !TeamID)
txtbxIssueDiscoveryDate.Value = !DateRunStarted
txtbxCurrentInvestigator = DLookup("[tempName]", "tblTeam", "[TeamID] = " & !IssueCurrentInvestigatorID)
txtbxTechNotes.Value = !InvestigationNotes
txtbxIssueID.Value = !RunID
If (!IssueReviewed) Then
chkbxIssueReviewed.Value = True
Else
chkbxIssueReviewed.Value = False
End If
If (!IssueClosedByID) Then
chkbxIssueClosed.Value = True
txtbxCurrentInvestigator.Enabled = False
Else
chkbxIssueClosed.Value = False
txtbxCurrentInvestigator.Enabled = True
End If
GetListOfSubAssys
Dim ctl As Control
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acCheckBox
Me.Controls(ctl.Name).Locked = True
End Select
Next
cmdOpenFRACASForm.Enabled = True
txtbxIssueID.Enabled = True
txtbxInstrumentSN.Enabled = True
txtbxFailureMode.Enabled = True
txtbxInstrumentSWVer.Enabled = True
txtbxAffectedSubassys.Enabled = True
txtbxFailure_Intervention.Enabled = True
txtbxConsumablesAffected.Enabled = True
txtbxAssayType.Enabled = True
txtbxSampleType.Enabled = True
txtbxWhoDiscovered.Enabled = True
txtbxIssueDiscoveryDate.Enabled = True
txtbxTechNotes.Enabled = True
chkbxIssueClosed.Enabled = True
chkbxIssueReviewed.Enabled = True
cmdOpenFRACASForm.SetFocus
DisplayFilterChoices (tempfilterArgs)
End If
.Close
End With
dbs.Close
'for sizing window - rtwh (unit=twips. 1440 twips = 1 inch)
DoCmd.MoveSize 100, 100, 16000, 13800
Else
tempResponse = MsgBox("Nothing was Selected", vbOKOnly, "No Records")
End If
End Sub
Was this reply helpful?
Hello Daniel,
The form's default view is set to Continuous Forms. No filters are set. A command button on the form launches a SQL string that executes a select query. the WHERE parameters are selected by the user from drop down menus on the form.
No code is running during the On Open/Load events.
Was this reply helpful?
Hello, I'll bet you didn't read my original post!
Was this reply helpful?
David -
Your code is populating the form controls directly by assigning values from the last record in the recordset. The form remains unbound, so it will never show controls for more than one record. I thought maybe you were assigning a SQL string to the form's RecordSource property, but that's not what you're doing.
An unbound form -- one that has no recordsource -- will never show multiple records, unless maybe you assign a recordset you create directly to the form's .Recordset property, rather than assigning the values of specific fields in your recordset to the individual controls on the form.
May I ask why you are using an unbound form? What are you trying to achieve, that couldn't be achieved with a bound form? Knowing that would let me advise you on how to address the problem you're faced with.
Dirk Goldgar, MS Access MVP 2001-2015
Access tips: www.datagnostics.com/tips.html
Was this reply helpful?
Hello,
Thanks for your timely response. Good question! Why am I using an unbound form? I don't have a solid answer for this other than I thought that doing so would give me greater flexibility. I'll try adding the qry to the form's record source and see if that resolves the problem. I think it probably will. I don't know why I didn't think of that earlier. I'm leaving on travel now so I won't have a chance to respond for several days but thank-you once again for your insight and support.
1 person found this reply helpful
·
Was this reply helpful?
Hello Dirk,
It's been awhile but using your recommendation, I changed the form's Record Source from Unbound to the query whose SQL code I posted to you earlier. At this point, it's finding all expected records successfully so some definite progress was made. Thank-you! I now need to work on reformatting the form so that the returned data presents as expected. That's the easy part.
Was this reply helpful?
Source: https://answers.microsoft.com/en-us/msoffice/forum/all/continuous-form-doesnt-display-all-records-from/e076f55e-2eb4-45f7-8708-e0808e2dad23