INSERT INTO from multi line text box

[Only4Access]

Hi Experts,

I have a table tblAccounts. ID (Autonumber), AccountNumber, TransDate, (Default as Date()), TransAmount, EntryDateTime (default as Now())

I have set up a form (frmTemp) with text box (Text1) with multi line enabled. In properties 'New Line in Field' so people will enter or copy paste from note pad. As we know will have multiple lines or just one line.

I have also created a form for tblAccount (frmAccounts) default view as Continuous Forms.

What I am trying to do is when click on a button;



1. Loop through each line in Text1 & insert each line in AccountNumber field in tblAccounts. TransDate: Date(), TransAmount: $0, EntryDateTime: Now()
2. Close this form (frmTemp) & open frmAccounts

If Text1 has 5 lines Insert these 5 accounts in tblAccount with other field it's default values.

Thank you for your help.

[June7]

Maybe something like:

Dim aryAcct As Variant
Dim i As Integer
aryAcct = Split(Me.Text1, vbCrLf)
For i = 0 to UBound(aryAcct) - 1
CurrentDb.Execute "INSERT INTO tblAccounts(AccountNumber, TransDate, TransAmount, EntryDateTime) VALUES('" & aryAcct(i) & "', Date(), 0, Now())"
Next

[Only4Access]

Thank you June7 for you reply.

This one works great, but always skip last line in text box. When I remove -1 in ubound works well.

Is that OK or should i do something else?

Thank you June7.

[June7]

That surprises me but as long as you get what you need.