NotInList event question 1

[LD1010]

Thanks for looking at my post.

I have a form, “frmJobCloseOutEntry” that is used to follow an 8 step file close out procedure. On small jobs all 8 steps can usually be completed in one sitting but on larger jobs the user may only be able to complete part of the steps and then come back to this file at another time. Two to three users are involved in this process.
A user upon opening the form begins by entering a file number in a combo box (cboFileNumber) with the record source set to “qryJobCloseOutEntry” [FileNumber] to see it the file close out has already been started. If the file does not already exist it then needs to be entered as a new record. So I thought I’d try to use the NotInList event to handle that. I haven’t had any experience in using the NotInList event but found an example in Martin Green’s web site

http://www.fontstuff.com.

The code works well in that it adds the new file to the table. But once it’s been added I would like it to become the active record in the form so the user can start working on the close out procedure for that file. How can I do this? Or should I be using a different event. Following is the code I have in the AfterUpdate and NotInList events of cboFileNumber.
Any help will be much apprecidated!

Private Sub cboFileNumber_AfterUpdate()
On Error GoTo cboFileNumber_AfterUpdate_Err

    Dim Rs As Object

    Set Rs = Me.Recordset.Clone
    Rs.FindFirst "[FileNumber] = " & Str(Nz(Me![cboFileNumber], 0))
    If Not Rs.EOF Then Me.Bookmark = Rs.Bookmark
    
cboFileNumber_AfterUpdate_Exit:
    Exit Sub
cboFileNumber_AfterUpdate_Err:
    MsgBox Err.Description, vbCritical, "Error"

End Sub

Private Sub cboFileNumber_NotInList(NewData As String, Response As Integer)
On Error GoTo cboFileNumber_NotInList_Err

Dim intAnswer As Integer
Dim strSQL As String

    intAnswer = MsgBox("The File Number " & Chr(34) & NewData & _
    Chr(34) & " you entered is new." & vbCrLf & _
    "Would you like to add it to the list now?" _
    , vbQuestion + vbYesNo, "JCA Job Close-Out")
    
    If intAnswer = vbYes Then
    strSQL = "INSERT INTO qryJobCloseOutEntry([FileNumber]) " & _
    "VALUES ('" & NewData & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    MsgBox "The new File Number has been added." _
    , vbInformation, "JCA Job Close-out"
    Response = acDataErrAdded
    
    Else
    MsgBox "Please choose a File Number from the list." _
    , vbInformation, "JCA Job Close-out"
    Response = acDataErrContinue
    End If
cboFileNumber_NotInList_Exit:
    Exit Sub
cboFileNumber_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
Resume cboFileNumber_NotInList_Exit

End Sub

 

[MajP]

Untested, but I think will work.

Private Sub cboFileNumber_AfterUpdate()
On Error GoTo cboFileNumber_AfterUpdate_Err

    gotoFile(Nz(Me![cboFileNumber], 0))
    
cboFileNumber_AfterUpdate_Exit:
    Exit Sub
cboFileNumber_AfterUpdate_Err:
    MsgBox Err.Description, vbCritical, "Error"

End Sub

Private Sub cboFileNumber_NotInList(NewData As String, Response As Integer)
On Error GoTo cboFileNumber_NotInList_Err

Dim intAnswer As Integer
Dim strSQL As String

    intAnswer = MsgBox("The File Number " & Chr(34) & NewData & _
    Chr(34) & " you entered is new." & vbCrLf & _
    "Would you like to add it to the list now?" _
    , vbQuestion + vbYesNo, "JCA Job Close-Out")
    
    If intAnswer = vbYes Then
    strSQL = "INSERT INTO qryJobCloseOutEntry([FileNumber]) " & _
    "VALUES ('" & NewData & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    MsgBox "The new File Number has been added." _
    , vbInformation, "JCA Job Close-out"
    Response = acDataErrAdded
    [b]
    gotoFile(NewData)  
    [/b]
    Else
    MsgBox "Please choose a File Number from the list." _
    , vbInformation, "JCA Job Close-out"
    Response = acDataErrContinue
    End If


cboFileNumber_NotInList_Exit:
    Exit Sub
cboFileNumber_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
Resume cboFileNumber_NotInList_Exit

End Sub


Public sub gotoFile(fileNum as long)
  Dim Rs As Object
  if fileNum <> 0 then
    Set Rs = Me.Recordset
    Rs.FindFirst "[FileNumber] = " & fileNum
    unless this is a text field then: Rs.FindFirst "[FileNumber] = '" & fileNum & "'" 
 end if
end sub

 

[LD1010]

Thanks for your reply MajP. I just got back to the office.
I copied and pasted the code you posted but it's not working just yet.
When I enter a new number in the combo box. It asks me if I want to enter it now. I click YES then I get the confirming MsgBox that the new data has been entered and when I click OK to that I get the first MsgBox again. If I click NO to the first MsgBox then I get the MsgBox Please choose a File Number from the list. When I clicking OK to that MsgBox I get an error message saying "This action was cancelled by an associated object."

Any other insight?

 

[MajP]

This is one of those things I would have to set up and play with to see it work. This gets a little tricky because you have competing events between the update and not in list.

You can probably do it the way you are going, to me it gets overly complicated and hard to control the program flow.
Personally I think I would break this up just to make the program flow easier.

1) In my not in list event I would simply have a Msgbox.
"This file number does not exist. Double click the combo box to add a new number."
2) The user can then double click to add a new filenumber (or possibly multiple file numbers if that makes sense for your buisness model). From the double click event pop up a simple dialog form to add a new file number(s). Ensure you use docmd.open form and ensure you use acdialog. Acdialog will stop the code in the calling form. With the pop up form you can better validate the data, and you can use a bound form instead of an insert query.
3) on the form have an OK, Cancel button. The OK button sets the form to invisible:
me.visible = false
The cancel closes the form.
docmd.close acform, me.name
4) The code that calls it can then read the invisible form and move to the correct file number.


So the double click event is something (untested) like.

dim frm as access.form
dim fileNum as long
'unless it is text then dim as string
DoCmd.OpenForm "frmAddFileNum", , , , acFormAdd, acDialog
'code stops here until pop up closes or is hidden
'If the form is open but hidden
if currentDb.allforms("frmAddFileNum").isLoaded then
set frm = forms("frmAddFileNum")
'Make sure the new record is committed
frm.dirty = false
fileNum = nz(frm.someControlOnPopUp,0)
'need to requery the form and the combo box since there is a new recor
docmd.close acform, frm.name
me.requery
me.cboFileNumber.requery
gotoFile(fileNum)
end if
'If the pop up was canceled then it just falls out

 

[LD1010]

Thanks again for your reply MajP.

I'm going to follow your advise about breaking this process up. In trying your code I ran into a compile error on the line:

If currentDb.allforms("frmAddFileNum").isLoaded Then

"AllForms" Method or data member not found.

An I missing a reference?

 

[MajP]

Sorry. Currentdb does not have that method, should be currentProject.allforms

 

[LD1010]

Thanks for all your help MajP. This was a good solution. Things are working well.