Data type mis match error

[Eranka]

Hi



when i run the below code i get the error "Data type mismatch in criteria expression"

CurrentDb.Execute "Update tblUser SET Pdate = Now() WHERE ID = '" & Me.UserLogin & "'"

Table Fields:
1. Pdate = Date/Time
2.UserLogin = Number

how to avoid this error?

[isladogs]

You have used text delimiters for loginID. Change it to

CurrentDb.Execute "Update tblUser SET Pdate = Now() WHERE ID = " & Me.UserLogin

[ranman256]

use a query instead of sql. It eliminates a lot of these errors.

[Eranka]

Hi Ridders

Thanks alot mate. it worked.

When i run the above code, after executing it i get the below message, how can ignore below message?

[isladogs]

Oh no - the dreaded write conflict error...
It can be one of the hardest to track down and you can't bypass it using error handling
Nor should you ignore it

Does it still happen if you:
a) restart the app
b) decompile & then compact
c) are the only user

If so, you need to identify the cause & fix it.
I used to get this with a split database & a SQL server BE.
On upsizing to SQL Server, I had neglected to set default values for boolean fields in several tables.
This is an issue as unlike Access boolean fields can be null in SQL Server
This confuses Access & triggered the write conflict in certain situations e.g. update queries
The solution in my case was to set default values for all boolean fields in all tables
I've never seen the error since.

If that's not the issue in your case, you will need to track down the cause methodically.
I would do so as a priority otherwise it is likely other issues will arise from this

[Eranka]

Hi ridders,

i doesn't happen to me when
a) restart the app
b) decompile & then compact
c) are the only user

i shows when i run the update query only.

[isladogs]

I see nothing in the update query that would trigger it.
Does it happen if you run that line on it's own rather than as part of a procedure?
What about if you run a query with that code?

If both still trigger it, look at the table design carefully. In fact do that first as it should be quick to check it for issues.

[Eranka]

Hi Ridders


instead of currentfb.execute cani use something like below?

StrSQL2= "Update tblUser SET Pdate = Date() WHERE ID = " & Me.UserLogin

do you this will work?

[isladogs]

Yes you can use a sql statement like this:

CurrentDb.Execute strSQL

Or

DoCmd.RunSQL strSQL

However these won't solve your write conflict issue. You'll need to sort out what's causing that.

[Eranka]

Hi Ridders

i used below before the update statement, it eliminated the write conflict error.

DoCmd.RunCommand acCmdSaveRecord

Thank you for your time and help.

[isladogs]

If that's all it was, you were very lucky.
If so, this would also have worked

If Me.Dirty Then Me.Dirty=False