Re: Combo allows not in list but after update causes runtime error

Perry,
A while back I looked at the "NotinList" to add back to the underlying table
and subtable that populates the combo box.

' Allows user to add a new contact name by typing the contacts name
' in the Contact combo box and opening the Customer edit Form.

Dim intNewContact As Integer, strtitle As String
Dim intMsgDialog As Integer, strmsg As String
Dim strFilter As String

' Evaluate filter before it is passed.
strFilter = "[CustomerName:] = " & (Me![CustomerName:].Value)

' Check if user has already selected a contact.
If IsNull(Me![Combo295]) Then

' Display message box asking if the user wants to add a new customer.
strtitle = "Contact Not in List"
strmsg = "Do you want to add a new contact?"
intMsgDialog = vbYesNo + vbExclamation
intNewContact = MsgBox(strmsg, intMsgDialog, strtitle)

If intNewContact = vbYes Then

DoCmd.OpenForm "CUST Edit Form", , , , , , strFilter ' openargs form

Response = acDataErrAdded
Me![Combo295].Undo 'the unbound combo box that selects contact
Me![Combo295].Requery ' sets combo so that new contact will be seen
for selection
Else
' Display the default error message.
Response = acDataErrDisplay
End If

End If

However I can't get the filter to open the form at the right customer - any
ideas.

--
Regards
Bill


"justagrunt" wrote:

Ta Perry,
So if I set the "NotinList" possibly with the same code as the example to
give a data added response , data has been added to the field in the base
table of the form, will this then suppress the "afterupdate" giving a runtime
error?
The filter looks at a different table.
The form has a table and the customer is a separate table.
--
Regards
Bill


"Perry" wrote:

You want to add a record to the target table when
a non existing item is entered in the combo, thus avoiding AfterUpdate from
crashing, correct?
So as to be on the safe side ...

Look up in VBA help
NotInList() event
(of comboboxes)

Kindly repost if the info in VBA help wasn't helpfull

Krgrds,
Perry

"justagrunt" <justagrunt@xxxxxxxxxxxxxxxxxxxxxxxxx> schreef in bericht
news:83E7DB49-A88C-498B-A69B-51EE09757611@xxxxxxxxxxxxxxxx
Hi,
A combo box filters contacts as per customer.
I have allowed the combo to not be restricted by the list.
If the name is in the list "the after update" does the following

'once the row is selected
'use the autonumber field as it is a unique reference
'to act as the filter
'concatenate the first and last names and store in field
'store the phone number

Dim strfilter_1 As String
Dim str1 As String
Dim str2 As String

strfilter_1 = "[ID] = " & [Combo295].Column(0) 'the first column index
is 0

str1 = Nz(DLookup("[FIRST_NAME]", "[tblCUSTCONTACTS]", strfilter_1))
str2 = Nz(DLookup("[LAST_NAME]", "[tblCUSTCONTACTS]", strfilter_1))
Me![EngCnct1:] = str1 & " " & str2
Me![EngPhone1:] = Nz(DLookup("[PHONE]", "[tblCUSTCONTACTS]",
strfilter_1))
Me![Conemail] = Nz(DLookup("[email]", "[tblCUSTCONTACTS]",
strfilter_1))

now if a name that is not in the list is added the "after update" causes a
runtime error because it cannot find the match.
Is there a way to stop this happening? too allow a conatct to be manaully
entered without causing the after update to cause problems?

--
Regards
Bill



.

Relevant Pages

  • data access page search code
    ... Dim SerVal As String 'Value to search against ... Dim strWhere As String 'The filter to use ... Dim strPrompt As String 'MsgBox prompt to use ...
    (microsoft.public.access.modulesdaovba)
  • RE: Set Filter from Startup form to another form
    ... Private Sub Form_Load ... Dim strPrg As String ... Private Function StartProgramAs String ... Now all that is necessary is to set the filter for each form where you want ...
    (microsoft.public.access.formscoding)
  • EXAMPLE: fill menu from path
    ... I wanted the ability to store any given filter to a user file, and be able to display it in a user organized menu system. ... Dim testPathsAs String ... Sub initForm() ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Multi-tier ASP.net web application
    ... I have a table "Customer" in the database. ... Dim password As String ... Public Sub New ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Multi-tier ASP.net web application
    ... Usually in Java applications, I created objects out of the database, ... I have a table "Customer" in the database. ... Dim password As String ...
    (microsoft.public.dotnet.framework.aspnet)
Loading