DCount "You canceled the previous operation"

[lars7]

Hi Again,
This one's driving me crazy,
I am updating 3 fields in my staff table with the following code:

CurrentDb.Execute "UPDATE tblstaffCodes SET Discipline = '" & Forms!FormJobDescriptions!FormStaffLookup!Discipline & "' WHERE PayNumber = '" & Forms!FormJobDescriptions!FormStaffLookup!Text1 & "' and AdditPayNumber =0"
CurrentDb.Execute "UPDATE tblstaffCodes SET Grade = '" & Forms!FormJobDescriptions!FormStaffLookup!Grade & "' WHERE PayNumber = '" & Forms!FormJobDescriptions!FormStaffLookup!Text1 & "' and AdditPayNumber =0"
CurrentDb.Execute "UPDATE tblstaffCodes SET JobDescription = '" & Forms!FormJobDescriptions!FormStaffLookup![Job Title/Description] & "' WHERE PayNumber = '" & Forms!FormJobDescriptions!FormStaffLookup!Text1 & "' and AdditPayNumber =0"

This works fine now (thanks rjoubert) but after this I want to change the "additPayNumber" to whatever record number it is for that staff member by counting their "PayNumber" with the following code:

Dim varx As Variant
varx = DCount("*", "[tblStaffCodes]", "[PayNumber]=" & Forms!FormJobDescriptions!FormStaffLookup!Text1)
CurrentDb.Execute "UPDATE [tblStaffCodes] SET [AdditPayNumber]=" & varx & " WHERE [AdditPayNumber] =0"

The button that fires the code is on a subform "FormStaffLookupSubform" which is on a form "FormStaffLookup" which is selected on a tab control on "FormJobDescriptions" the problem I have is that I am getting an error message saying "You canceled the previous operation" can anyone offer me any advice or help with this.

 

[GKChesterton]

But this message also appears when there is a different kind of error, when you attempt to use an SQL statement from VBA in which the data type of a WHERE clause's criteria does not match the data type of the corresponding field. For example you might have a date field and supply a text data type: [purple]WHERE tblStaff.BirthDate='Tuesday’[/purple] instead of matching the data types correctly: [purple]WHERE tblStaff.BirthDate=#09/27/1950#.[/purple]

This found at

http://www.fontstuff.com/access/acctut15.htm

(scroll to bottom of long page).

http://p2p.wrox.com/topic.asp?TOPIC_ID=30864

also has information about getting the fastidious details of your SQL right.

[purple]If we knew what it was we were doing, it would not be called
[blue]research[/blue] database[white].[/white]development, would it? [tab]-- Albert Einstein[/purple]​

 

[lars7]

Hi GK,
Thanks for responding.

Text1 --> unbound text box. paynumber --> text field

AdditPayNumber --> number field.

I have tested the varx part of the code on the main form and it works fine, it seems to have something to do with referencing text1 from the subform any idea how to overcome this without moving the button permanently.

 

[GKChesterton]

A troubleshooting step that may help: Test the SQL by pasting
it into an Access Query object (SQL View) and running it.

To capture the SQL, take your execute line as generated in code and run it to the debug window. Use that for your paste. If it doesn't work in the Query design window, fix it and use that to improve your code.

[purple]If we knew what it was we were doing, it would not be called
[blue]research[/blue] database[white].[/white]development, would it? [tab]-- Albert Einstein[/purple]​

 

[lars7]

Hi Again,

This is the code I used from a command button on the form "FormStaffLookup":

Dim varx As Variant
varx = DCount("*", "[tblStaffCodes]", "[PayNumber]='" & Me![Text1] & "'")
CurrentDb.Execute "UPDATE [tblStaffCodes] SET [AdditPayNumber]=" & varx & " WHERE [AdditPayNumber] =0"

and it works fine. My problem only occurs on the subform with this part:

"[PayNumber]=" & Forms!FormJobDescriptions!FormStaffLookup!Text1)

althou when debuging it does give me a reading of the paynumber in text1.

 

[lars7]

Hi GK,

I put a text box on my subform that with this control source:

=Forms!formJobDescriptions!FormStaffLookup!text1

and it work fine, thanks for taking the time to respond to my problem.