Error with code for record set

[dwAccessUser32]

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!

 

[missinglinq]

I'm not exactly clear as to what you mean here about the code to "create" the text boxes if no ID is present (I've been up 72 hours) but I did notice two things. One is that ID numbers are usually the unique identifier for a record and therefore should be the Primary Key and thus required. Without them the record basically doesn't exist and thus the the phone numbers won't show up.

And two, if this PersonID is text (number datatypes should only be used if you're actually going to do math with them, not for a "number" that's used for other purposes) you need to change your DCount line

DCount("PersonID", "tblPhoneNumbers", "[PersonID] = " & Me.PersonID)

adding some single quotes:

DCount("PersonID", "tblPhoneNumbers", "[PersonID] = "' & Me!PersonID &[/color red] "'[/color red]")



The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[dwAccessUser32]

Missinglinq,

Thanks for the help. I will look at this.

PS

I am afraid to ask where you work if you have to stay up 72 hours.