text box option group???

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Angi - 28 Apr 2005 02:44 GMT

Is there such a thing as text box option group?? ie: You type in one
box and the others have to stay empty...one or the other type thing. I
don't think there's such a thing, so I wrote the following if statement
but I get the msgbox when there's only field with data. I put a break
in to see where the problem was and it was the last elseif
(txtcophone). There is only data in the txtCoCOID (first If). I've
even disabled the other fields to see if that helped...it didn't. What
am I doing wrong or what is the better way to do this? TIA!

If Not IsNull(Me.txtCoCOID) And Not IsNull(Me.txtCoPhone) Or Not
IsNull(Me.cboCoName) Then
If MsgBox("There is more than one field with information.
Please choose only one field to search for.", vbCritical + vbOKOnly,
"Error!") Then
End If
ElseIf Not IsNull(Me.cboCoName) And Not IsNull(Me.txtCoCOID) Or Not
IsNull(Me.txtCoPhone) Then
If MsgBox("There is more than one field with information.
Please choose only one field to search for.", vbCritical + vbOKOnly,
"Error!") Then
End If
ElseIf Not IsNull(Me.txtCoPhone) And Not IsNull(Me.cboCoName) Or
Not IsNull(Me.txtCoCOID) Then
If MsgBox("There is more than one field with information.
Please choose only one field to search for.", vbCritical + vbOKOnly,
"Error!") Then
End If
End If

Reply to this Message

Van T. Dinh - 28 Apr 2005 05:32 GMT

I am not sure why you have the (inner) IF for each MsgBox command??? since
you are not even checking the returned value of the MessageBox.

The logic of the conditional expressions are also incorrect. The way you
have it at the moment:

Expr1 And Expr2 Or Expr3

wihch will be evaluated like

(Expr1 And Expr2) Or Expr3

i.e. the And operation first while you need

Expr1 And (Expr2 Or Expr3)

i.e. the Or operation first.

I think there is a much shorter way of writing this code. Try (untested):

If ( Not IsNull(Me.txtCoCOID) ) + ( Not IsNull(Me.txtCoPhone) ) + _
( Not IsNull(Me.cboCoName) ) < -1 Then
MsgBox "There is more than one field with information." & vbCrLf & _
"Please choose only one field to search for.", _
vbCritical + vbOKOnly, "Error!"
End If

HTH
Van T. Dinh
MVP (Access)

> Is there such a thing as text box option group?? ie: You type in one
> box and the others have to stay empty...one or the other type thing. I
[quoted text clipped - 24 lines]
> End If
> End If

Reply to this Message

Angi - 28 Apr 2005 07:38 GMT

Van,
That was some ugly coding, wasn't it??? Why do you think I needed
help??? Your "untested" code worked great!! I'm not sure I understand
the < -1 though. I've tried to logically think through it, but I'm not
getting it. As far as the inner IF on the msgbox...it was supposed to
say = vbok, but I left that out. Surprised it was working! As far as
why I do it that way, I guess the only good answer I can come up with
is, I've always done it that way. Thank you very much for that and I
hope someday I can think of code on the fly like that, test it, and
have it work!! Also, thank you for putting it on two lines...I hadn't
done that yet.

Just to show you the big picture (and honestly I'm anxious to see what
you say), this is the code that goes with the Select Case in the other
thread. You'll see why I wanted to consolidate statements. My Dlookup
isn't working, but I'll worry about that in a minute:

Dim phoneX As Integer

Select Case frmSearch
Case 1 'company button is selected
'check to see if more than one has data
If (Not IsNull(Me.txtCoCOID)) + (Not IsNull(Me.txtCoPhone)) + _
(Not IsNull(Me.cboCoName)) < -1 Then
MsgBox "There is more than one field with information." &
vbCrLf & _
"Please choose only one field to search for.", _
vbCritical + vbOKOnly, "Error!"
ElseIf Not IsNull(Me.txtCoCOID) Then 'COID has data only
Select Case frmSearch & frmOptions
Case 1 & 1
DoCmd.OpenReport "newcorec", acViewPreview, ,
"coid=" & Me.txtCoCOID
Case 1 & 2
DoCmd.OpenReport "companyrecord", acNormal, ,
"coid=" & Me.txtCoCOID
Case 1 & 3
DoCmd.OpenForm "companymain", acNormal, , "coid=" &
Me.txtCoCOID
End Select
ElseIf Not IsNull(Me.cboCoName) Then 'Company Name has data
only
Select Case frmSearch & frmOptions
Case 1 & 1
DoCmd.OpenReport "newcorec", acViewPreview, ,
"coid=" & Me.cboCoName.Column(0)
Case 1 & 2
DoCmd.OpenReport "companyrecord", acNormal, ,
"coid=" & Me.cboCoName.Column(0)
Case 1 & 3
DoCmd.OpenForm "companymain", acNormal, , "coid=" &
Me.cboCoName.Column(0)
End Select
ElseIf Not IsNull(Me.txtCoPhone) Then 'Phone # has data only
phoneX = DLookup("coid", "compmain", "phone = " &
Forms!frmSearch!txtCoPhone)
Select Case frmSearch & frmOptions
Case 1 & 1
DoCmd.OpenReport "newcorec", acViewPreview, ,
"coid=" & phoneX
Case 1 & 2
DoCmd.OpenReport "companyrecord", acNormal, ,
"coid=" & phoneX
Case 1 & 3
DoCmd.OpenForm "companymain", acNormal, , "coid ="
& phoneX
End Select
End If
End Select

Reply to this Message

Angi - 28 Apr 2005 08:19 GMT

Before you tell me, I just realized that the second select case
frmSearch is redundant. I didn't plan it to be but the If statement
had to go before the select case, so that's what happened. Because I
check the frmSearch first, I only need to check the frmOptions. DUH!!
Sleep deprivation...it's a bad, bad thing!

Reply to this Message

Van T. Dinh - 28 Apr 2005 08:37 GMT

1. True is internally stored as -1.

If 2 or more TextBoxes have entry, the sum of the Boolean expressions will
be -2 or -3 and the If condition will become True and the code will display
the MsgBox.

2. If "phone" is a Text field, you need to enclose the explicit value (you
pass in) with String delimiter, i.e. single or double quote. Try:

phoneX = DLookup("coid", "compmain", _
"phone = '" & Forms!frmSearch!txtCoPhone & "'")

3. I would suggest you do pseudo-code on paper first before actually writing
detailed code. This way, you can see the picture clearly and eliminate
unnecessary conditions / checks.

You can also "trace" the code logically and see if the logic is correct and
efficient before spending time doing detailed (actual) code.

For example, in the long code you posted, once you go into the first case of
the outer Select Case statement, frmSearch value _must_be 1 and therefore in
the (inner) Select Case statement(s), you don't need to check the value of
frmSearch again!

It sounds like you are going to end up with nested Select Case but the the
inner Select Case statements will be much simpler.

HTH
Van T. Dinh
MVP (Access)

> Van,
> That was some ugly coding, wasn't it??? Why do you think I needed
[quoted text clipped - 65 lines]
> End If
> End Select

Reply to this Message

Angi - 28 Apr 2005 08:48 GMT

Van,
Forgot about the -1 being True. The dlookup works now...that was the
problem. I knew how to fix it, I just had to many "'s in there. Did
you see my post right before yours about being redundant? I knew you
were going to call me on that! Believe it or not, I DID do the pseudo
code first...but I didn't know the IF couldn't go between the Select
Case and Case code until I typed it in and got the error. After that,
the flow of my code changed. It's still good to know how to use more
than 1 variable with Select Cases. I use them a lot! Search form
works great now and I consolidated 5 forms into one, so I feel good.
Thanks for all your help!!

Take care,
Angi

Reply to this Message

Van T. Dinh - 28 Apr 2005 08:52 GMT

I saw you post after since it took my a while to compile my post.

Van T. Dinh
MVP (Access)

> Van,
> Forgot about the -1 being True. The dlookup works now...that was the
[quoted text clipped - 10 lines]
> Take care,
> Angi

Reply to this Message