Looping through Records

[Dan]

I have tried everything that I can find on the internet, but am still unable
to get this to work. I want the following code to find records in a table
that fit a certain criteria, then copy one field from the record to a text
box. It should then move to the next record that fits the criteria and copy
it to the next text box.

Any ideas on where I am going wrong. It is currently finding the first
record and and puting that field into every text box.

Thanks,

Private Sub SL3()
Dim db As Database
Dim rst As DAO.Recordset
Dim FldNm As String
Dim Box As String
Dim strValue As String
Dim x As Integer

Set db = CurrentDb()
Set rst = db.OpenRecordset("SL3NSNDetail", dbOpenDynaset)


' FldNm = "Nomen"
Box = "txtSL3Item"
x = 0

With rst
If Not (.EOF And .BOF) Then
rst.MoveFirst
Do Until rst.EOF
For x = 1 To 3

' strValue = "Select SL3NSNDetail.[NOMEN] FROM SL3NSNDetail" & _
' "WHERE SL3NSNDetail.[ID] = '" & Forms![IssueForm]![cboWpnType1] & "'"

strValue = _
DLookup("SL3NSNDetail.[NOMEN]", "SL3NSNDetail", "[ID] = '" &
Forms![IssueForm]![cboWpnType1] & "'")

Me.Controls(Box & x).Value = strValue
rst.MoveNext
Next
Loop
End If
End With

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

 

[Mike Painter]

I have tried everything that I can find on the internet, but am still
unable to get this to work. I want the following code to find
records in a table that fit a certain criteria, then copy one field
from the record to a text box. It should then move to the next
record that fits the criteria and copy it to the next text box.

Any ideas on where I am going wrong. It is currently finding the
first record and and puting that field into every text box.

Thanks,
strValue = _
DLookup("SL3NSNDetail.[NOMEN]", "SL3NSNDetail", "[ID] = '" &
Forms![IssueForm]![cboWpnType1] & "'")

Strvalue is always the same and based on Forms![IssueForm]![cboWpnType1]
If there are not three records you will throw an error message.

Usually something like WpnType1 or xxx1 implies an xxx2 and xxx3 and this
throws up red flags to us.

 

[Dan]

That portion of the string is working properly. The WHERE clause will be
the same always, or at least for each time that the ComboBox is used which
sets of this procedure.

I am looking for records in the table that the [ID] field in the table are
equal to the ComboBox. What I am not getting it to do is Iterate through
the table and find other values that are also equal to the ComboBox. What
is truly strange to me is that it seems to be pulling the last value where
the field is equal to the ComboBox. I would think that it would pull the
first.

Do you have any suggestions for another routine to find the value, compare
it to each record and then copy one field [NOMEN] from the record.

Thanks,

I have tried everything that I can find on the internet, but am still
unable to get this to work. I want the following code to find
records in a table that fit a certain criteria, then copy one field
from the record to a text box. It should then move to the next
record that fits the criteria and copy it to the next text box.

Any ideas on where I am going wrong. It is currently finding the
first record and and puting that field into every text box.

Thanks,
strValue = _
DLookup("SL3NSNDetail.[NOMEN]", "SL3NSNDetail", "[ID] = '" &
Forms![IssueForm]![cboWpnType1] & "'")

Strvalue is always the same and based on Forms![IssueForm]![cboWpnType1]
If there are not three records you will throw an error message.

Usually something like WpnType1 or xxx1 implies an xxx2 and xxx3 and this
throws up red flags to us.

 

[Mike Painter]

Dlookup returns a single value. It does not return a recordset. Each time
you go through the loop, you lookup the same thing based on what ever is
current in the combo box.

I would write a select statement that returned all records equal to what you
want then move through that set.
If this will always return a fixed number of records I usually don't use a
loop.

With rst
.movefirst
!Box1 = .fieldy
.movenext
!Box2 = .fieldy
.movenext
!Box3 = .fieldy
'cleanup
end with

Usually when I do this it's for a larger set of fields and this "picture"
makes it easier for me to see where my error is.

I assume this is a mandated form because if not a subform and properly
related tables makes this a trivial problem requirening no programming.
Same if it is a report.

If the client was mine I would point out that it will cost more to do and if
they ever decide on a field4 it will cost them again.
Down is cheaper than across.

That portion of the string is working properly. The WHERE clause
will be the same always, or at least for each time that the ComboBox
is used which sets of this procedure.

I am looking for records in the table that the [ID] field in the
table are equal to the ComboBox. What I am not getting it to do is
Iterate through the table and find other values that are also equal
to the ComboBox. What is truly strange to me is that it seems to be
pulling the last value where the field is equal to the ComboBox. I
would think that it would pull the first.

Do you have any suggestions for another routine to find the value,
compare it to each record and then copy one field [NOMEN] from the
record.

Thanks,

I have tried everything that I can find on the internet, but am
still unable to get this to work. I want the following code to find
records in a table that fit a certain criteria, then copy one field
from the record to a text box. It should then move to the next
record that fits the criteria and copy it to the next text box.

Any ideas on where I am going wrong. It is currently finding the
first record and and puting that field into every text box.

Thanks,
strValue = _
DLookup("SL3NSNDetail.[NOMEN]", "SL3NSNDetail", "[ID] = '" &
Forms![IssueForm]![cboWpnType1] & "'")

Strvalue is always the same and based on
Forms![IssueForm]![cboWpnType1] If there are not three records you
will throw an error message.

Usually something like WpnType1 or xxx1 implies an xxx2 and xxx3 and
this throws up red flags to us.

 

[Dan]

Dlookup returns a single value. It does not return a recordset. Each time
you go through the loop, you lookup the same thing based on what ever is
current in the combo box.

I would write a select statement that returned all records equal to what you
want then move through that set.
If this will always return a fixed number of records I usually don't use a
loop.

With rst
.movefirst
!Box1 = .fieldy
.movenext
!Box2 = .fieldy
.movenext
!Box3 = .fieldy
'cleanup
end with

Usually when I do this it's for a larger set of fields and this "picture"
makes it easier for me to see where my error is.

I assume this is a mandated form because if not a subform and properly
related tables makes this a trivial problem requirening no programming.
Same if it is a report.

If the client was mine I would point out that it will cost more to do and if
they ever decide on a field4 it will cost them again.
Down is cheaper than across.

That portion of the string is working properly. The WHERE clause
will be the same always, or at least for each time that the ComboBox
is used which sets of this procedure.

I am looking for records in the table that the [ID] field in the
table are equal to the ComboBox. What I am not getting it to do is
Iterate through the table and find other values that are also equal
to the ComboBox. What is truly strange to me is that it seems to be
pulling the last value where the field is equal to the ComboBox. I
would think that it would pull the first.

Do you have any suggestions for another routine to find the value,
compare it to each record and then copy one field [NOMEN] from the
record.

Thanks,

Dan wrote:
I have tried everything that I can find on the internet, but am
still unable to get this to work. I want the following code to find
records in a table that fit a certain criteria, then copy one field
from the record to a text box. It should then move to the next
record that fits the criteria and copy it to the next text box.

Any ideas on where I am going wrong. It is currently finding the
first record and and puting that field into every text box.

Thanks,
strValue = _
DLookup("SL3NSNDetail.[NOMEN]", "SL3NSNDetail", "[ID] = '" &
Forms![IssueForm]![cboWpnType1] & "'")

Strvalue is always the same and based on
Forms![IssueForm]![cboWpnType1] If there are not three records you
will throw an error message.

Usually something like WpnType1 or xxx1 implies an xxx2 and xxx3 and
this throws up red flags to us.

 

[Dan]

Thanks,

There are over 100 boxes that may or may not get filled from this table.
These boxes are filled from one field in the table based on the criteria
which is another field. If [ID] is equal to the ID that they are using,
then I want to fill the TextBoxes with the values in [NOMEN]. This could
fill between 0 - 114 boxes. In the end, it is filling out a form that we
have used for more than 20 years and is still relevant for what we do today.
I am just trying to automate the process.

Dlookup returns a single value. It does not return a recordset. Each time
you go through the loop, you lookup the same thing based on what ever is
current in the combo box.

I would write a select statement that returned all records equal to what you
want then move through that set.
If this will always return a fixed number of records I usually don't use a
loop.

With rst
.movefirst
!Box1 = .fieldy
.movenext
!Box2 = .fieldy
.movenext
!Box3 = .fieldy
'cleanup
end with

Usually when I do this it's for a larger set of fields and this "picture"
makes it easier for me to see where my error is.

I assume this is a mandated form because if not a subform and properly
related tables makes this a trivial problem requirening no programming.
Same if it is a report.

If the client was mine I would point out that it will cost more to do and if
they ever decide on a field4 it will cost them again.
Down is cheaper than across.

That portion of the string is working properly. The WHERE clause
will be the same always, or at least for each time that the ComboBox
is used which sets of this procedure.

I am looking for records in the table that the [ID] field in the
table are equal to the ComboBox. What I am not getting it to do is
Iterate through the table and find other values that are also equal
to the ComboBox. What is truly strange to me is that it seems to be
pulling the last value where the field is equal to the ComboBox. I
would think that it would pull the first.

Do you have any suggestions for another routine to find the value,
compare it to each record and then copy one field [NOMEN] from the
record.

Thanks,

Dan wrote:
I have tried everything that I can find on the internet, but am
still unable to get this to work. I want the following code to find
records in a table that fit a certain criteria, then copy one field
from the record to a text box. It should then move to the next
record that fits the criteria and copy it to the next text box.

Any ideas on where I am going wrong. It is currently finding the
first record and and puting that field into every text box.

Thanks,
strValue = _
DLookup("SL3NSNDetail.[NOMEN]", "SL3NSNDetail", "[ID] = '" &
Forms![IssueForm]![cboWpnType1] & "'")

Strvalue is always the same and based on
Forms![IssueForm]![cboWpnType1] If there are not three records you
will throw an error message.

Usually something like WpnType1 or xxx1 implies an xxx2 and xxx3 and
this throws up red flags to us.

 

[Dan]

It sounds like the easiest way to do this would be to make a query based on
the [ID] and then decide how many records it has found, then write those
results to that number of TextBoxes.

BTW, this is an unbound form. It was based on too many things to make it
manageable otherwise. This is also the first program that I have ever
written in Access.

Thanks again,

Dlookup returns a single value. It does not return a recordset. Each time
you go through the loop, you lookup the same thing based on what ever is
current in the combo box.

I would write a select statement that returned all records equal to what you
want then move through that set.
If this will always return a fixed number of records I usually don't use a
loop.

With rst
.movefirst
!Box1 = .fieldy
.movenext
!Box2 = .fieldy
.movenext
!Box3 = .fieldy
'cleanup
end with

Usually when I do this it's for a larger set of fields and this "picture"
makes it easier for me to see where my error is.

I assume this is a mandated form because if not a subform and properly
related tables makes this a trivial problem requirening no programming.
Same if it is a report.

If the client was mine I would point out that it will cost more to do and if
they ever decide on a field4 it will cost them again.
Down is cheaper than across.

That portion of the string is working properly. The WHERE clause
will be the same always, or at least for each time that the ComboBox
is used which sets of this procedure.

I am looking for records in the table that the [ID] field in the
table are equal to the ComboBox. What I am not getting it to do is
Iterate through the table and find other values that are also equal
to the ComboBox. What is truly strange to me is that it seems to be
pulling the last value where the field is equal to the ComboBox. I
would think that it would pull the first.

Do you have any suggestions for another routine to find the value,
compare it to each record and then copy one field [NOMEN] from the
record.

Thanks,

Dan wrote:
I have tried everything that I can find on the internet, but am
still unable to get this to work. I want the following code to find
records in a table that fit a certain criteria, then copy one field
from the record to a text box. It should then move to the next
record that fits the criteria and copy it to the next text box.

Any ideas on where I am going wrong. It is currently finding the
first record and and puting that field into every text box.

Thanks,
strValue = _
DLookup("SL3NSNDetail.[NOMEN]", "SL3NSNDetail", "[ID] = '" &
Forms![IssueForm]![cboWpnType1] & "'")

Strvalue is always the same and based on
Forms![IssueForm]![cboWpnType1] If there are not three records you
will throw an error message.

Usually something like WpnType1 or xxx1 implies an xxx2 and xxx3 and
this throws up red flags to us.