INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Blank form with bound controls on load

Blank form with bound controls on load

(OP)
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?

RE: Blank form with bound controls on load

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

RE: Blank form with bound controls on load

(OP)
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

RE: Blank form with bound controls on load

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).

CODE --> vba

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

RE: Blank form with bound controls on load

(OP)
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.

RE: Blank form with bound controls on load

(OP)
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?

RE: Blank form with bound controls on load

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 winky smile

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

RE: Blank form with bound controls on load

(OP)
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.

RE: Blank form with bound controls on load

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

RE: Blank form with bound controls on load

(OP)
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.

RE: Blank form with bound controls on load

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

RE: Blank form with bound controls on load

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.

CODE -->

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.

RE: Blank form with bound controls on load

(OP)
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.

RE: Blank form with bound controls on load

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close