How do I query a text box?

chuckgoss

Registered User.
Local time
Today, 01:46
Joined
Mar 26, 2001
Messages
44
Hi all,
I have a combo box that successfully queries, getting criteria from 2 unbound controls. It returns either a Null or returns only 1 record. This then becomes the grist of an If, Than, Else statement. Because I want the user to avoid using a mouse to interact with the form, I am told, and believe it a better thing, that I should requery a text box instead of a combo or list box which would eliminate the need to ‘Select’ a record.
I know to use MyTextBox.Requery in my code. Where would I run my query from? Unlike a combo box, a text box isn’t tied directly to a query. I'm guessing that the text box would be bound, but, naturally, it would be bound to my table and I don't know how to get it to show a result of a query? Would it show up as an empty box if the query returned a Null, and show data if it returned with a ‘record found’ like a combo box does when clicked?
Also, is it possible to run an If, Then, Else statement in the criteria section of a query such as: IF DCount("[APartNumber]", etc, etc,.. >0 Than X Else Y?

Thanks in advance,

chuck
 
You could run the query in your code. Instead of MyTextBox.Requery use something like:

Code:
Dim MyRS As Recordset, strSQL As String

strSQL = "YourQuery"
Set MyRS = CurrentDb.OpenRecordset(strSQL)
If MyRS.NoMatch Then
    Me.ControlName = ""
Else
    Me.ControlName = MyRS.Fields(0)
End If

Replace YourQuery with the SQL statement of your query. You may need to play with the code a bit to get it just how you want. But, this should be fairly close. Also, this is for Access 97. If you're using 2000 it'll look more like:

Code:
Dim MyRS As ADODB.Recordset, strSQL As String

Set MyRS = New ADODB.Recordset
strSQL = "YourQuery”
MyRS.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not MyRS.RecordCount > 0 Then
    Me.ControlName = ""
Else
    Me.ControlName = MyRS.Fields(0)
End If

Hope this helps.

~Abby



[This message has been edited by Abby N (edited 08-23-2001).]
 
Abby,

Thanks for your quick reply. Something went wrong. I used the code you suggested (filling in local details), and got an error:
Too few parameters. Expected 2. (Error3061)
The exact code is as follows:
Dim MyRS As Recordset, strSQL As String
strSQL = "RelocateCharityG"
Set MyRS = CurrentDb.OpenRecordset(strSQL)
If MyRS.NoMatch Then
Me.TextQuery = ""
Else
Me.TextQuery = MyRS.Fields(0)
End If

Debug on the error points to the 3rd line of code.
To be sure, I'm very much a rookie at this and may be doing something quite silly to a pro.

thanks
chuck

[This message has been edited by chuckgoss (edited 08-23-2001).]
 
It doesn't seem silly at all. It's not like this stuff is particularly intuitive. Plus, helping people to learn is one of the main reasons I enjoy helping out on this board. Anyway, your problem is in the line:

strSQL = "RelocateCharityG"

I assume that is the name of the query you'd like to use to set your text box. Rather than the query name you need the query's SQL statement. To get that, open your query in design view. From the main menu select View>SQL View.

You'll see the SQL statement in your query window. Change any quotation marks ( " ) to single quotes ( ' ). Now copy the entire statement and paste it in place of RelocateCharityG. Your line of code will look something like this when you’re done. (Though more complex I'm sure.)

strSQL = "SELECT tblYourTable.txtField FROM tblYourTable WHERE (txtField =
SomeCriteria);"

Hope this solves your problem. Good luck.

~Abby

[This message has been edited by Abby N (edited 08-24-2001).]
 
Abby,
Thanks again for your reply. I was only just able to try what you said, and I got an error (the reason for which I'm fairly certain)...
I'll give details a little later, just wanted you to know that I'm still on the trail, but that I'm being pulled away from my desk for now.

chuck
 

Users who are viewing this thread

Back
Top Bottom