NotInList, combo, open form in data add ???

[fibayne]

Hi

I have looked though lots of threads and have pieced together the following in an attempt to open frm_Payee when the Payee is not in the combo list, it is working in a fashion but gives the message and opens the form but that is about as far as I can get...I am trying to have the message appear when the Payee is not in the list when the message asks do you want to add new payee frm_Payee opens inserting the not in list Payee name on the form and creating a new PayeeID !!! I have a related table tbl_Bank which I think is causing a problem also ie frm_Payee has a drop down for the relevant bank...am I asking too much many of the threads I read I sure will work for me but I cant apply them correctly would anyone out there be kind enough to point me in the right direction ...again...thanks in advance ...cheers Fi

Private Sub cb3_NotInList(NewData As String, Response As Integer)

Response = MsgBox("The Payee you have entered is new and not in this list." & vbCrLf & vbCrLf _
& "Would you like to add it?", vbQuestion + vbYesNo, "New Payee?")
If Response = vbYes Then
Set db = CurrentDb
stDocName = "frm_payee"
DoCmd.OpenForm stDocName, , , , acFormAdd, , strArgs
End If

End Sub

 

[wazz]

Private Sub cb3_NotInList(NewData As String, Response As Integer)

if MsgBox("The Payee you have entered is new and not in this list." & vbCrLf & vbCrLf _
& "Would you like to add it?", vbQuestion + vbYesNo, "New Payee?") = vbyes then
    'Set db = CurrentDb
    response = acdataerrcontinue
    me.yourcombobox.undo 
    stDocName = "frm_payee"
    DoCmd.OpenForm stDocName, , , , acFormAdd ', , strArgs
    forms(stdocname)!yourtextbox = newdata
else
    response = acdataerrcontinue
    'do something else
End If

End Sub

- the response is a response to the 'notinlist' event not the msgbox
- newdata is the "bad" data entered in the cbobox
- rename your cbobox to something meaningful like cboPayee (and the rest of the controls as well so they're easy to work with, especially in vbeditor.

 

[fibayne]

Hi wazz..many thanks for your reply, looks like i am still doing something wrong, here is my code and below th error message I get when frm_Payee opens

Private Sub cboPayee_NotInList(NewData As String, Response As Integer)

If MsgBox("The Payee you have entered is new and not in this list." & vbCrLf & vbCrLf _
& "Would you like to add it?", vbQuestion + vbYesNo, "New Payee?") = vbYes Then
'Set db = CurrentDb
Response = acDataErrContinue
Me.cbopayee.Undo
stDocName = "frm_Payee"
DoCmd.OpenForm stDocName, , , , acFormAdd ', , strArgs
Forms(frm_Payee)!Payee = NewData
Else
Response = acDataErrContinue
'do something else
End If

End Sub

Error message - Runtime 438, Object doesnt support this property or method
apologies for my lack of knowledge but thanks for your help !! cheers Fi

 

[fibayne]

Hi wazz...just changed
Forms(frm_Payee)!Payee = NewData
to
Forms!frm_Payee!Payee = NewData
and it appears to be working, does that look correct to you?? cheers Fi

 

[wazz]

yes, definitely. sry, i meant to put: Forms(stDocName)!Payee = NewData

wait a minute. that is what i put. did you try that? hmm, maybe you need an object. don't remember now. nevermind, leave it the way it is.

 

[fibayne]

Hi wazz..the form with the combobox isnt refreshing until I close and re-open ? I tried adding refresh into the NotIn List code but this doesnt seem to work, could you help me once again ???cheers Fi

 

[wazz]

the word you're looking for is requery not refresh. the are many way to do this. for now try this onclose of frmPayee (look up 'isloaded' in help):

    Dim prj As Object
    Set prj = Application.CurrentProject

    If prj.AllForms("frm_Payee").[B]IsLoaded [/B]Then
        Forms!frm_payee.cbopayee.requery
    end if

 

[fibayne]

Hi Wazz...works a treat just had to insert the name of the from where the combo box is rather than frm_payee
On post 4 and 5 it was just that you had put...Forms(frm_Payee)!Payee = NewData
to make it work ? I changed it to this...Forms!frm_Payee!Payee = NewData

Thank you so much for all your help with this...no doubt I'll have another question before the day is out ...thanks again Fi