Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Strange Problem Appending a Table (Access 2000)

    I'm having a strange problem...I'm trying to add some students from an Excel file to my Student table. When I tried to Import them, I got a message saying that it couldn't add them. So then I Imported them to it's very own table. Then I tried doing an Append Query to add them to the Student table. That didn't work. It said that I had validation violations. I looked at every field and there was no violation. (Make note that I have more fields in the Student table than in the Additions table--but that shouldn't matter should it?) So finally I just opened both tables and managed to copy and paste the students into the Student table. But now when I go to my Student Form, I can't access any of my students. It says "Run time error 3021 no current record". When I click on the Debug button, it shows me the following code:

    Sub Combo23_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[StudID] = '" & Me![Combo23] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End Sub

    Why am I getting this? Why am I having so much trouble appending?

    ****Okay, I solved the problem where I couldn't see my students, but that still doesn't solve my Append query problem.

    *****Scratch that! I still can't see my students! And it's still because of that code above.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Problem Appending a Table (Access 2000)

    Is StudID a numeric field ?
    Then you should use :
    Me.RecordsetClone.FindFirst "[StudID] = " & Me![Combo23]
    Francois

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Problem Appending a Table (Access 2000)

    For your import problem, have you checked that all the fields of the temp table are the same as in the student table? When importing excel data, numeric data could go in a text field.
    Francois

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Problem Appending a Table (Access 2000)

    StudID is a text field.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Problem Appending a Table (Access 2000)

    If you stop the code on the line
    Me.Bookmark = Me.RecordsetClone.Bookmark
    What is the content of Me![Combo23] ?

    Did the query in the combox render the same number of records as the query of the form ?
    Francois

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Problem Appending a Table (Access 2000)

    Pardon my dumb question, but how do you do that. I can't remember--I did it once a while back.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Problem Appending a Table (Access 2000)

    Could my problem appending be because my database is split? So when I run the append query, it tries to append the Student table from the front end, not that back end.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Problem Appending a Table (Access 2000)

    I think I figured out how to stop the code. When I hold the cursor over the code where it stopped (Me.Bookmark = Me.RecordsetClone.Bookmark) it says "Me.Bookmark=<no current record>". What does this mean?

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Problem Appending a Table (Access 2000)

    To stop code go to the vbe window and put the cursor on the line you want the code stop.
    Press F9 and the line will turn brown.
    When you run the code it will stop on that (now yellow) line. This line is not yet executed.
    You should hoover over the previous line (Me.StudID) or go to the Immediate window (CTRL-G) en type ? Me.StudID and enter.
    Now you have what the FindFirst is looking for. Did this exist in the recordset of the form ?

    As for linked tables, do you have 2 tables, one linked from the BE and one in the FE ?
    If so, they must have different names. Check your table name in the form or in the underlying query.
    In fact you should not have any table in the FE and work only with linked tables. Working with linked tables is no different to working with tables in the database (a few exceptions exist like the Seek function that not will work on linked tables)

    If this is not helping, could you post a stripped version of your db (FE and BE) so we can look at it ? see <post#=401925>post 401925</post#> from Hans to reduce the size of the db under the 100K
    Francois

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Problem Appending a Table (Access 2000)

    I went ahead and ran the code and had it stop on the "Me.Bookmark = Me.RecordsetClone.Bookmark". I then opened up the CTRL+G window and typed what you told me. The value it was looking for first is "null". Why is it null?

    I'll try and do a stripped-down version of my database, can't promise anything, though because it's quite complicated. But if I could just get the problem of not seeing my students solved, then I can live with the append problem.

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Problem Appending a Table (Access 2000)

    I suppose the combo is based on a table containing the student information.
    Like now it seems that some student don't have a StudID.
    Can you check these ?
    Francois

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Problem Appending a Table (Access 2000)

    If you prefer, you could also send the db's to my e-mail address (see my profile)
    Francois

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Problem Appending a Table (Access 2000)

    Well, of all the *%@#&%$!!! I just recopyed the database (front end) to the folder, and now it works. Go figure. Computers...grrrrrrrrr!!! Anyway, I can manage the appending if I copy and paste, so I'm going to do it that way.

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Problem Appending a Table (Access 2000)

    Well, guess what? I'm having the same problem again. I can't see my students on my Invoices by Student form! I tried recopying, and that doesn't work either. I don't know what's going on. You see, I'm copying my database to each of my schools on their own servers. I goto my WorkingCopy.mdb file and then I right click and select Copy. I then go to the school server and Paste into a folder. Then I open up the mdb file on their server and go to Tools, Database Utilities, Link Manager and direct the links to the tables on their server instead of my server. I'm doing that correctly, aren't I?

    I'll email you a copy of the database if you like, just let me know--because it's much bigger than 100k and it's a very complex database.

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Problem Appending a Table (Access 2000)

    No problem, send it to my e-mail. I'll have a look at it. Don't forget to send the back-end db also.
    One question: Do you store the front-end on the server ? It would be better if each user would have a local copy on their pc's.
    Francois

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
  •