Nested IF with INSERT INTO

[Only4Access]

Hi Forum,



I have an unbound form with multiple textbox. I am trying to force users to fill all required field, then INSERT to a table.

Conditions are,


Dim arrAcct As Variant
Dim i As Integer

arrAcct = Split(Me.TxtAccounts, vbCrLf)

If IsNull (Me.Text1) Then

msgbox "Text1 is empty"

Me.Text1.SetFocus

ElseIf IsNull (Me.Text2) Then

msgbox "Text2 is empty"

Me.Text2.SetFocus

ElseIf IsNull (Me.Text3) Then

msgbox "Text3 is empty"

Me.Text3.SetFocus

For i = 0 To UBound(arrAcct)

CurrentDb.Execute "INSERT INTO tblAccounts (TransAccount) VALUES ('" & arrAcct & "')"

Next

End If

But this is giving an error. If all fields are NOT filled out do not Execute INSERT. How can i use IF here properly.

Thank you for your valuable time.

[pbaldy]

What's the error? Does this help?

http://www.baldyweb.com/ImmediateWindow.htm

for one thing, you're not referring to the array value, like arrAcct(i)

[pbaldy]

Oh, and the loop should be in the Else clause.

[ipisors]

First, your End If should be after the last ElseIf.... not at the end of the entire code.

Second, you should Exit Sub in the conditions where things are not as you wish them to be.

Third, you shouldn't test for IsNull, it doesn't properly cover the possibilities.
There are several ways to solve this, I prefer something along the lines of
If Len("" & Me.ControlName.Value)=0 Then

[Only4Access]

Thank you Paul for your reply. In my real code, yes there is (i). That was typo.

Isaac Pisors,

First, your End If should be after the last ElseIf.... not at the end of the entire code.

Second, you should Exit Sub in the conditions where things are not as you wish them to be.

Could you please help me to revise the code?

[ipisors]

Isaac Pisors,

Could you please help me to revise the code?

I was kind of hoping you'd give it a shot yourself ... taking my very specific suggestions and implementing them. Try it out, let us know how it goes!

[pbaldy]

You can just add Else before the For... line. ipisors suggestion is simply another way to go.