asked on

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

PatHartman

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)

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)

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.

Mark Edwards

"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.

WonHop

ASKER

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 Nasr

Upload a sample database with few objects related to the issue.

Mark Edwards

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 Edwards

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 & "'")

Brendt Hess

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

WonHop

ASKER

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

ASKER CERTIFIED SOLUTION

Mark Edwards

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Edwards

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 Edwards

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 Edwards

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....

WonHop

ASKER

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)

<< 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 Edwards

Glad to be able to help....