Strange Problem Appending a Table (Access 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

StudID is a text field.

Reply | Quote

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 ?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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=”. What does this mean?

Reply | Quote

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 from Hans to reduce the size of the db under the 100K

Reply | Quote

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.

Reply | Quote

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 ?

Reply | Quote

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 ?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Looking at your db, the record source for the frmInvoices by student has no records.
I change the recordsource to tblStudent.
I modify the Last Name control to a textbox displaying the Last Name.
I add a comboBox in the footer of the form to allow you to search for a student. In this combobox I add code to find the student.
I send you the modified db by e-mail.

Reply | Quote

Works great now! Thanks so much, Francois.

Reply | Quote

Works great now! Thanks so much, Francois.

Reply | Quote

Looking at your db, the record source for the frmInvoices by student has no records.
I change the recordsource to tblStudent.
I modify the Last Name control to a textbox displaying the Last Name.
I add a comboBox in the footer of the form to allow you to search for a student. In this combobox I add code to find the student.
I send you the modified db by e-mail.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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 from Hans to reduce the size of the db under the 100K

Reply | Quote

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=”. What does this mean?

Reply | Quote

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 ?

Reply | Quote

StudID is a text field.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote