Add a forms label to a query

[CD Tom]

I have a query with fields defined from a table. I have a label on a form that I would like to add to this query, the forms name is params and the label name is match4. The labels caption can be changed on the form and I want that new caption to be in the query, can this be done. Any help would be appreciated.
Thanks again for your help.

[zmbd]

If you are talking about a query that you have built from the ribbon/menu then the only way is to use the qrydef collection.
Delete the old query and then rebuild and add it to the collection.
If you are talking about an sql string that is built in VBA and then used to open a record set, then just use the field caption in your code to build the string.

-z

[CD Tom]

Thanks for the reply. I'm not sure how to add a label from a form to a query. I use the simple query design not the wizard when I usually design a query, although I do also use the sql string to build query's when things in the query need to change. I not sure how to code a form label in the sql string or when I just use the query design.

[zmbd]

OK
In order not to go into the deep end we're going to have to understand what it is you're doing. Although it is not unusual to change a label caption (I use them for user feed back) it is unusual to use the control as I suspect you are doing; thus, please provide some detail as to what you're doing with the form and some example code with an explanation of any errors. (when you post code, select it with the mouse and click the <code/> button above next to the undo/redo.

-z

[CD Tom]

Boy, let's see if I can explain this further. I really don't have any code to show you as the only thing the query does is export the data to a .txt file. But here's what I'm trying to accomplish. The Params form is used to set all the options for the program. In the params form the user can setup 5 events, this is where the label caption comes in, they can change the names of the events, this give the user a little more flexibility so instead of just a standard name they can name the event what ever they wish. The user then can export the data to the .txt file where a hand held computer will import that data. The data for the hand held computer comes from the single table but I want to have the event name also show up with that data. I probably could add some columns to the table and store the data in there but I would like to just and the caption fields to the query.
I hope this makes sense if not let me know and I'll try again.

[Rabbit]

I don't understand either why you would want to include the label caption as a field in a query. Label captions don't change and are static unless you're using code/macros to change the caption value.
But if you use this in a query def, it will pull the label caption into the query.

SELECT PK, [Forms]![FormName]![LabelName].[Caption] AS LabelCaption
FROM someTable

With that, if my label caption is Name: and I have 3 records in the table, my results would be:

1 Name:
2 Name:
3 Name:

[zmbd]

I think what we have here is a failure to communicate

:) (chuckle)
Take a look at the following picture.
I believe that you are referring to textbox controls and not labels as labels are not normally something a user can modify in form view.

So all of the "unbound" entries are controls for user interaction.

If we're talking about textbox controls then you have basically the same thing as Rabbit's just for values:

SELECT PK, 
   [Forms]![FormName]![TextBoxName].[Value] 
  AS TextBoxValue 
FROM someTable 

-z

[CD Tom]

I understand the difference between the Textbox and the Label and I do use the Label. I know this is probably not the correct way to do this but because this is a new option in the program to extract this data out to a .txt file I'm trying to make it so I don't have to make big changes to the program. I tried the do the querydef but it doesn't work here's the code to build the query

(A cut and paste may not work on this )
Set ExtractParm = db.QueryDefs("Extractparm")
ExtractParm.SQL = 
   "SELECT [forms]![Params]![Match4].[Caption]
   as EventName, 
   Params.Club_name, Params.RegionalName, 
   Params.WildbunchStages, Params.Wildbunchdate,
   Params.RankPointsWB, Params.TotalTimeWB,
   Params.RankbyClassWB, Params.WBShotPoint, 
   Params.MisseTime, Params.PenaltiesTime,    
   Params.BonusstageWB1, Params.BonusstageWB2," & _
   "Params.BonusstageWB3, Params.BonusstageWB4, 
   Params.BonusstageWB5, Params.BonusstageWB6,    
   Params.BonusstageWB7, Params.BonusstageWB8, 
   Params.BonusstageWB9, Params.BonusstageWB10, 
   Params.BonusstageWB11, Params.BonusstageWB12," & _
   "Params.MatchDQTotal, Params.StageDQTotal,
   Params.DNFTotal, Params.StageDQTargets,
   Params.TargetPlus 
FROM Params"

When I run the query it ask for a value to enter in the forms field.

[zmbd]

CD Tom,
Hopefully you got the tag line... it's a play on Cool Hand Luke and the chuckle

I really was hoping that you ment text boxes and not labels. This would have been a very straight forward way of building a query.

By using labels for a job that text boxes are designed to do, you make your coding that much more difficult and the enduser will truely dislike haveing a dozen prompts showup (and I'm guessing here...) You won't beable to intercept the responses to build the query as you want because the engine is trying to get the values to compare against data... not values to build fields.

By using the textboxes, your user opens the form, enters the text in the boxes, and the vba builds the query for them when what ever command button is pressed. You can take the values and use them in a "where" clause or use them to build the query structure.

The effort (IMHO) to try and do what I think you are attempting will be somewhat the same, actually I think more difficult, as redoing what you've already done.

-z

[CD Tom]

I guess I'll try and change those labels to textboxs and give that a try. Thanks for you advice and help.