Combo allows not in list but after update causes runtime error

J

justagrunt

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("", "[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?
 
P

Perry

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
 
J

justagrunt

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 said:
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 said:
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("", "[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?
[/QUOTE]
Click to expand...
Click to expand...
 
P

Perry

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

justagrunt said:
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 said:
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 said:
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("", "[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?
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
J

justagrunt

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 said:
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 said:
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 said:
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("", "[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?
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
P

Perry

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
Click to expand...

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

justagrunt said:
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 said:
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 said:
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" <[email protected]> schreef in bericht
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("", "[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?
[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
J

justagrunt

Cheers for that.
Never thought of that.

--
Regards
Bill


Perry said:
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
Click to expand...

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

justagrunt said:
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 said:
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


:

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" <[email protected]> schreef in bericht
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("", "[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?
[/QUOTE][/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Check Name in a Specific Customer Record - after update 0
Combo On Not In List - am I asking too much? 2
Control afterupdate - run from module - insert data - set flag 6
Update a date field in sub-form from dates fields on main form 1
Combo Result adds check mark in another DB? 6
VBA works in orignal but fails in copy of database 1
bookmark - runtime error "3420" 2
Upsize to SQL ODBC error on rs!update 3

Top