Field Expression

[Kapellu2]

Hey everyone,

I have been writing a program for work and after about a month and a half I have everything working except one feature.

I was wondering if it was possible to write an expression in the field of one of the columns of a query?

I want to put something like this in the field box but cant quite get it to work:

Forms![Form Name].[ComboBox Name].

If I can get this to work, I will be able to make a report where the field of one of the boxes changes depending on what a user picks form a dropdown list on the main form.

This way the user can choose which fields show up on the report and can use the report to compare any feature between products.

Does anyone know why that would'nt work when i try it?

 

[boblarson]

Did you try it without the period at the end?

Forms![Form Name].[ComboBox Name].


should be

[Forms]![Form Name]![ComboBox Name]

if used as criteria in the query

 

[Kapellu2]

Ah sorry, just put that . to end the sentence, if that makes sense lol.

I have also tried
[Basic Program Info].Forms![Customer Info Search].VariableBox

But didnt have any luck. I am trying to place these in the field box. If i type Part Number in the field box, it will look for the field Part Number in the main table. I figured if i can get an expression that pulls the word Part Number from my form and puts it in that field box I would be able to get the same result.

 

[boblarson]

This:
[Basic Program Info].Forms![Customer Info Search].VariableBox

Is also non-existent syntax.

So, I'm a little confused on what it is you are exactly trying to do. Can you try explaining again and perhaps with some screenshots (if you do screenshots upload them here on the forum and not to some public photo sharing site as those are blocked by my work).

 

[Kapellu2]

I'm not by my main computer now to get a screenshot, but let me try to explain it a little better.

So I have a main table with about 50 different variables on it to describe a product.

I want to generate a report that prints a list of all the products. The report will display the product name, part number, and a third variable chosen by the user. It should look something like this:

Product: Product 1 Part Number: 1 User Defined Field: abc
Product: Product 2 Part Number: 2 User Defined Field: def
Product: Product 3 Part Number: 3 User Defined Field: ghi
Product: Product 4 Part Number: 4 User Defined Field: jkl
.
.
ect...

So every report will print the product name and part number of each product.

The report is opened using a command button on the main form. There is also a combo box on the main form filled with the names of different fields in the main table. So you can select fields such as color, shape, hardness, ect from this drop down menu.

So if the user picks "color" in the combo box and hit the print report button the report will look for the value color in the field of the main table. It will look like the following:

Product: Product 1 Part Number: 1 Color: Red
Product: Product 2 Part Number: 2 Color: Blue
Product: Product 3 Part Number: 3 Color: Green
Product: Product 4 Part Number: 4 Color: Yellow

or if they pick shape:

Product: Product 1 Part Number: 1 Shape: Round
Product: Product 2 Part Number: 2 Shape: Square
Product: Product 3 Part Number: 3 Shape: Triangular
Product: Product 4 Part Number: 4 Shape: Oval

Not sure if my intentions are clear or not, but hopefully this helped a little.

 

[Kapellu2]

Hmm so if i try to put an expression in the field box of the query, the table box will clear itself out. At this point im guessing there is really no way to implement a feature like this :/

 

[boblarson]

If I am understanding properly, you are looking to select a "Field Name" from a combo box and then display the report with that field name included in the report.

If that is the case, and only one of those can be selected at a time, you can use code to set your SQL string. So, if the combo has the name of the field bound to the bound column, you could use something like this:

Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim strOriginalSQL As String
 
strSQL = "Select Product, PartNumber, " & Me.ComboNameHere & _
            "FROM YourTableNameHere " & _
            "ORDER BY Product"
 
Set qdf = CurrentDb.QueryDefs("NameOfYourReportQueryHere")
 
strOriginalSQL = qdf.SQL
qdf.SQL = strSQL
 
DoCmd.OpenReport "ReportNameHere", acViewPreview
 
qdf.SQL = strOriginalSQL
qdf.Close
 
Set qdf = Nothing

So that would change the underlying query and then open the report and then change the query back to its original state.

 

[Kapellu2]

Private Sub Command154_Click()
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim strOriginalSQL As String

strSQL = "Select Product, PartNumber, " & Me.VariableBox & _
"FROM Basic Program Info" & _
"ORDER BY Product"

Set qdf = CurrentDb.QueryDefs("Variable Report")

strOriginalSQL = qdf.SQL
qdf.SQL = strSQL

DoCmd.OpenReport "Variable Report", acViewPreview

qdf.SQL = strOriginalSQL
qdf.Close

Set qdf = Nothing
End Sub

Thanks for the help, I appreciate it. I'm still a little confused as to how to apply that code though. I'm guessing I apply it to the "On Click" event on a command button on my form. Above is what I have entered so far. For the CurrentDb.QueryDefs line, do i type in database_path.queryname ?

 

[boblarson]

Private Sub Command154_Click()
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim strOriginalSQL As String

strSQL = "Select Product, PartNumber, " & Me.VariableBox & _
"FROM Basic Program Info" & _
"ORDER BY Product"

Set qdf = CurrentDb.QueryDefs("Variable Report")

strOriginalSQL = qdf.SQL
qdf.SQL = strSQL

DoCmd.OpenReport "Variable Report", acViewPreview

qdf.SQL = strOriginalSQL
qdf.Close

Set qdf = Nothing
End Sub

Thanks for the help, I appreciate it. I'm still a little confused as to how to apply that code though. I'm guessing I apply it to the "On Click" event on a command button on my form. Above is what I have entered so far.

Looks good.

For the CurrentDb.QueryDefs line, do i type in database_path.queryname ?

Nope, you leave the code CurrentDb.QueryDefs just as is and just provide the name of the query inside the parentheses in quotes. So, just like you have it if the query name is Variable Report. If the QUERY which you use for your report is named something else then you would use that name in that location.

 

[Kapellu2]

Haha well I think i'm getting close. Except now when I try to run it I get the error

Syntax error (missing operator) in query expression 'DepartmentFROM Basic Program InfoORDER BY Product'

 

[boblarson]

Haha well I think i'm getting close. Except now when I try to run it I get the error

Syntax error (missing operator) in query expression 'DepartmentFROM Basic Program InfoORDER BY Product'

That means you are missing spaces between those items. See the letters in red where there should be a space between. It looks like I missed putting those spaces in to the code.

'DepartmentFROM Basic Program InfoORDER BY Product'

So, the revised code would be:

Private Sub Command154_Click()
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim strOriginalSQL As String
 
strSQL = "Select Product, PartNumber, " & Me.VariableBox & _
[COLOR=red][B]" F[/B][/COLOR]ROM Basic Program Info" & _
[COLOR=red][B]" O[/B][/COLOR]RDER BY Product"
 
Set qdf = CurrentDb.QueryDefs("Variable Report")
 
strOriginalSQL = qdf.SQL
qdf.SQL = strSQL
 
DoCmd.OpenReport "Variable Report", acViewPreview
 
qdf.SQL = strOriginalSQL
qdf.Close
 
Set qdf = Nothing
End Sub

 

[Kapellu2]

Alright so I managed to get the command button to run error free, but it just opens a blank report. Do i have to code the report page in any way?

 

[boblarson]

Does the value of Me.VariableBox have any spaces in it?

 

[Kapellu2]

Nope, kept that one as just one word. Should the report im using be blank besides setting the query on it?

 

[boblarson]

Do you have data in there? If you still can't get it, perhaps you can upload a copy of the database (after compacting and then zipping) with bogus data, so we can take a look.

 

[Kapellu2]

Right now i have the report blank, and I have the query set as the source.

Let me see if I can understand the code. That might help me figure out what im doing wrong.



Private Sub Command154_Click()
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim strOriginalSQL As String

Selects the three field lists, Product, Customer, and whatever is in the VariableBox on the form.
strSQL = "Select Product, Customer, " & Me.VariableBox & _
Info is being pulled form the main table in the database
" FROM [Basic Program Info]" & _
" ORDER BY Product"

This tells the program what query that the report is based on so that it can pull the values from that source
Set qdf = CurrentDb.QueryDefs("Variable Query")

strOriginalSQL = qdf.SQL
qdf.SQL = strSQL

Tells the report to open
DoCmd.OpenReport "Variable Report", acViewPreview

qdf.SQL = strOriginalSQL
qdf.Close

Set qdf = Nothing
End Sub

So should I have the report blank? It looks like the code is telling the report which field values to pull from the query and display. What would cause me to just have a blank report?

I would try to upload a sample of the database but I remember I had a hard time the last time i tried to pull the files off my work server (so that I could try to work on them from home).

Thanks again for taking the time to help me, I really appreciate it.

 

[boblarson]

You almost had it.

[COLOR=#ff0000]This tells the program what query that the report is based on so that it can pull the values from that source[/COLOR]
Set qdf = CurrentDb.QueryDefs("Variable Query")
 
strOriginalSQL = qdf.SQL
[COLOR=red]This modifies the query to use the SQL we have above.  [/COLOR]
[COLOR=red]You might need to modify the SQL string up above to get all of the fields you need for your report.  [/COLOR]
[COLOR=red]I just focused on the ones you were talking about.[/COLOR]
qdf.SQL = strSQL

 

[Kapellu2]

But with the SQL string that we set up, should it pull those two values we have entered in? Ideally, should the report be printing those two values we have entered in addition to whatever field we selected in the combo box?

 

[boblarson]

Yes, it should. That's why I asked if you could upload a copy because I'd be interested to see what is happening myself since I don't have any clue right now as to why you aren't getting any data.

 

[Kapellu2]

Haha well i'll see if I can figure out a way to pull it. In the mean time ill try to play around with it a little and i'll let you know if I me and my limited access abilities can figure it out