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

LVL 4
kittenwhiskyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Like so for each case:


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

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
This will convert a Null value to an Empty String ("")

mx
0
Rey Obrero (Capricorn1)Commented:
why are you using the controlsource,
try


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.Value = rsContacts!txtLastName
    Me.txtFirstName.Value = rsContacts!txtFirstName
    Me.txtMiddleName.Value = rsContacts!txtMiddleName
    Me.txtTitle.Value = rsContacts!txtTitle
    Me.txtAddress1.Value = rsContacts!txtAddress1
    Me.txtAddress2.Value = rsContacts!txtAddress2
    Me.txtCity.Value = rsContacts!txtCity
    Me.txtState.Value = rsContacts!txtState
    Me.txtZip.Value = rsContacts!txtZip
    Me.txtWorkPhone.Value = rsContacts!txtWorkPhone
    Me.txtHomePhone.Value = rsContacts!txtHomePhone
    Me.txtCellPhone.Value = 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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

kittenwhiskyAuthor Commented:
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

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
kittenwhisky:

Are you trying to set the Name of the Control Source for the text box .. or the value of the text box?

mx
0
Rey Obrero (Capricorn1)Commented:


kittenwhisky

seen my post at http:#a29814467
0
kittenwhiskyAuthor Commented:
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

0
kittenwhiskyAuthor Commented:
gave some points to DatabaseMX, as you would've gotten there in the end, but capricorn answered first.
0
Rey Obrero (Capricorn1)Commented:
basically, using
Me.txtLastName=rsContacts!txtLastName
  without the .value also pertains to assigning the value to the control as

Me.txtLastName.Value = rsContacts!txtLastName


0
kittenwhiskyAuthor Commented:
yeah, given that .value is the default property for the textbox, I used the shorthand,
I'm lazy ;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.