Searching MS Access Table and Getting An Error

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
LVL 2
WonHopAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
1
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Mark EdwardsChief Technology OfficerCommented:
"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.
0
WonHopAuthor 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
0
Hamed NasrRetired IT ProfessionalCommented:
Upload a sample database with few objects related to the issue.
0
Mark EdwardsChief Technology OfficerCommented:
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.....
0
Mark EdwardsChief Technology OfficerCommented:
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 & "'")
0
Brendt HessSenior DBACommented:
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

0
WonHopAuthor 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
0
Mark EdwardsChief Technology OfficerCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark EdwardsChief Technology OfficerCommented:
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....
0
Mark EdwardsChief Technology OfficerCommented:
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....
0
Mark EdwardsChief Technology OfficerCommented:
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....
0
WonHopAuthor 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.
1
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< 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.
0
Mark EdwardsChief Technology OfficerCommented:
Glad to be able to help....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.