I wish to populate 2 text fields with information taken from a string.

I have used the VB Right method to look at he last 5 characters within the string (MSN field taken from qrywarrants), depending on the last 5 characters, this should populate the text fields of txtAccountNo and txtAccountyType on the fromwarrants form.

The routine is held on the open command of form frmwarrants.

For some reason it just wont do it, it only adds something on the first line, any help would be helpful.
I have attached the database in question, plus the code is below.



Private Sub Form_Open(Cancel As Integer)

'Set account type and account no

Dim accountno As Integer
accountno = Right([msn], 5)

DoCmd.Maximize
Forms!frmwarrant.RecordSource = "SELECT * From qrywarrant order by [warrantstart], [expr1], [issue created]"
txtwarrantno.SetFocus

If accountno = 10615 Then
txtAccountNo = 9300
txtAccountType = "Civilian"
End If

If accountno = 10507 Then
txtAccountNo = 9400
txtAccountType = "Civilian PE"
End If

If accountno = 10616 Then
txtAccountNo = 9700
txtAccountType = "Navy"
End If

If accountno = 10617 Then
txtAccountNo = 9800
txtAccountType = "Army"
End If

If accountno = 10618 Then
txtAccountNo = 9900
ttxtAccountType = "RAF"
End If


End Sub

Hi
An option could be to assign the descriptive values in the underlying query of the form:

SQL:

SELECT tblwarrants.[issue reference],
tblwarrants.warrantstart,
[warrantstart] + 49 AS expr1,
tblwarrants.uin,
tblwarrants.msn,
tblwarrants.[qty shipped],
tblwarrants.[who imported],
tblwarrants.[when imported],
tblwarrants.[issue created],
Iif(Right([msn],5) = 10615,9300,Iif(Right([msn],5) = 10507,9400,Iif(Right([msn],5) = 10616,9700,Iif(Right([msn],5) = 10617,9800,990)))) AS accno,
Iif(Right([msn],5) = 10615,"Civilian",Iif(Right([msn],5) = 10507,"Civilian PE",Iif(Right([msn],5) = 10616,"Navy",Iif(Right([msn],5) = 10617,"Army","RAF")))) AS acctype
FROM tblwarrants;

Then replace those form controls with the fields from the query..!! (field list)
accno
acctype



HTH's

Works a treat thanks very much for your help

Hi
You're welcome..

BTW, the reason it only worked on the first record was your choice of code placement. Code in the Form_Open or Form_Load events will only effect the first record. Form_Current has to be used for this kind of code to be applied to every record, and this will only work in a Single View form, not Datasheet or Continuous Views.Arry's solution has the advantage of working in all three views.