Storing values of a table in a VB variable? 2

[intern42]

I'm using an Access 2000 database and need to check the value of a table to decide on an action to perform.

Basically, I have an SQL query that returns a one-cell value of either 1 or 0. If the value is zero, I need to add a row to a table; if the value is one, I need to update a row of a table.

My question is this: How can I assign the result (one or zero) to a variable that I can use in some if logic?

I know it has something to do with creating a recordset, but my VB skills aren't what they used to be.

Any help?

 

[scriverb]

If you are going to only use the variable within the current form then a form global variable by putting this in the Declarations area:
Dim vVariableName as Integer

If you want the value to be able to be accessed from other forms or reports etc. then create the variable at the database module level:
Global vVariableName as Integer

This should get you started.


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[intern42]

Thanks Bob.

I know how to dimension variables, my problem is how do
I get the results of this SQL statement:

SELECT COUNT(*)
FROM PersonnelCourses
WHERE PersonnelID = Me!cboPersonnel
AND courseID = Me!cboCourses;

This query will return a one-cell table with either a zero or a one in the cell.

Let's say I have a variable declared with:

dim IsKey as Integer.

How do I say: IsKey = (value "result" of above query)?

I'm stumped.

 

[scriverb]

Your query should look like this:
SELECT 1 as RecNumber,COUNT(*) as CountOfRecords
FROM PersonnelCourses
WHERE PersonnelID = FORMS!<YourFormName>!cboPersonnel
AND courseID = FORMS!<YourFormName>!cboCourses;


IsKey = DLookup(&quot;[CountOfRecords]&quot;,&quot;qryYourQueryName&quot;,&quot;[RecNumber] = & 1)

I modified your query slightly so that we could use the DLookup function and select the first record. Just update the statement with the name of the query above. The red code in the query has to be used as the query will not know what Me! is. You must use the long version of identifying a form when referencing in a query.


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[mpastore]

Create a module and copy this in:
--------------------
Option Explicit
dim mIsKey as integer

function isKey_set(piKey as integer)
mIsKey = piKey
end function

function isKey_get() as integer
isKey_get = mIsKey
end function
----------------------
Now your query looks like:

SELECT isKey_set(COUNT(*))
FROM PersonnelCourses
WHERE PersonnelID = Me!cboPersonnel
AND courseID = Me!cboCourses;

------------------------

In order to access the value:

debug.print isKey_get()

 

[intern42]

That looks good, but I now get the error:

Run time error 3075: syntax error (missing operator) in
query expression '[RecNumber] = & 1'.

when this line of code executes:

IsKey = DLookup(&quot;[CountOfRecords]&quot;, &quot;Key Exists&quot;, &quot;[RecNumber] = & 1&quot

&quot;Key Exists&quot; is the name of my query.

Do I need to qualify this as &quot;[Querys]![Key Exists]&quot;
or is the error elsewhere?

Thanks again for the help!

 

[scriverb]

My apologies:
IsKey = DLookup(&quot;[CountOfRecords]&quot;,&quot;qryYourQueryName&quot;,&quot;[RecNumber] = 1&quot

Give this a try.


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[intern42]

That's it man!!

Thanks a TON.

That was the LAST bug I had to work out of this application.

Much appreciated.

 

[scriverb]

Great. Glad to be of assistance. Thanks for the star.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???