Report-Non allocated numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have designed a report based on a query that lists the persons name and an
Identification number (Number field - Indexed No duplicates). I do not want
this to be an Auto Number field.
When I run the report it lists all the records in Identification Number
order-no problem. eg: 1, 2, 3, 5, 7etc.
I would like a seperate report that just lists the numbers not allocated. eg
from above 4,6

Any help would be appreciated
 
Roger, the numbers have to come from somewhere, so you will need a table of
all the valid numbers. If you make a table named tblCount, with one Number
field named CountID, you could use the function below to populate it.

You can then use the Unmatched Query Wizard to create a query that selects
all records from tblCount that are not in your existing table.

Function MakeData(HowMany As Long)
Dim rs As DAO.Recordset
Dim lng As Long

Set rs = DBEngine(0)(0).OpenRecordset("tblCount", dbOpenDynaset)
For lng = 1 To HowMany
rs.AddNew
rs![CountID] = lng
rs.Update
Next
rs.Close
Set rs = Nothing
End Function
 
Thank you Allen for your advices. As I am not that good, could you please
enlighten me as to where I write the Function (is it a Query?) and is the
wording exactly as you have written ie: starting with the word Function etc.

Sorry to be such a nuisance, but you will get me out of the rut I am in and
Thank You immensely

Allen Browne said:
Roger, the numbers have to come from somewhere, so you will need a table of
all the valid numbers. If you make a table named tblCount, with one Number
field named CountID, you could use the function below to populate it.

You can then use the Unmatched Query Wizard to create a query that selects
all records from tblCount that are not in your existing table.

Function MakeData(HowMany As Long)
Dim rs As DAO.Recordset
Dim lng As Long

Set rs = DBEngine(0)(0).OpenRecordset("tblCount", dbOpenDynaset)
For lng = 1 To HowMany
rs.AddNew
rs![CountID] = lng
rs.Update
Next
rs.Close
Set rs = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Roger Bell said:
I have designed a report based on a query that lists the persons name and
an
Identification number (Number field - Indexed No duplicates). I do not
want
this to be an Auto Number field.
When I run the report it lists all the records in Identification Number
order-no problem. eg: 1, 2, 3, 5, 7etc.
I would like a seperate report that just lists the numbers not allocated.
eg
from above 4,6

Any help would be appreciated
Click to expand...
Click to expand...
 
1. Choose the Modules tab of the Database window.

2. Click New. Access opens a code window.

3. Below the "Option..." lines, paste in everything from "Function ..." to
"End Function".

4. To check that Access understands it, choose Compile from the Debug menu.
If Access 2000 or 2002 gives an error on the first line, see:
http://allenbrowne.com/ser-38.html

5. To use the function to create 1000 records, press Ctrl+G to open the
Immediate Window, and enter:
? MakeData(1000)
Then look in the tblCount table to see if the 1000 records are there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Roger Bell said:
Thank you Allen for your advices. As I am not that good, could you please
enlighten me as to where I write the Function (is it a Query?) and is the
wording exactly as you have written ie: starting with the word Function
etc.

Sorry to be such a nuisance, but you will get me out of the rut I am in
and
Thank You immensely

Allen Browne said:
Roger, the numbers have to come from somewhere, so you will need a table
of
all the valid numbers. If you make a table named tblCount, with one
Number
field named CountID, you could use the function below to populate it.

You can then use the Unmatched Query Wizard to create a query that
selects
all records from tblCount that are not in your existing table.

Function MakeData(HowMany As Long)
Dim rs As DAO.Recordset
Dim lng As Long

Set rs = DBEngine(0)(0).OpenRecordset("tblCount", dbOpenDynaset)
For lng = 1 To HowMany
rs.AddNew
rs![CountID] = lng
rs.Update
Next
rs.Close
Set rs = Nothing
End Function

Roger Bell said:
I have designed a report based on a query that lists the persons name
and
an
Identification number (Number field - Indexed No duplicates). I do not
want
this to be an Auto Number field.
When I run the report it lists all the records in Identification Number
order-no problem. eg: 1, 2, 3, 5, 7etc.
I would like a seperate report that just lists the numbers not
allocated.
eg
from above 4,6

Any help would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
You must be fed up with me by now, Allen. When I Debug-Compile I am getting
a Compile Error User-defined type not defined. The second line is
highlighted as follows: rs As DAO.Recordset. I am using Access 2003
Can you solve?
Many thanks

Allen Browne said:
1. Choose the Modules tab of the Database window.

2. Click New. Access opens a code window.

3. Below the "Option..." lines, paste in everything from "Function ..." to
"End Function".

4. To check that Access understands it, choose Compile from the Debug menu.
If Access 2000 or 2002 gives an error on the first line, see:
http://allenbrowne.com/ser-38.html

5. To use the function to create 1000 records, press Ctrl+G to open the
Immediate Window, and enter:
? MakeData(1000)
Then look in the tblCount table to see if the 1000 records are there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Roger Bell said:
Thank you Allen for your advices. As I am not that good, could you please
enlighten me as to where I write the Function (is it a Query?) and is the
wording exactly as you have written ie: starting with the word Function
etc.

Sorry to be such a nuisance, but you will get me out of the rut I am in
and
Thank You immensely

Allen Browne said:
Roger, the numbers have to come from somewhere, so you will need a table
of
all the valid numbers. If you make a table named tblCount, with one
Number
field named CountID, you could use the function below to populate it.

You can then use the Unmatched Query Wizard to create a query that
selects
all records from tblCount that are not in your existing table.

Function MakeData(HowMany As Long)
Dim rs As DAO.Recordset
Dim lng As Long

Set rs = DBEngine(0)(0).OpenRecordset("tblCount", dbOpenDynaset)
For lng = 1 To HowMany
rs.AddNew
rs![CountID] = lng
rs.Update
Next
rs.Close
Set rs = Nothing
End Function

I have designed a report based on a query that lists the persons name
and
an
Identification number (Number field - Indexed No duplicates). I do not
want
this to be an Auto Number field.
When I run the report it lists all the records in Identification Number
order-no problem. eg: 1, 2, 3, 5, 7etc.
I would like a seperate report that just lists the numbers not
allocated.
eg
from above 4,6

Any help would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
Reference problem.

As mentioned previously:
If Access 2000 or 2002 gives an error on the first line, see:
http://allenbrowne.com/ser-38.html
Click to expand...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Roger Bell said:
You must be fed up with me by now, Allen. When I Debug-Compile I am
getting
a Compile Error User-defined type not defined. The second line is
highlighted as follows: rs As DAO.Recordset. I am using Access 2003
Can you solve?
Many thanks

Allen Browne said:
1. Choose the Modules tab of the Database window.

2. Click New. Access opens a code window.

3. Below the "Option..." lines, paste in everything from "Function ..."
to
"End Function".

4. To check that Access understands it, choose Compile from the Debug
menu.
If Access 2000 or 2002 gives an error on the first line, see:
http://allenbrowne.com/ser-38.html

5. To use the function to create 1000 records, press Ctrl+G to open the
Immediate Window, and enter:
? MakeData(1000)
Then look in the tblCount table to see if the 1000 records are there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Roger Bell said:
Thank you Allen for your advices. As I am not that good, could you
please
enlighten me as to where I write the Function (is it a Query?) and is
the
wording exactly as you have written ie: starting with the word Function
etc.

Sorry to be such a nuisance, but you will get me out of the rut I am in
and
Thank You immensely

:

Roger, the numbers have to come from somewhere, so you will need a
table
of
all the valid numbers. If you make a table named tblCount, with one
Number
field named CountID, you could use the function below to populate it.

You can then use the Unmatched Query Wizard to create a query that
selects
all records from tblCount that are not in your existing table.

Function MakeData(HowMany As Long)
Dim rs As DAO.Recordset
Dim lng As Long

Set rs = DBEngine(0)(0).OpenRecordset("tblCount", dbOpenDynaset)
For lng = 1 To HowMany
rs.AddNew
rs![CountID] = lng
rs.Update
Next
rs.Close
Set rs = Nothing
End Function

I have designed a report based on a query that lists the persons name
and
an
Identification number (Number field - Indexed No duplicates). I do
not
want
this to be an Auto Number field.
When I run the report it lists all the records in Identification
Number
order-no problem. eg: 1, 2, 3, 5, 7etc.
I would like a seperate report that just lists the numbers not
allocated.
eg
from above 4,6

Any help would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
Back
Top