Error: Control can’t be edited; it’s bound to the expression... 1

[lmcc007]

I am doing a two-form approach for my search form. The form I am having trouble with is: frmFindACompany. frmFindACompany have 10 unbound text boxes, which I am using as labels because of the On Got Focus event.

There are 10 unbound text boxes that I am using to enter the data to find.

When txt1_CompanyID has focus, it will make txtFindWhat1 visible and so on.

When I try to enter the data to the FindWhat text box I get: Control can’t be edited; it’s bound to the expression “Company ID”.

Everything is unbound. This is just a search form. What am I doing wrong?

Thanks!

 

[lmcc007]

Okay, I put:

Select Case Me.lstSelectField.Value
Case 1: ' CompanyID
strWhere = "CompanyID =" & Me.txtFindWhat
If DCount("*", "Company", strWhere) Then
DoCmd.OpenForm "fmainCompany", , , strWhere
Else
DoCmd.OpenForm "frmSearchCompanyID"
End If

Wonderful!

A whole week down the drain trying to do FindFirst() and stuff. I knew it was something simple.

Thank you!

 

[dhookom]

Actually, you might want to change the code to:

  If DCount("*", "Company", strWhere) > 0 Then

The results should be the same but it is more acceptable coding.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Okay, it worked. What's the difference?

 

[dhookom]

You want the expression between the "If" and "Then" to evaluate to either true or false. DCount() returns almost any number from 0 to whatever. 0 is always false and numbers greater than 0 are evaluated as true.

Try open the debug window (Press Ctrl+G) and enter

? IIf(3,"true","False")

? IIf(0,"true","False")

? IIf(-3,"true","False")

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Thanks Duane!

For some reason I am having a hard time understanding debugging code--debug.print and immediate window and stuff.

Oh well, I'm still learning.

Thanks for all your help!

 

[PHV]

numbers greater than 0 are evaluated as true
In fact, numbers different than 0 are evaluated as true.

 

[lmcc007]

Yeah, because -3 was true.

So, is it saying if strWhere is greater than zero then open fmainCompany?

I am a little confused because I entered a fake number (which was not zero) and it opened "frmSearchCompanyID", which is correct. Maybe that's why I keep thinking <>.

 

[dhookom]

strWhere is just the filter/criteria applied to the records in Company. I don't know where you "entered a fake number". As we have pointed out any DCount() that does not return 0 will be evaluated as a True expression in this code.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

In the txtFindWhat box for CompanyID, I entered a CompanyID number that I know is incorrect.

 

[dhookom]

If the results aren't what you expect, learn how to debug your code faq705-7148.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

I found this bug in my code. I have four fields for phone number in Company table, which are:

Phone
Phone2
Phone3
Fax

Here's the code:

Case 4: ' Company Phone
strWhere = "CompanyPhone Like '" & Me.txtFindWhat & "*'"
If DCount("*", "qryF_PhoneNumbers", strWhere) > 0 Then
DoCmd.OpenForm "fmainCompany", , , strWhere
Else
DoCmd.OpenForm "frmSearchPhoneNumber"
End If

How do I add those fields in code?

 

[dhookom]

Why did you use
"CompanyPhone Like '" & Me.txtFindWhat & "*'"
when it appears there is no field named CompanyPhone? What is qryF_PhoneNumbers?

Can you take the time to tell us what you would like to happen?

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Sorry, my fast typing.

Company table has four fields which are: CompanyPhone, CompanyPhone2, CompanyPhone3, and CompanyFax. I created a union query so I could accurately search for all the numbers in one place.

(I know it would be better to have telephone numbers in a separate table, but this is what they have. They like their form look and so on.)

The Select Case will find the numbers in CompanyPhone. If I enter a fax number or CompanyPhone2 or CompanyPhone3, it will bring up fmainCompany displaying a new record.

 

[dhookom]

strWhere can't be used in both the DCount() and the DoCmd.OpenFrom since qryF_PhoneNumbers and the Company table don't have the same structure.


Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Oh, okay. Maybe that's why I can't get it to work.

Can it be done without strWhere? I tried "Or" and "And" but not working.

 

[lmcc007]

I changed the code to:

If DCount("*", "qryF_PhoneNumbers", "CompanyPhone") > 0 Then
DoCmd.OpenForm "fmainCompany", , , "CompanyPhone =" & Me.txtFindWhat
Else
DoCmd.OpenForm "frmSearchPhoneNumber"
End If

But keep getting an error.

I am trying to say: look in qryF_PhoneNumbers for all the records, if CompanyPhone field matches me.txtFindWhat open fmainCompany.

 

[dhookom]

I wouldn't create a union query.

strWhere = "CompanyPhone & '~' & CompanyPhone2 & '~' &  CompanyPhone3 & '~' & CompanyFax Like '*" & Me.txtFindWhat & "*'"
If Dcount("*","Company",strWhere) > 0 Then
   DoCmd.OpenForm "fmainCompany", , , strWhere
End If

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Thanks Duane,

I was trying something like this but I didn't include: & '~' &. What does "& '~' &" do?

All these quotes can be so confusing. Is there a way to work around using quotes?

 

[dhookom]

Did it work? Did you try it?
The ~ just keeps the values separated so 23455 doesn't match phone numbers like:
[red]555-1234[/red][blue]555-7777[/blue]

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Yes Duane, it work great!

Wow I didn't know that about "~". Learn something else new. Thank you!