Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi,

    I have an access database that works fine using access tables as the backend. Due to size of data, I am in the process of converting the tables to SQL tables. That has gone pretty smoothly (thank God) however I have some forms that give me an error on open when they OnLoad event is docmd.gotorecord,,acnewrec. It isn't happening on all of the forms just certain ones. I'm trying to see what is different between the forms it is working on compared to the forms it isn't Again, these forms were working fine with Access tables. Does anyone have any ideas of what I should look at?

    Thanks!
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Have you checked whether it is possible to go to a new record at all in those forms (when bound to SQL Server)?

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Hans,

    First I just noticed I posted this under Excel instead of Access. Sorry about that. Stressed I guess.

    Anyway, as I said, the Access database is linked to sql tables. Forms that have a datasource that is a table vs a query seem to open fine with the go to new record onload event. It blows up whenever there form is linked to a query, especially if the query has any type of filter. As I was searching the internet I found a reference that stated its not actually the gotorecord,,acnewrec that is the issue ut just that that is what is highlighted due to being the first line of code. Sure enough I have another form where the first line of code is txtUsername.setfocus (vs gotorecord) and that blows up. If I take the query off the form it works fine. These are all working fine in when linked to access tables. It's driving me nuts!

    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Do the forms bound to queries work OK if you comment out the On Load event procedure (and if you have one, the On Open event procedure too)?

    BTW, I have moved this topic to the Access forum.

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    I've never used the onOpen only the OnLoad (is one better than the other?). However I did comment out the code. The form opens but wth no records and if I try to go to a new record I get the error that that isn't available now. I tried the code in the OnOpen event with the same results.

    Leesha

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    OK, next question: do the queries themselves work correctly if you open them from the database window?

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    SQL runs fine if run on their own.

    For example, I have the following code on the onload event:

    DoCmd.GoToRecord , , acNewRec
    Me.StatusAdmitNumber = 2
    Me.StudentNameID = Forms!sfrmStudentReferralAdd!txtID


    The SQL for the form is:

    SELECT dbo_tblStudentStatus.StatusAdmitNumber, dbo_tblStudentStatus.StudentNameID, dbo_tblStudentStatus.StatusDefined, dbo_tblStudentStatus.StatusDate, dbo_tblStudentStatus.DischargeReason, dbo_tblStudentStatus.DischargeNoServiceComment, dbo_tblStudentStatus.DischargeOtherComment, dbo_tblStudentStatus.StatusComment
    FROM dbo_tblStudentStatus;

    I tried commenting out the second and third line of code and still it won't load correctly. If I comment out all three lines it opens but there are no records and I can't go to any.

    Leesha

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you create a query with the SQL that you posted, and switch to datasheet view:
    - Do you see existing records?
    - Can you edit existing records?
    - Can you go to a new record?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Why don't you use dbo_tblStudentStatus as record source for the form instead of the SQL statement?

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    I get the same error if I convert to datasheet view etc and I can't go to a new record. I can see the the existing records.

    To answer your next post, I have the same problems if I use dbo_tblStudentStatus as the record source. It doesn't seem to matter whether its a table or a query that its linked to.

    Leesha

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    So the problem isn't in the forms at all!

    Can you actually open the linked tables?

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    I can open the linked table but can't add or edit?????

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Each table needs to have a unique index, otherwise it won't be updateable from Access.
    Also, to prevent problems in the future, each table should have a field of type TIMESTAMP.
    After applying these changes in the SQL Server database, you must refresh the table links in your Access database.

    See Post 699758 for an explanation.

  14. #14
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    The tables that won't allow an update or addition all have unique fields but none of the tables have timestamps. I'll read up on the link you sent and go about putting those in and will post back if it works.

    Thanks!
    Leesha

  15. #15
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    I apologize in advance for how stupid this next question is going to sound, but do I code the the timestamp field to autofill somehow or does it do it on its own similar to an autonumber in access?

    Thanks,
    Leesha

Page 1 of 2 12 LastLast

Posting Permissions

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