Retrieve Record autonumber of data recently exported into external db

[Amras]

Good evening, all. Thanks for taking a moment to review my situation.



I've created a form and code in a '97-2003 Access db that will export the data from a local table into into an Access 2010 database via an INSERT INTO SQL statment. Once the data has been exported, I need to retrieve the "Record" autonumber from the 2010 database to use in an email that's being sent from the '97-2003 database. This task is probably simpler than I know, but I'm not writing my code correctly. Here's the relevant code:

'-------Export the contents of tblHSGrievanceData to the Medicare Grievances back-end db
530     strSQL = "INSERT INTO tblHSGrievanceData IN 'R:\Account Management Team  DO NOT DELETE!!!\Medicare\Admin\Medicare Grievances_BackEnd.accdb' " & vbCrLf & _
                    "SELECT * " & vbCrLf & _
                    "FROM tblHSGrievanceData;"
540     DoCmd.RunSQL strSQL
        
'-------Ensure the local tblHSGrievanceData is empty
'550     strSQL = "DELETE * " & vbCrLf & _
'                    "FROM tblHSGrievanceData;"
'560     DoCmd.RunSQL strSQL

561     Set db = CurrentDb()
562     strSQL = "SELECT tblHSGrievanceData.Record from tblHSGrievanceData IN 'R:\Account Management Team  DO NOT DELETE!!!\Medicare\Admin\Medicare Grievances_BackEnd.accdb' " & vbCrLf & _
                    "WHERE (((tblHSGrievanceData.TDate)= Date()) AND ((tblHSGrievanceData.HealthSpringID)= '" & Me.txtID & "'));"
                    
563     Set rsRecord = db.OpenRecordset(strSQL, dbOpenDynaset)
564     MsgBox rsRecord

The error that I'm getting is runtime error 13, type mismatch on line 563. Line 564 was just for myself to see if the code worked! I patterned lines 561 - 563 from information found at this web-site:

I appreciate any help that can be provided. Again, this is probably a relatively simple matter that I'm just not getting!

Make it a great day!

[CJ_London]

type mismatch means you are trying to compare incompatible types - in this case text and numbers so looks like your problem is here. numeric values do not use quotes (only text) so remove them

HealthSpringID)= '" & Me.txtID & "'));"

for future reference, after you have created your strSQL, add another line of code

Debug.Print strSQL

This will put the completed string in the immediate window - copy and paste this into a new query sql window and try to run it, the error will be much more obvious

[ItsMe]

You are using an Action query with your DAO. Try a SELECT query or don't use the DAO opendynaset.

[Amras]

Thanks for you quick reply Ajax, but that isn't the problem. The txtID field is being saved as text rather than a number since it isn't used in an calculations. I actually did have Debug.Print strSQL in place and it returned the the SQL statement with what I had entered as the ID number on the form. And when I entered that SQL statement into new query window, it returned exactly what I wanted/needed.

When I tried to remove the "dbOpenDynaset" from line 563, I got the same error ItsMe.

[ItsMe]

My vote is the date field, then. I did not see that strSQL was being reused.

(((tblHSGrievanceData.TDate)= Date())

[Amras]

I believe that the error is strictly with line 563 now, "Set rsRecord = db.OpenRecordset(strSQL)" or "Set rsRecord = db.OpenRecordset(strSQL, dbOpenDynaset)". Neither option is working for me.

[Amras]

Should I use something other than "dbOpenDynaset"?

[ItsMe]

Should I use something other than "dbOpenDynaset"?

I thought you were using this
strSQL = "INSERT INTO tblHSGrievanceData

But you are not. strSQL is reused with a select statement. If it is not the Date field and it is not the Text being inserted into a Number field, it must be the carriage return.

accdb' " & vbCrLf & _
                    "WHERE (((tblHSGrievanceData

Just use the line continuation & _ and not the vbcrlf

[CJ_London]

The txtID field is being saved as text rather than a number since it isn't used in an calculations

if you say so, though not at all clear from your code and don't understand your rationale, two tables with the same name, same data but different datatypes. Perhaps your TDate in the other table is text as well? It has to be one or the other.

[ItsMe]

Meh,

You need to double up on the quotes. You are in VBA. I'm not good wih single quotes but this should work. Other wise wrap the path in single quotes (something like "'" I think).

Try this

"SELECT tblHSGrievanceData.Record from tblHSGrievanceData IN ""R:\Account Management Team  DO NOT DELETE!!!\Medicare\Admin\Medicare Grievances_BackEnd.accdb"""

[Amras]

Thanks again for your continued persistence, ItsMe. I've found that line 563 works like this:

Set rsRecord = db.OpenRecordset(strSQL)

Now I just need to extract the Record number from the recordset. When I changed the msgbox to this:

Set rsRecord = db.OpenRecordset(strSQL)

...it popped-up with the SQL exactly as needed and the rest of my code ran flawlessly. Again, now I just need to extract that Record number from the recordset. My mind is blank right now. Could you recommend how to do so?

[ItsMe]

My mind is blank right now. Could you recommend how to do so?

Mine mind is a little tired too. Had a hard time reading your code. Let me take another look. If you could not get a dynaset to open, this means that the query was not updatable because of a join or something. Let me take a step back and come at it again.

[ItsMe]

If you say it works, it works. I think you have a couple more issues. You have spaces in your path and file name for the external DB. I think there may be some issues there that can be managed with extra quotes. Also, I believe Record is a reserved name. So I would change tblHSGrievanceData.Record to tblHSGrievanceData.[Record]

But if everything is working

     Set rsRecord = db.OpenRecordset(strSQL)
     
     rsRecord.MoveFirst
     MsgBox rsRecord![Record]

[Amras]

Thanks, ItsMe. That's basically what I did while tinkering with it a moment ago. I used this code and got the results I was looking for:

563     Set rsRecord = db.OpenRecordset(strSQL)
5631    strRecord = rsRecord.[Record]
5632    MsgBox strRecord

Now I can use strRecord where I need to later in my code. Really appreciate your pointers and tenacity, ItsMe! Have a great evening!

[ItsMe]

OK, it still looks crooked to me but as long as your happy...