Create Records through VBA

[ROUSOUG]

Hello all,

I am working in a telephone company. We were given a list of locations and the ranges of telephone numbers each location can handle.

for example: Location Number Range
A 220000 - 221999
A 232000 - 232199

B 223000 - 223999
B 235000 - 235099

I have designed a database in MS Access to match each telephone call with the location it is coming from, in order to calculate how many calls we have from a specific location.

The problem i have is that i need to have all the telephone numbers in a table (location, number / A, 223123).

Is there a way to use VBA to create the records of the table if i give the ranges?
i.e.
Location Number

Create A 220000
A 220001
A .
A .

Until A 221999

 

[KenReay]

Hi

Something like

For i = 220001 to 221999
INSERT INTO .... VALUES (i)
Next i

sorry cannot give the exact SQL because you did not give table name structure etc

Not sure you actually NEED to have all possible numbers in the table however, could you not do what you want to do using a query ?


Regards

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

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

UK

 

[PHV]

Have a look at the Between operator you can use in a criteria expression.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jimlee]

Hi, I haven't tried this as yet as I haven't got access on the computer i'm on but it should give the general idea...
You would have to run this subroutine for each location.

dim dbcurr as database
dim rst as recordset
set dbcurr = dbengine.databases(0).workspaces(0)
set rst = dbcurr.openrecordset("yourTableName")

dim start as integer
start = inputbox("Please enter start Number")
dim end as integer
end = inputbox("Please enter end Number")
dim Location as string
Location = inputbox("Please enter Location")

dim x as integer
with rst
for x = start to end
    .addNew
    .fields!Location = Location
    .fields!Number = x
    .edit
next x
end with

jimlad

 

[ROUSOUG]

Yes it is like the
For i = 220001 to 221999

The table name is "NUMBERING" and the fields "LOCATION" and "NUMBER".

I have tried to do this through a query using as criteria: >= StartNumber and <=EndNumber. However i had to put every time the numberrange in order to get the data of that range. since i have around 500 ranes, it is very time consuming to manually go through each exchange.

Is there a way for the same query to get the StartNumber and EndNumber from a table? i.e. Get range from record 1, calculate data, then go to record 2.

 

[PHV]

But why by the earth you want to store ALL the numbers ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ROUSOUG]

I want to avoid storing all these numbers, but i did not find the way to use a query that gets the criteria (from, to) from a table.

 

[PHV]

Have you read my first reply ?

 

[ROUSOUG]

I am trying to find out how to use the Between operator but there is no reference on it. Can you provide me with an example?

 

[ROUSOUG]

I have found the between operator but how can the query get the value1 and value2 (Between value1 And value2) from record1 of a table and then run the query again for the values of record2?

 

[PHV]

SELECT L.Location, C.PhoneNumber
FROM tblLocations AS L, tblCalls AS C
WHERE C.PhoneNumber Between L.StartRange And L.EndRange

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ROUSOUG]

Thanks
The final Code is:

ublic Sub addrange2()

Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("TelephonesPerLocation")
Set rs2 = db.OpenRecordset("Ranges")

Dim StartNo As Long
Dim EndNo As Long
Dim LocationCode As Long
Dim count As Long
Dim no As Long

With rs2

rs2.MoveLast
count = rs2.RecordCount
rs2.MoveFirst

For no = 1 To count

StartNo = rs2.Fields!from
EndNo = rs2.Fields!To
LocationCode = rs2.Fields!Location

Dim x As Long
With rs1
For x = StartNo To EndNo Step 1
.AddNew
.Fields!TelephoneNo = x
.Fields!Location = LocationCode
.Update
Next x
End With

rs2.MoveNext
Next no

End With

End Sub