Select

[cff_moiseszaragoza]

Hey i am new to access programming

I am having problem with a select
my code looks like

Private Sub LoadPDF_Click()

sql = " SELECT id FROM PROJECT WHERE city=1 and state = 1 "
CurrentDb.Execute (sql)

End Sub

When i run the query it works but not from my access file

[John_G]

That's correct, and it is because you cannot use Select queries with Docmd.Execute - DoCmd.Execute can only be used with action queries like Update..., Delete.. and Insert...

How do you want to use the query, i.e. what do you want to do with the records it selects?

John

[cff_moiseszaragoza]

Thanks for letting me know
Well i want to get 2 values out the id and a count
Then i want to add x to the count so i can
run a update

[John_G]

ID and Count are not really the same thing. "Count" implies you want to know how many records there are (in table PROJECT?) that meet certain criteria, in your case city=1 and state = 1. But what is ID? ID is normally an identifier in each individual record, so while a count will tell you how many records there are, the list of ID's tells you which ones they are.

You cannot get both from the same query (not directly, anyway) - so can you give more detail on what it is you want to do?

John

[cff_moiseszaragoza]

i know that id and count are 2 different things

but my problem is how do i get a select

sql = " SELECT id, field_that_holds_documet_count_as_val FROM PROJECT WHERE city=1 and state = 1 "
CurrentDb.Execute (sql)

My Process

Get values from query
id = id
count = field_that_holds_documet_count_as_val + more value
Update table Set field_that_holds_documet_count_as_val = 'count' Where id = id

[John_G]

How many records are there where city = 1 and state = 1?
If the answer to that is only 1, then you don't need a select at all - you can do the update directly:

currentdb.execute "Update Project set field_that_holds_documet_count_as_val = " & _
field_that_holds_documet_count_as_val + " & x & _
" where city = 1 and state = 1"

Now, if there is more than one record where city = 1 and state = 1, then you have to refine your where clause to identify which record you want to update. If you want to update all the records, the statement I showed you above will do that, just as I wrote it.

John

[cff_moiseszaragoza]

it only returns 1 record but i need to get a value.
There is a value in that that table that i need to add to then update

[cff_moiseszaragoza]

let me go back to the original question
How do you I get data from a Select statement

[John_G]

First question:
"There is a value in that that table that i need to add to then update "

I showed you that with this suggestion:

currentdb.execute "Update Project set field_that_holds_documet_count_as_val = " & _
field_that_holds_documet_count_as_val + " & x & _
" where city = 1 and state = 1"

Putting this in generic terms:

SQL = "Update [Table_Name] set [Field_Name] = Value where conditions"
currentdb.execute SQL

That will update 0, 1 or many records in the table Table_Name, depending on what is in the conditions.

Second question:

To use data retrieved by Select statement, you put it in to a recordset:

Dim SQL as string, rst as RecordSet

SQL = SELECT id, Count_Field FROM PROJECT WHERE city=1 and state = 1 "
currentdb.openrecordset(SQL)
if not rst.BOF then ' Needed to prevent an error if no records were retrieved
rst.edit
rst!Count_field = rst!Count_Field + x
rst.update
endif
rst.close

If the Select statement retrieves more than one record, only the first one is updated using this bit of code.

HTH

John