Another record lock violation problem 3

[JAES]

I have what seems like a very simple forumla that I can't get to work. In a multi-tab form two of my fields are TypeofInvestigation which is a combo box and CaseFileClosed which is a Yes/No box. All I want is when the phrase "Close" is somewhere in the TypeofInvestigation that the CaseFileClosed is changed to Yes. I have placed the following code in the TypeofInvestigation OnLostFocus event. I have just been trying to figure out how to do a specific combo box choice of "Opening/Closing" before trying to figure out how to get all of them to respond. I keep getting the "Didn't update... 1 record due to lock violations" error. Even stranger, the very first time I go into the database it seems to work, once.

Any help would be appreciated.

Private Sub Text87_LostFocus()

If Me![TypeofInvestigation] = "Opening/Closing" Then
DoCmd.OpenQuery "queCloseCase"
End If
End Sub

The SQL view of that query is
UPDATE [Case Files] SET [Case Files].CaseFileClosed = 1
WHERE ((([Case Files].[Case File Number])=[Forms]![frmEditInvestigationInformation]![CaseFileNumber]));


I also tried to use the DoCmd.RunSQL with the code directly with the same results.

I could not find a similar answer in my search of the TekTips forum.

 

[RichD]

JAES,

I would add another column to your combobox with the required value of your yes/no box. ie -1 or 0.

Use the afterupdate event of your combo box:

Private Sub Text87_AfterUpdate()
me.casefileclosed = me.typeOfInvestigation.column(1)
end sub

This assumes that the -1 or 0 is in the second column of your combobox.

HTH
RichD



Bespoke and off-the-shelf Access solutions

http://www.blsoftware.co.uk

 

[TheAceMan1]

How are ya JAES . . .

Perhaps this:

[blue]If InStr(Me![TypeofInvestigation].Column(1), "Clos") > 0 Then
   DoCmd.OpenQuery "queCloseCase"
End If[/blue]

You may have to play with [blue]Column(?)[/blue]. remember column index starts at zero and includes any columns hidden (width = 0) in the [blue]Column Widths[/blue] property.

See Ya! . . . . . .

 

[JAES]

Rich D.

I thought yours would be the simple one so I tried it first. (the query itself gave me errors too) When run, I get a visual basic error of:

Compile error: Method or data member not found.

The highlighted portion is .Column

The combo box TypeofInvestigation shows the second column with the proper -1 or 0 when I make the choice.

Jeff

I tried yours AceMan and it hangs up near the same spot with an error of Object does not support this property or method. I must be looking left when you guys say right!!!

 

[TheAceMan1]

JAES . . .

Scratch my initial post.

[blue]All I want is when the phrase "Close" is somewhere in the TypeofInvestigation that the [purple]CaseFileClosed is changed to Yes[/purple].[/blue]

I keep trying to figure out why your updating thru a query (unless your talking unbound controls). All you should need is:

[blue]If InStr(Me![TypeofInvestigation].Column(1), "Clos") > 0 Then
   Me.CaseFileClosed = true
End If[/blue]

Also . . . I don't know how your triggering the updating of [blue]CaseFileClosed[/blue] but indications are the record isn't saved yet. [purple]Thru query, you can only update previously saved records.[/purple]

Aside from the above your query is wrong. Should be:

[blue]UPDATE [Case Files] SET CaseFileClosed = True
WHERE ([[purple][b][i]PrimaryKeyName[/i][/b][/purple]]=[Forms]![frmEditInvestigationInformation]![[purple][b][i]PrimaryKeyName[/i][/b][/purple]]);[/blue]

[blue]Your Thoughts? . . .[/blue]

BTW: [blue]"Opening/Closing"[/blue] does not contain [blue]"Close"[/blue] . . .

See Ya! . . . . . .

 

[JAES]

Thanks TheAceMan1 - I agree it would be better to do it with code not a query. I was just getting errors with both and the query was something I thought I could test easier. I'm sticking with code and not looking back!

Here is the code now. BTW good catch on the Clos vs Close!!

Private Sub Text87_AfterUpdate()

If InStr(Me![TypeofInvestigation].Column(1), "Clos") > 0 Then
Me.CaseFileClosed = True
End If

End Sub

When I exit the combo box I now get the error: Runtime error "438" object doesn't support this property or method.
I get the message even if "Clos" is not in the description.

I'm currently showing the CaseFileClosed field just to verify it changes. I can check and uncheck the box with no problems. Both columns are bound. I'm leaning towards your suggestion that the problem is in the updating, of which I'm not doing anything special.

Help me Mr. Wizard

 

[PHV]

And this ?
If InStr(Me![Text87].Column(1), "Clos") > 0 Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JAES]

Sorry guys, I think I led you astray with calling the column TypeofInvestigation when in fact the name is Text87. (I got lazy back when this was first built).

PHV - I changed the code to:

If InStr(Me![Text87], "Clos") > 0 Then
Me.CaseFileClosed = True

I got rid of the Column(1) since I abandoned the second column in the table. It works great now. Sorry I wasted so much of everbody's time. I will be more careful in the future!

Jeff