Question about using "Instr" in Access VBA

[adibakale]

I am pretty new to VBA and managed to write some code to export some customer information to word similar to a mailmerge function. In my Access database, The customer name shows as:

LastName, FirstName Middle Initial
Smith, John A

I use the Instr function to use the comma position and export the data to word so that it shows in this format:

FirstName, Middle Initial LastName
John A Smith

There are some instances where there is no comma in the customer name. When this happens, the code stops running and doesn't continue to export information into the word document.

I have tried using If - Then - Resume Next but I have been unable to figure this out. Any help would be greatly appreciated.

Here is my code:

---

Dim commaposition As Integer

commaposition = InStr(Forms!frm_main!CustomerName, ",")

.ActiveDocument.Tables(2).Cell(1, 1).Select
.Selection.Text = Mid(CStr(Forms!frm_main!CustomerName), commaposition + 1)
.ActiveDocument.Tables(2).Cell(1, 2).Select
.Selection.Text = Left(CStr(Forms!frm_main!CustomerName), commaposition - 1)
.ActiveDocument.Tables(3).Cell(1, 1).Select

---

If there is no comma, the code stops running and does not send any more information to word.

What I need is to add code to address occurences were there is no comma.

 

[ranman256]

commaposition = InStr(Forms!frm_main!CustomerName, ",")

if commaposition  = 0 then
    'just use the whole name?
else
  .ActiveDocument.Tables(2).Cell(1, 1).Select
  .Selection.Text = Mid(CStr(Forms!frm_main!CustomerName), commaposition + 1)
  .ActiveDocument.Tables(2).Cell(1, 2).Select
  .Selection.Text = Left(CStr(Forms!frm_main!CustomerName), commaposition - 1)
  .ActiveDocument.Tables(3).Cell(1, 1).Select
end if

 

[adibakale]

Great thanks so much!