asked on

ControlSource fails on Null values of connected recordset

I am populating record information into text boxes of an MS Access Form from another Access database table using an ADODB connection.

I link the ControlSource of each text box to the value of the record's appropriate field from the retrieved recordset rsContacts, the code breaks when the record field (in this case the Address2 text field) of the original recordset rsContacts is Null.

How do I allow for null values?

Sub PopulateControlsOnForm()

'populate the controls of the form with the values of the current record
'in the local disconnected recordset.

If Not rsContacts.BOF And Not rsContacts.EOF Then
    Me.txtLastName.ControlSource = rsContacts!txtLastName
    Me.txtFirstName.ControlSource = rsContacts!txtFirstName
    Me.txtMiddleName.ControlSource = rsContacts!txtMiddleName
    Me.txtTitle.ControlSource = rsContacts!txtTitle
    Me.txtAddress1.ControlSource = rsContacts!txtAddress1
    Me.txtAddress2.ControlSource = rsContacts!txtAddress2
    Me.txtCity.ControlSource = rsContacts!txtCity
    Me.txtState.ControlSource = rsContacts!txtState
    Me.txtZip.ControlSource = rsContacts!txtZip
    Me.txtWorkPhone.ControlSource = rsContacts!txtWorkPhone
    Me.txtHomePhone.ControlSource = rsContacts!txtHomePhone
    Me.txtCellPhone.ControlSource = rsContacts!txtCellPhone
ElseIf rsContacts.BOF Then
    'past beginning of recordset so move to next record
    rsContacts.MoveNext
ElseIf rsContacts.EOF Then
    'past end of recordsete so move to previous record
    rsContacts.MovePrevious
End If

End Sub

Open in new window

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Like so for each case:

Me.txtLastName.ControlSource = Nz(rsContacts!txtLastName,"")

mx

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

This will convert a Null value to an Empty String ("")

mx

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kittenwhisky

ASKER

I amended the code as you suggested databaseMX,

now the code doesn't break on the null fields anymore, but all the fields that do contain values show the error "#Name?" in the text boxes.

The non-null fields return a string value, which is of compatible type to the .ControlSource property, not sure where this new error comes from.

Sub PopulateControlsOnForm()

'populate the controls of the form with the values of the current record
'in the local disconnected recordset.
'use the same field names as the tblContacts from which it was generated
If Not rsContacts.BOF And Not rsContacts.EOF Then
    Me.txtLastName.ControlSource = Nz(rsContacts!txtLastName, "")
    Me.txtFirstName.ControlSource = Nz(rsContacts!txtFirstName, "")
    Me.txtMiddleName.ControlSource = Nz(rsContacts!txtMiddleName, "")
    Me.txtTitle.ControlSource = Nz(rsContacts!txtTitle, "")
    Me.txtAddress1.ControlSource = Nz(rsContacts!txtAddress1, "")
    Me.txtAddress2.ControlSource = Nz(rsContacts!txtAddress2, "")
    Me.txtCity.ControlSource = Nz(rsContacts!txtCity, "")
    Me.txtState.ControlSource = Nz(rsContacts!txtState, "")
    Me.txtZip.ControlSource = Nz(rsContacts!txtZip, "")
    Me.txtWorkPhone.ControlSource = Nz(rsContacts!txtWorkPhone, "")
    Me.txtHomePhone.ControlSource = Nz(rsContacts!txtHomePhone, "")
    Me.txtCellPhone.ControlSource = Nz(rsContacts!txtCellPhone, "")
ElseIf rsContacts.BOF Then
    'past beginning of recordset so move to next record
    rsContacts.MoveNext
ElseIf rsContacts.EOF Then
    'past end of recordsete so move to previous record
    rsContacts.MovePrevious
End If

End Sub

Open in new window

SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

kittenwhisky

seen my post at http:#a29814467

kittenwhisky

ASKER

hi capricorn1,

sorry I saw your post after commenting as I did not refresh the page. Thanks for spotting the ControlSource error, it was a legacy from the previous version of the code where the Form was directly bound to the table (before I used the ADODB connection).

Below the working code.

Thanks for your help guys!

Sub PopulateControlsOnForm()

'populate the controls of the form with the values of the current record
'in the local disconnected recordset.
'use the same field names as the tblContacts from which it was generated
If Not rsContacts.BOF And Not rsContacts.EOF Then
    Me.txtLastName = rsContacts!txtLastName
    Me.txtFirstName = rsContacts!txtFirstName
    Me.txtMiddleName = rsContacts!txtMiddleName
    Me.txtTitle = rsContacts!txtTitle
    Me.txtAddress1 = rsContacts!txtAddress1
    Me.txtAddress2 = rsContacts!txtAddress2
    Me.txtCity = rsContacts!txtCity
    Me.txtState = rsContacts!txtState
    Me.txtZip = rsContacts!txtZip
    Me.txtWorkPhone = rsContacts!txtWorkPhone
    Me.txtHomePhone = rsContacts!txtHomePhone
    Me.txtCellPhone = rsContacts!txtCellPhone
ElseIf rsContacts.BOF Then
    'past beginning of recordset so move to next record
    rsContacts.MoveNext
ElseIf rsContacts.EOF Then
    'past end of recordsete so move to previous record
    rsContacts.MovePrevious
End If

End Sub

Open in new window

kittenwhisky

ASKER

gave some points to DatabaseMX, as you would've gotten there in the end, but capricorn answered first.

Rey Obrero (Capricorn1)

basically, using
Me.txtLastName=rsContacts!txtLastName
without the .value also pertains to assigning the value to the control as

Me.txtLastName.Value = rsContacts!txtLastName

kittenwhisky

ASKER

yeah, given that .value is the default property for the textbox, I used the shorthand,
I'm lazy ;-)