If Then OR Select Case to populate a field...

[Elijahnzl]

Hi All

I have a Form = Form1
I have a Subform = Sub1

In Sub1 I have four Fields: Name, Work, Mobile, Email
The Name field is always populated.

In Form1 I have a Memo field which I need to populate by concatenating the four Fields from Sub1.

All clear so far?

The Project: an organisations telephone directory.

The issue is this:
Whilst the name Field is always populated the other three aren't.

I would like to have rows (paragraphs) in the Memo field that display the contents of the four Sub1 Fields as long as they are not empty and in the same order each time.

Would this require a SELECT CASE query with an abundance of IF... Then's or is there an easier way around this issue?

Your input would be most helpful. Thank you.

 

[Elijahnzl]

Thanks for your reply Pat. Below is thecode that almost doea what I need it to do. The problem now is that I get a run-time error code 3021 because when I rs.MoveNext the record has no data.

I figure that I there is something wrong however I do not know what it is. Can someone please help me solve this (probably easy) puzzle? Thanks. :banghead:

Do While Not rs.EOF
If rs!Individual_Work <> 0 Then
iWork = "(wk) " & rs!Individual_Work & " "
Else: iWork = ""
End If

If rs!Individual_Mobile <> 0 Then
iMobile = "(mob) " & rs!Individual_Mobile & " "
Else: iMobile = ""
End If

If rs!Individual_Email <> 0 Then
iEMail = "(email) " & rs!Individual_Email
Else: iEMail = ""
End If

Details = rs!Individual_Name & ": " & iWork & iMobile & iEMail

rs.MoveNext
Details2 = rs!Individual_Name & ": " & iWork & iMobile & iEMail

If rs!Individual_Work <> 0 Then
iWork = "(wk) " & rs!Individual_Work & " "
Else: iWork = ""
End If

If rs!Individual_Mobile <> 0 Then
iMobile = "(mob) " & rs!Individual_Mobile & " "
Else: iMobile = ""
End If

If rs!Individual_Email <> 0 Then
iEMail = "(email) " & rs!Individual_Email
Else: iEMail = ""
End If

Family_Names.SetFocus
Family_Names.Text = AdultName

Family_Children.SetFocus
Family_Children.Text = Children

memIndividual_Details = Details & vbCrLf & Details2
Loop

 

[Reflex_ht]

Have you tried the Nz function to determinate if some of your variables has Null, 0 , "" or " " ?

I hate the damn Null and notihng values in Access