I have a form that allows a user to enter information on a person. One of the pieces of information that is collected is the phone number of the person. It does this with 4 different text boxes:

txtHome
txtWork
txtMobile
txtFax

The problem is that when there is no record for a person the textboxes vanish as I scroll through 10 or so different entries on the form.

The code to generate the 4 different phone number textboxes is shown below:

Private Sub Form_Current()

Dim rs As New ADODB.Recordset
Dim i As Integer
If Me.NewRecord And IsNull(Me.PersonID) Then Exit Sub

If DCount("PersonID", "tblPhoneNumbers", "[PersonID] = " & Me.PersonID) = 0 Then
    rs.Open "tblPhoneNumbers", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        For i = 1 To DCount("*", "tluPhoneNumberTypes")
            rs.AddNew
            rs!PhoneNumberTypeID = i
            rs!PersonID = Me.PersonID
            rs.Update
        Next i
    Me.[tblPhoneNumbers subform7].Requery
End If

End Sub


And the code that I tried to get to work for creating the textboxes if no PersonID record exists doesn't work:

Private Sub List131_AfterUpdate()
Me.Dirty = False
Call Form_Current
End Sub


List131 is a listbox that lets a user select the category of person (e.g., salesman, manager, etc.)

Can anyone offer any advice on how to solve this problem? Again, the problem is that when I open the form and browse through the 10 or so different records the textboxes are fine and fill with values that come from the database. However, as soon as no PersonID from tblPeople exists the 4 textboxes disappear and I cannot enter a value to create a new record.

Any advice would be greatly appreciated.

Thanks!

Why would you need to remove the text boxes if there was no record? Why not remove the code and just have all the data entry pieces available for new reocrds?

Otherwise, you can set the text boxes to be Visible = No, and then write code to make them visible when a new record was created. I can see where you might not want someone creating a new record by just entering a phone number and no name, but just disable them until a name is entered.

HTH

mmcdonal

Thank you so much for your reply.

I want when there is no record for a person to create a new record. This actually works, except for phone numbers. So, when I rifle through the list of records on my form, everything is fine until I reach the end of the records for people. At this point the form brings up all the other textboxes (e.g., address, etc.) but the phone number textboxes vanish from the form.

I don't want them to vanish. I want them to bring up new empty textboxes that will allow data entry. Any idea why they are vanishing and how to get them not to vanish?

Thanks again.

Check the form events and see if they are being set to visible = False on some condition, and then remove those conditions. Check the On Current event of the form.

mmcdonal

Thanks for your help again. I checked the form and don't have any event for the On Current. The only code that I have for the subform is:

Private Sub Form_Current()
Dim rs As New ADODB.Recordset
Dim i As Integer
If Me.NewRecord And IsNull(Me.PersonID) Then Exit Sub
If DCount("*", "qryDoPhonesExist") = 0 Then
    rs.Open "tblPhoneNumbers", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        For i = 1 To DCount("*", "tluPhoneNumberType")
            rs.AddNew
            rs!PhoneNumberTypeID = i
            rs!PersonID = Me.PersonID
            rs.Update
        Next i
'Me.Dirty = False
Me.Dirty = False
Call Form_Current
    Me.frmPhoneNumbersSubform7.Requery
End If

End Sub

Private Sub WorkPhone_AfterUpdate()
Me.PhoneNumberTypeID = 2
End Sub

Private Sub Form_AfterUpdate()
Me.Dirty = False
Call Form_Current
End Sub

Private Sub Text25_AfterUpdate()
Me.Dirty = False
Call Form_Current
End Sub


Any ideas? Again, I don't want the textboxes to disappear. I want to have the option for someone to enter a new record and type in a new phone number.

Thanks again!