Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Nov 2007
    Posts
    23

    Unanswered: Can't Get Sub Form to Link Properly With Main Form Using MSSQL 2005 Express Server

    Hello,

    I am building an app in Access 2007 using MSSQL 2005 Express Server as a back end and I am having some problems.

    I am modeling a paper based Bill of Lading as a set of tables. I have a header table and a details table for the BOL. They are linked on BOLHeaderUID --> BOLDetailUIDRef.

    If I model this in Access and set the link master/child fields to BOLHeaderUID/BOLDetailUIDRef. the form works perfectly.

    I also have the following code behind both the header an the detail (main and sub) forms:

    Private Sub Form_Load()
    DoCmd.GoToRecord , , acNewRec

    End Sub

    This works perfectly.

    My problem surfaces when I create the same forms against the MSSQL Database. When I do this I get an error message (2105) "You can't go to the specified record"

    I got this error after following advise on a problem already posted here that I *thought* was essentially identical to mine (http://www.dbforums.com/showthread.p...2#post6337312).

    The advise was to change the data source of the forms to queries rather than straight up table name references.

    Previous to making this change my app was exhibiting the exact same behavior as this other post. I could add header records but the subform would sit on an existing record and not go to a new record automatically when I tried to add a new record.

    AllowAdditions is set to yes on both the main and the sub forms...

    I have tried everything I can think of to no avail. Does anyone have any idea what might be causing this issue?

    Thanks!

  2. #2
    Join Date
    Nov 2007
    Posts
    23

    Additional Info

    Ok, I tried to create a new set of forms once again. I created the main form and then added a subform control (previously I was creating a sub form and dragging it to the main form)

    I can enter data just fine until I backup and then try and move forward to a new record. At that point I get a new record in teh header and the last record of the details database in the sub form. I'm guessing it has somethign to do with the interoperability of the navigation buttons on the main form with the record pointer in the subforms underlying data table but I'm at a loss as to what it is at the moment. Any suggestions would be greatly appreciated.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Which line of code in which form generates the error 2105?

    Once the new record is saved and you navigate off that record and then back onto it, does the subform behave as it should? Perhaps a subform.ReQuery is needing to be executed AfterInserting a new record on the mainform? Doesn't islolate the problem itself but it may be an acceptable work-around.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Nov 2007
    Posts
    23
    Startrekker,

    The line that generates the Error is: DoCmd.GoToRecord , , acNewRec but, here's the kicker. It does not always give me the error. This morning when I fired it up to look at it Access did not complain about the line of code. It also did not let me enter any data into the sub form... very perplexing. I used to write in Access all the time and it has been a while but I can't believe that I have become *that* rusty - lol. Maybe it is time for me to spend the afternoon with a few books...

    I added the re-query in another copy of the form in another MDB and it dies not solve the problem. I can enter one recored and move to a second but when I click the next record button the third time instead of going me a new header and a new sub form it just goes to a newline on the subform.

    I even tried adding it to both the main and the sub forms but got no love that way either.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Which FORM is generating the error?

    Try using a subform.ReQuery in your On Current event procedure and see if that helps. Largely guessing here as I don't have a lot of experience with SQL 2005 Express Server.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Nov 2007
    Posts
    23
    ST -

    It was the subform that I would, inconsistently, get the error from. I have made many changes and I am not getting the error anymore but the form is still broken.

    I *think* it must have somethign to do with viewing and adding the records to the back end DB in the same form. My thinking is that I should have an ADD form and and EDIT form...

    Again, to (hopefully) clarify What happens is that I have a main form with a subform. The main form's PK GUID is generated when I step off the main form to the subform. The main form gets written to the DB. Then the subform data gets written when I click the next button in the navigation bar. Personally I'd like to figure out how to get the whole thing written at once as what happens if the user cancels the entry after the header is written? Also, I discovered that Access automagically writes the PK from the main form to the FK of the subform without my having to tell it to.

    Is there some standard way I should be handling this? I have poured over a half dozen books or so and from everything I have read this should work as is. I can not recall the last time somethign drove me this bonkers trying to figure it out. The worst part is that it *sort of* works. It works sometimes and then breaks.

  7. #7
    Join Date
    Nov 2007
    Posts
    23
    Ok, I went back and I moved the SQL string to the After update event on the Main form. That was a serious case of can't see the forest for the trees because I was up all night long with a lantern staring into the darkness... thanks for smacking me upside the noggin on that one :-)

    The basic functionality is now there. I now get a fresh form each time I click next and can move forward/backward through the records in the header but now it looks like I will need to tell Access when to write the BOLHeaderUID to the BOLDetail.BOLHeaderUIDRef field as it is no longer automagically populating which, I assume, is due to breaking the Master/Child link properties and replacing them with pure inline SQL.

    I put the following in the Main form:


    Private Sub Form_AfterUpdate()
    Me.frm_BOLDetail.Form.RecordSource = "Select * FROM dbo_BOLDetail WHERE BOLHeaderUIDRef = " & BOLHeaderUID
    End Sub

    And this seems to work except I get a funky pop up parameter query box (see attached jpg) with a bunch of odd garbage characters in the title. If I click through it I can then enter data on the subform.

    If I change it to:

    Private Sub Form_AfterUpdate()
    Me.frm_BOLDetail.Form.RecordSource = "Select * FROM dbo_BOLDetail WHERE BOLHeaderUIDRef = BOLHeaderUID"
    End Sub

    It *appears* to work but you said in an earlier post that this is not a valid SQL statement because Access won't know what BOLHeaderUID is and, shouldn't that really be a left outer join I'm using? I was just copying your example as I figured it may be some 'shorthand' Access knows since the fields are on the forms.

    So, I have a purely tangential question regarding a SQL 2005 Express connection string but I'll post it in a new thread...

    I'll post again to let you know if it works 100%, or what percent, when I make these last mods I'm thinking of - or possibly to pick your brain about the right way to get that GUID in the detail table if I muck it up ...

    ...

    And, actually, I went back and looked at it and think the only reason it is working is there *is* no real link. that SQL statement is definitely not doing what it is supposed to. How do I tell Access to link from one form to the other in SQL? I thought I grocked it but I guess I don't. I don't get the & <field name> bit you showed me before. Is there an online reference that explains it you could point me to?

    ...

    Ok, one last thing for tonight... In an effort to see what the heck was going on I did the following:

    Private Sub Form_AfterUpdate()
    Dim varBOLHeadUID As String
    Dim varBOLDetailHeadUIDRef As String
    varBOLHeadUID = Me.BOLHeaderUID
    Me.txtShowBOLUIDVar.Value = varBOLHeadUID
    Me.frm_BOLDetail.Form.RecordSource = "Select * FROM dbo_BOLDetail WHERE BOLHeaderUIDRef = " & BOLHeaderUID
    End Sub

    txtShowBOLUIDVar is a txt box on the form. It displays either Japanese or Chinese text... not sure what *that* is all about. The top value is the field from the DB as placed on the form from the field picker. The bottom value is what the above code generates. Guess it goes overseas before it prints on my screen - lol. (see attached screen shot)
    Attached Thumbnails Attached Thumbnails Access Funky Parameter Prompt.jpg   more odd behavior.jpg  
    Last edited by 4SightDS; 06-17-08 at 07:05.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    what happens if the user cancels the entry after the header is written?
    The "cancel button" deletes the mainform record.

    Is there some standard way I should be handling this? I have poured over a half dozen books or so and from everything I have read this should work as is.
    I concur. This should happen so long as you have set the link fields for the subform object.

    Who told you to change the Link fields to SQL? ^^ It wasn't me

    As for the screenshot image... um... W.T.F

    I think perhaps either the version of SQL Server you're running is borked up or your installation of it might be. Perhaps this is the underlying problem too all your problems, considering that what you are trying to do is normally automatic and quite straight-forward.... even on SQL Server main / subforms don't behave this badly. Might just be a re-installation of SQL Server Express?

    Could possibly be a corrupted database too I guess... have you tried those off-the-shelf fixes like Compact and Repair and importing all objects into a new database etc?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    From briefly reading your problem with adding records to the subform, I wanted to ask if you have a couple of things:

    1. Does the PK field (linked to the main form) exist on the subform and does it have a default value set? If not, try adding it (making it invisible), and set the default value =Forms!MyMainFormName!PKField. And set the tab order so it's the 1st or 2nd field. Also make sure your autonumber field is on the subform (invisible if wanted.) You can make the linking field on the subform visible for testing to make sure it's putting the proper PK in.

    2. On the main form, viewing the properties and selecting the subform, are the linked child/master fields set to link on the PK field?

    I usually have the recordsource of the subform set to something like "Select * from MySubformTable where PKLinkingField = " & Forms!MyMainForm!PKField & ""
    I might then try doing a me.MySubForm.requery after some key field in the main form is updated (afterupdate) (ie. the PK should be generated on the main form and the subform is requeried to show a blank record) But this may not be necessary.

    Lastly, make sure you have autonumber (int) fields are set to increment in both the main table and the sub-table on SQL Server. There have been a few occasions after upsizing where the autonumber fields (in the Access table) did not translate to actually increment on the SQL Server tables.
    Last edited by pkstormy; 06-17-08 at 14:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I just read the part about SQL Server and somehow the backups "may" dictate whether you can update or not.

    I've seen this on occasion and once I configured the TranLogs to backup on SQL Server (along with the main backups), this seemed to resolve any issues with updating. Tranlog backups are always a good idea as they backup for point-in-time restores and I will usually configure these. But this may or may not be your issue. I just wanted to throw this out there.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Nov 2007
    Posts
    23
    ST -

    Oh, sorry, the SQL thing was another post somewhere else. I've been really hammering on this issue... sorry for the confusion. Someone told me to switch from using the Master/Child properties on the Data Tab of the Subform to directly specify the link with SQL code and, actually, that I should do so in any event there the records might change or be updated. Tis really seemed a bit excessive to me but being that I've been pulling out what little is left of my hair over this I figured I'd try just about anything.


    I'm assuming from your comment about the screen shots that this is really odd behavior - lol. My initial thought had been that somehow stuffing a SQL data type of uniqueidentifier into an Access string was somehow causing some weird conversion to take place but I'm not real sure at this point.

    As for the database being corrupt it would have to be multiple databases because I have created more than one set of tables to test off of and, as for it being a bad install I run Crystal Reports reports off of another database on this server just fine so I'm not sure. The install was a straight out of the box plain vanilla Ll the defaults kind of install.

    I will poke around with moving it to another server when I find some time later tonight...

  12. #12
    Join Date
    Nov 2007
    Posts
    23
    pkstormy -

    Thanks for the reply and, yes, I put the PK on the main form and the FK on the sub form. For testing purposes they are visible so I can see if they are the same.

    The way this is set up is that the PK fields in the DB are created in SQL server with a default value of =NewID() so SQL server is generating a GUID for the Primary Keys.

    The Master/Child Properties were being used originally but someone somewhere else told me to use a SQL statement in an event rather than Master/Child and that is what I ave been trying to accomplish since yesterday. I'm more than happy to use the Master/Child properties on the Data Tab if I can but it dawned on me that, when adding a record, there are no detail records with a matching FK as they have not been created yet. To be more precise, the PK field in the header (main form)record does not get populated until you click into the detail section (subform). Does Access handle this internally somehow and they get auto linked as they are created?

    I don't quite understand the format of your PK linking field statement " & Forms!MyMainForm!PKField & "" - could you explain please what the &'s are for? I get the reference to the field via the notation used otherwise.

    As for the autonumbers on SQL Server, that all appears to work just fine. Like I said, SQL Server has NewID() set as the default value.

    As for the comment about backups - are you saying I may have to configure a backup maintenance plan before the master/child properties will work right? Sorry, you lost me on that last thought...

  13. #13
    Join Date
    Nov 2007
    Posts
    23
    Oh - and could someone please tall me what ya'll mean by "upsizing"? I've heard the term a few times now and my assumption is it means moving an Access DB to SQL Server.

    In this case, I designed the table in SQL Server and am building the front end in Access.

    I've done similar projects in the past and never had these kinds of issues. I was beginning to think I was crazy so it has been nice to hear some other folks say it is suppose to work the way I thought it should - lol.

  14. #14
    Join Date
    Nov 2007
    Posts
    23

    Smile Fixed It (well sort of)

    So, I've NO idea why I was having the problem I was having per se but I did finally get past the issue.

    I'm a bit rusty with Access and so what I started out doing was building my project using linked tables and MS SQL Server Express. I have NO idea what the issue was with the form/subform linking but I created a new ADP project rather than a MDB with linked files, recreated the forms in a basic format, and viola! It works.

    I guess I can chalk that up to experience even though it is about 40 hours of my life I'll never be able to get back - lol.

    Now all I have to do is recreate all the forms in the new project. On the upside I read that queries are faster via an ADP than they are via a MDB with linked tables so I guess at least I gained somethign from the exercise.

    Actually I gained a lot of insight into other areas of Access along the way so not all was a waste of time.

    Thank you all for taking the time to try and address my issue. I *really* appreciate it.

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    4SightDS,

    I'll try to address all of your questions over the next day or two. Here's regarding using the " and a little explanation of the recordsource query SQL statement. I apologize in advance if it seems like I'm addressing some info you already know.

    First thing I'd have to ask is: How is your main table auto-incrementing (integer) field in SQL Server being generated? When you mentioned that the default value (in SQL Server) for your main ID field is NewID() it concerns me (unless I read you wrong) as this field (that I'm refering to) is the (integer type) field which is set to automatically increment by 1 (for the context of this discussion we'll call this field the PKField but it doesn't necessarily have to stand for Primary Key Field which could be another field - but for discussion purposes, again, we'll call this the PKField but it really represents whatever your incremental field is in the main table). This field would be the same as the autonumber field in an Access table. When you add a new record, again, this value increase by 1 from the previous value. You should have an autonumber type (ie. integer field type which increments by 1) in both the main table and your relational linked table. In your relational linked table, you would also have another field (which is integer type and does NOT increment) which would then be relational set to link on the (PKField). For discussion purposes, we'll call this field the PKLinkingField (but it really represents the relational field in your relational table). On SQL Server (as you would do in Access for relationships), you want to make sure PKField is linked to the PKLinkingField.

    Now, when utilizing a mainform/subform with Access in a table-linked table relationship (ie. the mainform is based on the main table and the subform is based on the linked table), what I found works best is to have the mainform based ONLY on the main table and the subform is based ONLY on the linked table. What I mean by this is that your recordsources for the mainform only has a SQL statement based on 1 table and the same for the subform. For example, the mainform recordsource might have a SQL statement such as "Select * from MyMainTable". To make your mainform open faster, I will often have a search form open prior to the mainform where someone enters criteria and the mainform opens based on that criteria (see some of the posts in the MSAccess code bank in this forum for examples on how to do this.)

    The subform then ideally has a recordsource where the subform only shows records where the linking field in the subform matches the PKField in the mainform. Hence, you might have a recordsource SQL statement for the subform like this: "Select * from MySubFormTable where PKLinkingField = " & Forms!MyMainFormName!PKField & ""

    This is a typical SQL statement where you want to select all records matching a certain criteria and the " represents that your criteria is based on an integer field (you would use ' " (spaced out to show syntax) for criteria based on a string field. The key here is that your basically telling the subform to return records for the subform where the linking field (PKLinkingField) for the subform table matches the main primary key field in the mainform (PKField).

    On the subform, you would want to make sure you have the PKLinkingField on that subform and set the default value of it =Forms!MyMainFormName!PKField. This then automatically puts in a default value of the PKField from the mainform when adding a new record to the subform. This is essential and I found it often causes problems if I don't have this field on the form with the default value set (even though the link child/master field is set correctly for the subform.)

    One thing you could try (just to make sure that this now works correctly), is to add a button on your mainform (not the subform) which the code below is then going to show you how to add a new record to the subform. Add a new button on your mainform (call it AddRecordToSubform) and put this code in the OnClick event:

    me.MySubformName.setfocus
    DoCmd.GoToRecord , , acNewRec

    Now test this part out by opening the mainform, going to an existing record, and clicking the AddRecordToSubform button. (You should make the PKLinkingField visible on the subform to make sure that it puts in the proper ID and again - make sure the default value for the PKLinkingField is set to =Forms!MyMainFormName!PKField (replacing MyMainFormName with your mainform name and PKField with what your actual incremental field is on the mainform).) Hence, upon clicking the AddRecordToSubform button, you should see the same ID value automatically inserted into this field (PKLinkingField on the subform) as the PKField on the mainform. You should also be able to add data to your other fields on the subform. If you don't see the PKLinkingField ID value automatically inserted with the PKField value, then let me know what happens and we'll take it from there (sorry but it's past my bedtime for tonight and I haven't slept in a few days.) Oh, and to make it easier to view the data on the subform, you may want to set the subform default view property to a "single form". And ideally, you might want to test if PKField is null (ie. blank) when clicking this button and before being able to go to a new record (if not isnull([PKField]) then). This code might go in the line before the me.MySubformName.setfocus (adding an end if at the end.) But test it on a record with an existing PKField value first.

    As a last comment, regarding Upsizing - it is simply the process of converting an MSAccess table to SQL Server. In MSAccess, you have what is called the "Upsizing Wizard". This wizard works extremely well for moving (ie. converting) any tables from MSAccess to SQL Server. I will usually design my tables in MSAccess first to work out any kinks/relationships/etc... and then "upsize" them to SQL Server after I've completed designing all the tables in MSAccess. This process of using the MSAcess Upsizing Wizard has always worked very well for me in converting MSAccess tables to SQL Server.

    I'll try to address any further questions you have tomorrow (or tomorrow night if I end up sleeping for the day). But let me know how the info helps so far and what happens after trying my suggestion above.
    Last edited by pkstormy; 06-18-08 at 04:47.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •