Dlookup or Select?

[Thompyt]

I have an entity that will have added to its title ("TO'd to" and the destination (TO'd to 1-555I), if it is detached to another entity in the excel spreadsheet (Imported to Access table). The destination entity has ("TO'd frm" and the origin (TO'd frm 1-111F).

What my intentions is to find the information that meets the first criteria
If rV![Bumper # / Plat ID] Like "*TO'd frm 1-111F*" And rV![Equip LIN] = LinArray Then

Then I would like to get the information from that row of data:
rv![Title]


rv![Role / FE / Node Name]
rV![Bumper # / Plat ID]
rV![Networks]

and use that information to update the Networks Cell in the gaining place.

Use rv![Title] to translate of the detination unit to filter the origin paragraph down, then use the role name in origin to filter down some more, and then use the LIN even more.

Would this be done best in a Dlookup?

Private Sub TOdFires_Click()
Dim rV As DAO.Recordset
Dim i As Integer, LinArray As Variant
LinArray = Array("XB0145", "R57606", "XXB840", "XXB841", "R30343")
Set rV = CurrentDb.OpenRecordset("Data")
     Do While Not rV.EOF
        rV.Edit
For i = 1 To 3
    If rV![Bumper # / Plat ID] Like "*TO'd frm 1-111F*" And rV![Equip LIN] = LinArray Then
        If rV![Para Desc] = "RIPL " & i & "Hq" Then
           If rV![Role / FE / Node Name]..........
        End If
    End If
Next I

            rV![Networks] = Networks
                rV![Waveforms] = Wave
                rV.Update
                rV.MoveNext
                
     Loop
                
Set rV = Nothing

End Sub

All, before it gets said, I am aware of the name and space issue. I have to be able to return the updated spreadsheet as I received it.

[June7]

If you want to return the value of a single field of a single record, then DLookup(). But the code shows opening and looping through recordset to update every record with same two values. Exactly where would a DLookup() be utilized?

Need to better indent your code.

Have you tested it?

[aytee111]

You could do this in an update query too, using "IIF". The general rule for DLookups is that they are very slow, so a few records are ok but more than that it becomes a performance issue. Using code like you have above, depending on the number of records you will need to update, you can use FindFirst with a variable or read them all and only update the ones required.

[Thompyt]

June7,
i know my indenting sucks. What I provided in the attached db was a sanitized sample of the original. What I want to do is if the row information is repeated elsewhere, task organized to another entity, to update the networks column of the original and have a Kodak moment and make the destination the same.

aytee11, an update query would be an acceptable way to do it, but how?

You have the destination information weeded out by the bumper TO'd from.... but the title of the destination is different from the origin. You can determine the part of the origin to look at in this instance by what unit it it ended (Ex. ACO 1-555) means it identifies with [Para Desc] -IBN1FSP.

[aytee111]

Your code has 3 If statements, no need for this, you could have put them all into one line using "And's". Same for a query, in the criteria line you would have this same statement. The Para Desc can be Like "RPL ?Hq", or type them all out. The array can be In("XB0145", "R57606", "XXB840", "XXB841", "R30343"). I don't quite understand what you are describing above, however.

[Thompyt]

aytee11,
I have tried with the - "For next I" and always only got the last result. I can't use "RPL ?Hq" because the nets are different between 1, 2, and 3.

Origin


Destination


I need the origin Nets Column to be transferred to the Destination with the correct LIN and correct Role.

Note I had to add the Destination role information to the table as I did not have any dismounted.


How can I do "In("XB0145", "R57606", "XXB840", "XXB841", "R30343")" if the array criteria is used to limit what you are looking for in rV1![Equip Lin]? Don't you have to reference the field?

[aytee111]

I need some real information. The database you sent does not contain the same tables you are talking about here. Also, if you could show samples - the Origin table above and what will be in the Nets field on Destination.