By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

Query Results As VB Variables

100+
P: 365
Good day to you all, im making a very simple (or so it should be) database and im trying to do things too difficultly.

Background, i work in a lab and the purpose of the database is to record the reagent levels (like a stock control database) i have been writting queries as vb code so that i can change events etc as things happen and its all going fine, until i build on it more, i want thinks to happen with IF commands, and what i want is for example, IF "the results of an SQL (VB) code gives me a result" THEN do something else, ive done lots of looking up and i keep getting back to finding the only way to do this is with recordsets, well i really cant get the damn things to work, i follow examples (adapting for my cause of course) and i cant get the outcome i want, im beginning to wish i had done it the long way and used Excel, lol.

I would very much appreciate any help or pointing in the right direction you pros could give me.

thanks
Oct 10 '07 #1
Share this Question
Share on Google+
15 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, Dan.

Generally SQL command doesn't return any kinf of VB variable you can use the way you want. Explain the logic you want to implement with the code.

Regards,
Fish
Oct 10 '07 #2

100+
P: 365
OK thanks for the reply, there is a few things i want to do and they all include if commands, and queries
i can limit the queries to single answers 'cos the "database" is so simple,
(one table with ID num. Reagent name, batch number, expiry, quantity, and a section splitter (just to break up the list a bit) i have the basic frame done and i have used select, update delete queryies in vb code (as update events and so on on forms to get my source data restricted and so on blah blah.

but like i say i want some if commands.

the first being; if a reagent is aproaching a low number, then i want a msgbox to pop up and say "hey dude, im hungry" (or similar lol) so simple query based on the ID # and >5 for example,

another is for the delete query where i want to run either one or another update query followed by the delete, so i need another IF.

and the newsest to the list is so that i can have a checkbox field to show weither or not the item is in use yet or not (relating to batch numbers and using old stuff 1st!)

so i just need to knw how to get the value out from sql, cant fathom recordset, and with one answer im not sure it would be worth it?? and i tried just "making" a query and selecting the data (qryxyz.[blah1].value = x... etc...)

sorry for the long read but anything you can give would be most helpful.
Oct 10 '07 #3

FishVal
Expert 2.5K+
P: 2,653
OK thanks for the reply, there is a few things i want to do and they all include if commands, and queries
i can limit the queries to single answers 'cos the "database" is so simple,
(one table with ID num. Reagent name, batch number, expiry, quantity, and a section splitter (just to break up the list a bit) i have the basic frame done and i have used select, update delete queryies in vb code (as update events and so on on forms to get my source data restricted and so on blah blah.

but like i say i want some if commands.

the first being; if a reagent is aproaching a low number, then i want a msgbox to pop up and say "hey dude, im hungry" (or similar lol) so simple query based on the ID # and >5 for example,
Don't you want to simply popup a query window or form based on the query
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM [tblMain] WHERE quantity < ... some treshold level;
  2.  
?


another is for the delete query where i want to run either one or another update query followed by the delete, so i need another IF.
What do you expect delete query will return?

and the newsest to the list is so that i can have a checkbox field to show weither or not the item is in use yet or not (relating to batch numbers and using old stuff 1st!)

so i just need to knw how to get the value out from sql, cant fathom recordset, and with one answer im not sure it would be worth it?? and i tried just "making" a query and selecting the data (qryxyz.[blah1].value = x... etc...)
If you've narrowed query output to one or less record, then you may use DLookup() function.
Expand|Select|Wrap|Line Numbers
  1. DLookup("blnIsInUse", "tblMain", "ID=" & ...id to find)
  2.  
Oct 10 '07 #4

100+
P: 365
well i am using an update query to add/subtract values from the quantity and when it gets to a certain level then i want a msgbox, the only way i can get that is with If "query" = <x then msgbox "...."

and the reason for the delete query is to remove old stock so thats irrelivant really. (i have amend queries in place for an audit table to track usage, but that is irrelivant for this too.

the check box thing i want as a safeguard, so that new batches can be entered but not selected, so that they can't be used prematurely, and if by accident an attempt is made it warns that it cant be amended, i.e if checkbox is null/false then msgbox "slow it down dude, u havent used the old stuff yet,"

yeah?
Oct 10 '07 #5

FishVal
Expert 2.5K+
P: 2,653
?????

You are using db in such an unusual and sophisticated way, Do you have a some kind of form, is it bound to your table or is it series of buttons triggering InputBoxes? Does the form reflects current state of your table?
Oct 10 '07 #6

100+
P: 365
i have the tabel set up as listed above somewhere, and i have a single main form wit ha combo box, that selects a category, which relates to a list box and runs a changing query (via vb) to show a list, and on the list i have a double click event which allows you to add or subtract a number (from a textbox input) via an update query.

if you want me to show you i can email you it? its not really a database more like a program, with shortcuts (using access rather than writting in VB!)
Oct 10 '07 #7

FishVal
Expert 2.5K+
P: 2,653
So what about usung DLookup() function?
Oct 11 '07 #8

100+
P: 365
I've tried that and that returns no result too :s
Oct 11 '07 #9

100+
P: 365
Anyone know how to do this!?
Oct 11 '07 #10

FishVal
Expert 2.5K+
P: 2,653
I've tried that and that returns no result too :s
Hi, Dan.

Post table metadata.

Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
And your code you have a problem with.
Oct 11 '07 #11

100+
P: 365
Field; Type; IndexInfo
ReagentID; AutoNumber; PK
Reagent, String
Lot Number; String
Expiry Date; Date;
Section; "value" List
In Use; Yes/no

example of one of the queries i am trying to do

"SELECT tblMain.InUse FROM tblMain Where tblMain.ReagentID = [Forms]![frmMain]![Searchlist] AND tblMain.InUse = True"

i have the SQL mounted in VB and i want the result of the query as VB variable, i have tried to get tis to out put as a recordset value and use a count but i keep getting errors, and dlookup does return a value from the query, which when the query works as supposed to a single true checkbox is resulted.

thanks for your help so far
Oct 11 '07 #12

FishVal
Expert 2.5K+
P: 2,653
Well, Dan.

Try this function
Expand|Select|Wrap|Line Numbers
  1. DLookup ("InUse", "tblMain", "tblMain.ReagentID = " & [Forms]![frmMain]![Searchlist] & " AND tblMain.InUse = True")
  2.  
which will return tblMain.InUse value or Null if no record will be found
or this function
Expand|Select|Wrap|Line Numbers
  1. DCount ("*", "tblMain", "tblMain.ReagentID = " & [Forms]![frmMain]![Searchlist] & " AND tblMain.InUse = True")
  2.  
which will return number of records in tblMain satisfying the criteria (0 or 1 in your case)
Oct 11 '07 #13

100+
P: 365
Well would you believe it it works, i dont know y it didnt work for me (dcount)

i didnt use the "*" at the beginning (can you explain that to me)
and i believe i had the [Forms]!..... inside of the ""

(well i spose i do know what i did differently, lol)

but it looks like it has worked,
so thank you very much, ther will be no stopping me now, until next time lol
Oct 11 '07 #14

FishVal
Expert 2.5K+
P: 2,653
Well would you believe it it works, i dont know y it didnt work for me (dcount)

i didnt use the "*" at the beginning (can you explain that to me)
Domain aggregate functions require field name. Actually if you pass particular field name to DCount function it will return count or records satisfying the criteria disregard those there field is null. Using "*' (all fields) will force DCount function to return number of records where at least one field is not null.

and i believe i had the [Forms]!..... inside of the ""

(well i spose i do know what i did differently, lol)

but it looks like it has worked,
so thank you very much, ther will be no stopping me now, until next time lol
You are welcome.

Happy coding and good luck.
Oct 11 '07 #15

100+
P: 365
got it all done nicely, even managed to use the dlookup as a string result so that i could have msg boxes saying

"you are low on "dlookup" blah blah!!!"

smashing works like a charm finally
and i dont need record sets!!! woohoo

so thank you again!!!!
Oct 11 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.