Hey all, I am looking for a way to query my database table only once in order to add an item and also to check what last item count was so that i can use the next number.

  strSQL = "SELECT * FROM productr"

After that code above, i add a few product values to a record like so:

  ID | Product      | Price  | Description      | Qty | DateSold  | gcCode
  --------------------------------------------------------------------------
  5  | The Name 1   | 5.22   | Description 1    | 2   | 09/15/10  | na
  6  | The Name 2   | 15.55  | Description 2    | 1   | 09/15/10  | 05648755
  7  | The Name 3   | 1.10   | Description 3    | 1   | 09/15/10  | na
  8  | The Name 4   | 0.24   | Description 4    | 21  | 09/15/10  | 658140

i need to count how many times it sees gcCode <> 'na' so that i can add a 1 so it will be unique. Currently i do not know how to do this without opening another database inside this one and doing something like this:

  strSQL2 = "SELECT COUNT(gcCode) as gcCount FROM productr WHERE gcCode <> 'na'

But like i said above, i do not want to have to open another database query just to get a count.

Any help would be great! Thanks! :o)

Originally asked by: StealthRT on Stack Overflow