access_student
New member
- Local time
- Today, 08:18
- Joined
- Sep 11, 2014
- Messages
- 1
Hello beautiful people of the Access Community!
I have a question pertaining to concept of a query.
First, for purposes of context, I will be referring to MS Access 2007 as the platform software I am using.
The question is, how do I change each of the parameters that show up in a column within the design view using the VBA component of Access? By that I mean, if I was to normally create a query, I would first go to query design mode, select the tables I wanted, then fill in the appropriate parameters I needed to get me the information I wanted, and then run the query. When I say parameters, I am referring to the list on the lower left that display the words, - "field:", "table:"," sort:", "show:", "criteria:", and "or:".
Now having said that, the first part of the question is, how do I, using VBA, change any of those parameters. For instance, if I wanted to create an on/off button on a form, how could I go about attaching the code to the "show:" button in the design view grid to uncheck and check that box. I can create the button in the form section, and open/run the query, but I do not know how to link it the show button. The reason for the question is because I want to give a user the flexibility in the future for determining what columns to look at based on form base input. So, for example, if I have a table called "Cars" with 5 columns of information in, all numbers, with headers name file1, file2, file3, file4, and file5, I want the user (in the form section, through a control mechanics), to choice which of those five columns to see when the query is run. Essentially, allowing the user to pick and choice before the query is run. Can this be done? if so how?
The second part of the question relates to the same area of conversation mention above, the focus being on the field and table parameters. Below in my search for an solution to my question, I found a little vb code that comes close to what I am looking for, but what I don't know pertains to passing or referencing other values. By that I mean, normally if I run this code it will spit out in a query called query1, with 5 columns of information; which is great. Now another way I could run it is if I go into the vba code and change one of those fields or table parameters manually before running the code, then run the code, and it does what I wanted it to, run 5 columns of information; also great.
Change the trains field to a bicycle field and I am good to go.
But what I am currently unable to figure out is how do I assign a numeric or nonnumeric value to a field or table. For instance, if I wanted to assign a different column of information from a table to a slot within the "SELECT' or "FROM' section, how would I do that? For example, let use the first slot in the "SELECT" section which is called "planes", if I wanted to give a user through a form and a text input box an opportunity to change that field within the "SELECT" section to, let say "Cars" (assuming that there was a column in a table somewhere) without going into the vb section and changing it, how would that be done?
Current working code I was referring to above:
Private Sub Transport_Click()
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("query1")'qryTest.SQL = "SELECT Planes, Trains, [sheet1].[1], [sheet2].[3], [sheet1].[1] + [sheet2].[3] as Total FROM sheet1;"
DoCmd.OpenQuery "query1"
End Sub
Current working premise:
Private Sub Transport_Click()
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Set dbsCurrent = CurrentDb
Dim vehicle as a string
Vehicle = {input from text box} ** could be planes, trains, cars or whatever viable field**
Set qryTest = dbsCurrent.QueryDefs("query1")'qryTest.SQL = "SELECT Vehicle , Trains, [sheet1].[1], [sheet2].[3], [sheet1].[1] + [sheet2].[3] as Total FROM sheet1;"
DoCmd.OpenQuery "query1"
From my working theory, you can see what I am trying to do. I am changing the planes slot to a generic vehicle naming variable and I am trying to get that variable to equal whatever is inputted in a textbox somewhere else on a form.
Any help on this would greatly be appreciated!
Thank you
v/r
AS
I have a question pertaining to concept of a query.
First, for purposes of context, I will be referring to MS Access 2007 as the platform software I am using.
The question is, how do I change each of the parameters that show up in a column within the design view using the VBA component of Access? By that I mean, if I was to normally create a query, I would first go to query design mode, select the tables I wanted, then fill in the appropriate parameters I needed to get me the information I wanted, and then run the query. When I say parameters, I am referring to the list on the lower left that display the words, - "field:", "table:"," sort:", "show:", "criteria:", and "or:".
Now having said that, the first part of the question is, how do I, using VBA, change any of those parameters. For instance, if I wanted to create an on/off button on a form, how could I go about attaching the code to the "show:" button in the design view grid to uncheck and check that box. I can create the button in the form section, and open/run the query, but I do not know how to link it the show button. The reason for the question is because I want to give a user the flexibility in the future for determining what columns to look at based on form base input. So, for example, if I have a table called "Cars" with 5 columns of information in, all numbers, with headers name file1, file2, file3, file4, and file5, I want the user (in the form section, through a control mechanics), to choice which of those five columns to see when the query is run. Essentially, allowing the user to pick and choice before the query is run. Can this be done? if so how?
The second part of the question relates to the same area of conversation mention above, the focus being on the field and table parameters. Below in my search for an solution to my question, I found a little vb code that comes close to what I am looking for, but what I don't know pertains to passing or referencing other values. By that I mean, normally if I run this code it will spit out in a query called query1, with 5 columns of information; which is great. Now another way I could run it is if I go into the vba code and change one of those fields or table parameters manually before running the code, then run the code, and it does what I wanted it to, run 5 columns of information; also great.
Change the trains field to a bicycle field and I am good to go.
But what I am currently unable to figure out is how do I assign a numeric or nonnumeric value to a field or table. For instance, if I wanted to assign a different column of information from a table to a slot within the "SELECT' or "FROM' section, how would I do that? For example, let use the first slot in the "SELECT" section which is called "planes", if I wanted to give a user through a form and a text input box an opportunity to change that field within the "SELECT" section to, let say "Cars" (assuming that there was a column in a table somewhere) without going into the vb section and changing it, how would that be done?
Current working code I was referring to above:
Private Sub Transport_Click()
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("query1")'qryTest.SQL = "SELECT Planes, Trains, [sheet1].[1], [sheet2].[3], [sheet1].[1] + [sheet2].[3] as Total FROM sheet1;"
DoCmd.OpenQuery "query1"
End Sub
Current working premise:
Private Sub Transport_Click()
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Set dbsCurrent = CurrentDb
Dim vehicle as a string
Vehicle = {input from text box} ** could be planes, trains, cars or whatever viable field**
Set qryTest = dbsCurrent.QueryDefs("query1")'qryTest.SQL = "SELECT Vehicle , Trains, [sheet1].[1], [sheet2].[3], [sheet1].[1] + [sheet2].[3] as Total FROM sheet1;"
DoCmd.OpenQuery "query1"
From my working theory, you can see what I am trying to do. I am changing the planes slot to a generic vehicle naming variable and I am trying to get that variable to equal whatever is inputted in a textbox somewhere else on a form.
Any help on this would greatly be appreciated!
Thank you
v/r
AS
