select query inside VBA code

[kes]

Hi all,

I'm trying to create a query inside VBA code.
the problem is that my query is a select query and therefore I can't use RunSQL

I tried to work around it withbut had no luck... this is the code:

strSql = "SELECT '" & Me.number & "' ,Karin.[subject] " & "From Karin " & "WHERE '" & Me.number & "'" = done

Do you have any ideas?

 

[pbaldy]

This should help:

http://www.baldyweb.com/ImmediateWindow.htm

What are you doing with the SQL after this?

 

[kes]

I want to show the result of the query.

I tried as your link suggeted, but the query isn't finished in the picture...

I'm only using vba for this query because I want the select query to take the name of the column as in the text box. if you have an idea on how to do that in a regular query, I would like to hear it...

thanks!

 

[pbaldy]

As a value?

SELECT Forms!FormName.TextboxName As Whatever, FieldName
FROM TableName

 

[MSAccessRookie]

As long as the Form is opened, you should be able to gain access to the values in all of the Fields contained on it. Try smething like the following (Untested) Query.

SELECT [forms]![YOURFORMNAMEHERE].[Number], Karin.[subject] 
FROM Karin 
WHERE [forms]![YOURFORMNAMEHERE].[Number] = "done"

Some Items to Note:

1. Consider renaming your Field Named Number, since that can be a reserved word in SQL Processing.
2. There was no definition of what "Done" is, so that may need to be handled.

Is this what you were looking for?

-- Rookie

Looks like pbaldy was answering at the same time I was, and said nearly the same thing. Once again I come up a littleslow, but I believe that my noted advice still holds.

 

[kes]

thanks guys for your help!
I tried what you suggested, but is giving me a blank results

the code is:

SELECT Karin.subject, [Forms]![Choose_Press]![press] as Upgrades
FROM Karin
WHERE ((([Forms]![Choose_Press]![press])="done"));

not sure whats wrong...

 

[pbaldy]

Are you sure you don't want to compare a field in the table in the criteria, like:

SELECT Karin.subject, [Forms]![Choose_Press]![press] as Upgrades
FROM Karin
WHERE FieldName="done"

 

[kes]

the fields names are names of presses.
I need the user to choose a press name in the combobox and to get all the upgrades that are done on the selected press , therefore I can't use the field name and I need to use the reffer to the combo box...

 

[pbaldy]

That is not a normalized table. I would correct the design:

http://www.r937.com/Relational.html

If you want to keep it like it is, you will have to do this in VBA. You will find yourself working around that design for the rest of eternity though.

 

[kes]

thanks for the link, but this is a list from SharePoint that a lot of people are updating every day, so I can't change the design...
do you know how can I excute it in vba?

 

[gblack]

Seems to be you're putting quotes around both your numbers making them text values... is Access giving you a type mismatch?

Lets say your number was 23, and your subject was books your strSQL:
strSql = "SELECT '" & Me.number & "' ,Karin.[subject] " & "From Karin " & "WHERE '" & Me.number & "'" = done

Would look like this:
"Select '23', Books From Karin WHERE '23'"

The last part (i.e. "= done") wasn't added to the string but if you did you'd have the select statement looking like:

"Select '23', Books From Karin WHERE '23' = done"

Is that what you want?
That makes no sense to me...

 

[MSAccessRookie]

I think that part of the remaining problem lies in the fact that you are using the literal phrase "DONE" as the value to test for, while that was not what I meant. Your example provided little or no context as to how to determine that the current number was "DONE" and therefore should be selected. I assumed that you could provide the required context and make the proper substitutions.

Sorry for any confusion.

-- Rookie

 

[kes]

gblack, the textbox called number is a text field. it's contains both numbers and letters, for example: M100