Blank form with bound controls on load

[MissionCreep]

I have a form with unbound text boxes and a search button at the top, and then the rest of the form has bound controls. The problem is that the query (selecting all records) that is the recordsource of the form has gotten big so that the form takes a lot of time to load over a slow network connection and it really serves no purpose because no one is going to scroll through 50,000 records.

How do I load a blank form and then populate it after the user enters search criteria? Do I have to separate the 2 parts of the form into 2 forms or do I bind the controls in code when the search button is clicked or is there some other way?

 

[dhookom]

What is the code behind your search button? Are you applying a filter or altering the SQL statement in the form's record source? I typically use altering the record source so I can save the form with a SQL statement that doesn't return any records. When the button is clicked, the SQL statement is built and applied to the form's record source.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MissionCreep]

I'm altering the SQL statement in the form's record source. I can change the SQL statement so that no records are returned on form load. The bound controls are hidden, which is fine. But then the unbound textboxes with the search criteria don't work. I get the "you can't reference a property or method for a control unless the control has the focus" message, e.g.

Private Sub txtSearchLastName_AfterUpdate()
SearchLastName = txtSearchLastName.Text
End Sub

 

[dhookom]

You rarely use the Text property of a control in Access VBA. 99% of the time use the Value property (or no property since Value is the default).

Private Sub txtSearchLastName_AfterUpdate()
   SearchLastName = Me.txtSearchLastName
End Sub

If that doesn't work, we need more information about the code in your form.

Also, please use the TGML code tag when posting code. It's much easier to read.

BTW: Welcome to Tek-Tips!

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MissionCreep]

OK, that works. I had to add some code for the instance where the textbox is null, because I'm using a string variable.

Thanks.

 

[MissionCreep]

What do you do when you need the text property, for example you want to enable a button if there is text in the textbox and disable the button if the textbox is blank?

 

[dhookom]

You still don't use the Text property. In Access you will almost always use the Value property which you can omit for all bound controls. The Text property is used when the control actually has the focus. You might have code that capture each keystroke and you can't wait until the user has left the control.

I know other forms outside of Access use the Text property so it can be confusing ;-)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MissionCreep]

I found a way to work around this. If you're using a textbox strictly as an unbound textbox you can use the Microsoft Forms 2.0 textbox instead of the native Access textbox. Then you can reference the text property without a problem.

 

[dhookom]

So why do you want to use the Text property? Using Microsoft Forms is a huge amount of unnecessary effort. Why doesn't the value property of a standard Access text box work for you?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MissionCreep]

The value property doesn't work for me because, in my scenario, the value has not been updated yet. I want to have a search button that is enabled only when a textbox contains search criteria and disabled when the textbox is blank. Using the MS Forms textbox didn't take much effort.

 

[dhookom]

If that is the case, you should abandon the AfterUpdate event since that event fires after the control loses the focus. The Change event fires as every character is entered or the Text is changed. Since the focus is still on the text box, you can use the Text property.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

There is no real reason ever to use a MS forms textbox that I can think of. This maybe one of the rare cases where you may need to use the text property instead of the value property. Only time you need to do this is if you want to check the text in the text box while you are still in the textbox. In that case the text and value will differ, but no problem since you will already have focus and can simply call the .text property. If the control does not have focus then the value and text properties will be the same and use the value property.

Private Sub TextOne_Change()
   Me.cmdOne.Enabled = Not (Trim(Me.TextOne.Text & " ") = "")
  ' Me.cmdOne.Enabled = Not (Trim(Me.TextOne.Value & " ") = "")
End Sub

The above code works with the text property but not the value property. Also look at how I check for "null". Isnull will not work on the text property because it returns an empty string and not a null.

Most of the time we do checks at the before update because we are checking the complete entry and not a character. In that case the value and text will match.

 

[MissionCreep]

Yeah, you would think that would work, and it did work when the form's record source had records, but when I changed the record source so that it would not have any records when the form loaded, then I got the 2185 error "you can't reference a property or method for a control unless the control has the focus" message even on the change event of the control.

 

[dhookom]

I expect you were using the afterupdate event which doesn't occur until the control loses focus. As we have suggested, the Change event should work, at least it has always worked without error for me.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016