automating adding records to a table

[Paul]

I am looking for advice for the best way to solve a problem.

I am developing a system to record details of telephony extensions etc at a
workplace, some sites may have 200+.

I am trying to automate entering all the extension numbers, rather than
manually inputting them.

I have a form with two unbound fields, startExt and endExt to input and hold
the first and last extension numbers at site. I need a method to add records
to a table starting with the first number in the startExt box, then separate
records for all the numbers between the start and up to & including the end
number.

The table is called tblExtNo. The extension number needs to be added to a
field called actExtension.

 

[Graham R Seach]

Paul,

This should do it:

Public Function AddExtensions(CompanyID As Long, _
StartExt As Integer, EndExt As Integer) As Boolean
Dim ws As Workspace
Dim db As Database
Dim strSQL As String
Dim intExtension As Integer

On Error Goto Proc_Err

Set ws = DBEngine(0)
Set db = CurrentDb

ws.BeginTrans
For intExtension = StartExt To EndExt
strSQL = "INSERT INTO tblExtNo " & _
"SET actExtension = " & intExtension & " " & _
"WHERE CompanyID= " & CompanyID
db.Execute strSQL, dbFailOnError
Next intExtension
ws.CommitTrans

Proc_Exit:
On Error Resume Next
ws.Close
Set db = Nothing
Set ws = Nothing
Exit Function

Proc_Err:
ws.RollBack
DoCmd.Beep
MsgBox "Error " & Err.Number & vbCrLf _
Err.Description, vbOKOnly+vbExclamation, _
"Failed to add extensions"
Resume Proc_Exit
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Paul]

Thanks Graham

Need a bit of help.

I do not have a field call companyId so the code is falling over. can the
SQL be amended to exclude this statement ?

The actExtension in the table is a text field - does this make any
difference to the way the intExtension is handled.

Cheers

 

[Graham R Seach]

Of course you can omit it, but I presumed you have more than one customer.
You have to have some way to separate one customer's extensions from another
customer's extensions; if it's not by CustomerID, then perhaps it's by phone
number (or something else). Just substitute CompanyID for whatever your
defining attribute is.

Yes, actExtension being text does make a difference. Amend the code as
follows:
strSQL = "INSERT INTO tblExtNo " & _
"SET actExtension = """ & CStr(intExtension) & """ " & _
"WHERE CompanyID= " & CompanyID

This still assumes that intExtension is numeric. If it's not, let us know
the format of the extension numbers.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Paul]

sorry - still struggling.

I have stripped down the table to only the required fields.

I have put in on my website.

http://www.paulwest.me.uk/accessstuff/extensions.zip

can you take a look as i am getting errors which i do not know how to resolve.

cheers

Paul

 

[Norman Goetz]

On Sun, 12 Nov 2006 06:09:01 -0800, Paul wrote:
Hello Paul,

sorry - still struggling.

I have stripped down the table to only the required fields.

I have put in on my website.

http://www.paulwest.me.uk/accessstuff/extensions.zip

can you take a look as i am getting errors which i do not know how to resolve.

Use something like
strSQL = "INSERT INTO tblExtNo ( actExtension )"
strSQL = strSQL & vbCrLf & "VALUES(" & CStr(intExtension)
& ")"

hth


Norman Goetz

 

[Paul]

Thats done the trick,

Graham / Norman owe you a pint

seriously Thanks for all your help

Paul

 

[Graham R Seach]

Oh bloody hell! What was I thinking? Sorry Paul - thanks Norman.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia