Stringing & Concatenation !!!!!

[Milan]

Can you help out here People!!!!!!

Dim rst As Recordset, FieldID As Variant, FieldID1 As Variant
Dim strtest As Variant
Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset("QRYBAJCLASSPROCESS", dbOpenDynaset)
strtest = rst!Sequence

Do Until rst.EOF
With rst
FieldID = strtest
If Not IsNull(FieldID) Then
StrLoad1 = rst!StdJClass
StrLoad = DLookup(FieldID, "TblCwLoadscalesPc43", "PaxLoad=" & StrLoad1)
.Edit
![Quantity1] = StrLoad
.Update
End If
.MoveNext
End With
Loop
rst.Close


The Dlookup function uses FieldID uses the value in Strtest .This value is currently set to rst!Sequence , which is the name of a field in the QRYBAJCLASSPROCESS query. I need to get the procedure to loop 5 times and lookup 5 more field each time it goes through the loop so therefore -:

1st loop

strtest = rst!Sequence – the procedure will lookup field called seqence.

2nd loop

strtest = rst!Sequence1 – the procedure will lookup field called seqence1.

3rd loop

strtest = rst!Sequence2 – the procedure will lookup field called seqence2.

4th loop

strtest = rst!Sequence3 – the procedure will lookup field called seqence3.

5th loop

strtest = rst!Sequence4 – the procedure will lookup field called seqence4.



I was thinking about using the For & Next Statement to loop 5 times :
Dim intvalue as integer
Dim I as integer
Dim rst As Recordset, FieldID As Variant, FieldID1 As Variant
Dim strtest As Variant
Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset("QRYBAJCLASSPROCESS", dbOpenDynaset)

Intvalue = 5

For I = 1 to intvalue Step +1
strtest = rst!Sequence&intvalue
Do Until rst.EOF
With rst
FieldID = strtest
If Not IsNull(FieldID) Then
StrLoad1 = rst!StdJClass
StrLoad = DLookup(FieldID, "TblCwLoadscalesPc43", "PaxLoad=" & StrLoad1)
.Edit
![Quantity1] = StrLoad
.Update
End If
.MoveNext
End With
Loop
Next I



The problem I've got is with the Stringing of strtest. When I run the procedure it fails to recognise the value in Strtest as a field value and cause an error. Imagine the problem is with the string concatenation. Would appreciate your help here!!.


Thanks

Milan

 

[Mile-O]

Try this:

    Dim intCounter as Integer 
    Dim rst As Recordset

    Set rst = CurrentDb.OpenRecordset("QRYBAJCLASSPROCESS", dbOpenDynaset) 

    For intCounter = 1 to 5 Step 1
        With rst 
            Do Until .EOF 
            If Not IsNull(rst.Fields("Sequence" & IIf(intCounter = 1, "", intCounter) Then 
                .Edit 
                !Quantity1 = DLookup(rst.Fields("Sequence" & IIf(intCounter = 1, "", intCounter), _
                    "TblCwLoadscalesPc43", "PaxLoad = " & rst!StdJClass) 
                .Update 
            End If 
            .MoveNext
            Loop
        End With 
    Next intCounter

 

[Milan]

Hey!!!

Thank you very much for doing that for me!!!! I really appreciate it!!!you have saved me lots of brain ache!!!!

Thanks again!! is always really nice to have you wounderful people about to help out!!!!

Milan

 

[Milan]

Code Not Working!!!!Can You Help!!!

Hi,

I have a little problem -: this part of the code is coming up in red text showing and invaild code. Any ideas what might be wrong!!!

Thanks

Milan


'If Not IsNull(rst.Fields("Sequence" & IIf(intCounter = 1, "", intCounter) Then




!Quantity1 = DLookup(rst.Fields("Sequence" & IIf(intCounter = 1, "", intCounter), _
"TblCwLoadscalesPc43", "PaxLoad = " & rst!StdJClass)

 

[AncientOne]

'If Not IsNull(rst.Fields("Sequence" & IIf(intCounter = 1, "", intCounter)))

Some closing brackets are required: - you have three left brackets and only one right bracket in the expression. The error is repeated later in the code.

 

[Mile-O]

Whoops!