Datatype mismatch on form

[firework]

Hi,

I have been getting this error the whole afternoon and having been trying to reduce the parameters to identify which one is causing the problem. Then tried different datatye but still no joy.

I have double checked again and again with the tables layout, and think only one place would be the problem but as I have already tried different datatype so I am lost.


Dim xtemp as string (also tried long, double, integer)

xtemp = DLookup("[ProjItemID]", "TProjectitems", "[ProjectID] = " & Forms!FProjectItems.ProjectID)

ProjectItemID is autonumber
ProjectID is text
Forms!FprojectItems.projectID is from TProject and the projectID is text

Thank in advance for your help.

 

[pbaldy]

xtemp = DLookup("[ProjItemID]", "TProjectitems", "[ProjectID] = '" & Forms!FProjectItems.ProjectID & "'")

 

[firework]

Hi there,

Thanks for suggestion. But it still doesn't work, the same error.

I have been trying to delete the record rather then read it but still not getting anywhere.

DoCmd.RunSQL "DELETE * FROM TProjectitems WHERE ProjItemID = " & Forms!FProjectItems.ProjectID

Since the lookup doesn't work, I tried to delete. Previously, I have another condition to it but doens't work so I removed the additional criteria from the statement and expect all record which match the projectID will be deleted. NO, it doesn't work. I have the project ID show which is as expected value. So what is going on and what have I done wrong in here ????

 

[pbaldy]

Those seem incompatible. You said ProjectItemID is an autonumber, but ProjectID is text?

 

[firework]

Yes,

ProjectID is text which is the unique key on TProject
ProjectItemID is a autonumber on TprojectItems and ProjectID is another field on the same table but as text which has relationship with TProject

BTW, does anyone has the same experience, I have to keep logging on to the forum. Even I logon to check reply, when I tried to send reply, I was asked to logon again. It is so annoying especially when the database still not working and I have to face the music tomorrow.

 

[Dreamweaver]

Just A Long Shot but
Change
Forms!FProjectItems.ProjectID

For
Forms!FProjectItems!ProjectID

Mick

 

[pbaldy]

Try this

DoCmd.RunSQL "DELETE * FROM TProjectitems WHERE ProjItemID = " & CLng(Forms!FProjectItems.ProjectID)

 

[firework]

Hi pbaldy,

The change doesn't make any different, I have the alert notification on which said 0 row of record deleted. The value for the project ID remain unchanged.

Hi Mick,

Still same error.

I have tried other combinations eg making xtemp as long but not try other combinations yet.

 

[Zaeed]

What does Forms!FProjectItems.ProjectID actually hold? is it text or is it just a number

 

[pbaldy]

How about a sample db?

 

[firework]

Hi,

before I sent my reply, the IE crushed.

the actual value is 1 but as I said, it is a text frield and show so in the form and on the coding "1"

 

[Zaeed]

so if its simply a number, why not change the field type to a number instead of text, then your comparison ProjItemID = " & Forms!FProjectItems.ProjectID should work

 

[firework]

The reason which was 1, just because I was using 1 to test but the project ID shoudl be text.

After saying this, I did run a test with your suggestion and it does work with number. when it works with number, I added the removed additonal condition in and still work.

I went to change the field back to text and got a syntax error. I was running round like a headless chicken at the end, found a sample with 2 conditions. the only different is there is a single quote on the Forms!FpprojectItems!ProjectID

hence the final statement is like
xtemp = DLookup("[ProjItemID]", "TProjectitems", "[ProjectID] = '" & Forms!FProjectItems!ProjectID & "' AND [ItemID] =" & Me!ItemID)


Thanks a milion everyone. Have a good day or a good night. I still have to carry on to work out the rest of the databse before tomorrow meeting. Watch out for me please