UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Data Type Conversion Error, Access 2007    
 
   
nmartin1230
post Dec 20 2018, 03:00 PM
Post#1



Posts: 470
Joined: 20-May 14



I'm getting the error when this code is run on form load event. I got this code from this site in the code archive a few years back. I have since used it a few times in different DB's with success.

CODE
Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("ztblUserLog", dbOpenDynaset)
    
    With rst
        .AddNew
        ![SecurityID] = User.SecurityID
        .Update
    End With


I have a module that I got from the archive and a table to store the data. The data types for the table fields are as follows: LogID - AutoNumber, SecurityID - Number, TimeIn - Date/Time and TimeOut - Date/time. The field SecurityID is a fk from another table. This is the code in the module.

CODE
Public Type UserInfo
    ViewID As Integer
    AccessID As Integer
    Active As Boolean
    Password As String
    UserID As String
    SecurityID As String
End Type

Public User As UserInfo
Go to the top of the page
 
theDBguy
post Dec 20 2018, 03:03 PM
Post#2


UA Moderator
Posts: 76,801
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Which line is hitting the error?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
nmartin1230
post Dec 20 2018, 03:10 PM
Post#3



Posts: 470
Joined: 20-May 14



oh sorry, I guess I left that part out. iconfused.gif

It is erroring out on ![SecurityID] = User.SecurityID
Go to the top of the page
 
theDBguy
post Dec 20 2018, 03:31 PM
Post#4


UA Moderator
Posts: 76,801
Joined: 19-June 07
From: SunnySandyEggo


Thanks. What is the data type of the !SecurityID field in the table?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
nmartin1230
post Dec 20 2018, 03:38 PM
Post#5



Posts: 470
Joined: 20-May 14



The field's data type is Number - long Integer. I have changed the data type to Text and declared it as a String and as an Integer to no avail. When I have changed it to Text or declare it as an Integer I do not get the error but the value is not passed to the table. When I debug it gives the correct value for it. The only thing I can think of is that maybe at the time the code is fired the value is null?
Go to the top of the page
 
theDBguy
post Dec 20 2018, 04:12 PM
Post#6


UA Moderator
Posts: 76,801
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I don't know what you have done so far but I asked about the data type because you have declared SecurityID As String in your Public Type UserInfo. So, if the field was a Long Integer, then assigning a String to it might give a Type Conversion error.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
nmartin1230
post Dec 20 2018, 04:22 PM
Post#7



Posts: 470
Joined: 20-May 14



What do you suggest that I change it to? I've changed the data type to Text previously and the error went away but the value wasn't being passed to the table.
Go to the top of the page
 
theDBguy
post Dec 20 2018, 04:29 PM
Post#8


UA Moderator
Posts: 76,801
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I was just saying you were probably getting the type conversion error because you were trying to match two different data types. I can't tell you what to change it to without knowing how you're initially assigning a value to User.SecurityID. Can you show us that part of the code please? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
nmartin1230
post Dec 21 2018, 09:27 AM
Post#9



Posts: 470
Joined: 20-May 14



Sorry, I miss understood you. I will include the sample security db I am using the code from in the hopes that it will help. In the sample, it has no error. All I did was copy the code into my forms and changed some of the code to open or close my forms. It's getting the value from tblSecurity field by the same name which in that table is stored as a number.
Attached File  98966_SecurityDemo2000.zip ( 91.19K )Number of downloads: 7
Go to the top of the page
 
theDBguy
post Dec 21 2018, 09:32 AM
Post#10


UA Moderator
Posts: 76,801
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Not sure how a working demo will help us help you figure out what could be wrong with some of the changes you made to the code after copying it into your database if you don’t show us those changes. Why can’t you show us your code?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
jwild
post Dec 21 2018, 09:46 AM
Post#11


UtterAccess VIP
Posts: 872
Joined: 28-September 05
From: Ontario, Canada


Seems to me, if SecurityID is defined as Number, LongInteger in the table then your type declaration should be
SecurityID as Long
Joan

--------------------
Joan Wild
Go to the top of the page
 
nmartin1230
post Dec 21 2018, 10:57 AM
Post#12



Posts: 470
Joined: 20-May 14



Okay, well I got it to work. I moved the code from the on load event to form timer event. Kept the variable SecurityID as String, in the table ztblUserLog field SecurityID kept its data type as Number. The one thing I changed was the part of the code where it was updating to the ztblUserLog with ![SecurityID]=User.UserID and it insert the UserID into the table and all I did was changed it back to ![SecurityID]=User.SecurityID and it started inserting the correct UserID into the table. Thanks for all the help! And sorry to theDBguy for not uploading my actual DB, it has a lot of personal info it and other items. Didn't want to take the time to sanitize it. I understand what you were saying about not being able to help when you can't see what I am talking about first hand. Again my sincerest apologies and gratitude to you and the very helpful and awesome forum and it's people. Happy Holidays to everyone!!!!! uarulez2.gif
Go to the top of the page
 
theDBguy
post Dec 21 2018, 11:14 AM
Post#13


UA Moderator
Posts: 76,801
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Congratulations! We're glad to hear you got it sorted out in the end. Good luck with your project and Merry Christmas!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 06:26 AM