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!

 

[dhookom]

The control source property should be blank.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

It's blank. Here is what I have:

Text Box 1 used as a label:
Name = txt1_CompanyID
Control Source = ="Company ID"
On Got Focus = [Event Procedure] = Me.txtFindWhat1.Visible = True
LostFocus() = [Event Procedure] = Me.txtFindWhat1.Visible = False

Text Box 1 used to enter the data:
Name = txtFindWhat1
Control Source = (blank—nothing there—and it says Unbound)
Visible = No

 

[dhookom]

This is quite unconventional. Why do you set txtFindWhat1.visible to false? Wouldn't this render the text box unusable?

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Why do you set txtFindWhat1.visible to false? Wouldn't this render the text box unusable?"

Because when it moves to txtFindWhat2, I want it to be visible.

That's a good question. Let me think about it some more.

 

[lmcc007]

Thanks Duane,

You are correct! When I removed the LostFocus() code then I was able to type in the txtFindWhat1 box.

The problem is: If I choose txt2_CompanyName, the txtFindWhat1 box is still visible. I no longer want to see it; I want to see only txtFindWhat2.

The frmFindACompany unbound form allows me to enter a CompanyID, CompanyName, and so on, and then opens the filtered Data-entry form.

 

[dhookom]

If/when you choose txt2_CompanyName then make txtFindWhat1 invisible.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Okay Duane, I did this:

Private Sub txt2_CompanyName_GotFocus()
Me.lbl1_CompanyID.Visible = False
Me.txtFindWhat1.Visible = False

Me.lbl3_EventDate.Visible = False
Me.txtFindWhat3.Visible = False

Me.lbl2_CompanyName.Visible = True
Me.txtFindWhat2.Visible = True
End Sub

for the row of fields--that is:

Company ID
Company Name
Event Date

and so on.

 

[lmcc007]

Okay Duane,

I went back to the old db to see why I decided not to use the list box. I was trying to use FindFirst() function and kept receiving an error. Below is the code I was using:

Private Sub txtFindWhat_AfterUpdate()

DoCmd.OpenForm "fmainCompany"

Dim rs As DAO.Recordset
Set rs = Forms!fmainCompany.RecordsetClone

Select Case Me.lstSelectField.Value
Case 1: ' CompanyID
Call rs.FindFirst("CompanyID =" & Me.txtFindWhat)
Forms!fmainCompany.Recordset.Bookmark = rs.Bookmark
Case 2: ' CompanyName
Call rs.FindFirst("CompanyName Like '" & Me.txtFindWhat & "*'")
Forms!fmainCompany.Recordset.Bookmark = rs.Bookmark
Case 3: ' Date of Event
DoCmd.OpenForm "fdlgEventDetail"
Call rs.FindFirst("EventDate =" & Me.txtFindWhat)
Forms!fdlgEventDetail.Recordset.Bookmark = rs.Bookmark
Case Else
MsgBox "Invalid selection", vbExclamation
Exit Sub
End Select
End Sub

When I got to Case 3, it doesn't open FdlgEventDetail form; it open fmainCompany, and I received an error.

I couldn't figure out how to get it to work so I tried something else.

 

[dhookom]

I don't know where the list box comments are coming from (probably a previous thread). I don't know what you are attempting to do. I don't open forms and then attempt to use bookmarks on the newly opened form.

Why would you open fmainCompany if you want to open fdlfEventDetail?

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

I was trying to do a FindFirst(), which require the form be opened. Case 1 and 2 will open fmainCompany. Case 3 should open fdlgEventDetail. Case 4 should open another form and so on.

I was reading Automating Microsoft Access with VBA, pg. 198, which states:

“... Unbound text boxes are useful when you want to allow the users to enter data that’s used transiently. For instance, the Billing Report Setup form in the TimeTrack database allows the users to select a client, start date, and end date, and open a report in preview mode….”

I am trying to do a simple unbound form like this.

 

[dhookom]

I expect none of the samples in the book use recordsets. If you want to open a form or report to a specific record, consider using the WHERE CONDITION in the DoCmd.Open... method.

     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

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Thanks Duane,

Correct, the sample in the book is not using recordsets. Actually there code is similar to yours--that is: DoCmd.OpenReport "BillingReport", acViewPreview, WhereCondition:=txtWhere.Value (txtWhere is the unbound box). I was told on another site to use FindFirst() because it was more accurate and so on. So, I started reading up on it and trying to use it.

Okay, now back to the code. The Select Cases are opening to the correct form, but the WHERE CONDITION is not working--not filtering.

Am I missing something?

 

[lmcc007]

Got Duane,

I went back and added another comma and its working. THANK YOU VERY VERY MUCH!!!

 

[dhookom]

I might have missed a comma prior to strWhere. Look in Help.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Duane,

How do I handle when no match? How do I tell it open another form instead?

 

[dhookom]

You can use DCount() with the same strWhere to test for matches prior to opening the form.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

DCount()? Not following you. Let me look it up.

 

[lmcc007]

Okay, I tried this:

Case 1: ' CompanyI
If DCount("[CompanyID]", "Company", "[CompanyID] <> 'me.txtFindWhat'") Then
strWhere = "CompanyID =" & Me.txtFindWhat
DoCmd.OpenForm "fmainCompany", , , strWhere
End If

The DCount here doesn't do anything. If I enter a wrong ID, it just open a new blank form.

 

[dhookom]

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

Duane
Hook'D on Access
MS Access MVP