By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,367 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Add a forms label to a query

100+
P: 434
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.
Sep 12 '12 #1

✓ answered by 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

Share this Question
Share on Google+
11 Replies


zmbd
Expert Mod 5K+
P: 5,287
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
Sep 12 '12 #2

100+
P: 434
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.
Sep 12 '12 #3

zmbd
Expert Mod 5K+
P: 5,287
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
Sep 12 '12 #4

100+
P: 434
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.
Sep 13 '12 #5

Rabbit
Expert Mod 10K+
P: 12,315
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT PK, [Forms]![FormName]![LabelName].[Caption] AS LabelCaption
  2. FROM someTable
With that, if my label caption is Name: and I have 3 records in the table, my results would be:
Expand|Select|Wrap|Line Numbers
  1. 1 Name:
  2. 2 Name:
  3. 3 Name:
Sep 13 '12 #6

zmbd
Expert Mod 5K+
P: 5,287
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT PK, 
  2.    [Forms]![FormName]![TextBoxName].[Value] 
  3.   AS TextBoxValue 
  4. FROM someTable 
-z
Attached Images
File Type: jpg byte_frm_example.jpg (114.1 KB, 1461 views)
Sep 13 '12 #7

100+
P: 434
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
Expand|Select|Wrap|Line Numbers
  1. (A cut and paste may not work on this )
  2. Set ExtractParm = db.QueryDefs("Extractparm")
  3. ExtractParm.SQL = 
  4.    "SELECT [forms]![Params]![Match4].[Caption]
  5.    as EventName, 
  6.    Params.Club_name, Params.RegionalName, 
  7.    Params.WildbunchStages, Params.Wildbunchdate,
  8.    Params.RankPointsWB, Params.TotalTimeWB,
  9.    Params.RankbyClassWB, Params.WBShotPoint, 
  10.    Params.MisseTime, Params.PenaltiesTime,    
  11.    Params.BonusstageWB1, Params.BonusstageWB2," & _
  12.    "Params.BonusstageWB3, Params.BonusstageWB4, 
  13.    Params.BonusstageWB5, Params.BonusstageWB6,    
  14.    Params.BonusstageWB7, Params.BonusstageWB8, 
  15.    Params.BonusstageWB9, Params.BonusstageWB10, 
  16.    Params.BonusstageWB11, Params.BonusstageWB12," & _
  17.    "Params.MatchDQTotal, Params.StageDQTotal,
  18.    Params.DNFTotal, Params.StageDQTargets,
  19.    Params.TargetPlus 
  20. FROM Params"
When I run the query it ask for a value to enter in the forms field.
Sep 13 '12 #8

zmbd
Expert Mod 5K+
P: 5,287
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
Sep 14 '12 #9

100+
P: 434
I guess I'll try and change those labels to textboxs and give that a try. Thanks for you advice and help.
Sep 14 '12 #10

100+
P: 434
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.
Sep 14 '12 #11

zmbd
Expert Mod 5K+
P: 5,287
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

Expand|Select|Wrap|Line Numbers
  1. 'This code is written from memory, it has not
  2. 'been checked for typos against the complier
  3. 'sorry, not at a PC with Access or VB installed
  4. '
  5. Dim zdb As DAO.Database, zqrydef As DAO.QueryDef
  6. Dim zsql as string
  7. Dim zuserinput1 AS string, zuserinput2 AS string 
  8. '
  9. 'setup for the database
  10. Set zdb = CurrentDB
  11. '
  12. 'check to see if the query exsists
  13. 'qry_example
  14. 'and if so then remove it from the collection
  15. For each zqrydef in zdb.querydefs
  16.    '
  17.    if lcase(zqrydef.name)="qry_example" then
  18.       zqrydef.delete "qry_example"
  19.    end if
  20. next zqrydef
  21. '
  22. 'now build the query for the selected fields:
  23. zuserinput1 = me.txtclubname.value
  24. 'example user entered userclubname
  25. '
  26. zuserinput2 = me.txtregionalname.value
  27. 'example user entered userregionalname
  28. '
  29. 'very simple select query, no wheres no whys
  30. 'just get the two fields from the table and
  31. 'show all records within the table tbl_example1
  32. '
  33. 'NOTE the space appended in string!
  34. zsql = "Select " & zuserinput1 & "," & _
  35.        "zuserinput2 & " " & _
  36.        "FROM tbl_example1;"
  37. '
  38. 'Using our example this string should evaluate to:
  39. 'Select userclubname,
  40. '   userregionalname
  41. 'From tbl_example1;
  42.  
  43. 'create the query (see why we searched first?)
  44. 'if you try to create a query with the same name
  45. 'access will have a tempertantrum
  46. '
  47. Set zqrydef = zdb.CreateQueryDef("qry_example", zsql)
  48. '
  49. 'That's it... you can add code to open the query
  50. 'the user can open the query from the navigation pane
  51. 'you can use the query in the transfersheet method, etc..
  52. '
  53. 'Clean UP - - Always a cleanup step
  54. zdb.close
  55. set zdb = nothing
  56. set zqrydef = nothing
  57. 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:
Expand|Select|Wrap|Line Numbers
  1. 'This code is written from memory, it has not
  2. 'been checked for typos against the complier
  3. 'sorry, not at a PC with Access or VB installed
  4. '
  5. Dim zdb As DAO.Database, 
  6. Dim zrs As DAO.recordset '<<<notice
  7. Dim zsql as string
  8. Dim zuserinput1 AS string, zuserinput2 AS string 
  9. '
  10. 'setup for the database
  11. Set zdb = CurrentDB
  12. '
  13. 'now build the query for the selected fields:
  14. zuserinput1 = me.txtclubname.value
  15. 'example user entered userclubname
  16. '
  17. zuserinput2 = me.txtregionalname.value
  18. 'example user entered userregionalname
  19. '
  20. 'very simple select query, no wheres no whys
  21. 'just get the two fields from the table and
  22. 'show all records within the table tbl_example2
  23. '
  24. 'NOTE the space appended in string!
  25. zsql = "Select * " & zuserinput1 & "," & _
  26.        "FROM tbl_example2 " & _
  27.        "WHERE(([clubnames] = '"  & zuserinput1 & "')" & _
  28.        "AND ([regionalnames] = '" & zuserinput2 & "'));"
  29. 'Using our example this string should evaluate to:
  30. 'Select *
  31. 'From tbl_example2
  32. 'WHERE (([clubnames] = 'userclubname') AND
  33. '(regionalnames] = 'userregionalname'));
  34. '
  35. 'Now lets open a record set based on the sql string:
  36. set zrs = zbd.openrecordset(zsql,dbOpenDynaset)
  37. '
  38. 'That's it... you can add code to pull information out
  39. 'of the record set for display on the forms or whatever
  40. 'you desire (well... almost)
  41. '
  42. 'Clean UP - - Always a cleanup step
  43. zdb.close
  44. set zdb = nothing
  45. set zrs = nothing
  46. 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
Sep 14 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.