Combo On Not In List - am I asking too much?

[Billp]

Hi,

To fulfil some wishes of a user entering a persons name into a combo box,
that is not on the list and having three choices i thought of the following,
Its not working gets hung up before it starts.
User choices are too add the name to the list and open a form, to not do
anything and allow the name to populate the field overriding the Not In List
and third to just add the name to the list an not open the form

From the form call up a function in a module as follows.

Private Sub Combo106_NotInList(NewData As String, Response As Integer)
' Allows user to add a new Contact by typing the customer's name
' in the Customer_ID combo box and opening the Customer edit Form.

Dim intNewCustomer As Integer, strtitle As String
Dim intMsgDialog As Integer, strMsg As String

' Check if user has already selected a customer.

If IsNull(Me![Combo106]) Then

Call SER_Edit_Not_In_List

Response = acDataErrAdded
Else
' Display the default error message.
Response = acDataErrDisplay

End If
End Su
‘************************************************************************************************************
Function SER_Edit_Not_In_List()

'user has entered a new Contact that is not in the list

Dim strsql As String
Dim strsql1 As String
Dim str1 As String
Dim str2 As String
Dim intpak As Integer
Dim stDocName As String



intpak = MsgBox("Do you wish to add a new contact?" & _
" Select YES to open Customer Table to Enter / Delete
contacts?" & _
" - NO to accept entry as One-off, " & _
" - Cancel To append Name to customer List without Details",
vbYesNoCancel, _
"Update Customer")

'separate the entry into first and last names
'str1 = Left(Forms![Sales Enquiry Register edit form]![Contact Name],
InStr(1, Forms![Sales Enquiry Register edit form]![Contact Name], "") - 1)
'str1 = ParseWord(Forms![Sales Enquiry Register edit form]![Contact
Name], 1)
str1 = ParseWord(Forms![Sales Enquiry Register edit
form]![Combo106].NewData, 1)

'str2 = Right(Trim(Forms![Sales Enquiry Register edit form]![Contact
Name]), Len(Trim(Forms![Sales Enquiry Register edit form]![Contact Name])) -
InStr(1, Forms![Sales Enquiry Register edit form]![Contact Name], " "))
'str2 = ParseWord(Forms![Sales Enquiry Register edit form]![Contact
Name], -1)
str2 = ParseWord(Forms![Sales Enquiry Register edit
form]![Combo106].NewData, -1)

Select Case intpak

'*******************************************
Case vbYes
'open customer edit form
DoCmd.SetWarnings False

'Firstly Enter new contact
strsql = "INSERT INTO [tblCUSTCONTACTS] (FIRST_NAME, LAST_NAME)
" _
& "VALUES('" & str1 & "', '" & str2 & "')" _
& "WHERE [CustID] = " & Forms![Sales Enquiry Register
edit form]![CustID]

DBEngine(0)(0).Execute strsql, dbFailOnError

stDocName = "CUST_edit_Swit_FORM"
DoCmd.OpenForm stDocName, , , "[ID:]= " & Forms![Sales Enquiry
Register edit form]![CustID]
DoCmd.SetWarnings True


I cannot get the Name - say Fred Jones to separate.
Using Left() function I Get the instruction at intpak and got invalid use
of Null.
Can't progress
The ParseWord uses Allen Brownes Function and I also get the instruction at
intpak and got invalid use of Null.
Can't progress .
In the third iteration I got the error “The text you entered etc†.
All in all not working.

Ant advise and a method to allow the wishes on the NotIn List really
appreciated.

 

[Billp]

Yes i asked too much especially the hope that I could get it to do anything
before it was added to the list. I have reverted back to a more silplar - I
hope

intpak = MsgBox("Do you wish to add a new contact?" & vbCrLf & _
"Select " & vbCrLf & _
" - YES to open Customer Table to Enter / Delete contacts?" &
vbCrLf & _
" - No To append Name to customer List without Details",
vbYesNo, _
"Update Customer")

'separate the entry into first and last names
'str1 = Left(NewData, InStr(1, NewData, "") - 1)
str1 = ParseWord(NewData, 1)
'str2 = Right(Trim(NewData), Len(Trim(NewData)) - InStr(1, NewData, " "))
str2 = ParseWord(NewData, -1)


'**************************************************************************************************
Select Case intpak

'*******************************************
Case vbYes
'open customer edit form
DoCmd.SetWarnings False

'Firstly Enter new contact
strsql = "INSERT INTO [tblCUSTCONTACTS] (FIRST_NAME, LAST_NAME)
" _
& "VALUES('" & str1 & "', '" & str2 & "');" _
& "WHERE [CustID] = " & Me![CustID]
Debug.Print strsql

DBEngine(0)(0).Execute strsql, dbFailOnError

stDocName = "CUST_edit_Swit_FORM"
DoCmd.OpenForm stDocName, , , "[ID:]= " & Me![CustID]
DoCmd.SetWarnings True

Now getting problems with sql statement.
Characters found after end of SQL Statement

"INSERT INTO [tblCUSTCONTACTS] (FIRST_NAME, LAST_NAME) VALUES('Fred',
'Jones');WHERE [CustID] = 1775

I need the reference to the WHERE to get the record to append correctly so
that it can be visible in a form sub form relationship.

Thanks

 

[Dirk Goldgar]

Yes i asked too much especially the hope that I could get it to do
anything
before it was added to the list. I have reverted back to a more silplar -
I
hope

intpak = MsgBox("Do you wish to add a new contact?" & vbCrLf & _
"Select " & vbCrLf & _
" - YES to open Customer Table to Enter / Delete contacts?" &
vbCrLf & _
" - No To append Name to customer List without Details",
vbYesNo, _
"Update Customer")

'separate the entry into first and last names
'str1 = Left(NewData, InStr(1, NewData, "") - 1)
str1 = ParseWord(NewData, 1)
'str2 = Right(Trim(NewData), Len(Trim(NewData)) - InStr(1, NewData, "
"))
str2 = ParseWord(NewData, -1)


'**************************************************************************************************
Select Case intpak

'*******************************************
Case vbYes
'open customer edit form
DoCmd.SetWarnings False

'Firstly Enter new contact
strsql = "INSERT INTO [tblCUSTCONTACTS] (FIRST_NAME, LAST_NAME)
" _
& "VALUES('" & str1 & "', '" & str2 & "');" _
& "WHERE [CustID] = " & Me![CustID]
Debug.Print strsql

DBEngine(0)(0).Execute strsql, dbFailOnError

stDocName = "CUST_edit_Swit_FORM"
DoCmd.OpenForm stDocName, , , "[ID:]= " & Me![CustID]
DoCmd.SetWarnings True

Now getting problems with sql statement.
Characters found after end of SQL Statement

"INSERT INTO [tblCUSTCONTACTS] (FIRST_NAME, LAST_NAME) VALUES('Fred',
'Jones');WHERE [CustID] = 1775

I need the reference to the WHERE to get the record to append correctly so
that it can be visible in a form sub form relationship.

I haven't had time yet to look through your complete wish list, but this
simple error is occurring because you are building a semicolon into your SQL
string before the WHERE clause. Change your code to:

strsql = _
"INSERT INTO [tblCUSTCONTACTS] (" & _
"FIRST_NAME, LAST_NAME) " & _
"VALUES('" & str1 & "', '" & str2 & "') " & _
"WHERE [CustID] = " & Me![CustID]