Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / January 2007

Tip: Looking for answers? Try searching our database.

Combo allows not in list but after update causes runtime error

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
justagrunt - 29 Jan 2007 22:03 GMT
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?

Signature

Regards
Bill

Perry - 29 Jan 2007 22:28 GMT
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

> Hi,
> A combo box filters contacts as per customer.
[quoted text clipped - 26 lines]
> Is there a way to stop this happening? too allow a conatct to be manaully
> entered without causing the after update to cause problems?
justagrunt - 29 Jan 2007 22:54 GMT
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.
Signature

Regards
Bill

> You want to add a record to the target table when
> a non existing item is entered in the combo, thus avoiding AfterUpdate from
[quoted text clipped - 40 lines]
> > Is there a way to stop this happening? too allow a conatct to be manaully
> > entered without causing the after update to cause problems?
Perry - 29 Jan 2007 23:37 GMT
Aha, ok
But yr code is clean and should run.

Where's the error trapping mechanism to the afterupdate, btw?
What is the error number?

Is there some other code in the afterupdate or perhaps some other setting
you forgot to mention?
Again, the code is clean, good defensive programming from what I see.
(apart from error handling, that is).

Krgrds,
Perry

> Ta Perry,
> So if I set the "NotinList" possibly with the same code as the example to
[quoted text clipped - 53 lines]
>> > manaully
>> > entered without causing the after update to cause problems?
justagrunt - 29 Jan 2007 23:44 GMT
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.

Signature

Regards
Bill

> Ta Perry,
> So if I set the "NotinList" possibly with the same code as the example to
[quoted text clipped - 48 lines]
> > > Is there a way to stop this happening? too allow a conatct to be manaully
> > > entered without causing the after update to cause problems?
Perry - 30 Jan 2007 00:25 GMT
> However I can't get the filter to open the form at the right customer -
> any
> ideas.
>        DoCmd.OpenForm "CUST Edit Form", , , , , , strFilter ' openargs
> form

Any reason why you using openargs to prepopulate the form filter?
Why don't you pass strFilter to the Where argument of OpenForm() command?

Krgrds,
Perry

> Perry,
> A while back I looked at the "NotinList" to add back to the underlying
[quoted text clipped - 100 lines]
>> > > manaully
>> > > entered without causing the after update to cause problems?
justagrunt - 30 Jan 2007 01:03 GMT
Cheers for that.
Never thought of that.

Signature

Regards
Bill

> > However I can't get the filter to open the form at the right customer -
> > any
[quoted text clipped - 112 lines]
> >> > > manaully
> >> > > entered without causing the after update to cause problems?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.