Results 1 to 5 of 5
  1. #1
    kevdfisch is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    3

    Controls go blank on empty query result

    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

  2. #2
    kevdfisch is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    3
    Bump.

    Anybody have any ideas? I really need to get this resolved.

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You could set the AllowAdditions property true if Me.Recordset.RecordCount = 0

  4. #4
    kevdfisch is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    3
    Well, that did give me something to work with, thank you. I have coded that if it is 0 then i turn on the allow additions and I display a message of No Records Found over the blank, updatable row so they can't enter anything.

    But, I still would like to understand why the controls go blank on an empty resultset and why it can't just be 0 rows in the continuous form.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It is simply the way Access works when AllowAdditions is set false.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export a simple query result to an excel file
    By wop_nuno in forum Import/Export Data
    Replies: 1
    Last Post: 05-21-2009, 04:18 AM
  2. Result of Count Query not known elsewhere
    By Carole in forum Access
    Replies: 1
    Last Post: 09-07-2008, 09:39 AM
  3. Replies: 1
    Last Post: 09-05-2008, 12:07 PM
  4. Replies: 2
    Last Post: 08-04-2008, 04:16 PM
  5. Replies: 1
    Last Post: 07-14-2008, 12:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums