I have a form where each day is a unique record or statistical information. The form is bound to a single table with one record per date, no duplicates. For this reason, I put an unbound text box with a short date and used the date picker so that the user can find a record with the calendar.
I am using DoCmd.SearchForRecord to then find the correct record. It finds it and displays the correct record, but then blocks the user from making any edits with the error message "Update or CancelUpdate without AddNew or Edit".
Yet, when the user uses the navigation arrows at the bottom of the screen to locate the record, they can edit it with no problem. In fact, if they use the date picker to find the record, click the navigation button back one record and then forward to the correct record, they can edit it just fine.
I have checked all the form's edit flags before and after the SearchForRecord command and cannot find where anything is getting reset that would prevent an edit.
Anyone ever seen this behavior before?
Full Version: Date Picker resets edit mode
Hi Mike,
instead of using DoCmd to find the record, use this method:
WHERE
Date_controlname is the Name property of the control to collect the date to find
fieldname is the name of the field you want to search
~~~
I have seen that error message before ... best to
1. save record before moving pointer
2. don't reference form controls in RowSource for combos and listboxes
3. based each form/subform where you change data on just one table
instead of using DoCmd to find the record, use this method:
CODE
'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function
'save current record if changes were made
If me.dirty then me.dirty = false
make sure valid date was entered
if not IsDate(Me.Date_controlname) then
msgbox "You must enter a valid date",,"Date not valid"
Me.Date_controlname.setfocus
exit sub
end if
'find the first value that matches
Me.RecordsetClone.FindFirst "DateValue([fieldname]) = #" & Me.Date_controlname & "#"
'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
If IsNull(Me.ActiveControl) Then Exit Function
'save current record if changes were made
If me.dirty then me.dirty = false
make sure valid date was entered
if not IsDate(Me.Date_controlname) then
msgbox "You must enter a valid date",,"Date not valid"
Me.Date_controlname.setfocus
exit sub
end if
'find the first value that matches
Me.RecordsetClone.FindFirst "DateValue([fieldname]) = #" & Me.Date_controlname & "#"
'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
WHERE
Date_controlname is the Name property of the control to collect the date to find
fieldname is the name of the field you want to search
~~~
I have seen that error message before ... best to
1. save record before moving pointer
2. don't reference form controls in RowSource for combos and listboxes
3. based each form/subform where you change data on just one table
oh, instead of
Me.ActiveControl
use
Me.Date_controlname
... modified some other code and didn't replace everything...
Me.ActiveControl
use
Me.Date_controlname
... modified some other code and didn't replace everything...
from your PM: "Thanks for your response. Someone in another forum sent essentially the same response and I implemented it. It worked very well. It was so fast on a large database that I have begun implementing it for more of my searches. Thanks again. "
you're welcome
happy to help
[color="green"]'°`°º¤[color="orange"]ø,¸¸,ø¤º`°[color="pink"]º¤ø,¸¸,ø¤[color="blue"]º°`°º¤[color="yellow"]ø,
[color="blue"]Wel[color="brown"]come [color="green"]to [color="blue"]U[color="black"]tter [color="blue"]A[color="black"]ccess !
[color="green"],¸¸,ø¤[color="orange"]º`°º¤ø,¸¸,ø[color="pink"]¤º°`°º¤ø[color="blue"],¸¸,ø¤[color="yellow"]º`
[color="purple"]
you're welcome
happy to help[color="green"]'°`°º¤[color="orange"]ø,¸¸,ø¤º`°[color="pink"]º¤ø,¸¸,ø¤[color="blue"]º°`°º¤[color="yellow"]ø,
[color="blue"]Wel[color="brown"]come [color="green"]to [color="blue"]U[color="black"]tter [color="blue"]A[color="black"]ccess !
[color="green"],¸¸,ø¤[color="orange"]º`°º¤ø,¸¸,ø[color="pink"]¤º°`°º¤ø[color="blue"],¸¸,ø¤[color="yellow"]º`
[color="purple"]
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.