Write conflict problem - simplified version of thread702-810987 2

[hanosm]

Nobody seems to have replied to thread702-810987, so I thought i'd ask the question in a more simplified way.

I have a form called frmOrder based on table tblOrder. In tblOrder there is a memo field called GoodsComments.

In frmOrder, instead of placing GoodsComments directly on this form, I have created a seperate form (frmGoodsComments) also based on tblOrder. frmGoodsComments only has the GoodsComments fields on it.

On frmOrder, there is a button that opens frmGoodsComments. Whenever I make any changes to frmGoodsComments and move away from the current record or save the record in frmOrder, I get a write conflict and I can't save the changes.

I understand why this is happening, as I have the same record opened by two different forms. I have changed the record locks option to "no locks" on both forms, but still can't get around the problem.

Any ideas??

 

[JeremyNYC]

You won't be able to do this wiht two forms, no matter what the locking setting. Why do you want a separate form for this? If you're trying to save on screen real estate, I would suggest using a tab control, which will allow the user to see the more commonly used controls when the form is opened and give them the ability to flip to a different tab that just contains the comments field.

Jeremy



==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[hanosm]

Jeremy,

Thanks for the reply. I was just trying to save "screen real estate" as you so eloquently put it.

It seems a bit of a waste to have a tab control just for a comments box. It looks like I am going to have to put the GoodsComments field onto the frmOrder form, reduce it's size a bit and add a scrollbar.

Thanks again.

Hanif

P.S. Would you mind having a look at thread702-810987? I would appreciate any answers to point 1.

 

[OnTheFly]

If you want to use a seperate 'pop-up' type form for comments, try making it an unbound form and then on close or clicking a save button. Insert it into the record. I do this on many occasion and it works fine.

Hope this helps.

OnTheFly

 

[hanosm]

OnTheFly

How would I do this? I'm guessing I create a seperate form like the one I already have (frmGoodsComments). Add an unbound memo box on the form.

I'd then need to add some code to the "on close"(?) event for the form that inserts the data in the memo box into the GoodsComments field of tblOrder.

If I am right so far, what would the code be?

Thanks

 

[OnTheFly]

This is how I would approach this. First you probably want this to be sort of an Edit/Add type of situation so that if there are already comments entered you would just be adding to them, not overwriting them. If this is the case, this is how I would do it.

The comments form would have two controls. Let's call them txtID and txtComments. They are both text boxes. txtID should be set to Visible=false.

On opening the form you would insert the ID from the current record into txtID and insert the any comments from the current record into txtComments. My choice to do this would be with code on the original form from a button click or a menu item. The code would look something like this:

in the on click event of the button (for example)

Private Sub cmdAddComments_Click()
Dim lngID As Long
Dim strComments As String

'Get the values of the parameters needed for the form
lngID = Me!OrderID
strComments = Nz(Me!Comments, "")

DoCmd.OpenForm "frmComments"

Forms!frmComments.txtID = lngID
Forms!frmComments.txtComments = strComments
Forms!frmComments.txtComments.SelStart = Len(Forms!frmComments.txtComments.Value)
End Sub

Then I would suggest a Save button on the comments form so that if they make a mistake they don't have to save it.

Private Sub cmdSave_Click()
Dim cmdUpdate As ADODB.Command
Dim strSQL As String
Dim Conn As ADODB.Connection
Dim strComment As String
Dim lngID As Long

Me.Refresh

strComment = Me.txtComments
lngID = Me.txtID

Set Conn = CurrentProject.Connection
strSQL = "UPDATE tblOrder SET Comments = '" & strComment & "' " _
& "WHERE OrderID=" & lngID

Set cmdUpdate = New ADODB.Command
With cmdUpdate
.ActiveConnection = Conn
.CommandType = adCmdText
.CommandText = strSQL
.Execute
End With
Set cmdUpdate = Nothing
Conn.Close
Set Conn = Nothing

'Forms!frmOrders.Requery
DoCmd.Close acForm, Me.Name, acSaveNo


End Sub

Private Sub Form_Close()
Dim intAnswer As Integer

intAnswer = MsgBox("Do you want to Save Changes you made to this comment?", _
vbCritical + vbYesNo, "Save Changes")
If intAnswer = vbYes Then
cmdSave_Click
End If

End Sub



Hope this helps.

OnTheFly

 

[hanosm]

I'll give it a go. I don't really need the save button, so i'll try and use the applicable bits of code and see if that works.

I'll write another post after I've done the test.

Thanks!

 

[JeremyNYC]

Using a separate unbound form will still give you the write conflict when you try to save the record on the bound form, because you will have updated the record on the bound form. You MIGHT be able to get around this by leaving the unbound form open and not doing saving the data there until after the record was saved on the bound form (afterUpdate event). But it's really not worth the record. I would just use a tab control. It's rather common to have a comments box on a tab control, though certainly not optimal.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[OnTheFly]

Jeremy is correct to a certain extent. I did not account fot the fact that the record could have been edited before adding the comments. Sorry.

However, you do not have to leave the comments form open (actually, I don't know what that would accomplish). To fix this you would need to add a Refresh to the original form to commit the record before opening the comments form.

Here is a revision of the code. There was also a glitch in the Save button and On Close event from earlier that this code corrects. Since you weren't going to use it I did not detail the changes.

Code for Orders Form:
Private Sub cmdAddComments_Click()
Dim lngID As Long
Dim strComments As String

'Get the values of the parameters needed for the form
Me.Refresh 'Commits the current record
lngID = Me!OrderID
strComments = Nz(Me!Comments, "")

DoCmd.OpenForm "frmComments"

Forms!frmComments.txtID = lngID
Forms!frmComments.txtComments = strComments
Forms!frmComments.txtComments.SelStart = Len(Forms!frmComments.txtComments.Value)
End Sub

Code for Comments Form:

Public blnSaved as Boolean

Private Sub cmdSave_Click()
Dim cmdUpdate As ADODB.Command
Dim strSQL As String
Dim Conn As ADODB.Connection
Dim strComment As String
Dim lngID As Long

Me.Refresh

strComment = Me.txtComments
lngID = Me.txtID

Set Conn = CurrentProject.Connection
strSQL = "UPDATE tblOrder SET Comments = '" & strComment & "' " _
& "WHERE OrderID=" & lngID

Set cmdUpdate = New ADODB.Command
With cmdUpdate
.ActiveConnection = Conn
.CommandType = adCmdText
.CommandText = strSQL
.Execute
End With
Set cmdUpdate = Nothing
Conn.Close
Set Conn = Nothing

blnSaved=True

'Forms!frmOrders.Requery
DoCmd.Close acForm, Me.Name, acSaveNo


End Sub

Private Sub Form_Close()
Dim intAnswer As Integer

If blnSaved=False Then
intAnswer = MsgBox("Do you want to Save Changes you made to this comment?", _
vbCritical + vbYesNo, "Save Changes")
If intAnswer = vbYes Then
cmdSave_Click
End If
End If

End Sub






Hope this helps.

OnTheFly

 

[JeremyNYC]

The problem with commiting the record when the user opens the comments is that that's not at all how Access normally behaves. Normally, you can hit the escape key a couple of times and undo any changes you've made to the record.

What leaving the unbound form open would accomplish is that you you would be able to save the comment to the record after you save the record displayed on the form. I'm not positive that this would avoid the write conflict, but it seems likely that it would.

Still, a tab control is far more standard and far less work.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[hanosm]

OnTheFly,

I tried using some of the code, but unfortunately we have Access 97 at my workplace and it doesn't seem to recognise what ADODB is.
My Access is quite basic compared to you guys, so I think I am going to have to take Jeremy's advice and just go for the tab control.

I suppose I could cheat a little and create a seperate table called tblGoodsComments that holds the OrderID and GoodsComments fields. Then frmGoodsComments can be based on tblGoodsComments as opposed to tblOrder.

There will be a one-many relationship with tblOrder, but because the GoodsComments field will be a "single form" and it will not have any navigation buttons, I don't foresee any problems.

Not being an expert however, I don't think I can "see" very far! I'd appreciate any comments you have on this idea before I apply it to my DB.

Many thanks for your help and time guys.

Hanif