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.

[CD Tom]

Ok, I changed those labels to textboxs and change the querydefs to .[value] but I get the same thing. I think maybe I need to come up with some other way to get this done.
Thanks for all your help.

[zmbd]

CD Tom,
Don't give up just yet.
Let's look at the two textboxes Params.Club_name, Params.RegionalName for ease let's call them txtClubname and txtRegionalName.

When the user enters the information into these two boxes so that [textclubname]=userclubname and [txtRegionalName]=userregionalname let's determine what you want to do...

Somewhere we have to have a table to work with...

Now this table either has to have fields in it named "userclubname" and "userregionalname" or you're trying to gather records where the information in the table field meets that requirement.

Case one:
tbl_example1
[PK] autonumber - primary key
[userclubname] - text(50) required, no duplicate
[userregionalname] - text(50) required, no duplicate

'This code is written from memory, it has not
'been checked for typos against the complier
'sorry, not at a PC with Access or VB installed
'
Dim zdb As DAO.Database, zqrydef As DAO.QueryDef
Dim zsql as string
Dim zuserinput1 AS string, zuserinput2 AS string 
'
'setup for the database
Set zdb = CurrentDB
'
'check to see if the query exsists
'qry_example
'and if so then remove it from the collection
For each zqrydef in zdb.querydefs
   '
   if lcase(zqrydef.name)="qry_example" then
      zqrydef.delete "qry_example"
   end if
next zqrydef
'
'now build the query for the selected fields:
zuserinput1 = me.txtclubname.value
'example user entered userclubname
'
zuserinput2 = me.txtregionalname.value
'example user entered userregionalname
'
'very simple select query, no wheres no whys
'just get the two fields from the table and
'show all records within the table tbl_example1
'
'NOTE the space appended in string!
zsql = "Select " & zuserinput1 & "," & _
       "zuserinput2 & " " & _
       "FROM tbl_example1;"
'
'Using our example this string should evaluate to:
'Select userclubname,
'   userregionalname
'From tbl_example1;
 
'create the query (see why we searched first?)
'if you try to create a query with the same name
'access will have a tempertantrum
'
Set zqrydef = zdb.CreateQueryDef("qry_example", zsql)
'
'That's it... you can add code to open the query
'the user can open the query from the navigation pane
'you can use the query in the transfersheet method, etc..
'
'Clean UP - - Always a cleanup step
zdb.close
set zdb = nothing
set zqrydef = nothing
end sub

Case two:
tbl_example2
[PK] autonumber - primary key
[clubnames] - text(50) required, no duplicate
[regionalnames] - text(50) required, no duplicate

We're going to repeat some of the above code:

'This code is written from memory, it has not
'been checked for typos against the complier
'sorry, not at a PC with Access or VB installed
'
Dim zdb As DAO.Database, 
Dim zrs As DAO.recordset '<<<notice
Dim zsql as string
Dim zuserinput1 AS string, zuserinput2 AS string 
'
'setup for the database
Set zdb = CurrentDB
'
'now build the query for the selected fields:
zuserinput1 = me.txtclubname.value
'example user entered userclubname
'
zuserinput2 = me.txtregionalname.value
'example user entered userregionalname
'
'very simple select query, no wheres no whys
'just get the two fields from the table and
'show all records within the table tbl_example2
'
'NOTE the space appended in string!
zsql = "Select * " & zuserinput1 & "," & _
       "FROM tbl_example2 " & _
       "WHERE(([clubnames] = '"  & zuserinput1 & "')" & _
       "AND ([regionalnames] = '" & zuserinput2 & "'));"
'Using our example this string should evaluate to:
'Select *
'From tbl_example2
'WHERE (([clubnames] = 'userclubname') AND
'(regionalnames] = 'userregionalname'));
'
'Now lets open a record set based on the sql string:
set zrs = zbd.openrecordset(zsql,dbOpenDynaset)
'
'That's it... you can add code to pull information out
'of the record set for display on the forms or whatever
'you desire (well... almost)
'
'Clean UP - - Always a cleanup step
zdb.close
set zdb = nothing
set zrs = nothing
end sub

As I said, I pulled all of the code for the two blocks out of my head... amazing how lazy I've gotten with the vba editor window... so there might be a syntax error.
-
z