4 queries in code

[marc p]

I wonder who could help me with this:

I need the code that:

(1) Eliminates all registers in Table "1_PVSystems" where
field "PV Systems" is blank

(2) Counts remaining number of registers in
Table "1_PVSystems".

(3) Updates this number to Table "9_Autonumber" ,
field "ID" [Long Integer]; which is a table with only one
register and one field, and

(4) Appends value from Table "9_Autonumber" and unique
field "ID" to the firts table "1_PVSystems" and field "ID"
[Autonumber]; which is the primary key.

I would like to do it with code, without having to use
four different queries for eliminating, counting, updating
and appending.

The idea is to eliminate any record where "PV System" is
blank and then to bring back the autonumber key "ID" the
the next value.

Thank you,

Marc p

 

[Dirk Goldgar]

I wonder who could help me with this:

I need the code that:

(1) Eliminates all registers in Table "1_PVSystems" where
field "PV Systems" is blank

(2) Counts remaining number of registers in
Table "1_PVSystems".

(3) Updates this number to Table "9_Autonumber" ,
field "ID" [Long Integer]; which is a table with only one
register and one field, and

(4) Appends value from Table "9_Autonumber" and unique
field "ID" to the firts table "1_PVSystems" and field "ID"
[Autonumber]; which is the primary key.

I would like to do it with code, without having to use
four different queries for eliminating, counting, updating
and appending.

The idea is to eliminate any record where "PV System" is
blank and then to bring back the autonumber key "ID" the
the next value.

Thank you,

Marc p

The first three items on your list can be dealt with easily enough using
code like this:

Dim db As DAO.Database

Set db = CurrentDb

db.Execute _
"DELETE FROM 1_PVSystems " & _
"WHERE [PV Systems] Is Null;",
dbFailOnError

db.Execute _
"UPDATE 9_Autonumber SET ID=" & _
"DCount('*', '1_PVSystems');",
dbFailOnError

Set db = Nothing

But for the fourth, I don't understand what you mean:

(4) Appends value from Table "9_Autonumber" and unique
field "ID" to the firts table "1_PVSystems" and field "ID"
[Autonumber]; which is the primary key.

Do you want to create a record in 1_PVSystems having the count of
records as its autonumber ID field? That wouldn't really be an
autonumber then, would it? What would you want to have happen if a
record with that particular key value already exists?

I'd like to understand better what you're after here. If you're trying
to treat an autonumber field as if it has any meaning other than as a
unique record key, you're making a mistake and will run into endless
frustration.

 

[John Spencer (MVP)]

First question, do you want to DELETE the records or just identify them and not
show them in a query?
Second question, are you trying to append only one record?

Assuming that the answer to the above are:
1) Just count all records that have a value in the field [PV Systems]
2) Yes, append one record.

The following statement will get a count of records where [Pv Systems] is not
blank (null)

DCount("*","[1_PvSystems]","[PV Systems] is Not Null"

Given that, the following SQL should work for you.

Insert Into [1_PvSystems]
([Pv Systems])
Values (DCount("*","[1_PvSystems]","[PV Systems] is Not Null"))

NOTE: You don't include the Autonumber field ([ID]) in this statement because it
is an autonumber and is automatically generated and assigned to the [ID] field.

 

[marc p]

Thank you Dirk for your help. What I am trying to do is to
bring the Autonumber down to the next record after
deleting some of them. In that, way the autonumber will be
correlative. I know that I should not try to use it other
than as a unique record field, this is why in step 2 I
count the records after deleting some of them in step 1.

-----Original Message-----

I wonder who could help me with this:

I need the code that:

(1) Eliminates all registers in Table "1_PVSystems" where
field "PV Systems" is blank

(2) Counts remaining number of registers in
Table "1_PVSystems".

(3) Updates this number to Table "9_Autonumber" ,
field "ID" [Long Integer]; which is a table with only one
register and one field, and

(4) Appends value from Table "9_Autonumber" and unique
field "ID" to the firts table "1_PVSystems" and field "ID"
[Autonumber]; which is the primary key.

I would like to do it with code, without having to use
four different queries for eliminating, counting, updating
and appending.

The idea is to eliminate any record where "PV System" is
blank and then to bring back the autonumber key "ID" the
the next value.

Thank you,

Marc p

The first three items on your list can be dealt with easily enough using
code like this:

Dim db As DAO.Database

Set db = CurrentDb

db.Execute _
"DELETE FROM 1_PVSystems " & _
"WHERE [PV Systems] Is Null;",
dbFailOnError

db.Execute _
"UPDATE 9_Autonumber SET ID=" & _
"DCount('*', '1_PVSystems');",
dbFailOnError

Set db = Nothing

But for the fourth, I don't understand what you mean:

(4) Appends value from Table "9_Autonumber" and unique
field "ID" to the firts table "1_PVSystems" and field "ID"
[Autonumber]; which is the primary key.

Do you want to create a record in 1_PVSystems having the count of
records as its autonumber ID field? That wouldn't really be an
autonumber then, would it? What would you want to have happen if a
record with that particular key value already exists?

I'd like to understand better what you're after here. If you're trying
to treat an autonumber field as if it has any meaning other than as a
unique record key, you're making a mistake and will run into endless
frustration.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.