Searching MS Access Table and Getting An Error

WonHop
WonHop used Ask the Experts™
on
Hello All.  I need to loop thru  "tbl_Compiled_List_Working", get a Term.  Then find that Term in "tbl_Appendix_A_Working".
Every thing works fine until it tries to do the actual Search.

I get a "runtime error 3421 data type conversion error"

Both are "Short Text" in the tables.

Dim rsttblCompiledListWorking As Recordset
Dim rsttlAppendixAWorking As Recordset
Dim strtxtTermCLW As String
Dim strtxtDefinitionCLW As String

Dim db As Database

Set db = CurrentDb
Set rsttblCompiledListWorking = db.OpenRecordset("tbl_Compiled_List_Working", DB_OPEN_DYNASET)
Set rsttblAppendixAWorking = db.OpenRecordset("tbl_Appendix_A_Working", DB_OPEN_DYNASET)

rsttblCompiledListWorking.MoveLast
inttblCompiledListWorking = rsttblCompiledListWorking.RecordCount

intCount = 0

If inttblCompiledListWorking > 0 Then
    rsttblCompiledListWorking.MoveFirst
    Do Until rsttblCompiledListWorking.EOF = True
        strTermCLW = rsttblCompiledListWorking!txt_Term_CLW

        THIS IS WHERE THE ERROR MESSAGE HAPPENS,
        rsttblAppendixAWorking.OpenRecordset "Select * From tbl_Appendix_A_Working Where tbl_Appendix_A_Working.txt_Term_AAW = '" & strTermCLW & "'"
     
         Other code stuff
   Loop
End If
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
To resolve this error, create a new variable to hold the select statement and use that variable in the OpenRecordset method.  That way, you can put a stop in the code and display the exact SQL string that is being executed.  You can also copy that string into the QBE in SQL view and run it there where you might get better, more informative errors.

On the surface, the likely issue is that strTermCLW contains a single quote.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Use option explicit at the top of the module and try to do a compile.

You've got things like:

Dim strtxtTermCLW As String

vs

 & strTermCLW &

Jim.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Also, unless you *absolutely* need the record count, do this:

intCount = 0

 Do Until rsttblCompiledListWorking.EOF = True
        strTermCLW = rsttblCompiledListWorking!txt_Term_CLW

        THIS IS WHERE THE ERROR MESSAGE HAPPENS,
        rsttblAppendixAWorking.OpenRecordset "Select * From tbl_Appendix_A_Working Where tbl_Appendix_A_Working.txt_Term_AAW = '" & strTermCLW & "'"
     
         Other code stuff
   Loop

 If .EOF is not true right off, then there are *some* records.    Same is true if you check for .RecordCount >0.   That also tells you there are some records, just not how many.

Jim.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mark EdwardsChief Technology Officer

Commented:
"Data Type Conversion" can occur for several reasons:
First, you need to handle those situations where the table field may be null and you're trying to populate a string variable with a null:
"strTermCLW = rsttblCompiledListWorking!txt_Term_CLW"
blows up if txt_Term_CLW is null.  Can't put a null into a string variable.

Also, you are putting your recordcount into "inttblCompiledListWorking" where the prefix "int" usually indicates an integer which is limited to a little over 32K.  Also, I don't see where you declare "inttblCompiledListWorking", so can't tell if this an issue.

Also, is "tbl_Appendix_A_Working.txt_Term_AAW" a string field where you are trying to compare it to a string.  If it isn't, there's your problem.

See if any of the previous tips and these solve your problem.

Author

Commented:
PatHartman - I don't know how to do the SQL thing you mentioned.  If the Single Quote could be the issue.  What should the syntax be?

Jim Dettman - I did the Option Explicit.  That did not work.  The Tern "A/L" is captured in the statement:
strTermCLW = rsttblCompiledListWorking!txt_Term_CLW

Mark Edwards- For the first one, The Tern "A/L" is in both tables.  At some point I will run into the case where it will not find a match.
When that happens, I will add the Term.

Right now, it is not giving me results when there is a for sure match in both tables.

Could it be the wrong syntax?

Thanks
WonHop
Hamed NasrRetired IT Professional

Commented:
Upload a sample database with few objects related to the issue.
Mark EdwardsChief Technology Officer

Commented:
Okay, does anyone see what's wrong with:

rsttblAppendixAWorking.OpenRecordset "Select * From tbl_Appendix_A_Working Where tbl_Appendix_A_Working.txt_Term_AAW = '" & strTermCLW & "'"

the method "OpenRecordset" of a recordset object takes two optional parameters:  .OpenRecordset [Type], [Options]
The SQL statement is neither and doesn't match the data type for either parameter.

Somebody fix this for this guy.....
Mark EdwardsChief Technology Officer

Commented:
Okay, the suspense is killing me....

 Set rsttblAppendixAWorking = db.OpenRecordset("Select * From tbl_Appendix_A_Working Where tbl_Appendix_A_Working.txt_Term_AAW = '" & strTermCLW & "'")
You can simplify this into a single query, using only one connection. Try this code (note: I may have minor syntactical errors because I don't have access to Access right now):

Dim rsttblCompiledListWorking As Recordset
Dim rsttlAppendixAWorking As Recordset
Dim strtxtTermCLW As String
Dim strtxtDefinitionCLW As String

Dim db As Database

Set db = CurrentDb

intCount = 0

Set rsttblCompiledListWorking = db.OpenRecordset("Select * From tbl_Appendix_A_Working INNER JOIN tbl_Compiled_List_Working  ON tbl_Appendix_A_Working.txt_Term = tbl_Compiled_List_Working.txt_Term_CLW", DB_OPEN_DYNASET)

   Do Until rsttblCompiledListWorking.EOF = True
         <Other code stuff>
   Loop
End If 

Open in new window


You would replace the asterisk in the query above with the actual fields you need from both recordsets. If there are not multiple records in the tbl_Appendix_A_Working table for any given value, you will go through the same number of rows total. As an alternative, if you want to work just with the data in tbl_Appendix_A_Working, you can specify the table name in front of the asterisk, e.g.
SELECT tbl_Appendix_A_Working.* FROM ...

Open in new window

Author

Commented:
Here is the Fake Version with Fake Data.  The Code is in Module 1.  Company won't let be send actual Data.
Table Name and Field Name are the same.

I copied some previous code and tried just changing the names.  I was fixing stuff line by line.
When I got to the Select line.  I just could not fix it.

Thanks
WonHop
Database21.accdb
Chief Technology Officer
Commented:
Wonhop:

I downloaded your db and made the change as I described above and the line now works, but you misspelled the recordset  name a little farther down:

        Set rsttblAppendixAWorking = db.OpenRecordset("Select * From tbl_Appendix_A_Working Where tbl_Appendix_A_Working.txt_Term_AAW = '" & strTermCLW & "'")

Now this line fails because you misspelled "rsttlAppendixAWorking" - should have a "b" in "rsttl" as "rsttbl"

                intWorkingRecordCount = rsttlAppendixAWorking.RecordCount
Mark EdwardsChief Technology Officer

Commented:
Further analysis shows that the recordset "rsttlAppendixAWorking" is declared, but never set, thus causing the "object variable missing or not set" error in the line:

  intWorkingRecordCount = rsttlAppendixAWorking.RecordCount

You've still got a little house cleaning to do....
Mark EdwardsChief Technology Officer

Commented:
okay, my bad.  I see you have the recordset "rsttblAppendixAWorking" set at the beginning.  I think you want to set
rsttlAppendixAWorking:
Set rsttlAppendixAWorking = db.OpenRecordset("Select * From tbl_Appendix_A_Working Where tbl_Appendix_A_Working.txt_Term_AAW = '" & strTermCLW & "'")

Sorry for the confusion....
Mark EdwardsChief Technology Officer

Commented:
Just a tip:  when naming things in code with the same name, better to just add a "_2" on the end instead of dropping a letter in the middle of a long name - those kinds of difference are hard to see and look like typos....

Author

Commented:
Mark.  That works.  
Thanks for the tip.  I have since learn that.  The code that I was using worked when I wrote it in 2009. I was trying to replace the names for the current project.  I have learned some about naming since then because of that very reason.  :0)
I was making changes line by line.  Then I got stuck
I think I can move on from here.  If I have any other questions, I will be back.


Thank you very much.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<< I was trying to replace the names for the current project. >>

 Make sure you are always using option explicit.    If you mis-spell something, it helps in that you will get a compile error if you have not Dim'd it.

Jim.
Mark EdwardsChief Technology Officer

Commented:
Glad to be able to help....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial