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]

You can use any character in place of ~ as long as it's not found in any of your phone numbers.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Okay, I got you.

 

[lmcc007]

In DCount the Null is not working. Should I use Trim to check for null? Something like:

If Trim(Me.txtFindWhat & " ") = "" Then
DoCmd.OpenForm "frmSearchCompanyID"
DoCmd.Close acForm, "frmFindACompany"
End If

I just need to check for null and if it is null to open the search form. The DCount is not doing anything when the field is empty.

 

[dhookom]

What Null in the DCount?

Please be more explicit. I can't see any DCount() in the snippet you provided.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Okay, maybe I am wrong. I thought using DCount with > 0 would check for blanks. But when blank it does nothing.

I want it to check for Null and if is blank open the advanced search form. If not blank, search fmainCompany for the data in txtFindWhat box. If the data is not found, then go to the advanced search form.

 

[dhookom]

Check to see if WHAT is blank. Typically you just use:

If Me.txtYourControlName & "" = "" Then
   'do something
 Else
   'do something else
End If

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Okay Duane,

I tried several ways, but it doesn't work. Here is what I just tried again three times differently:

If Me.txtFindWhat & "" = "" Then

If IsNull(Me.txtFindWhat) Then

If Trim(Me!txtFindWhat & " ") = "" Then

All I'm trying to say if txtFindWhat is totally blank open search form. Now, when I put a "space" and hit enter it opens the search form.

 

[dhookom]

I don't see any code that opens any form.
Again, learn how to set break points so you can debug.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Below is what I have for Case 1:

Select Case Me.lstFindField.Value
Case 1: ' Company ID
strWhere = "CompanyID =" & Me.txtFindWhat
If IsNull(Me.txtFindWhat) Or Trim(Me!txtFindWhat & " ") = "" Then
DoCmd.OpenForm "frmSearchCompanyID"
DoCmd.Close acForm, "frmFindACompany"
ElseIf DCount("*", "Company", strWhere) > 0 Then
DoCmd.OpenForm "fmainCompany", , , strWhere
DoCmd.Close acForm, "frmFindACompany"
Else
DoCmd.OpenForm "frmSearchCompanyID"
DoCmd.Close acForm, "frmFindACompany"
End If

Oh, I wish I could comprehend that debug window but me brain just not getting it. I'm sure it will save me hours and hours asking simple questions.

 

[lmcc007]

Okay Duane,

I tried the below code on the On Enter and On Exit event:


If IsNull(Me.txtFindWhat) Or Trim(Me!txtFindWhat & " ") = "" Then
DoCmd.OpenForm "frmSearchCompanyID"
DoCmd.Close acForm, "frmFindACompany"
End If

The code works, but the problems are:

1) On Enter -- if I click anywhere it Opens the search form and try to close the form.

2) On Exit -- I keep getting an error: Run-time error '2585': This action can't be carried out while processing a form or report event.

 

[lmcc007]

Duane Duane,

I think I found the answer! I did the following:


Private Sub txtFindWhat_KeyDown(KeyCode As Integer, Shift As Integer)

If IsNull(Me.txtFindWhat) Or Trim(Me!txtFindWhat & " ") = "" Then
If KeyCode = vbKeyReturn Then
DoCmd.OpenForm "frmSearchCompanyID"
DoCmd.Close acForm, "frmFindACompany"
End If
End If

End Sub

So far, it's working.

 

[PHV]

Did you try the AfterUpdate event instead ?

Private Sub txtFindWhat_AfterUpdate()
  If Trim(Me!txtFindWhat & "") = "" Then
    DoCmd.OpenForm "frmSearchCompanyID"
    DoCmd.Close acForm, "frmFindACompany"
  End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lmcc007]

Did you try the AfterUpdate event instead ?" Yes, I tried that first.

The "Private Sub txtFindWhat_KeyDown" is working except that it interferes with the AfterUpdate event--you know:

rivate Sub txtFindWhat_AfterUpdate()

On Error GoTo ErrorHandler

Dim strWhere As String

Select Case Me.lstFindField.Value
Case 1: ' Company ID
strWhere = "CompanyID =" & Me.txtFindWhat
If DCount("*", "Company", strWhere) > 0 Then
DoCmd.OpenForm "fmainCompany", , , strWhere
DoCmd.Close acForm, "frmFindACompany"
Else
DoCmd.OpenForm "frmSearchCompanyID"
DoCmd.Close acForm, "frmFindACompany"
End If

Both codes are running at the same time.

 

[PHV]

You don't need both event procedures !
Get rid of the KeyDown procedure and use this AfterUpdate:

Private Sub txtFindWhat_AfterUpdate()
Dim strWhere As String
On Error GoTo ErrorHandler
  If Trim(Me!txtFindWhat & "") = "" Then
    DoCmd.OpenForm "frmSearchCompanyID"
    DoCmd.Close acForm, "frmFindACompany"
    Exit Sub
  End If

  Select Case Me!lstFindField.Value
    Case 1:                                     ' Company ID
      strWhere = "CompanyID =" & Me.txtFindWhat
      If DCount("*", "Company", strWhere) > 0 Then
        DoCmd.OpenForm "fmainCompany", , , strWhere
      Else
        DoCmd.OpenForm "frmSearchCompanyID"
      End If
      DoCmd.Close acForm, "frmFindACompany"
    Case 2:
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lmcc007]

I tried that several times and does nothing. I just tried it again. When I hit the Enter key, it does nothing. If I hit "Space Bar" and then "Enter" then it opens frmSearchCompanyID and closes frmFindACompany.

For some reason the below works:

Private Sub txtFindWhat_KeyDown(KeyCode As Integer, Shift As Integer)

Select Case Me.lstFindField.Value
Case 1: ' Company ID
If IsNull(Me.txtFindWhat) Then
If KeyCode = vbKeyReturn Then
DoCmd.OpenForm "frmSearchCompanyID"
DoCmd.Close acForm, "frmFindACompany"
End If
End If
Case 2: ' Company Name
If IsNull(Me.txtFindWhat) Or Trim(Me!txtFindWhat & " ") = "" Then
If KeyCode = vbKeyReturn Then
DoCmd.OpenForm "frmSearchCompanyName"
DoCmd.Close acForm, "frmFindACompany"
End If
End If
Case 3: ' Street Number
If IsNull(Me.txtFindWhat) Or Trim(Me!txtFindWhat & " ") = "" Then
If KeyCode = vbKeyReturn Then
DoCmd.OpenForm "frmSearchAddress"
DoCmd.Close acForm, "frmFindACompany"
End If
End If
Case 4: ' Company Phone
If IsNull(Me.txtFindWhat) Or Trim(Me!txtFindWhat & " ") = "" Then
If KeyCode = vbKeyReturn Then
DoCmd.OpenForm "frmSearchPhoneNumber"
DoCmd.Close acForm, "frmFindACompany"
End If
End If
Case 5: ' Zip Code
If IsNull(Me.txtFindWhat) Or Trim(Me!txtFindWhat & " ") = "" Then
If KeyCode = vbKeyReturn Then
DoCmd.OpenForm "frmSearchZipCode"
DoCmd.Close acForm, "frmFindACompany"
End If
End If
Case Else
MsgBox "Invalid selection", vbExclamation
Exit Sub
End Select

End Sub


The only problem is when I enter some data in txtFindWhat box the AfterUpdate event runs (like it should) as well as the On Key Down event (which it should not because txtFindWhat has some data).