locked
Only showing 1 row out of 3 on a continuous subform with SQL Server linked table RRS feed

  • Question

  • I'm having the strangest thing occur.  I have a linked SQL Server table through DSNless ODBC connection.  I open the table, it shows three rows.  I have a continuous form bound to that table, which, when I open by itself, shows all three rows.  When I display this as a subform on an unbound main form, only one row shows up.  There are no Master / Child fields defined, and there are no filters defined.

    Any ideas?


    Richard

    Tuesday, October 17, 2017 9:00 PM

Answers

  • Hi Rick,

    Until I find something more definitive, I might suggest leaving the main form unbound and simply use code to look up and assign the values from the proDatabaseSelection table into global variables (or TempVars) for your use.

    Hope it helps...

    • Marked as answer by Hemmingway Wednesday, October 18, 2017 7:19 PM
    Wednesday, October 18, 2017 5:29 PM

All replies

  • Hi,

    Can you post a screenshot? Obvious question: Is the subform container big enough to show more than one record?

    Tuesday, October 17, 2017 9:03 PM
  • Hi,

    Can you post a screenshot? Obvious question: Is the subform container big enough to show more than one record?

    Hi DBGuy,

    Attached are three screen shots. THe first showing form view of the main form and subform.  The second showing design view of the main form (you can see there is enough real estate).  The third is design view of the form being used as the subform.  Something else I should mention, I've tried rebuilding a form from scratch, using a linked view, using a passthrough query, all give me the same result...the second record entered.

    I just tried to submit, but apparently my account needs to be verified before I can do that.  I will try and find a place to put them and send the URL.


    Richard


    Tuesday, October 17, 2017 9:33 PM
  • Very frustrating.  It won't let me include a link either.  If you go to my Facebook page, Richard Collens, I've created one album called "Access Issues" which I've shared with the public.  Kind of a round about way of showing the issue, but it should work.
    Tuesday, October 17, 2017 9:37 PM
  • Hi Richard,

    Are these your images? I only saw two images.



    • Edited by .theDBguy Tuesday, October 17, 2017 9:58 PM
    Tuesday, October 17, 2017 9:53 PM
  • Hi Richard,

    I must be Facebook illiterate because I can't find you after a few tries. Sorry.

    Hi DBGuy,

    Lets try this.  if you put this after facebook.com see if you can see the album.

    /rick.collens/media_set?set=a.10155729810351963.1073741895.609551962&type=3&pnref=story

    Let me know.

    Thanks.


    Richard

    Tuesday, October 17, 2017 9:59 PM
  • Yes!  Those are the images.  There are three there, I'll check the second image to make sure it's shared publicly.
    Tuesday, October 17, 2017 10:03 PM
  • Here is the link to the other image (after facebook.com)

    /photo.php?fbid=10155729810386963&set=a.10155729810351963.1073741895.609551962&type=3&theater

    Honestly, this is the most bizarre method of sharing screenshots. ;)

    Richard

    Tuesday, October 17, 2017 10:04 PM
  • Hi Richard,

    That helped. Here's the last image:

    What I don't see in the images is the Default View for the subform. What happens if you change it Datasheet?

    Tuesday, October 17, 2017 10:04 PM
  • Hi DBGuy,

    When I change it to Datasheet, the same results occur (only showing in datasheet).

    Richard

    Tuesday, October 17, 2017 10:11 PM
  • Can you post the code you're using to display the subform when the user selects it from the main form? Also, are there any Open or Load event code running behind the subform?
    Tuesday, October 17, 2017 10:15 PM
  • Hi DBGuy,

    There's no code on load of the subform.  Below is the code for changing the subform on the main form.

    Private Sub lstSelectForm_Click()
        ChangeForm Me, Me.frmSubform, lstSelectForm.Column(2), lstSelectForm.Column(1)
    End Sub

    Public Sub ChangeForm(frm As Form, subfrm As SubForm, strkey As String, strDesc As String)

        subfrm.SourceObject = strkey
        subfrm.Visible = True

    End Sub

    Tuesday, October 17, 2017 10:19 PM
  • Hi Rick,

    Just to isolate the backend, have you tried creating a local copy of the table and bind the form to it just to see if the form behaves the same way? If not, then it must be something about the connection to the backend, I would think.

    Just a thought...

    Wednesday, October 18, 2017 12:32 AM
  • Hi DBGuy,

    I did that and still get the same results.  It did uncover a different problem I've been running into.  I am having an issue with any new record getting inserted.  It's trying to force a duplicate primary key (increment) that already exists.  Not sure why that is happening, but I suspect it's a different root cause than this issue.


    Richard

    Wednesday, October 18, 2017 2:00 PM
  • Hi Rick,

    Unfortunately, this would be hard for us to troubleshoot without being able to examine the file(s). Any way you can share it without any sensitive data?

    Wednesday, October 18, 2017 2:50 PM
  • Hi Rick,

    Unfortunately, this would be hard for us to troubleshoot without being able to examine the file(s). Any way you can share it without any sensitive data?

    Hi DBGuy,

    Do you have a dropbox or something I can use?  The zipped file is 500kb.  It has SQL links in it, but I've copied the table to a local table, so you can still use the "Regions" link on the frmMenu form and see the same results.

    Let me know.

    Thanks!

    Richard

    Wednesday, October 18, 2017 3:34 PM
  • Hi Rick,

    See if this link work. Thanks.


    • Edited by .theDBguy Wednesday, October 18, 2017 4:21 PM
    Wednesday, October 18, 2017 4:20 PM
  • It appears to have worked on my end.
    Wednesday, October 18, 2017 4:34 PM
  • Hi Rick,

    Got it and so far, all I was able to figure out is if you take out the Record Source from frmMenu, then you will see all the records in the subform. Maybe you can figure out why the Record Source is causing the subform to become filtered. I'll let you know if I find out anything else. Are you having the same issue with the other subforms or just the Regions one?

    Wednesday, October 18, 2017 5:19 PM
  • Hi Rick,

    Until I find something more definitive, I might suggest leaving the main form unbound and simply use code to look up and assign the values from the proDatabaseSelection table into global variables (or TempVars) for your use.

    Hope it helps...

    • Marked as answer by Hemmingway Wednesday, October 18, 2017 7:19 PM
    Wednesday, October 18, 2017 5:29 PM
  • Hi DBGuy,

    Yes, I think that's the cheapest and easiest approach.  I'm not sure why that matters either, but unbinding the main menu isn't a show stopper for me.  I'll use that approach.  

    Many thanks for your time!  Much appreciated!

    Richard

    Wednesday, October 18, 2017 7:19 PM
  • Hi Richard,

    Sorry I couldn't find a real answer, but I will let you know as soon as I discover something different.

    Good luck with your project.

    Wednesday, October 18, 2017 7:22 PM
  • No worries.  Thanks again!
    Thursday, October 19, 2017 2:39 PM