Combo allows not in list but after update causes runtime error

[Guest]

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?

 

[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

 

[Guest]

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

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]

 

[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

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

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]

 

[Guest]

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

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

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]

 

[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

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
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

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]

 

[Guest]

Cheers for that.
Never thought of that.

--
Regards
Bill

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
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

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]