Combo Box Question

[jeremy0028]

I have a table called diagnosis which has the following
Diagnosis, description

On a form i have 4 combo box that look up the diagnosis table i also have a field called dx pointers

the 4 combo box are labled dx1, dx2, dx3 dx4

What i want do to is if a user select dx 1 it will show
in the dx pointer field as 1 if user selects dx1 and dx2
combo it will show in the dx pointer field as 1,2 and so forth so if user select all four dx combo in the pointer
field it would show as 1,2,3,4 any ideas

 

[Lilliabeth]

YOu want to store 1,2,3,4 in a single field?

 

[jeremy0028]

Yes i want to store it in a single field

 

[Remou]

I think I'm with Lilliabeth, but anyway:

Sub SortDx()
    Dim i1, i2, i3, i4
    If InStr(Me!dxpointers, "1") > 0 Then i1 = "1"
    If InStr(Me!dxpointers, "2") > 0 Then i2 = "2"
    If InStr(Me!dxpointers, "3") > 0 Then i3 = "3"
    If InStr(Me!dxpointers, "4") > 0 Then i4 = "4"
    Me!dxpointers = i1 & i2 & i3 & i4
End Sub

Private Sub dx1_AfterUpdate()
    Me![dxpointers] = "1" & Me![dxpointers]
    SortDx
End Sub

Private Sub dx2_AfterUpdate()
    Me![dxpointers] = "2" & Me![dxpointers]
    SortDx
End Sub

Private Sub dx3_AfterUpdate()
    Me![dxpointers] = "3" & Me![dxpointers]
    SortDx
End Sub

Private Sub dx4_AfterUpdate()
    Me![dxpointers] = "4" & Me![dxpointers]
    SortDx
End Sub

Is that what you mean?

 

[Remou]

Posted too soon. I have not said anything about nulls or blanks because I do not know how your combo boxes work, so the above is only a rough idea.

 

[RoyVidar]

- nother view, perhaps close to Lilliabeth's -

No, you don't want to store repeating groups. You want to read about Fundamentals of Relational Database Design

A concatenation like that should only be performed for display purposes, never be stored in a field (unless you really, really, really understand the implications)

Roy-Vidar

 

[jeremy0028]

Exactly what i mean but where would i put the
also does that include the commas ex 1,2,3,4 in the dx pointer field.

Sub SortDx()
Dim i1, i2, i3, i4
If InStr(Me!dxpointers, "1") > 0 Then i1 = "1"
If InStr(Me!dxpointers, "2") > 0 Then i2 = "2"
If InStr(Me!dxpointers, "3") > 0 Then i3 = "3"
If InStr(Me!dxpointers, "4") > 0 Then i4 = "4"
Me!dxpointers = i1 & i2 & i3 & i4
End Sub

 

[Remou]

Paste the whole lot, including SortDx() into a (test) form module. Commas can be included easily enough. Either:
[tt]If InStr(Me!dxpointers, "1") > 0 Then i1 = "1,"
If InStr(Me!dxpointers, "2") > 0 Then i2 = "2,"
If InStr(Me!dxpointers, "3") > 0 Then i3 = "3,"
If InStr(Me!dxpointers, "4") > 0 Then i4 = "4"[/tt]

Or
[tt]Me!dxpointers = i1 & "," & i2 & "," & i3 & "," & i4[/tt]

Mutter, mutter RoyVidar mutter link mutter

 

[RoyVidar]

Talking about my favourite Rammstein record? ;-)

Roy-Vidar

 

[Remou]

Did they cover Al Jolson?