Error: Control can’t be edited; it’s bound to the expression “Company ID”

[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 “CompanyID”.

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

Thanks!

 

[vbaInet]

Looks like a Control Source issue. Recheck that control's property again. If you're still not having any luck, post your db and I'll have a look.

 

[lmcc007]

vbaInet,

here is the db. I saved it as a 2000 version. I am using 2007.

Thanks!

 

[vbaInet]

Well, I wouldn't be able to replicate your problem if all you attached is the form without the tables.

Why 10 individual hidden textboxes? Can't you use just one?

 

[lmcc007]

Well, I wouldn't be able to replicate your problem if all you attached is the form without the tables.

Why 10 individual hidden textboxes? Can't you use just one?

Well, okay.

This form is not bound to a table. This is just a search form.

As you move through the fields, you should see them change.

Once I get it to work, each FindWhat box will open a different table and so on.

I tried it with a list box using one FindWhat box, but had problems when I was trying to search and so on.

 

[vbaInet]

Are you users going to be using the tables for data entry once it's opened? Have you heard of the Tag property? You could use that!!

 

[lmcc007]

Are you users going to be using the tables for data entry once it's opened? Have you heard of the Tag property? You could use that!!

No this form will not be used for data entry. It will be used to search for a Company by one of the fields listed. If the data is found, it will open the data-entry form filtered for that company.

No, I have not used the Tag property. I am still learning VBA. When I read about it [Tag], I did not understand it or its use.

All I am doing is trying to give them the look they requested without having to continue say it can't be done or I don't know how.

 

[vbaInet]

No this form will not be used for data entry. It will be used to search for a Company by one of the fields listed. If the data is found, it will open the data-entry form filtered for that company.

I was basing my response on what you said below:

Once I get it to work, each FindWhat box will open a different table and so on.

That was why I asked to ensure you weren't using tables as data-entry.

No, I have not used the Tag property. I am still learning VBA. When I read about it [Tag], I did not understand it or its use.

I will re-attach your db with an example.

 

[vbaInet]

It's not a copy and paste thing. I have re-attached your database with my code and written a few explanations. It's only four lines of code but it does the job you were trying to do and even better.

What I've used is a listbox so no Tag property used. Have a look. The forms, frm1 and frm2 will open depending on which item is selected.

For the searching aspect, use a DLookup() function. Here are some links:

http://www.techonthenet.com/access/functions/domain/dlookup.php
http://www.mvps.org/access/general/gen0018.htm

 

[boblarson]

You do NOT need so many text boxes. Why are you not using a single text box for the search input?

 

[lmcc007]

vbaInet,

I went back to my original form where I was using a list box. Here is the code I am using:

Private Sub txtFindWhat_AfterUpdate()
Dim strWhere As String

Select Case Me.lstSelectField.Value

Case 1: ' CompanyID

strWhere = "CompanyID =" & Me.txtFindWhat
DoCmd.OpenForm "fmainCompany", , , strWhere​

Case 2: ' CompanyName

strWhere = "CompanyName Like '" & Me.txtFindWhat & "*'"
DoCmd.OpenForm "fmainCompany", , , strWhere​

Case 3: ' Date of Event

strWhere = "EventDate =#" & Me.txtFindWhat & "#"
DoCmd.OpenForm "fdlgEventDetail", , , strWhere​

Case Else

MsgBox "Invalid selection", vbExclamation​

Exit Sub
End Select​

End Sub​

I still would like to resize the labels like you did. Are you saying I have to go back and add the size to a column? And, on the width, I still can't figure out how to get the width by using a msgbox.

Thanks for your help!

 

[vbaInet]

It's up to you whether you want it in the column (which is a much elegant way) or you use a select case statement.

You do this to get the width:

Msgbox Me.Controls("NameOfTextBox").width

Or just

Msgbox [NameOfTextbox].width - without the square brackets of course

Put that code on the CLICK event of one of your controls. Change the name for each textbox. You can also use Debug.Print if you know where the Immediate Window is located.


@boblarson: That's correct. I had already mentioned that to the OP and the DB I re-attached uses only one.

 

[lmcc007]

... or you use a select case statement

vbaIntet,

How do I get it to work with the above Select Case code? I've been trying to do it but can't figure it out.

Thanks!

 

[vbaInet]

Create a variable at the Declarations section of your form module and set that variable to the width of the label in each CASE statement.

Options Compare Database
Options Explicit

Dim lblFindWidth as Integer

 

[lmcc007]

Hey vbaInet,

I am practicing trying to follow your form. This is what I have so far:

Private Sub Form_Load()

List6.Value = vbNullString
lblFindWhat.Caption = "select one"
txtFindWhat.Width = 1800​

End Sub
​

Private Sub List6_Click()

lblFindWhat.Caption = Nz(List6.Column(2), "me blank")
txtFindWhat.Width = Val(Nz(List6.Column(4), 1800))
txtFindWhat.LeftMargin = Val(Nz(List6.Column(5), 3))​

End Sub
​

I did like you did; that is: I added the data to the columns. It's working. Only problem is the LeftMargin is off. I do not know how to figure twips. Anyway, in Column 5 I entered the LeftMargin as 3.0417, 3.2917, 3.2083, 3.4583, and 2.833 respectively.

I am searching the Internet trying to find anything on how to figure out twips for 3.0417 and so on.

Any suggestions?

 

[vbaInet]

I think what you should be using is LEFT and not LEFTMARGIN. Set the TOP or LEFT of one of your controls to 3.0417 and use a Msgbox to get the twips equivalent.

 

[lmcc007]

Thanks a lot vbaInet,

Never would have figured that twips thing out. And, the MsgBox trick is gret.

Learn something new--Loving It!!

 

[vbaInet]

Debug.Print to the Immediate window is even better than Msgbox if you want to be able to take a copy of the output. Look into using that.

You're welcome.

 

[lmcc007]

Okay vbaInet,

Now that I have the list box doing what I want it to using:

Private Sub List6_Click()

lblFindWhat.Caption = Nz(List6.Column(2), "i'm blank")
txtFindWhat.Width = Val(Nz(List6.Column(4), 1800))
txtFindWhat.Left = Val(Nz(List6.Column(5), 0))​

End Sub​

will I need to change the Select Case code below:

Private Sub txtFindWhat_AfterUpdate()

Dim strWhere As String
Select Case Me.lstSelectField.Value

Case 1: ' CompanyID

strWhere = "CompanyID =" & Me.txtFindWhat
DoCmd.OpenForm "fmainCompany", , , strWhere​

Case 2: ' CompanyName

strWhere = "CompanyName Like '" & Me.txtFindWhat & "*'"
DoCmd.OpenForm "fmainCompany", , , strWhere​

Case 3: ' Date of Event

strWhere = "EventDate =#" & Me.txtFindWhat & "#"
DoCmd.OpenForm "fdlgEventDetail", , , strWhere
​

Case Else

MsgBox "Invalid selection", vbExclamation​

Exit Sub
End Select
​

End Sub​

 

[vbaInet]

Don't use the After_Update event of the Find textbox. Create a button that when clicked will open the form. All that you need to know is in the db.