[RESOLVED] Access 2003 - Binding A TextBox To Fields In A Query

[Hack]

I have three queries stored in an Access Database.

Each query is completey identical with respect to the SELECT clause. It is selecting all fields in a table, which I list out individually. The only difference in the three queries is the WHERE clause. There are three records in the table on which these queries are based. Each record has 24 entries. The first entry indicates the record type (in this case, type equals either "Family" "Peds" or "Adult")

On a form I have three sets of 23 textboxes, for a total of 69. Each set of 23 textboxes should display the values in the fields resulting from the field in the query to which the textbox is bound. For example the 23 textboxes bound to the "Family" query will display the values for each field in the record where type = 'Family'; the 23 textboxes bound to the "Peds" query will display the values for each field in the record where type = 'Peds'; the 23 textboxes bound to the "Adult" query will display the values for each field in the record where type = 'Adult';

The fields in the three separate queries are identical.

In the control source for the 23 textboxes on the Family group I have =[gryFamily!FieldName]
In the control source for the 23 textboxes on the Peds group I have =[gryPeds!FieldName]
In the control source for the 23 textboxes on the Adult group I have =[gryAdult!FieldName]

Everything keeps coming up with #Name?

Incidentially, in this scenerio "Group" means nothing more than a set of textboxes all lined up together. They are not programmatically related in any way other than being bound to the same query, but different field in that query.

I'm redoing an existing form. On the existing form there is a data control which moves between the three records and flawlessly display the proper values. Management does not want to have to do this. They want the values for all three "types" displayed at the same time when the form opens. Thats what I'm trying to do. I've never done anything like this with Access before, so I don't know why I'm getting the #Name? thing for two of my sets of textboxes. I done a fair amount of Googling and have found things mentining the #Name thing, but I'm still not sure what I'm doing wrong here. From what I've read I am, theoretically anyway, doing what I'm supposed to be doing.

Screen shots representing one field in each of the three queries.

[RobDog888]

Just the basic stuff to go over.

The querries run properly in the query designer?
Any ambiguous field names or names with invalid characters?
All bound fields return "#Name"?
Data type mismatches?

[Hack]

The querries run properly in the query designer?

Yes

Any ambiguous field names or names with invalid characters?

The field names in all three queries are identical, but not ambigous and do not contain invalid characters. Here are the queries

]'qryAdult
SELECT PCMH_Capabilities, EBCR_Adult, EBCR_Peds, Preventive_Adult, Preventive_Peds, 
PCS_ED_Use_Adult, PCS_ED_Use_Peds, GDR_Rate_Adult, GDR_Trend_Adult, GDR_Rate_Peds,
GDR_Trend_Peds, Low_Tech_Adult, Low_Tech_Peds, High_Tech_Adult, Number_of_PU,
PCMH_Points_Imp_CapAdult, PCMH_Points_Imp_Cap_Peds, Small_n_Members, 
Require_3_Metrics, Number_of_PUs, Required_Total_Score, Required_PU_Percentile, 
Required_Num_Members, PU_Eligiblity_Threshold
FROM tbl_Variables
WHERE pu_type = 'Adult';

'qryPeds
SELECT PCMH_Capabilities, EBCR_Adult, EBCR_Peds, Preventive_Adult, Preventive_Peds, 
PCS_ED_Use_Adult, PCS_ED_Use_Peds, GDR_Rate_Adult, GDR_Trend_Adult, GDR_Rate_Peds,
GDR_Trend_Peds, Low_Tech_Adult, Low_Tech_Peds, High_Tech_Adult, Number_of_PU,
PCMH_Points_Imp_CapAdult, PCMH_Points_Imp_Cap_Peds, Small_n_Members, 
Require_3_Metrics, Number_of_PUs, Required_Total_Score, Required_PU_Percentile, 
Required_Num_Members, PU_Eligiblity_Threshold
FROM tbl_Variables
WHERE pu_type = 'Peds';

'qryFamily
SELECT PCMH_Capabilities, EBCR_Adult, EBCR_Peds, Preventive_Adult, Preventive_Peds, 
PCS_ED_Use_Adult, PCS_ED_Use_Peds, GDR_Rate_Adult, GDR_Trend_Adult, GDR_Rate_Peds,
GDR_Trend_Peds, Low_Tech_Adult, Low_Tech_Peds, High_Tech_Adult, Number_of_PU,
PCMH_Points_Imp_CapAdult, PCMH_Points_Imp_Cap_Peds, Small_n_Members, 
Require_3_Metrics, Number_of_PUs, Required_Total_Score, Required_PU_Percentile, 
Required_Num_Members, PU_Eligiblity_Threshold
FROM tbl_Variables
WHERE pu_type = 'Family';

.

All bound fields return "#Name"?

Yes

Data type mismatches?

No

[RobDog888]

Ok got a few more ideas...

1. The textboxes can not find the fields they are bound to which is what the "#Name" error means. Double check.
2. What is the Forms SourceControl? A SQL statement?
3. If so then does that SQL statement contain the tables and/or fields being referenced in your textboxe's SQL statements?

[Hack]

The form did have the table that all three queries were running SELECT statements on as a control source. I thought that might be a problem, so I removed it. At the moment, there is no control source for the form itself.

I'm wondering if instead of putting all the textboxs on the form if I should split them up and put the textboxes for one query on the main form and then the textboxes for the other two queries on individual subforms. The only drawback to that I can see right now is that I've never ever done anything with a subform.

But, if that might get it to work....

[RobDog888]

Oh the textboxes are on a subform?

Create a secondary
"test" form and place a couple textboxes on it and bind them to just one query. If it works then add more textboxes until one "set" is working. then add a couple more textboxes that will be bound to the second query and so forth.

This should help identify what and where the issue may be

[Hack]

No, they are not on a subform. My thought was to move them to a subform (actually two subforms) and have a form with one set of textboxes on it, then a subform with another set, and then a second subform with another set.

I don't know how to do that...but I found a subform tutorial that I'm playing around with.

I have no idea if this will fix the problem, but I figure it is worth a shot.

[RobDog888]

I was thinking that a test form may help if there is some issue with the one you have built up so far.

I have a small subform faq in my OD FAQ sticky thread in the OD forum
I would try to just get it working first on a single standard form and go from there.

[Hack]

I would try to just get it working first on a single standard form and go from there.

I'm not exactly sure what you mean by this...if I could get it working on a single form, I wouldn't need to go or do anything else.

In fact the form I'm using at the moment is brand new (it is still called Form1). Once I get it working then I need to get it working on the real form (or, I guess I could just delete the existing form an rename Form1).

[RobDog888]

Ok, I just dont know what your form looks like or how you have it designed and associated code

You can still place just a couple textboxes on it and the single query to try to get that working. Set the forms controlsource too

[Hack]

Ok...what would the form's control source be? The table I'm running the three queries against?

[RobDog888]

Yea try it

[Hack]

Ok...that worked. I now have textboxes bound to one of the queries and I'm getting the numbers from the table.

The next question is how to I add the other two queries to the same form?

[RobDog888]

Looking at your querries I would try to use just one. I believe there is a Filter property you should be able to use to separate out the three on each textbox

[Hack]

The only filter property there is for a textbox is "Filter Lookup" - there are 3 choices: Never, Database Default, Always

I don't see where this does me any good.

What is the ADO equivalent of: Set db = Access.Application.CurrentDb?

My tact now is to use unbound textboxes and create recordsets out of the three queries and load my textboxs that way. I haven't used DAO in 12 or 13 years and have never used it in VBA so I'm not sure how to create a connection object to a database within which the connection code is running.

[Hack]

I added a new form, using the form wizard, and did not stipulate a control source.

On the next wizard screen I selected, one at a time, all three queries. After each selection, I moved all fields over to the "Selected Fields" listbox. It let me do that, and prefaced each field name with the name of the query. For instance, I have qryAdult.PCMH_Capabilities, qryPeds.PCMH_Capabilities and qryFamily.PCMH_Capabilities, and so on for all 23 fields for each query.

When I hit the "Next" button, I got this

You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from on the table or only the query.

Well Dah! I have chosen fields from only the query...I have simply chosen three queries to be displayed on the same form.

How can an Access Database Wizard not connect to a record source that is located within itself?

I'm beginning to think Access is not going to let me do this.

[RobDog888]

Just think of VBA as VB6 and create your ADO/DAO connection as you would normally would.
IMO its much easier to do the coding yourself vs the wizard and bound controls.

[Hack]

Well, I was trying something like that but I get an error when I try to open the database. This application is going to be emailed out, with the access database as an attachment, to about 500 doctors offices around the state. I have no earthly clue where they are going to put it on the machine they decide to put on. I've tried

Dim ADOCn As ADODB.Connection
Set ADOCn = New ADODB.Connection
ADOCn.Provider = "Microsoft.Jet.OLEDB.4.0"
ADOCn.Open Access.Application.CurrentDb

But I get type mismatch on ADOCn.Open

So, I switched it out to

Dim ADOCn As ADODB.Connection
Set ADOCn = New ADODB.Connection
ADOCn.Provider = "Microsoft.Jet.OLEDB.4.0"
ADOCn.Open CurrentProject.FullName

CurrentProject.Fullname gave me an interesting error.

So, how to I open the darn thing from within itself (which, frankly, seems a little silly to begin with)

[RobDog888]

I would use the DAO enginge since you can not guarentee that a machine will have ADO on it but DAO is native for Access.

Option Compare Database

Public Function ConnectToRS() As DAO.Recordset
    Dim oCnn As DAO.Connection
    Set ConnectToRS = CurrentDb.OpenRecordset("qryMyQuery", dbOpenDynamic, dbFailOnError, dbOptimistic)
End Function

[Hack]

DAO?????? Noooooooooooooooooooooooooooo! NOT DAOooooooooooooooo...

Oh alright...if I must

[RobDog888]

Well you "could" create a setup installation package so ADO gets deployed and then you can be safe to use it accross machines. But noooooooooo, someone doesnt want to use one lol jk

[Hack]

So I'm breaking down and using DAO, and now I'm being told that the control has to have focus before I can use a property of method of it. What is that? Here is the code

Private Sub LoadFamily()
Dim rst As Recordset
Dim db As DAO.Database

Set db = Access.Application.CurrentDb
Set rst = db.OpenRecordset("qryFamily")
'add recordset for captotalfactor variables also check for new values
Set db = CurrentDb()
txtPCMH_Capabilities_Family.Text = rst.Fields("pcmh_capabilities")
txtEBCR_Adult_Family.Text = rst.Fields("ebcr_adult")
txtEBCR_Peds_Family.Text = rst.Fields("ebcr_peds")
txtPreventive_Adult_Family = rst.Fields("preventive_adult")
txtPreventive_Peds_Family.Text = rst.Fields("preventive_peds")
txtPCS_ED_Use_Adult_Family.Text = rst.Fields("pcs_ed_use_adult")
txtPCS_ED_Use_Peds_Family = rst.Fields("pcs_ed_use_peds")
End Sub

Here is the error...how would I populate these textboxes with the result of the query?

[Hack]

Ok...I fixed it by doing this

txtPCMH_Capabilities_Family.SetFocus
txtPCMH_Capabilities_Family.Text = rst.Fields("pcmh_capabilities")
txtEBCR_Adult_Family.SetFocus
txtEBCR_Adult_Family.Text = rst.Fields("ebcr_adult")
txtEBCR_Peds_Family.SetFocus
txtEBCR_Peds_Family.Text = rst.Fields("ebcr_peds")
txtPreventive_Adult_Family.SetFocus
txtPreventive_Adult_Family = rst.Fields("preventive_adult")
txtPreventive_Peds_Family.SetFocus
txtPreventive_Peds_Family.Text = rst.Fields("preventive_peds")
txtPCS_ED_Use_Adult_Family.SetFocus
txtPCS_ED_Use_Adult_Family.Text = rst.Fields("pcs_ed_use_adult")
txtPCS_ED_Use_Peds_Family.SetFocus
txtPCS_ED_Use_Peds_Family = rst.Fields("pcs_ed_use_peds")

I have always been a big believer in "if it works then its right", but this is stretching even my liberal coding philosophy. There HAS to be a better way.

[Hack]

Some suggested using subforms (which I've never used) and put one query on the form, and then the other two on their own, individual, subforms. So, I'm gonna give that a shot.

[RobDog888]

I belive you need to use the proper form event to set the fields to your dao rs fields. Cant remember what that is right now at work but if I can find my old faq db it should have it in there already.

[Hack]

I don't suppose you happen to have an FAQ on how to create a subform or two do you?

[Hack]

Never mind...I figured it out. Turns out I didn't have the Subform/Subreport button on my toolbar.

But despair not...I have a ton more Access questions to ask in, as yet, unborn threads.

[WKevinDavis]

A Form or a Subform needs its own query as its source. The easy solution for this issue would have been to either go with your subform idea, or put all three queries into a single union query that you could have used as the forms control source.