Entering a range of numbers in a many to many set up.

[IPO]

I want to define the range by putting numbers into 2 different fields? I have telephone extension table that relates back to telephone number table, the telephone number and extension tables use a many to many set up.

I want to enter the first telephone extension and the last telephone extension and have the numbers in between automatically populate the extension table. These extensions must be unique and the range must have the option to be expanded to from time to time.

Can someone assist.

I did search the forum but the closest to what I require is http://www.access-programmers.co.uk/forums/showthread.php?t=64735&highlight=define+range+numbers . However, this doesn't fully cover with a many to many build.

Any help appreciated.

BTW this also relates to my other query - http://www.access-programmers.co.uk/forums/showthread.php?t=190021&page=2

Thanks,
IPO

 

[namliam]

You shouldnt have a many to many, instead you would have 1 to many and many to one relationships

To make the numbers, use VBA to loop thru the numbers entered on your form, then append them to your tables/relationships as needed.

 

[IPO]

Hi Namliam,

The easiest way to explain would be that RefA is a telephone number and RefB relates to 30 extensions connected to the telephone number, each extension will have its own data that need to be recorded and made active/nonactive whenever a user starts/leaves the company.

No. 1124 contains 30 extensions 01, 02, 03 ect up to 30
No. 1101: contains 60 extensions 01, 02, 03 up to 60
ect
ect

As you can see, there can be a large amount of extensions showing as '01', '02' ect - Is 1 to many the correct way building the tables??

"To make the numbers, use VBA to loop thru the numbers entered on your form, then append them to your tables/relationships as needed." - can you clarify further please??

Btw this is my first db build.

Thanks,
IPO

 

[DCrake]

Think about it this way. the main number plus the extension number makes it unique. Therefore even though there may be 20 main lines and numerous extensions per line each one has to be unique.

It may be that in your extensions table 01 appears 20 times your two part key will make it unique

Main Lines Table

ID : 1 - Primary Key
MainLineNo : 1234
Other Info : ....

ID : 2 - Primary Key
MainLineNo : 1011
Other Info : ....


Extensions
ID : 1
MainLineFK : 1011 } Primary
ExtensionNo : 0001 } Keys

ID : 2
MainLineFK : 1011 } Primary
ExtensionNo : 0002 } Keys

ID : 2
MainLineFK : 1011 } Primary
ExtensionNo : 0003 } Keys

And as mailman said you only need a 1:many relationship

 

[IPO]

Thanks, it's clear now. Any help with looping thru the numbers appreciated?

 

[IPO]

Private Sub cmdAddRange_Click()

Dim lngExtension As Long

For lngExtension = Me.txtExtensionStart To Me.txtExtensionEnd
CurrentProject.Connection.Execute _
"INSERT INTO Extension (Extension, Telephonenumber) " & _
"VALUES(" & lngExtension & "," & Me.Telephonenumber ");"

Next lngExtension

End Sub


Am I on the right track???

 

[namliam]

CurrentProject.Connection.Execute _
...

Am I on the right track???

Not sure on the exact command, but looks fine.

I would probably use "Currentdb.Execute" intead of that command, then again...

Or use DAO or ADO to open the table and append values.

 

[IPO]

Hi,

I've had a little success in creating the VB code, however it's the SQL string giving me issues now

Private Sub AddRange_Click()
Dim lngExtension As Long
For lngExtension = CLng(ExtensionStart.Value) To CLng(ExtensionEnd.Value)
CurrentProject.Connection.Execute ("INSERT INTO Extension (Extension, Telephonenumber) " & _
"VALUES(" & lngExtension & "," & Telephonenumber.Value & ");")

Next
End Sub

Run-time error '-2147217900(80040e14)
Syntax error in INSERT INTO statement

 

[DCrake]

Dim Rs As DAO.Recordset

Set Rs = CurrentDb.OpenRecordset("TblExtensions")

For x = ExtensionStart To ExtensionEnd
   Rs.AddNew
   Rs.TelephoneNumber = TelNo
   Rs.Extension = Format(X,"0000")
   Rs.Update
Next x

Rs.Close
Set Rs = Nothing

Code simplified for brevity.

This assumes that all extension number are sequential and there are no missing numbers.

The format function ensures that all leading zeros are included in extension. So if range it 1 to 30 it would add 0001 to 0030. based on a a 4 digit extension number.

 

[IPO]

Hi David,
Thanks for the code. Although I've been able to amend to suit, I'm still having a issues

I can can populate tbl_Extensions with the new extensions (e.g 1 - 10), however the telephone number from tbl_telphonenumber table (or form) won't pull across.

How do I reference the first table?

See below

Private Sub AddRange_Click()
Set rs = CurrentDb.OpenRecordset("TblExtensions")
For x = ExtensionStart To ExtensionEnd
rs.AddNew
rs.Extension = x
rs.Update
Next x
rs.Close
Set rs = Nothing
End Sub

BTW way, I'm hopeless with VB, it's SQL I work with predominantly.

Thanks

 

[IPO]

I've managed to get it working, cheers