Loop through specific fields in a recordset ?

[Wayne-I-M]

Hi

Is it possible to loop through records on a continous form but only only for
each instance of a specific field ?

eg.
Person A has 1 record = eg 1 Booking Ref and 1 flightID
Person B has 1 record = eg 1 Booking Ref and 1 flightID
Person C has 3 records = eg 1 Booking Ref and 3 flightID's
Person D has 2 records = eg 1 Booking Ref and 2 flightID's

I only want to run the code once for each person
in this case it would be 4 times
at the moment it's running 7 times (in this example)

Any ideas ?

 

[Wayne-I-M]

it's ok - got it

 

[Wayne-I-M]

Hi Ken

I cheated - - - sorry but got bored with this daft button (taken me 2
days now)

I just created a a simple totals query (count of PersonID) and based a
hidden form on that
The code just open the hidden form and loops through that and then updates
the main form then closes the hidden stuff.

I know, I know !! lazy

Stupid thing is that as soon as this is done it's going to freeware anyway
(marketing dept's idea)

cheers anyway

PS. dead sunny up here at the mo

 

[Wayne-I-M]

Ken - just tested your code snippet (always trying to learn) and am getting
error 91
With block variable not set


here is the code section thats playing up
Do While Not .EOF

note I change the PersonID to booking ref (many to many)
tables are ClientID can have lots of BookingRef's

As before the rs is not set for boxes starting txt and as they are unbound
(in case you're wondering) - but it is for the bookingref

'code start ------'

Dim rst As Object
Dim strPBookingRefList As String
Dim strBookingRef As String
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rst
Do While Not .EOF
strBookingRefID = "~" & rst.Fields("BookingRef") & "~"
If InStr(strBookingRefIDList, strBookingRef) = 0 Then
strBookingReflist = strBookingReflist & strBookingRef
Do Until rs.EOF

CurrentDb.Execute "INSERT INTO tblflights " & _
"(BookingRef, FlightDate, FlightFrom, FlightTo, FlightDepart,
FlightArrive, FlightNumber, Flight_ETicket, Carrier) VALUES (" & _
rs!Forms![frmDataSheet_FlightsList]![BookingRef] & ",#" &
Format([txtFlightDate], "mm\/dd\/yyyy") & "#," & [txtFlightFrom] & "," &
[txtFlightTo] & ", #" & _
[txtFlightDepart] & "# , #" & [txtFlightArrive] & "# , " &
[txtFlightNumber] & ", " & [txtFlight_ETicket] & "," & [txtCarrier] & ")"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Me.Requery
End If
.MoveNext
Loop
End With


'code end -------'

 

[Wayne-I-M]

ooops



cheers

--
Wayne
Manchester, England.

Wayne:

You don't appear to have Set the rst object variable to anything.

Ken Sheridan
Stafford, England

Ken - just tested your code snippet (always trying to learn) and am getting
error 91
With block variable not set

here is the code section thats playing up
Do While Not .EOF

note I change the PersonID to booking ref (many to many)
tables are ClientID can have lots of BookingRef's

As before the rs is not set for boxes starting txt and as they are unbound
(in case you're wondering) - but it is for the bookingref

'code start ------'

Dim rst As Object
Dim strPBookingRefList As String
Dim strBookingRef As String
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rst
Do While Not .EOF
strBookingRefID = "~" & rst.Fields("BookingRef") & "~"
If InStr(strBookingRefIDList, strBookingRef) = 0 Then
strBookingReflist = strBookingReflist & strBookingRef
Do Until rs.EOF

CurrentDb.Execute "INSERT INTO tblflights " & _
"(BookingRef, FlightDate, FlightFrom, FlightTo, FlightDepart,
FlightArrive, FlightNumber, Flight_ETicket, Carrier) VALUES (" & _
rs!Forms![frmDataSheet_FlightsList]![BookingRef] & ",#" &
Format([txtFlightDate], "mm\/dd\/yyyy") & "#," & [txtFlightFrom] & "," &
[txtFlightTo] & ", #" & _
[txtFlightDepart] & "# , #" & [txtFlightArrive] & "# , " &
[txtFlightNumber] & ", " & [txtFlight_ETicket] & "," & [txtCarrier] & ")"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Me.Requery
End If
.MoveNext
Loop
End With

'code end -------'

Wayne:

[quoted text clipped - 41 lines]

Any ideas ?