what is wrong with this VB/SQL

[oraclejunior]

Dim MaxCells As Integer
Dim CellsAdded As Integer
Dim Diff As Integer

MaxCells = [Forms]![createcampaignform]![NumberOfCells]
CellsAdded = "(SELECT COUNT(celltable.[internaljobid])" & _
"FROM celltable, campaigntable" & _
"WHERE ((celltable.[internaljobid]) = [Forms]![createcampaignform]![JobID]));"
Diff = (MaxCells) - (CellsAdded)

If Diff > 0 Then

 

[KenReay]

Hi

by assigning an sql string to a variable

ie your line:

CellsAdded = "(SELECT COUNT(celltable.[internaljobid])" & _
"FROM celltable, campaigntable" & _
"WHERE ((celltable.[internaljobid]) = [Forms]![createcampaignform]![JobID]));"

you are not retrieving the required data, in order to do that you need to open a recordset

you can use ADO or DAO to do this

Which version of Access are you using?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[oraclejunior]

I am using access 2000.

 

[oraclejunior]

How does one open a record set.

The name of the DB is ISDB and the table is called CellTbl.

KR

Nick

 

[gol4]

Also look at dcount function

dCOUNT("[internaljobid]" ,"celltable", "[internaljobid]) =" & [Forms]![createcampaignform]![JobID])

if job id is a number field

or

dCOUNT("[internaljobid]" ,"celltable", "[internaljobid]) ='" & [Forms]![createcampaignform]![JobID] & "'")

if it text

 

[sunmorgus]

You have you "CellsAdded" declared as an Integer. In order for access to run it, you need to have it as a string. Then, you can open a recordset like so:

Dim rs as DAO.Recordset

set rs = db.openrecordsets.("CellsAdded")

so your final code could look similar to this (note, I changed your SQL slightly):

Dim MaxCells As Integer
Dim CellsAdded As Integer
Dim Diff As Integer
Dim rs as DAO.Recordset

MaxCells = [Forms]![createcampaignform]![NumberOfCells]
CellsAdded = "(SELECT COUNT(celltable.[internaljobid] As CountOfInternaljobid)" & _
"FROM celltable, campaigntable" & _
"WHERE ((celltable.[internaljobid]) = [Forms]![createcampaignform]![JobID]));"

set rs=db.openrecordsets.("CellsAdded")

Diff = (MaxCells) - (rs!CountOfInternaljobid)

rs.close
set rs=Nothing

If Diff > 0 Then

 

[KenReay]

Hi

Sorry, been away from my PC because it is the weekend and the sun is shining (unusual for the UK)!

Basically agree with sunmorgus but

two points

If using Access 2000 you will need to set a reference to DAO library to use this code, to do this when in code view choose from menu Tools \ References and select the DAO library from the list presented

also I think it should be

set rs=db.openrecordset.("CellsAdded")


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[oraclejunior]

Hi Chaps, thank for all the help so far.

I have utilized the code above, but I am getting a debug error (compile error) showing the following line in RED

Set rs = db.openrecordset.("CellsAdded")

I have also tried to edit this by changing the set to sets and vice versa but then I get a the following message:

Expected: identifier or bracketed expression

Am I missing something out from the line above

Thanks

 

[greely]

All the posts so far are on the money, but there are some typos and other problems.

1. Declare CellsAdded as a string.
Dim CellsAdded as String

2. Declare db as Database
Dim db as Database

3. then set db
Set db = currentdb()

4. Set the recordset like this (CellsAdded is a variable and should not have quotes.) Also,omit the dot
Set rs = db.openrecordset(CellsAdded)

5. In your SQL, to reference the contents of the textbox
"...WHERE ((celltable.[internaljobid]) =" & Int([Forms]![createcampaignform]![JobID]) & "));"

I think thats all.




Ron

Isn't it great when it works the first time?

 

[oraclejunior]

on running the code, I recieve a syntax error in the union query. In the VB

Set rs = db.OpenRecordSet(CellsAdded)

is highlighted in yellow?

 

[Remou]

Maybe you are looking for something like the line below. I got it by pasting your sql into QBE window and taking bits away until the window stopped complaining -).

="SELECT Count(celltable.[internaljobid]) AS CountOfInternaljobid FROM celltable WHERE (((celltable.[internaljobid])=" & Int([Forms]![createcampaignform]![JobID]) & "));"[code]
(You can usually get away with a lot fewer brackets.)

 

[Remou]

or even

="SELECT Count(celltable.[internaljobid]) AS CountOfInternaljobid FROM celltable WHERE (((celltable.[internaljobid])=" & Int([Forms]![createcampaignform]![JobID]) & "));"

(You can usually get away with a lot fewer brackets.)

 

[greely]

If you still experience an error with Remou's recommendation, try:

if internaljobid is datatype numeric in your table:

CellsAdded = "SELECT COUNT(celltable.[internaljobid]) As CountOfInternaljobid FROM celltable, campaigntable WHERE ((celltable.[internaljobid]) =" & [Forms]![createcampaignform]![jobid] & ")"

if it's text:

CellsAdded = "SELECT COUNT(celltable.[internaljobid]) As CountOfInternaljobid FROM celltable, campaigntable WHERE ((celltable.[internaljobid]) ='" & [Forms]![createcampaignform]![jobid] & "')"



Ron

Isn't it great when it works the first time?

 

[oraclejunior]

Thanks chaps, I got it working with ur help. Now I will be using the same principle to further make the system idiot proof.

Sunday morning 0011 hrs, I can sleep with a little smile now.

Thanks

Nick

 

[oraclejunior]

By the way, I was tripping over at the last bit of the code by using " as to '"

TA