Hi Guys
I have been struggling with a problem
I have two forms
frmAddNotes
frmAddContacts
On the form frmAddNotes i have a "ComboBox" with the names of contacts associated with a customer called "ContactNameCBOBox"
on the Notinlist event of the combobox i have this code
Code:
Dim AddContact
AddContact = MsgBox("Would You Like To Add " & _
NewData & " As A New Contact? ", vbYesNo)
If AddContact = vbNo Then
' cancel the edit.
ContactNameCBOBox.Undo
' tell access not to show the error message you have already dealt with it
Response = acDataErrContinue
Else
'tell access not to worry as you are adding the missing contact
Response = acDataErrAdded
' open the add contact form with these details
DoCmd.OpenForm "frmAddContact", , , , , acDialog, NewData
'cancel the edit. thats because you need to refresh the list before you can select the new Product
ContactNameCBOBox.Undo
'refresh the list
ContactNameCBOBox.Requery
'the contact name is being pulled in from frmAddContact close event to allow for changes
End If
this opens up the form "frmAddContact", if the combobox on the form "frmAddNotes" form contained a space for example "Steve Smith" then these are broken out correctly into the firstnametxtbox and lastnametxtbox when the form "frmAddContact" form opens
if how ever only a single name has been entered for example "Steve" i.e the combo box does not contain any spaces, i am getting a "Run Time error 9, Subscript out of range" Error
The code i have on the form "frmAddContact" is below
Code:
Select Case CurrentProject.AllForms("frmCustomersAddNote").IsLoaded
Case Is = True
Me.CustomerID = [Forms]![frmCustomersDetails]![CustomerID]
[ContactFullName] = OpenArgs
'now we split contact ful name string into first name last name
Dim LString As String
Dim LArray() As String
LString = [ContactFullName]
LArray = Split(LString)
'if the contactfullname textbox has a a string like "Steven Hawkins", put the firstname into the firstnametxtbox and the last name into the lastnametxtbox
If Not (IsEmpty(LArray(0))) And Not (IsEmpty(LArray(1))) Then
Me.FirstNameTXTBox = LArray(0)
Me.LastNAmeTXTBox = LArray(1)
Me.FirstNameTXTBox = StrConv(FirstName, vbProperCase)
Me.LastNAmeTXTBox = StrConv(LastName, vbProperCase)
Exit Sub
ElseIf Not (IsEmpty(LArray(0))) And (IsNull(LArray(1))) Then
Me.FirstNameTXTBox = LArray(0)
Me.FirstNameTXTBox = StrConv(FirstName, vbProperCase)
End If
Case Is = False
Me.CustomerID = [Forms]![frmCustomersDetails]![CustomerID]
End Select
The red line is the point at which the code fails if only "steve" is entered into the combobox on the form "frmAddNote"
How can i change the code so that if the string being entered into the combobox does not contain a space, then put this value in the firstname field
but if it does have a space such as "Steven Hawkins" the put "Steven" in the firstname and "Hakings" into the lastname textbox
any help would be wonderful
many thanks
Steve