Saving Problem

[LittleMan22]

I'm trying to requery a subform and keep getting the message "you must save current field before running the requery action". This is happening because before I requery the subform I'm passing a value onto it within a field called "STATUS". How can I get around this error (i.e. get the field to save?).

Ryan.

 

[ToeShot]

Unbind the field from the table or query

 

[LittleMan22]

How do I unbind the field from the table? The thing is that this situation occurs because I have created a new policy for a client and thereby subsequently converted an existing one. The requery command will update my recordset with the policy that I have created, however, I don't want to delete the existing policy - only change it's "status". Here's the code that's giving me the problem (marked by **):

Response = MsgBox("Do you want enter this data as a new policy?", vbYesNo)
If Response = vbYes Then
**Forms!frmClients!subFormPolicies!cmbSTATUS = "CHANGE"
SaveRecord
Convert "Trans" 'Create a New Policy Function
Forms!frmClients!subFormPolicies.Requery
DoCmd.Close
Else
DoCmd.Close
End If

 

[ToeShot]

I notice you say cmbSTATUS is this a comboBox? if it is does it have the word "CHANGE" in its list? if it does try this

Forms!frmClients!subFormPolicies!cmbSTATUS.SelText = "CHANGE"

 

[BoxHead]

Ryan,

I've spent part of the evening wrestling with something that I think may be similar.

From a combo box selection I start a series of actions that leads to an update query. When all is said and done, I get a "Write Conflict" error when I try to go to the next record because the change to the combo box was never saved.

I resolved that by putting

DoCmd.DoMenuItem 0,5,4,,70

to save the record on the combo's Change event. This created an error on any 'not in list' typing that a user may do so I ended up with

Private Sub Combo15_Change()
On Error GoTo NotIn15
   DoCmd.DoMenuItem 0,5,4,,70
   UpdateAssist 'a function 
Exit Sub

NotIn15:
MsgBox "The text you've entered..."
Exit Sub

End Sub

It's been ugly here.
Hope this can be helpful for you.



John

Use what you have,
Learn what you can,
Create what you need.

 

[LittleMan22]

ToeShot: Now I get the error message saying that I can't reference a control on a form unless the form has the focus...

BoxHead: What does Docmd.DoMenuItem 0,5,4,,70 do?

Thanks again guys,
Ryan.

 

[LittleMan22]

I've thought of another way that might work as well...

I've tried to open the recordset in my code, update the cmbstatus field, and then close the recordset...Here's what I have but I'm getting errors:

Private Sub ConvertExit_Click()
Dim Response
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("Policies&quot

Response = MsgBox("Do you want enter this data as a new policy?", vbYesNo)
If Response = vbYes Then
With rst
.OpenRecordset
.Edit
Forms!frmClients!subFormPolicies!cmbSTATUS = "CHANGE"
.Update
.Close
End With
SaveRecord
Convert "Trans"
DoCmd.Close
Else
DoCmd.Close
End If

Any ideas....it's telling me I can't update without an Edit command...

 

[ToeShot]

You don't need the second OpenRecordset you also need to get to the recordset that you are currently editing that is what the book mark statement is doing, it is setting its record to the one that is on the Subform, then you edit it.
doing this you don't need the save record statement. The code below may still give you a problem with the bookmark if the Subform does not have focus.

HTH

The error you got was because the Subform does not have focus when the status was trying top be change

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("Policies&quot

Response = MsgBox("Do you want enter this data as a new policy?", vbYesNo)
If Response = vbYes Then
With rst
.BookMark = Me!subFormPolicies.BookMark
.Edit
![Status_Field_Name_In_The_Table] = "CHANGE"
.Update
.Close
End With
Convert "Trans"
DoCmd.Close
Else
DoCmd.Close
End If