[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 936
  • Last Modified:

"Update or CancelUpdate without AddNew or Edit .."ERROR"

While simulating the errors users might generate unintentionally, I performed search functions using one of my popup forms and made some corrections in some instances to the data returned from the search function without clicking the update button to save the data.

There came this error: "Update or CancelUpdate without AddNew or Edit"
                                   Run-time error '-2147352567 (800200009)'

..debug highlighted:
Me.txtEmplyID2 = fOSUserName

....from this code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
 If IsNull(Me.txtEmplyID1) Then
    Me.txtEmplyID1 = fOSUserName
    Me.txtDataEntryDate = Now()
Else

    Me.txtEmplyID2 = fOSUserName
    Me.txtDateModified = Now()
End If

End Sub

Any help will be appreciated.
0
billcute
Asked:
billcute
  • 25
  • 12
  • 7
  • +2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Do a right-click:Definition on fOSUserName, and copy-paste the (I'm assuming) function fOSUserName into this question.

Looking at your BeforeUpdate event, there doesn't appear to be any code that cancels it.

0
 
billcuteAuthor Commented:
Jim,
Thanks for responding.
Here is the function:

Option Compare Database
Option Explicit

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
'----------------------------------------------------------------------------------------------------------------
' This code is used to retrieve the network user name by accessing the API apiGetUserName.
' Created by: Unknown (Found on Dev Ashish web site http://home.att.net/~dashish/api)
' This code has not been altered in anyway.
' Added to database: 19 Feb 2002
' Added by: Richard Rensel
'-----------------------------------------------------------------------------------------
Function fOSUserName() As String
On Error GoTo fOSUserName_Err

    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
   
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
       
    If lngX <> 0 Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = ""
    End If
 
fOSUserName_Exit:
  Exit Function
 
fOSUserName_Err:
  MsgBox Error$
  Resume fOSUserName_Exit
End Function
' ********

Regards
Bill
0
 
puppydogbuddyCommented:
Bill,
Are you entering changes on the search form or on the main form record returned by the search form?  
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
donaldmaloneyCommented:
Bill,
You have some interesting questions.


What happens if you changed the code

Me.txtEmplyID2 = fOSUserName

to
 Dim fldTest as string

fldtest = fOSUserName
Debug. print fldtest, Me.txtEmplyID2
Me.txtEmplyID2 = fldtest

Then if the error occurs,what are the values in the immediate window?

Just curious
Don
0
 
billcuteAuthor Commented:
puppydogbuddy,
Thanks for participating.
I am entering changes on the main form in which record returned by the search form.

Regards
Bill
0
 
billcuteAuthor Commented:
Don,
Thanks for your suggestion. I placed your code as directed..and I received the following from the Immediate Window:

Bill          Null

Regards
Bill
0
 
billcuteAuthor Commented:
..and debug highlighted ..

Me.txtEmplyID2 = fldTest

from your code.

Regards
Bill
0
 
puppydogbuddyCommented:
Bill,
I like participating in your posts because your questions are always challenging.

In this case, here is what I think you need to do: Place the follwng code on the first line of your main form's AfterUpdate event.  The access message is misleading because you are making changes to the main form, not the search form.

Private Sub Form_AfterUpdate()
If Me.Dirty Then
    Me.Dirty = False             'commit changes that have not been saved
End If
0
 
donaldmaloneyCommented:
Bill,
OK so its not the function fOSUserName.

What happens if you use
Forms!THEFORMNAME!txtEmplyID2 = fldtest

where you substitute THEFORMNAME to the name of the active form.

Don
0
 
donaldmaloneyCommented:
Puppy,
You are right, not only challenging but in one instance i used the answer about two weeks later in my own app.
Don
0
 
donaldmaloneyCommented:
OK bed for me  1:30 am here.  looks like puppy is on the trail.
see you in the am.
Don
0
 
puppydogbuddyCommented:
Hi Don,
I too, have also learned several new techniques that I have been able to apply to my own apps.
Good night.

PDB
0
 
billcuteAuthor Commented:
puppydogbuddy,
I did run a test with your suggested code the same error persists.

Regards
Bill
0
 
puppydogbuddyCommented:
Are you sure you placed the code on the form's afterupdate event and not the afterupdate event of a form control?  

Try this test on your search form.  enter the search info, then find the record. don't change record on main form....just start a new search to find a new record.  Do you get the error message. If you don't (and you  shouldn't) then it pinpoints it to the changes you are making to the main form.  If the me.dirty code didn't resolve the problem, it is because Access does  not know that the changes that have been saved on the main form relate to the record retrieved by the search form.  Try this : place your search form in design view and go to the property sheet and make sure the recordset type property is set to snapshot....the  search form is only reading records, not writing records, so changing the recordset type from dynaset to snapshot might fix the problem.
0
 
billcuteAuthor Commented:
Don,
I tried your other suggestion, the Immediate Window result was:

  Null          
' ************
Private Sub Form_BeforeUpdate(Cancel As Integer)
 If IsNull(Me.txtEmplyID1) Then
    Me.txtEmplyID1 = fOSUserName
    Me.txtDataEntryDate = Now()
Else
     Dim fldTest As String

   Debug.Print fldTest, Me.txtEmplyID2
    Forms!frmSewer!txtEmplyID2 = fldTest

    Me.txtDateModified = Now()
End If
End Sub


Regards
Bill
0
 
billcuteAuthor Commented:
puppydogbuddy,
My test result was just as you've described it in your last comment.

After changing the search form design to "snapshot", I tested again, the original Run-time error did not disappear as a matter of fact it now freezes my main form when it occurs.

Regards
Bill
0
 
billcuteAuthor Commented:
Don / puppydogbuddy,
Thanks for your beautiful comments above. It's very well appreciated.

Yes, I do ask probing questions as it comes to mind and I am glad that you experts like challenging questions. I found out over a period of time that some experts found my questions very cumbersome and worrysome forgetting the fact that both the questioners and the "executioners" are benefactors of the problems they tackle.

However, your acknowlegement that you gain from such "challenging" questions show you are magnanimous "noble".in character.

However, your active participation / assistance over the years have contributed to my success in my application.

There are many experts like you who share the same wisdom you both demonstrated, and I guarantee you that these are experts that have touched the lives of others through unrelentless efforts to tackle the problem of others.

Certainly experts like you will  be remembered in history long after you've retired from active EE forum.

I am hoping some day that I will help others here on EE as well as I have learnt a great deal over the years with your help.

Thanks for your usual support / assistance.

Regards
Bill
0
 
billcuteAuthor Commented:
..and by the way...my application is baout 95% done. Things were rough when I initially joined in 2004as I knew practically nothing;

I have come to the conclusion that I have no regrets as a member of the EE community. I have benefitted tremendously from the forum.

Regards
----Bill
0
 
puppydogbuddyCommented:
Wow! Your mainform freezes when your search form  is changed to snapshot??? What happens if you change the search form recordset property to dynaset-inconsistent updates??? What "DoCmd.Open" syntax are you using to open your search form??  I am going to have to get back to you.
0
 
billcuteAuthor Commented:
puppydogbuddy,
Both settings "snapshot or dynaset" freezes on me.

Here is the command that opens the search form.

Private Sub CmdFindBlockLot_Click()
frmname = OpenArgs
DoCmd.OpenForm "frmSearch1", , , , , , frmname
DoCmd.Close acForm, "frmSearchMenu"
End Sub

Regards
Bill
0
 
puppydogbuddyCommented:
Bill,
I think openArgs may have something to do with the problem.  When the form freezes, invoke the immediate window (Ctrl+G) get the value of openargs like this:
?OpenArgs

What is the value?? before and after the changes to your code below.

change this:
   frmname = OpenArgs
   DoCmd.OpenForm "frmSearch1", , , , , , frmname
   DoCmd.Close acForm, "frmSearchMenu"

to this:
If Not IsNull OpenArgs then
   frmname = OpenArgs
   DoCmd.OpenForm "frmSearch1", , , , , , frmname
   DoCmd.Close acForm, "frmSearchMenu"
Else
   DoCmd.OpenForm "frmSearch1"
End If
End Sub
0
 
billcuteAuthor Commented:
puppydogbuddy,
There is a problem with this line.."If Not IsNull OpenArgs then" ...it was highlighted in red.
...compile error  .....on OpenArgs
msgbox"
   Expected: then or GoTo

Regards
Bill
0
 
puppydogbuddyCommented:
Bill,
Sorry about the syntax error....do it this way:

If IsMissing(Me.OpenArgs) Or IsNull(Me.OpenArgs) Then
    DoCmd.OpenForm "frmSearch1"
Else
   frmname = Me.OpenArgs
   DoCmd.OpenForm "frmSearch1", , , , , , frmname
   DoCmd.Close acForm, "frmSearchMenu"
End If
0
 
puppydogbuddyCommented:
oops! forgot something:

If IsMissing(Me.OpenArgs) Or IsNull(Me.OpenArgs) Then
    DoCmd.OpenForm "frmSearch1"
   DoCmd.Close acForm, "frmSearchMenu"
Else
   frmname = Me.OpenArgs
   DoCmd.OpenForm "frmSearch1", , , , , , frmname
   DoCmd.Close acForm, "frmSearchMenu"
End If
0
 
billcuteAuthor Commented:
Don / puppydogbuddy,
Here is what I suggest might take care of the problem.
(a). When user first search for the first time and data is returned to
      frmSewer -
       (i). if user does not make any changes then allow next search
       (ii). If user has made some changes, as soon aas he clicks the
             "cmdFind", request user if he wants to save the previous changes
              to frmSewer -- vbYesNo
       (iii). If user's response is "Yes", then ..DoCmd.RunCommand acCmdSave
        (iv). If "No" response from user, then undo user's last changes and
               "requery" so as to reset the cmdFind for the next search.
   How does this sound?

I have lifted a similar code from my application for cmdExit, please help me out here - you are free to edit mine if you so choose.
' *********    
Dim txStr As String
  If Not Me.Dirty Then
   If Me.NewRecord Then
     txStr = "No record to save"
   Else
     txStr = "No changes made"
   End If
   If MsgBox(txStr & vbCrLf & _
     "(Cancel to return to the form.)", vbOKCancel) = vbCancel Then GoTo Finish
End If

If Me.Dirty Then
  txStr = "Do you want to save the new data?"
  Select Case MsgBox(txStr & vbCrLf & _
    "(Cancel to return to the form.)", vbYesNoCancel)
  Case vbYes
    DoCmd.RunCommand acCmdSave
  Case vbNo
  Forms!frmSewer.Undo   ' <<<--- undo previous changes made by user
    DoCmd.RunCommand acCmdUndo
    End Select
End If
' ......................
' .......................
<<<<<place cmdFind Code next here<<<<<
End Sub.
' ***********

Regards
Bill
0
 
billcuteAuthor Commented:
puppydogbuddy,
I have just tried your latest suggestion - the error is the same. Would you mind to consider my last proposition?

Regards
Bill
0
 
puppydogbuddyCommented:
Bill,
I have to go out for a few hours, but will review when I get back.
<<<I have just tried your latest suggestion - the error is the same>>>Please explain???
0
 
billcuteAuthor Commented:
When I searched as described earlier without clicking the update button, I received "Update or CancelUpdate without AddNew or Edit"
Run-time error '-2147352567 (800200009)'

..debug highlighted:
Me.txtEmplyID2 = fOSUserName

Regards
Bill
0
 
bonjour-autCommented:
Hi Bill,

just gave a short look on it
this is a complex probelem, because if you bridge the described error it generates a next one in 'frmSearch1' at  the line 'Forms(num).Bookmark = rst.Bookmark' with same error text but different error number (3020)

maybe you will need to rework your complete logic search/edit/uodate

a workaround could be to save eventually done changes anyway with out asking the user

if i find time at the weekend i will look closer to this

regards, Franz
0
 
billcuteAuthor Commented:
To All,
EE has made it easier to upload sample db...I will upload my sample upon request from any expert.

Regards
Bill
0
 
donaldmaloneyCommented:
Bill,

Send us a link please.

Can you also send what you entered, and what buttons you clicked so we can recreate the error?

I have an idea but would like to have real app.

Thanks

Don

0
 
puppydogbuddyCommented:
Bill,
I believe the solution to your concurrency problem is in this link. Leave your search forms as modal, but instead of closing them when you want to update the record retrieved by t he search, set the search forms visible property to no, causing them to lose focus. When the search forms lose focus,you will be able to update your main form and save the changes without the concurrency error you are getting.  You of course,  would have code to make the search form visible again for the next search.........  
 
http://www.members.shaw.ca/AlbertKallal/Dialog/Index.html
0
 
puppydogbuddyCommented:
Ps: let me know if you want to pursue this solution.
0
 
billcuteAuthor Commented:
puppydogbuddy,
I saw the thread. It sound pretty good but not really sure if it will resolve the problem stated above. However, I will appreciate any effort you provide in order to get a solution.

Regards.
Bill
0
 
billcuteAuthor Commented:
To All Experts,
This is to inform you that I have uploaded my sample db into EE uploading site. Bravo EE.

Direct link to your file
http://www.ee-stuff.com/Expert/Upload/getFile.php?fid=107 

Further Explanation:
When frmSearch1 is opened - it's kept afloat all the time until I click the close button. In this case after making my first search and I get the result, then I will click on frmSewer, make changes to the returned data while frmSearch1 is still kept afloat.
 
After making changes to frmSewer data, I will choose another BlockNo from the List, and the corresponding LotNo then clcik the search button again, here the Run-time error will be generated.
 
The idea of leaving the frmSearch1 afloat is that it saves time for users instead of having to close the form before changes could be made to frmWater.
 
I hope this is clearer now.

Regards
Bill
0
 
billcuteAuthor Commented:
Jimhorn,
Thanks for bringing this EE "upload" feature to my attention very recently. It's appreciated.

I have uploaded the same file twice in error. Would you mind deleting one of them?

Regards
Bill
0
 
donaldmaloneyCommented:
Bill,

Just a note.  been busy on work issues.  
Tried your db and get the same error.

I am getting the error on the bookmark code also.

Will look at it later today.
Don
0
 
bonjour-autCommented:
Hi Bill,

Good news on that.

At last i can offer you some sort of workaround.

The problem was tricky, but if we stay with the most basic facts, we have a clear solution.
The only important question is, wether the form(frmSewer) is dirty or not. If yes a series of logical dependend code-pieces lead to the described problem, if we launch a new search.

So the best thing is to put a code at the beginning of the search-process, which looks, if the underlying form is dirty or not. If yes, it prompts the user to finish or cancel the pending recordchange. The good thing witnh that is, that the already typed in new search argument stays intact, so the user has just to decide to hit on Update ot Cancel button on the underlying form and then to hit Search again on the frmSearch1/2/3. (you need the code in all 3 frmSearch#)

If Forms(CInt(Me.OpenArgs)).Dirty = True Then
  MsgBox "You made changes on form '" & Forms(CInt(Me.OpenArgs)).Name & "'. Please Update or Cancel the record before searching another record."
  Exit Sub
End If

Pretty short, and it works fine (tested with frmSewer and frmSearch1)

Regards, Franz

PS By the way you could straigthen out your code using the Forms(CInt(Me.OpenArgs)) instead of the Forms(num)
The Openargs is always String, also if you have put a number in the OpenForm statement, so you need to convert it , if used as number again. This does not require a select case, just CInt() or CDbl().
0
 
billcuteAuthor Commented:
Franz,
I just tested your suggestion...it's neat and works great.

In your recommendation, you recommendation to use
Forms(CInt(Me.OpenArgs)) instead of the Forms(num)

What part of my form are you referring?

Regards
Bill
0
 
bonjour-autCommented:
in the codes for cmf_find and cmd_query

you set the form(num) by select case

you can do that shorter by using  Forms(CInt(Me.OpenArgs)) instead of the Forms(num)

e.g.

Dim frm As Form
Select Case OpenArgs
Case 0
num = 0
Case 1
num = 1
Case 2
num = 2
Case 3
num = 3
Case 4
num = 4
End Select
Set frm = Forms(num)
Select Case frm.Name

can be very much shorter:

Select Case Forms(CInt(Me.OpenArgs)).Name

or e.g. in the other place, where you use the term Form(num) in several places:

Dim rst As DAO.Recordset
Dim frm As Form
Select Case OpenArgs
Case 0
num = 0
Case 1
num = 1
Case 2
num = 2
Case 3
num = 3
Case 4
num = 4
End Select

Set frm = Forms(num)

can be shorter:

Dim rst As DAO.Recordset
Dim frm As Form

num = CInt(Me.OpenArgs)
Set frm = Forms(num)


Regards, F ranz
0
 
billcuteAuthor Commented:
Are you then suggesting to just add one line to my current cmdFind and cmd_requery by adding just one line of   such that the code would be:

num = CInt(Me.OpenArgs)
Set frm = Forms(num)
Set rst = Forms(num).RecordsetClone

..now your code would then be configured like the one shown below -- Am I correct?

 If Forms(num).Dirty = True Then
  MsgBox "You made changes on form '" & Forms(num).Name & "'. Please Update or Cancel the record before searching another record."
  Exit Sub
End If

Please correct me if I am wrong. Also I will appreciate it if you could send me your own amended sample to compare with me.

Regards
Bill
0
 
billcuteAuthor Commented:
correction..

.....to compare with mine.
0
 
bonjour-autCommented:
i did not change that in "my" sample, as it is not relevant for correct function.
it is just additional input for you

yes , you ca use either

Forms(CInt(Me.OpenArgs))     or

num = CInt(Me.OpenArgs)
Forms(num)

your last post it would improve to e.g.

num = CInt(Me.OpenArgs)
Set frm = Forms(num)
Set rst = frm.RecordsetClone

If Forms(num).Dirty = True Then
  MsgBox "You made changes on form '" & Forms(num).Name & "'. Please Update or Cancel the record before searching another record."
  Exit Sub
End If

**************
complete changed code for the find button:

Private Sub cmdFind_Click()
    Me.AllowAdditions = True
    Me.AllowEdits = True
    Me.AllowDeletions = True
   
Dim strSearch As String
Dim num As Integer
Dim frm As Form

num = CInt(Me.OpenArgs)
Set frm = Forms(num)

If frm.Dirty = True Then
  MsgBox "You made changes on form '" & frm.Name & "'. Please Update or Cancel the record before searching another record."
  Exit Sub
End If
     
If Not IsNull(cboFindBlockNo) And Len(Trim(cboFindBlockNo)) > 0 Then _
strSearch = "BlockNo = " & cboFindBlockNo
If Not IsNull(cboFindLotNo) And Len(Trim(cboFindLotNo)) > 0 Then _
strSearch = strSearch & IIf(Len(strSearch) > 0, " AND ", "") & "LotNo = " & cboFindLotNo
If IsNull(strSearch) Or Len(Trim(strSearch)) = 0 Then
MsgBox "No search criteria was provided", vbCritical, "Validation Error"
Exit Sub
End If

Dim rst As DAO.Recordset

Set rst = frm.RecordsetClone
'MsgBox rst.RecordCount
If rst.EOF = False Or rst.BOF = False Then
         rst.MoveLast                                       ' <<<<--- Error line
          rst.MoveFirst
         rst.FindFirst strSearch
End If
'MsgBox "test2"
If rst.NoMatch Then
MsgBox "The matching record was not found", vbInformation, "No Record Found"
Else
' set the form to the found record
'On Error GoTo erHand
frm.Bookmark = rst.Bookmark
rst.FindNext strSearch
End If
If rst.NoMatch Then
rst.Close
Set rst = Nothing
Else
rst.FindPrevious strSearch
btnFindNext.Enabled = True
btnFindNext.SetFocus
CmdFind.Enabled = False
btnStop.Enabled = True
End If
Exit Sub
erHand:
MsgBox Err.Number & " / " & Err.Description & " / " & Err.Source

End Sub
*****************

Regards, Franz
0
 
billcuteAuthor Commented:
Franz,
Thanks for the code, I will review my code and subject your suggestion to a rigorous test in my application and then give you a feedback on the outcome.

Thanks for your input on the subject - it's very thoughtful.

Regards
Bill
0
 
billcuteAuthor Commented:
Franz,
I ran into my first COMPILE error after changing cmdFind code:
Run-time error '2501';
The Openform action was cancelled

and debug highlighted:
DoCmd.OpenForm "frmSearch1", , , , , , frmname

In this case, how would I approach these line code in light of your suggested code:
(i).
Private Sub btnFindDEP_DOBNo_Click()
        frmname = OpenArgs
        DoCmd.OpenForm "frmSearch3", , , , , , num
        DoCmd.Close acForm, "frmSearchMenu"
End Sub

(ii)
Private Sub CmdFindBlockLot_Click()
       frmname = OpenArgs
       DoCmd.OpenForm "frmSearch1", , , , , , frmname
       DoCmd.Close acForm, "frmSearchMenu"
End Sub

(iii). Global code...
      Public num As Integer

Regards
Bill

0
 
donaldmaloneyCommented:
Franz,

Nice fix for the error Bill was getting.
I was on another path then got delayed with issues at work.
Looking at adding code on onupdate but then would need code on all objects.

See im still learning.

Don

0
 
puppydogbuddyCommented:
hi Franz,
Nice solution.  I think I am learning something also.  In my first recommendation [see post Comment from Date: 05/18/2006 01:08AM EDT], I proposed that Bill do the following:

Private Sub Form_AfterUpdate()
If Me.Dirty Then
    Me.Dirty = False             'commit changes that have not been saved
End If


which did not work.  If I understand the solution correctly, the reason my code didn't work is because Access did not know which form was being updated, and it required the Cint function along with OpenArgs to match to the correct form.  Is my understanding correct? Thanks.
0
 
bonjour-autCommented:
Hi Bill and Don

as it is always a problem to work on apps, where different design-ideas have been woven together,
i may give some further input on above postings

to pass information between forms, you have several options

1. the direct method
is not possible in Bills case, because he wants the same searchforms working on different base-forms

2. open args

we can do this either
by formname >> DoCmd.OpenForm "frmSearch1", , , , , , Me.Name
Usage in called form: Froms(Me.OpenArgs)

or by Froms-Index >>DoCmd.OpenForm "frmSearch1", , , , , , num
(where num has to be computed first in a loop)
Usage in called form: Forms(CInt(Me.OpenArgs)) - As an OpenArg value is alway passed as String

3. public variable:

also either by fromname
assume Public frmName As String
calling form:
frmName=Me.Name
DoCmd.OpenForm "frmSearch1"
Usage in called form: Froms(frmName)

or by Forms-Index
assume Public frmIndex As Integer
calling form:
Dim i As Integer
For i = 0 To Forms.Count-1
    If Forms(i).Name = Me.Name Then frmIndex=i
Next i
DoCmd.OpenForm "frmSearch1"
Usage in called form: Froms(frmIndex)
!! Usage of Forms-Index has potential problems as it is a dynamic collection of actually open forms

Bill did a mixture of this above. So the Public variable num is redundant when using the OpenArgs
But finally the app is working fine, so it is ok
i personally prefer the OpenArgs-method with Me.Name

Regards, Franz
0
 
billcuteAuthor Commented:
Franz,
Based on your above suggestions, here is what I came with in my cmdFind code..the same changes were made to cmdFindNext and cmdRequery1 and
it seems to work on all forms. Thanks

Private Sub cmdFind_Click()

        Me.AllowAdditions = True
        Me.AllowEdits = True
        Me.AllowDeletions = True
   
        Dim strSearch As String
        Dim num As Integer
        Dim frm As Form
        Set frm = Forms(Me.OpenArgs)  ' changed by Franz

If frm.Dirty = True Then
        MsgBox "You made changes on form '" & frm.Name & "'. Please Update or Cancel the record before searching another record."
  Exit Sub
End If
     
If Not IsNull(cboFindBlockNo) And Len(Trim(cboFindBlockNo)) > 0 Then _
        strSearch = "BlockNo = " & cboFindBlockNo
If Not IsNull(cboFindLotNo) And Len(Trim(cboFindLotNo)) > 0 Then _
        strSearch = strSearch & IIf(Len(strSearch) > 0, " AND ", "") & "LotNo = " & cboFindLotNo
If IsNull(strSearch) Or Len(Trim(strSearch)) = 0 Then
        MsgBox "No search criteria was provided", vbCritical, "Validation Error"
Exit Sub
    End If

        Dim rst As DAO.Recordset
        Set rst = frm.RecordsetClone                ' changed by Franz
'MsgBox rst.RecordCount
If rst.EOF = False Or rst.BOF = False Then
        rst.MoveLast                                      
        rst.MoveFirst
        rst.FindFirst strSearch
End If
'MsgBox "test2"
If rst.NoMatch Then
        MsgBox "The matching record was not found", vbInformation, "No Record Found"
Else
' set the form to the found record
'On Error GoTo erHand
        frm.Bookmark = rst.Bookmark          ' changed by Franz
        rst.FindNext strSearch
    End If
   
If rst.NoMatch Then
        rst.Close
        Set rst = Nothing
Else
        rst.FindPrevious strSearch
        btnFindNext.Enabled = True
        btnFindNext.SetFocus
        CmdFind.Enabled = False
        btnStop.Enabled = True
    End If
Exit Sub
erHand:
        MsgBox Err.Number & " / " & Err.Description & " / " & Err.Source

End Sub
' ************
Note:
I want to use this opportunity to thank all contributors on this post for their relentless efforts in ensuring a huge success of this post. It seems reasonable at this point to award Franz the full points.

Regards
Bill
0
 
billcuteAuthor Commented:
and here is the launch code for the records who knows someone else could find this code useful on EE:
DoCmd.OpenForm "frmSearch1", , , , , , Me.OpenArgs

Regards
Bill
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 25
  • 12
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now