Topics Search

Create a variable from a query created in VBA

Create a variable from a query created in VBA
Views: 8
I would like to create a dynamic query using VBA and use the query's result as a variable. I have done this before, without using dlookup (I think it's too complex for dlookup), but I cannot seem to remember how.

What I'd essentially like to be able to do is create a query:

docmd.runsql("SELECT x from y where z;") [note: actual query will be a lot more complex than that and include subqueries]

.and use the result of that as a variable. I don't want to create permanent queries because I'd like to avoid the clutter that would come from that.
Sponsored Links:

More topics

 

Problem with query accessing VBA function

Preview
I have written some MS ACCESS 2003 VBA that allows a variable to be passed to a query, using a form to harvest criteria for the query.

Unfortunately, the variable does not seen to be passed on to the query, although the query function call is triggering the VBA - it just doesn't seem to be returning the value into the query.

I have tested the rest of the code and the value I am trying to get into the query criteria is working up to the point of the query calling the function,and then the criteria doesn't work in the query.

I have another function in the same query, set up the same way, and that one works fine for an integer value that I am using to return a unique event ID.
 

Date variable is being displayed as an empty time variable

Preview
Looking through this forum you can see that I have been trying to work out a vba function and query problem.

I have recently discovered that the variable being stated as Date has an output of time.

In the vba function I define both the function (calcdate(showthisdate)) and variable (showthisdate) as Date

the variable then as a date stored into it using a combination of the DateValue function.

I then placed calcdate(showthisdate) as a query critria. After this not working and serveral changes did nothing I placed the function as a new field to see if the variable was being passed. This field defined as Date is showing 0:00:00 in all the rows.

Looking into it it I found that defining something as Date also adds a time format aswell

Question how can I define the function and variable just as date instead of date and time
 

Filting query results within VBA code module

Preview
I use Access 2010 but haven’t written much VBA code and could use some help. I have a table that provides the name of a query in a text field. The field name is “Query”.

I have a field in this same table that shows a department value for each record. That fieldname is “Dept”.

Within a huge VBA function module I’m writing, I have a string variable assigned to the Query field called strQuery. I also have string variable assigned to the Dept field called strDept.

The query that is assigned to the variable strQuery is designed to return ALL results for ALL departments. However, I want the VBA to run the query and return results for only the department that are assigned to the variable strDept.

If I leave out the department limitation, it works fine with the following code:
 

Embedded/nested? Variable

Preview
Is there some sort of internal variable or table that can be used to store temporary variables? While running code you can obviously store data in variable, the variable are dumped.

So I want to store some variable at some place in the vba or access that I can afterwards collect that variable again when I rerun my code.

Only way I know is to add it into a table, but is there some other, easier way?
 

Create a query in VBA, Access 2007

Preview
I need to create a query in VBA. I'm using Access 2007.

I have a combo box and based on the user's selection(s), a 'where clause' is created. I need to either create a query that includes my where clause, or use an existing query and use the where clause.

I had created a query in VBA in Access 2003 using DAO defs, e.g.,
set qdef = MyDB.CreateQueryDef("q_regions", strSQL)
This does not work in 2007.
 

Variable Not passing to query

Preview
I have a command button which when clicked, runs a VBA macro which saves a report to a PDF for the current record. The report is based on a predefined filter for based on the current ID field on the original form.

I have tried setting the criteria field to the current record of the form, setting a variable to the ID field. But the query is not getting the information to compare and the output of thereport is #Type and #Error.

When the macro is run, the query runs and prompts for the information the criteria is looking for.

How do I pass a variable from a form to a query in VBA?
 

Lose Variable

Preview
I have my users login so I can set UserPolicy. When they login I store their User Name in a Public Variable called strUserLogin. However, the weird issue is it seems to lose the variable after a while. Because I use the variable also to set who created an appointment or modified it last. Unfortunately, a lot of them are coming up blank. I checked the code, the code is correct - I also set the date modified and date created and they are always getting filled therefore the only thing that I can think of is it is losing the variable. Any idea why it will lose the public variable and how I can prevent it from happening
 

How to call a Public variable in a query?

Preview
I want to be able to, when a certain button is pushed, pass a variable to a query. These buttons would be utilized in several forms as subform so I can just code the event procedures once.

I think storing a value to a public variable is fairly easy, but Im not sure how, in a query, to call that variable as a criteria
 

VBA doesn't recognzie variable's contents

Preview
I have declared a public variable in my form's vba code

[code]

Returns goes back to the original code.

But when I check the value of the variable in the originals form code with a msgbox. it is blank.

When the code finishes running (there is just one or two lines of code which do not affect the variable). I debug.print strinput and it has a value.

Why does debug.print recognize the value of the variable but the code within the form (it is an afterclick event subroutine) not recognize it? The public variable is declared in the original form.
 

Build Query in VBA using List Box to Select Fields

Preview
The user would like to be able to create queries dynamically. ie. She would like to be able to select which fields are in the query. Each of the "variable" fields to be selected are Yes/No Checkboxes.

I thought the best way to do this is to create a query with the basic fields needed and then add the "variable" fields by allowing the user to select them using a list box.

How do I transfer the listbox selection to the SQL statement