How to Convert field value to field name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I hope someone can tell me how to “convert†field value to field name

I have a table with test results for a lot of samples
I have different field name which is named equivalent to the type of the
microbe 16, 33, 41, 67 etc.
Each record consist of a sample with 0 to 4 test results stored as Res1,
Res2, Res3, Res4
(i.e. Res1 = 16, Res2 = 33), data type = text

Interesting field names for this record are: SampleID, 16, 33, Res1, res2
Values are imported from another system and the record will be as such (i.e.):
8765,,,16,33
I want to make a procedure which store the Res1-value (16) into the field
name field name with same value (16) (and the Res2-value (33) into the field
name with same value (33)
After the procedure the values in the same record should be: 8765,16,33,,,

If having a field name with only numbers creates problem, the field names
could be initiated with MIC (i.e. MIC16, MIC33, MIC41, MIC67)

*gublues
 
gublues said:
I hope someone can tell me how to “convert” field value to field name

I have a table with test results for a lot of samples
I have different field name which is named equivalent to the type of the
microbe 16, 33, 41, 67 etc.
Each record consist of a sample with 0 to 4 test results stored as Res1,
Res2, Res3, Res4
(i.e. Res1 = 16, Res2 = 33), data type = text

Interesting field names for this record are: SampleID, 16, 33, Res1, res2
Values are imported from another system and the record will be as such (i.e.):
8765,,,16,33
I want to make a procedure which store the Res1-value (16) into the field
name field name with same value (16) (and the Res2-value (33) into the field
name with same value (33)
After the procedure the values in the same record should be: 8765,16,33,,,

If having a field name with only numbers creates problem, the field names
could be initiated with MIC (i.e. MIC16, MIC33, MIC41, MIC67)


You can use this kind of syntax:
Me("MIC" & Me.res1) =Me.res1
I haven't tried it, but this might(?) even work:
Me(CStr(Me.res1)) =Me.res1
 
Thanks,
Your suggestion converted field value to field name.
Next challenge is related to looping.
My procedure does not move to next record. It goes in an endless loop.
I use a query (Testres1)

Private Sub cmdKonverter_Click()

Dim T As Integer
Dim rs As Recordset

With Me.RecordsetClone
.MoveLast
.MoveNext
T = .RecordCount

If T > 0 Then
.MoveFirst
Do Until .EOF

If IsNull(HPVTYPE_1) Then
GoTo Avslutt
End If
Me("HPV" & Me.HPVTYPE_1) = Me.HPVTYPE_1

If IsNull(HPVTYPE_2) Then
GoTo Avslutt
End If
Me("HPV" & Me.HPVTYPE_2) = Me.HPVTYPE_2

If IsNull(HPVTYPE_3) Then
GoTo Avslutt
End If
Me("HPV" & Me.HPVTYPE_3) = Me.HPVTYPE_3

If IsNull(HPVTYPE_4) Then
GoTo Avslutt
End If
Me("HPV" & Me.HPVTYPE_4) = Me.HPVTYPE_4
Avslutt:
.MoveNext
Loop
End If
End With
End Sub


Marshall Barton skrev:
 
gublues said:
Your suggestion converted field value to field name.
Next challenge is related to looping.
My procedure does not move to next record. It goes in an endless loop.
I use a query (Testres1)

Private Sub cmdKonverter_Click()

Dim T As Integer
Dim rs As Recordset

With Me.RecordsetClone
.MoveLast
.MoveNext
T = .RecordCount

If T > 0 Then
.MoveFirst
Do Until .EOF

If IsNull(HPVTYPE_1) Then
GoTo Avslutt
End If
Me("HPV" & Me.HPVTYPE_1) = Me.HPVTYPE_1

If IsNull(HPVTYPE_2) Then
GoTo Avslutt
End If
Me("HPV" & Me.HPVTYPE_2) = Me.HPVTYPE_2

If IsNull(HPVTYPE_3) Then
GoTo Avslutt
End If
Me("HPV" & Me.HPVTYPE_3) = Me.HPVTYPE_3

If IsNull(HPVTYPE_4) Then
GoTo Avslutt
End If
Me("HPV" & Me.HPVTYPE_4) = Me.HPVTYPE_4
Avslutt:
.MoveNext
Loop
End If
End With
End Sub


I'm having some difficulty following the code, but I do not
see how it can go into an endless loop.

Why are you looping through all the records in the form's
record source without ever referring to a field in the
recordset?

Note that you should not do a .Move... ubtil after you check
that there is at least one record:

With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
. . .
 
Back
Top