Problem adding a subform record

Bruce,

Add this line of code:

Me.lstPhones = -1

at the end of the Current event code, so whenever you move into another
record no phone is selected.

To edit, you can still use an InputBox. The idea is that you first
delete the old phone by means of a DELETE query, and then add the new
one in the same way as you did with the Add button. Sample code:

Private Sub cmdEditPhone_Click()
Dim strOld As String, strNew As String, strSQL As String
Dim db As Database
If Me.lstPhones = -1 Then
MsgBox "You must select a phone number to edit.", vbExclamation, _
"Cannot Edit Number"
Exit Sub
End If
strOld = Me.lstPhones
strNew = InputBox("Please type in the phone number to replace:" & vbCrLf & _
"* " & strOld, "Edit Phone Number", strOld)
If strNew = "" Then Exit Sub
Set db = CurrentDb
strSQL = "DELETE FROM tblPhone WHERE ContactID = " & _
Me.ContactID & " AND Phone = '" & strOld & "'"
db.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO tblPhone ( ContactID, Phone ) " & _
"SELECT " & Me!ContactID & ", '" & strNew & "';"
db.Execute strSQL, dbFailOnError
Me.lstPhones.Requery
Me.lstPhones = strNew
Set db = Nothing
End Sub

Of course, you could use a recordset operation instead:

Private Sub cmdEditPhone_Click()
Dim strOld As String, strNew As String, strSQL As String
Dim db As Database
Dim rs As New ADODB.Recordset
If Me.lstPhones = -1 Then
MsgBox "You must select a phone number to edit.", vbExclamation, _
"Cannot Edit Number"
Exit Sub
End If
strOld = Me.lstPhones
strNew = InputBox("Please type in the phone number to replace:" & vbCrLf & _
"* " & strOld, "Edit Phone Number", strOld)
If strNew = "" Then Exit Sub
rs.Open "SELECT Phone FROM tblPhone WHERE ContactID = " & _
Me.ContactID & " AND Phone = '" & strOld & "'", _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.MoveFirst
rs.Fields(0) = strNew
rs.Update
rs.Close
Set rs = Nothing
Me.lstPhones.Requery
Me.lstPhones = strNew
End Sub

Regards,
Nikos

Oops! In the recordset operation solution, you do not need to

Dim db As Database

It was forgotten there from the action query solution.

Nikos

Thanks once again, Nikos. I have it working smoothly now, and with much
less effort than I had originally used. This stuff will be valuable far
beyond the current project. That new line of code is wonderfully simple,
and does the trick. I really appreciate the time you have taken to explain
all of this and to write the sample code. This thread is going into my
personal library of Access Help documents. I am using the first option for
editing the number because I understand it; I prefer to use code I
understand, so if it needs adjusting I know what to do. The recordset
option is a bit beyond me in a few places, so I will save it for future
reference.

"Nikos Yannacopoulos" wrote in message
...
Bruce,

Add this line of code:

Me.lstPhones = -1

at the end of the Current event code, so whenever you move into another
record no phone is selected.

To edit, you can still use an InputBox. The idea is that you first delete
the old phone by means of a DELETE query, and then add the new one in the
same way as you did with the Add button. Sample code:

Private Sub cmdEditPhone_Click()
Dim strOld As String, strNew As String, strSQL As String
Dim db As Database
If Me.lstPhones = -1 Then
MsgBox "You must select a phone number to edit.", vbExclamation, _
"Cannot Edit Number"
Exit Sub
End If
strOld = Me.lstPhones
strNew = InputBox("Please type in the phone number to replace:" & vbCrLf &
_
"* " & strOld, "Edit Phone Number", strOld)
If strNew = "" Then Exit Sub
Set db = CurrentDb
strSQL = "DELETE FROM tblPhone WHERE ContactID = " & _
Me.ContactID & " AND Phone = '" & strOld & "'"
db.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO tblPhone ( ContactID, Phone ) " & _
"SELECT " & Me!ContactID & ", '" & strNew & "';"
db.Execute strSQL, dbFailOnError
Me.lstPhones.Requery
Me.lstPhones = strNew
Set db = Nothing
End Sub

Of course, you could use a recordset operation instead:

Private Sub cmdEditPhone_Click()
Dim strOld As String, strNew As String, strSQL As String
Dim db As Database
Dim rs As New ADODB.Recordset
If Me.lstPhones = -1 Then
MsgBox "You must select a phone number to edit.", vbExclamation, _
"Cannot Edit Number"
Exit Sub
End If
strOld = Me.lstPhones
strNew = InputBox("Please type in the phone number to replace:" & vbCrLf &
_
"* " & strOld, "Edit Phone Number", strOld)
If strNew = "" Then Exit Sub
rs.Open "SELECT Phone FROM tblPhone WHERE ContactID = " & _
Me.ContactID & " AND Phone = '" & strOld & "'", _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.MoveFirst
rs.Fields(0) = strNew
rs.Update
rs.Close
Set rs = Nothing
Me.lstPhones.Requery
Me.lstPhones = strNew
End Sub

Regards,
Nikos

By the way, the recordset operation option is more efficient in terms of
resource requirements, as each action query employs a recordset
operation behind the scenes, so it's one vs. two.
Of course, for this use (one record in a local table) you will never be
able to tell the difference in performance, but in more demanding
situations it might become noticeable.

Regards,
Nikos

There's one more thing I need to learn, then. Seriously, though, thanks for
the information. Again, it could be significant beyond the current project.

"Nikos Yannacopoulos" wrote in message
...
By the way, the recordset operation option is more efficient in terms of
resource requirements, as each action query employs a recordset operation
behind the scenes, so it's one vs. two.
Of course, for this use (one record in a local table) you will never be
able to tell the difference in performance, but in more demanding
situations it might become noticeable.

Regards,
Nikos