HOW DO I Access a query in vba

[ryetee]

I want to run a SELECT command in VBA.
I had a quick research and I see you have to run a query.
I have a query now that produces the desired result.

I have a few questions,
1. how do i pass a parameter to the query.
2. how do I Stop the query from producing the datasgeet view (I don't want the user to see it but to get data from it to be used in the VBA code and onto a form).
3. How do I get the values of the field into my VBA code.

Incidentally the query should only produce 1 row if I can pass over a parameter to it (to be used in the where statement).

The sql from the query is
SELECT [Component SWAPS].JobPartID, Last([Component SWAPS].ModelNumber) AS LastOfModelNumber
FROM [Component SWAPS]
GROUP BY [Component SWAPS].JobPartID;


I want to pass to it a value for [Component SWAPS].JobPartID within VBA.
I then want to pick up the value of LastOfModelNumber
.

 

[namliam]

I will forgo the usual discussion about WHY?

I think you want something like:

dim rs as dao.recordset
dim mySQL as string
mySQL = "SELECT [Component SWAPS].JobPartID " & _
              ", Last([Component SWAPS].ModelNumber) AS LastOfModelNumber " & _ 
       " FROM [Component SWAPS] "  & _ 
       " Where JobPartID = " & YourParameter & _ 
       " GROUP BY [Component SWAPS].JobPartID; "

set rs = currentdb.openrecordset(mysql)

Me.YourControl = rs.LastOfModelNumber 

set rs = nothing

Just FYI, using last isnt always the smartest thing to do as "last" is a subjective thing
Instead you would be better off if your record has a date/time field to Order it desc by that field... properly getting the most recent entry.

 

[plog]

I think one 'why' is appropriate:

Last([Component SWAPS].ModelNumber) AS LastOfModelNumber

Why are you using 'Last' in your query? I've yet to find a use for that horrible keyword. Most likely it's not returning what you are expecting it to return.

 

[ryetee]

I will forgo the usual discussion about WHY?

I think you want something like:

dim rs as dao.recordset
dim mySQL as string
mySQL = "SELECT [Component SWAPS].JobPartID " & _
              ", Last([Component SWAPS].ModelNumber) AS LastOfModelNumber " & _ 
       " FROM [Component SWAPS] "  & _ 
       " Where JobPartID = " & YourParameter & _ 
       " GROUP BY [Component SWAPS].JobPartID; "

set rs = currentdb.openrecordset(mysql)

Me.YourControl = rs.LastOfModelNumber 

set rs = nothing

Just FYI, using last isnt always the smartest thing to do as "last" is a subjective thing
Instead you would be better off if your record has a date/time field to Order it desc by that field... properly getting the most recent entry.

I can't use select in VBA. I have a query that returns the right values but I need to know how to pass a parameter to the query and how to pick up the results from the query rather than having a data sheet displayed.

 

[ryetee]

I think one 'why' is appropriate:

Last([Component SWAPS].ModelNumber) AS LastOfModelNumber

Why are you using 'Last' in your query? I've yet to find a use for that horrible keyword. Most likely it's not returning what you are expecting it to return.

This is really a carry on from this post http://www.access-programmers.co.uk/forums/showthread.php?t=270280

Basically I want to populate the next new line of a form with values from different fields of the 'last' row.

Not sure what the why refers to but I've been told various things ling use a select which I can't do in VBA. I have to use a query.

If last is no good - how do I pick the latest record (see above link for info).

 

[namliam]

WHY cant you use select in vba? the given code should work, I think?

If you want to use the query object, research querydefs a bit, see what that gets you...
Come back here if you get stuck and need more help

How to pick the latest record? Get the most recent entered line either by ID or by date/time fields

 

[ryetee]

WHY cant you use select in vba? the given code should work, I think?

If you want to use the query object, research querydefs a bit, see what that gets you...
Come back here if you get stuck and need more help

How to pick the latest record? Get the most recent entered line either by ID or by date/time fields

SELECT doesn't work in VBA - you get an error message. Only allows 'ACTION' verbs like UPDATE INSERT DELETE etc.

Think I may have got around it with some help from JHB but still needs refining. I'll report back here when it's working perfectly!

 

[namliam]

That is nonsense, yes, you can only execute or runsql action queries....

If you use the code I gave you, you open the query in a recordset, that I can guarantee you will work just fine.

 

[Frothingslosh]

Namliam is right - if you just want to work with a recordset, just use the method he showed you.

My guess is you're trying to use either DoCmd.RunQuery or Currentdb.Execute rather than the OpenRecordset method. The former indeed do require an action query, while the latter uses a select query.