Solved: Primary Key Criteria in the WHERE clause!

[afh110]

Hi all,

ok im getting a "Type Miss Match" Error when i try to run this snap of code:

I know that the problem is in the RecordKey section in the query. RecordKey value is tobe compared to the 'Key' field in the Activity_Log table. the 'Key' field is of type AutoNumber and RecordKey is of type Integer! How can i solve this issue?

Dim db as DAO.Database
Dim rs as DAO.recordset
Dim strResult as String
Dim User as String
Dim RecordKey as Integer

User="Admin"
RecordKey="4"

strResult = "Select * from Activity_Log Where Creation_User='" + User + "' AND Key='" + RecordKey + "');"

Set db = CurrentDb
Set rs = db.OpenRecordset(strResult)

[OBP]

If me memory serves me correctly, Autonumbers are type Long.
Does the code work with just the User, as the code for using a Form's Field would normally be
strResult = "Select * from Activity_Log Where Creation_User=" & me.User
It does not have the "); on the end.
Here is an example
Dim er As Double, rs As Object, SQL As String
SQL = "SELECT LabourRates.* " & _
"FROM LabourRates " & _
"WHERE Date1 <= #" & Format(Me.Date, "mm/dd/yyyy") & "#" & _
"And Date2 > #" & Format(Me.Date, "mm/dd/yyyy") & "#"
Set rs = CurrentDb.OpenRecordset(SQL)
er = rs.EndUserRate
rs.Close
Set rs = Nothing

[OBP]

Or
Dim SQL As String
zeroes = "000000": year = Format(Date, "yyyy"): month = Format(Date, "mm")
SQL = "SELECT Que_CallNumber.* " & _
"FROM Que_CallNumber " & _
"WHERE DealerCode = " & Me.Dealercode
Set rs = CurrentDb.OpenRecordset(SQL)

[afh110]

The code works fine without the RecordKey! it works just fine with the User as the only parameter.

I tried setting RecordKey to type Long, but still the same error!

[OBP]

OK, remove the ' from around the RecordKey, as in
strResult = "Select * from Activity_Log Where Creation_User='" + User + "' AND Key=" + RecordKey + ");"

[afh110]

Nope.. same error. I really think it has more to do with the Dim type (Integer/Long..ect)

[CreganTur]

Looking at your original code I see one obvious error:

RecordKey="4" '<<<Why are you trying to pass in a string value for an Int?


Try this code:
Dim db as DAO.Database
Dim rs as DAO.recordset
Dim strResult as String
Dim User as String
Dim RecordKey as Long

User = "Admin"
RecordKey = 4

strResult = "Select * from Activity_Log Where Creation_User='" + User + "' AND Key=" + RecordKey + ");"

Set db = CurrentDb
Set rs = db.OpenRecordset(strResult)

See if that takes care of the issue.

[afh110]

Hi Randy,

your right about my typo... the "4" is a string in this case, but just just typed it to as an example. In my actual code, RecordKey is getting its value from a combo box which i then do a Cint() to convert it to an integer and load it into RecordKey. (Recordkey=cint(comboBox.value))

but the primary issue remains.. VBA doesnt like me trying to pass RecordKey of type int or long as a parameter for the Key column in my table of type AutoNumber

[Imdabaum]

Hi Randy,

your right about my typo... the "4" is a string in this case, but just just typed it to as an example. In my actual code, RecordKey is getting its value from a combo box which i then do a Cint() to convert it to an integer and load it into RecordKey. (Recordkey=cint(comboBox.value))

but the primary issue remains.. VBA doesnt like me trying to pass RecordKey of type int or long as a parameter for the Key column in my table of type AutoNumber

Have you tried that same code with Clng(comboBox.value)?

[afh110]

Hi Randy,

your right about my typo... the "4" is a string in this case, but just just typed it to as an example. In my actual code, RecordKey is getting its value from a combo box which i then do a Cint() to convert it to an integer and load it into RecordKey. (Recordkey=cint(comboBox.value))

but the primary issue remains.. VBA doesnt like me trying to pass RecordKey of type int or long as a parameter for the Key column in my table of type AutoNumber

[afh110]

I just tried using Clng() but still the same damn error!!!!

[afh110]

Hi Randy,

your right about my typo... the "4" is a string in this case, but just just typed it to as an example. In my actual code, RecordKey is getting its value from a combo box which i then do a Cint() to convert it to an integer and load it into RecordKey. (Recordkey=cint(comboBox.value))

but the primary issue remains.. VBA doesnt like me trying to pass RecordKey of type int or long as a parameter for the Key column in my table of type AutoNumber

[OBP]

Why not post a simplified version of your database in Access 2003 format for us to work with?

[Imdabaum]

I just tried using Clng() but still the same damn error!!!!


I've never used + as a method for concatination in VBA. Does it work the same way & does?

[afh110]

Trying to upload the DB but its just not letting me! My internet connection is crap. Anything else i can do other than DB to help u help me :D?

[OBP]

Did you mean to post an Attachment?

[Imdabaum]

Trying to upload the DB but its just not letting me! My internet connection is crap. Anything else i can do other than DB to help u help me :D?


Did you zip it first?
Also compacting it before you zip might help trim it down.

[afh110]

I did that already. tried again, not workin!!

[afh110]

Ok for some odd reason now everything works! so two points to sum it all up
1- Set the Variable that is carrying in the RecordKey to type Long
2- remove the single quotes around the RecordKey in the Sql query as in
" Where Activity_Log.Key=" & RecordKey &";"
vs.
"Where Activity_Log.Key='" & RecordKey &"';"

pretty much what you guys recommended, but i guess i was missing something . But not it works :)
Thanks to you all

[Imdabaum]

Congrats.