I can't figure this out! I could use some help.
I use Access 2007 adp with SQL Server 2005 backend.
I have a form bound to a SQL stored procedure. The form is a continuous form. The form has a header with some comboboxes. These comboboxes are used to provide parameters to the SP. When a combobox value changes, the form's recordsource changes. All comboboxes have an afterupdate event that calls a routine to change the recordsource.
The problem is, as long as the SP returns a value the continuous form shows all the results and the comboboxes retain their values, but when the result is empty the form is empty (as expected) but the comboboxes have gone blank (the values that were there are no longer displayed). Yet, if you look at the values of the boxes, there is a value, it just doesn't show. So what happens is that the user has to fill them back in. They DO NOT like this.
A have a couple of these types of forms and they all behave the same. Interestingly enough, if I chose Updatable Snapshot and Allow Additions YES, the comboboxes work as desired, but the continuous form has a last row empty and available for new input. I do not want new input. If I change either Updatable Snapshot to Snapshot or Allow Additions to NO then the comboboxes go blank on an empty SP result.
The forms bound record source:
exec dbo.qryPDPMemberSearch '999', '%', '%', '%', '%', '%', '%', '', '', '%', 'HicNo', 'Asc'
(the parameters are used in LIKE statements so that I can return all if the user didn't specify a parameter)
The combobox event:
Private Sub cboDiv_AfterUpdate()
Record_Source
End Sub
The record_source function:
Private Function Record_Source()
Dim strDiv As String, strReqType As String
Dim strStatus As String, strApplication As String, strName As String, strComplete As String, strInputpar As String
strInputpar = "exec dbo.qryPDPMemberSearch '" & cboDiv & "', '" & cboGroupID & "', '" & cboHicNo & "', '" & cboFirstName & "', '" & cboLastName & "', '" & cboContractID & "" & "', '" & cboSSN & "', '" & cboDOB & "', '" & cboEffDate & "', '" & cboStatusCategory & "', '" & txtSortOrder & "', '" & txtAscDesc & "'"
With Me
.RecordSource = strInputpar
End With
Exit Function