Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using Queries with VBA

Posted on 2011-05-13
14
Medium Priority
?
388 Views
Last Modified: 2012-08-14
Hi guys
I am using MS Access 2003
I have a standard module inwhich I need to choose a catagory from a query
and store it to variable.
The name of the query is Disp_Cat
Can someone provide me with the VBA script I should use to do that.
Thanks
Dory
0
Comment
Question by:dory550
  • 7
  • 7
14 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35753815
post the codes from the module.
post the sql statement of the query.

if your query returns only one value
myVariable=dlookup("Var","Disp_cat")
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35753820
if your query returns only one value
myVariable=dlookup("Var","Disp_cat")

other wise you will need a criteria added to the dlookup() function

myVariable=dlookup("Var","Disp_cat",<criteria>)
0
 

Author Comment

by:dory550
ID: 35756965

capricorn1:
Sorry
Maybe I did not clarify enough
I am working with a stadard module.
I need a function or sub that would  enable a user to choose a category lfrom a list  that is obtained from a query Called 'Disp_cat'  and store i the 'Category number' and the 'Category Desription'  in 2 variables
Thanks
Dory

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35757192
dory550,

better upload a copy of your db.

i already gave you a function, dlookup() to get the values from the query.
the information you provided is not enough.
0
 

Author Comment

by:dory550
ID: 35757975
capricorn1:

Below please find the text output  of ' Disp_Cat' query (refered to above)
Query has 2 columns Category & Description
                                   
          CATEGORY    DESCRIPTION
          501     (USA) Duracell RePa
          855     AIR FRESHENER SPRAY
          964     Aluminum Pans
          525     Audio Tape Cassette
          875     Baby Care Etc
          942     BANDAID / First-Aid
          904     Candy Gum ETC
          931     Car-Freshener Tree
          547     CD n DVD Media
          933     Chapped Lips Remedi
          600     Cigarette Paper etc
          912     Contempo Condoms
          938     COUGH N COLD
          951     CRAYOLA
          935     Deodorants N Anti P
          211     Disposable Cameras
          575     Disposable Lighters
          950     Drugs And HBA
          937     DRUGS Single Dose
          500     Duracell (Original
          410     Duracell imported
          400     ENERGIZER /  EVEREA
          879     ENERGY / VITAMINS
          947     EXTENSION CORDS
          968     Eye Care
          977     Feminine Hygiene
          103     FILMS and CAMERAS
          616     Flashlights
          881     FOOTWEAR
          960     General Merchandise
          934     GIFT-BAGS N Wrap
          943     GILLETTE Original U
          960A    Gloves
          926     Grocery Bags
          890     Hair Accessories
          780     HAIR CARE Shampoo e
          936     HEADACHE & PAIN
          870     HeadPhones COBY & O
          978     Household Misc Item
          946     KEYCHAINS
          930     KITCHEN AIDS
          909     LIFESTYLE Condoms
          700     LIGHT BULBS
          510     Lithium Batteries
          969     LUCKY HAIR COLOR
          867     MASTER Locks



0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35758197
ok.

you can only assign one value at a time to your variable.

how do you pick the value you want to assign?

how do you intend to use the variables?
0
 

Author Comment

by:dory550
ID: 35758880
capricorn1:

Thanks for your input
OK
One variable is fine
Let me rephrase
The sulution I need  is a function  that would  present the user a list of  categories .
The list  is obtained from a query Called 'Disp_cat' (See sample text output of 'Disp_Cat' above)
Function should return one variable  only (as you indicated in your last post)
The Variable is to contain the category number (left column, see above)

Thanks
Dory


"
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1500 total points
ID: 35758927
so you want a list to choose from,

create a form, place a  listbox
use the query "Disp_Cat" as the Row Source of the List box
set the following properties of the listbox

Column Count 2
Bound Column 1



0
 

Author Comment

by:dory550
ID: 35759098
capricorn1:

Ok
I set the form/listbox
I created the function below
When I run this function it opens the form and allowd the user to choose a category and fires up a click event.
My question is how do I  return to my function after the click event ?

If I close the form in the click event , I will not be able to retrieve the category chosen
I'd rather not use a global variable

thanks
Dory




Function GatCategory
DoCmd.OpenForm "disp_category", , , , , acDialog
Other commands.......
End Funcion
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35760107
dory550,

it will be better to give details on what you really want to achieve here?
start from the beginning and the ultimate goal
0
 

Author Comment

by:dory550
ID: 35760518
Hi There
I know that there many ways to skin a cow
At the moment I am focused on only one area
My only goal at the moment is the solution to the question below

The sulution I need  is a function  that would  present the user a list of  categories .
The list  is obtained from a query Called 'Disp_cat' (See sample text output of 'Disp_Cat' above)
Function should return one value  only (as you indicated in a previous post)
The returned value is to contain the category number (left column, see above)
The working solution I currently  have  involves the use of a global variable.
I would like to have a solution that would exclude the use of global variables

Thanks
Dory





0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35760548
it is important to know where you will use the item selected from the list.
0
 

Author Comment

by:dory550
ID: 35762091

Hi
I realize that you are trying to help here and I thank you for that
My intended use is  really too complicated to explain and it is totally not relevant in this case
I am not seeking a workaround or anything like that
In this particular instance I am only looking a for a solution to the  scenario described above
Nothing more nothing less.
Thank you  for your time and effort
Dory


0
 

Author Closing Comment

by:dory550
ID: 35775215
Capricorn1
Thanks
Dory
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Implementing simple internal controls in the Microsoft Access application.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question