Binding text box to a query, on form bound to a table

[jpl458]

I have a data entry form that was created from table "MasterEntrytbl", and all of the textboxes have that table as source for each textbox control Source. I've add a textbox and I want to control source to the output of a query. But in the property sheet there are only the fields for the underlying table. I just scanned the web and can't find an answer.

Thanks

 

[theDBguy]

Is the query based on the same table as the form? Do you want users to update the value in this Textbox?

 

[Gasman]

Use a DLookUp() ?

 

[XPS35]

What do you want to accomplish with this? You are entering data into your table, so what do you want with that field?

 

[jpl458]

Is the query based on the same table as the form? Do you want users to update the value in this Textbox?

What do you want to accomplish with this? You are entering data into your table, so what do you want with that field?

There is first name field on the form that's a text box "contactfirstnametb". When the use tabs out of that text box I them to see, in an adjacent textbox the number of times (a count) that that first name is in the master table, and in another textbox the row numbers that the name appears in in the master file.. The form looks like this:

Say they enter Adam into the left textbox, then I need to have the number of "Adam"'s that appear in the master file. In the right most text box I want to have the auto ID numbers for the corresponding records, in other words their location. I've been reading and I think I need to use DLookup, but if you could point me in the right direction I would be thankful. The istcontact field in the master file is short text, and the 1stcontacttb is plain text.

 

[June7]

Yes, DLookup() or DCount(), depending on the query or table that is searched.

Expression in textbox ControlSource like: =DCount("*", "tableName", "1stContactName = '" & [1stContactName] & "'")

To return all the ID numbers that have "Adam" requires VBA (unless your backend is not Access but something like SQLServer or MySQL). Review http://allenbrowne.com/func-concat.html

 

[jpl458]

Yes, DLookup() or DCount(), depending on the query or table that is searched.

Expression in textbox ControlSource like: =DCount("*", "tableName", "1stContactName = '" & [1stContactName] & "'")

To return all the ID numbers that have "Adam" requires VBA (unless your backend is not Access but something like SQLServer or MySQL). Review http://allenbrowne.com/func-concat.html

This is not a big app from a data stand point. There is no back end. I figured the row numbers would require VBA, but I'm trying to knock this out one step at a time. Would what you showed require an SQL query, or would the textbox expression do the job on it's own. If not query, that is great. Thanks for the response. It was clear and to the point. Thanks again.

 

[June7]

The DCount() I show pulls from table, no query required, based on my limited understanding of what you want to accomplish. Assumes values in 1stContactName are just a first name, not a full name, and you want exact match.

 

[jpl458]

That's it in a nutshell. Once I am familiar with it I can add last plus other stuff.

Thanks again.

 

[jpl458]

The DCount() I show pulls from table, no query required, based on my limited understanding of what you want to accomplish. Assumes values in 1stContactName are just a first name, not a full name, and you want exact match.

I just noticed that in this expression =DCount("*", "tableName", "1stContactName = '" & [1stContactName] & "'") that the last 2 members are the same (1stContactName). Is the first member the name of the textbox?

 

[June7]

No, the first one is field in table. The second is field/control on form. It is concatenated as the variable input for the calculation.

I always name controls different from fields they are bound to, like tbxContact. I don't know if you have done that. Or if these are UNBOUND controls with Access-assigned names like Text3.

 

[jpl458]

No, the first one is field in table. The second is field/control on form. It is concatenated as the variable input for the calculation.

I always name controls different from fields they are bound to, like tbxContact. I don't know if you have done that. Or if these are UNBOUND controls with Access-assigned names like Text3.

That make sense. I try to make sensible names for things with what they are at the end of the name, as in '1stcontacttb'. Forms always end in frm, queries qry, etc.. BTW, what does "*" accomplish in the expression.

Really appreciate the help

 

[June7]

"*" is wildcard character. DCount does not require specifying a field in order to count records but something must be passed in the argument. However, many would use unique identifier field, commonly named ID.

 

[jpl458]

I was just trying the =DCount expression; =DCount("*", "MasterTbl3", "1stContactName = '" & [1stcounttb] & "'"). The count text box is not in the Tab List, so how do I get the expression to execute? I would think in the lost focus event of the textbox called 1stcontacttb. Is that correct?

 

[jpl458]

I put the expression in the control source of the textbox the should display the count. Here's what I have in the text box
1stcounttb, in the record source =DCount("*", "MasterTbl3", "1stContactName = '" & [1stcontacttb] & "'");
MasterTbl3 = Table Name
1stContactName = Is the field in the Table
1stcontacttb = Textbox on the form that has a name in it

Now when I go into form view the is a #Error in the text box that has the expression in the record source.

Thanks for your patience.

 

[theDBguy]

I put the expression in the control source of the textbox the should display the count. But when I go into form view I see #Type in the textbox before i enter any data. Don't know what that means, except that there is something wrong with the expression.

I think #Type is short for "Type Mismatch." Is the field "[1stcounttb]" a Number or Text field?

 

[June7]

If you want to provide db for analysis, follow instructions below.