Msg box for errors

[IPO]

Hi Guys,
I'm attempting to create a error message to notify an end user whenever a record exists on a database. However, I'm getting an error @ "For Each record In rs.Extension"

Run-time error '3251':
Operation is not supported for this type of object

Thanks

Private Sub AddRange_Click()
Set rs = CurrentDb.OpenRecordset("tbl_extensions")
Dim isgood As Boolean
For x = ExtensionStart To ExtensionEnd
isgood = True
For Each record In rs.Extension
If x = record And rs_telephonenumber.record = telephonenumber Then
isgood = False
MsgBox ("Entry exists;" & x)
End If
Next
If isgood = True Then
rs.AddNew
rs.extension = x
rs.telephonenumber = telephonenumber
rs.telephonefk = TelephonenumberID
rs.Update
End If
Next x
rs.Close
Set rs = Nothing
End Sub

 

[KenHigg]

This looks like some cut a pasted code- ? For starters I'm not sure what rs.Extension is supposed to mean/do.

Second, why not use a dlookup()...

 

[DCrake]

Private Sub AddRange_Click()
Set rs = CurrentDb.OpenRecordset("tbl_extensions")
Dim isgood As Boolean
For x = ExtensionStart To ExtensionEnd
isgood = True
For Each record In rs.Extension
If x = record And rs_telephonenumber.record = telephonenumber Then
isgood = False
MsgBox ("Entry exists;" & x)
End If
Next
If isgood = True Then
rs.AddNew
rs.extension = x
rs.telephonenumber = telephonenumber
rs.telephonefk = TelephonenumberID
rs.Update
End If
Next x
rs.Close
Set rs = Nothing
End Sub

Would better coded as follows

Private Sub AddRange_Click()
Dim xGood As Integer
Dim xBad As Integer


For x = ExtensionStart To ExtensionEnd
   set rs = currentdb.OpenRecordset("Select * from tblExtensions Where Extension = '" & x & "'")

   If Rs.EOF and Rs.BOF Then
      xGood = xGood + 1
      Rs.AddNew
      rs.extension = Format(x,"0000")
      rs.telephonenumber = telephonenumber
      rs.telephonefk = TelephonenumberID
      Rs.Update
   Else
      xBad = xBad + 1
   End If
Next x
rs.Close
Set rs = Nothing

MsgBox xGood & " Extention(s) added" & vbnewline & xBad & " Extension(s) aready in use.",VbInformation+vbOkOnly,"Tel No:" & TelephoneNumber

End Sub

This way you are only displaying the message box as a summary not every time.

Do not know where you gor your code example from For Each record In rs.Extension never seen that used so that was the route of the syntax error.

You also seemed to have dropped the formatting of the extension number to preserve leading zeros. Have included it again.

 

[IPO]

Hi David,

The code is correct for the error message box - excellent, thanks. Unfortunately it doesn't reference specifically to the telephone number.

An example:
Telephone 9999 add 20 extensions results in 20 extensions being added. (i.e. no error)
Telephone 3333 add 15 extensions results in "0 extension (s) added 15 extension(s) already in use."
Telephone 3333 add 21 extensions results in "1 extension (s) added 20 extension(s) already in use."

If I add a new number to the database along with the relevant extension range, Access thinks that the previous 20 extensions entered are part of the same batch??

Any ideas??

 

[jdraw]

Hi David,

The code is correct for the error message box - excellent, thanks. Unfortunately it doesn't reference specifically to the telephone number.

An example:
Telephone 9999 add 20 extensions results in 20 extensions being added. (i.e. no error)
Telephone 3333 add 15 extensions results in "0 extension (s) added 15 extension(s) already in use."
Telephone 3333 add 21 extensions results in "1 extension (s) added 20 extension(s) already in use."

If I add a new number to the database along with the relevant extension range, Access thinks that the previous 20 extensions entered are part of the same batch??

Any ideas??

What exactly are you trying to do?
What exactly causes an error that you want to report?

 

[IPO]

I have a table of telephone numbers that relate to an extension table, every telephone number contains multipe extensions that record individual data.

I need the code to define a range of numbers but it must specifically relate to each individual telephone number.... the rest is explained in my previous posts.

Any assistance appreciated..

 

[DCrake]

For x = ExtensionStart To ExtensionEnd
   set rs = currentdb.OpenRecordset("Select * from tblExtensions Where Telephonenumber = '" & telephonenumber & "' And Extension = '" & Format(x,"0000") & "'")

   If Rs.EOF and Rs.BOF Then
      xGood = xGood + 1
      Rs.AddNew
      rs.extension = Format(x,"0000")
      rs.telephonenumber = telephonenumber
      rs.telephonefk = TelephonenumberID
      Rs.Update
   Else
      xBad = xBad + 1
   End If
Next x

Omission in code snippet forgot to filter the main telephone number. Try this example.

 

[IPO]

Morning David,

It is now referencing the telephone number, but allows duplicates to be added to the extension table??

 

[DCrake]

Can you post what code you are now using

 

[IPO]

Ah it was due to an Integer value - removed single quotes from

Extension = '" & Format(x,"0000") & "'")

Thanks for all your help.