'Next n' Looping rather than moving to the next record (2 Viewers)

[kevnaff]

Hi All.

The code I have below is meant to run for the amount of records found in the 'QueryBarcodeScannerReturns'. At the moment I have ten records in the query and the code is looping ten times on the same record, but not moving to the next record. I am sure something simple just needs to be added or removed to make this work.

Can anybody spot why this may be happening?

Thanks

Dim db As DAO.Database
Dim REC, REC2, REC3, REC4 As Recordset
Dim strSql As String
Dim TotalRecords As Integer
Dim TotalExistingRecords As Integer
Dim n As Integer
Dim LastID As Integer
Dim YesOrNo As Integer

' Count records in found set
Set db = CurrentDb()
Set REC = db.OpenRecordset("QueryBarcodeScannerReturns", dbOpenDynaset)
If REC.EOF Then REC.Close: Exit Sub
REC.MoveLast
TotalRecords = REC.RecordCount
REC.Close

' Get out message
Message = "MediPool will automatically update " & TotalRecords & _
            " records in the Pool Bookings records."
Title = "Medipro2000 Auto PPM Job Builder"
Response = MsgBox(Message, vbOKCancel, Title)

For n = 1 To TotalRecords

YesOrNo = DCount("BookingID ", "QueryPoolBookingsDateInIsNull", "[CodeNo] = '" & Me.BarcodeValue & "'")

If YesOrNo > 0 Then

LastID = Nz(DMax("BookingID ", "PoolBookings", "[CodeNo] = '" & Me.BarcodeValue & "'"))

'Update PoolBookings with Returns Information
strSql = "SELECT * FROM [PoolBookings] WHERE [BookingID] =  " & LastID & ""
Set db = CurrentDb()
Set REC = db.OpenRecordset(strSql, dbOpenDynaset)
Set REC2 = db.OpenRecordset("ReturningEquipmentScanner", dbOpenDynaset)
REC.Edit
REC("DateIn") = Date
REC("TimeIn") = Time()
REC("BookedInBy") = "Test"
REC("DepartmentBookingIn") = "Test"
REC.Update
REC2.Edit
REC2("Status") = "Processed"
REC2.Update
REC.MoveNext
REC2.MoveNext

Else

'Update - No History of Equipment being booked out, so new record added to PoolBookings
Set db = CurrentDb()
Set REC2 = db.OpenRecordset("ReturningEquipmentScanner", dbOpenDynaset)
Set REC3 = db.OpenRecordset("PoolBookings", dbOpenDynaset)
Set REC4 = db.OpenRecordset("ImportEquipment", dbOpenDynaset)
REC3.Edit
REC3.AddNew
REC3("CodeNo") = Me.BarcodeValue
REC3("DateOut") = Date - 1
REC3("TimeOut") = Time()
REC3("DateIn") = Date
REC3("TimeIn") = Time()
REC3("BookedOutBy") = "Not Recorded"
REC3("DepartmentBookingOutNotInList") = "Not Recorded"
REC3("BookedInBy") = DLookup("FullName", "Login", "[TempVars]![tvarUser]=[Username]")
REC3("DepartmentBookingIn") = "Equipment Pool"
REC3.Update
REC2.Edit
REC2("Status") = "Processed"
REC2.Update
REC3.MoveNext
REC2.MoveNext

End If

Next n

 

[Gasman]

Walk your code and see where it takes you.

FWIW why not just use the normal method

Do While Not rs.EOF

Not sure I like this

REC3.Edit
REC3.AddNew

You should be either editing or adding surely?

 

[kevnaff]

Walk your code and see where it takes you.

FWIW why not just use the normal method

Do While Not rs.EOF

Not sure I like this

REC3.Edit
REC3.AddNew

You should be either editing or adding surely?

Thanks Gasman.

I've not used this method before. I have put this in to my code, and have removed some extra bits that aren't required to test why the record won't move to the next one.

I have also removed the REC3.Edit as you said, as this isn't required.

I have introduced the Do While Not rs.EOF. My understanding is that this code will run until there are no records left to run through. When I introduced this, I got the message 'Do without Loop'. I added a Loop in to the code, but this introduced an infinite loop that kept on creating the same record until the program crashed.

The code I now have is below...

Dim db As DAO.Database
Dim REC, REC2, REC3, REC4 As Recordset
Dim strSql As String
Dim TotalRecords As Integer
Dim TotalExistingRecords As Integer
Dim n As Integer
Dim LastID As Integer
Dim YesOrNo As Integer

' Count records in found set
Set db = CurrentDb()
Set REC = db.OpenRecordset("QueryBarcodeScannerReturns", dbOpenDynaset)
If REC.EOF Then REC.Close: Exit Sub
REC.MoveLast
TotalRecords = REC.RecordCount
REC.Close

' Get out message
Message = "MediPool will automatically update " & TotalRecords & _
            " records in the Pool Bookings records."
Title = "Medipro2000 Auto PPM Job Builder"
Response = MsgBox(Message, vbOKCancel, Title)

For n = 1 To TotalRecords

YesOrNo = DCount("BookingID ", "QueryPoolBookingsDateInIsNull", "[CodeNo] = '" & Me.BarcodeValue & "'")

If YesOrNo > 0 Then

LastID = Nz(DMax("BookingID ", "PoolBookings", "[CodeNo] = '" & Me.BarcodeValue & "'"))

'Update PoolBookings with Returns Information
strSql = "SELECT * FROM [PoolBookings] WHERE [BookingID] =  " & LastID & ""
Set db = CurrentDb()
Set REC = db.OpenRecordset(strSql, dbOpenDynaset)
Set REC2 = db.OpenRecordset("ReturningEquipmentScanner", dbOpenDynaset)
REC.Edit
REC("DateIn") = Date
REC("TimeIn") = Time()
REC("BookedInBy") = "Test"
REC("DepartmentBookingIn") = "Test"
REC.Update
REC2.Edit
REC2("Status") = "Processed"
REC2.Update
REC.MoveNext
REC2.MoveNext

Else

'Update - No History of Equipment being booked out, so new record added to PoolBookings
Set db = CurrentDb()
Set REC2 = db.OpenRecordset("ReturningEquipmentScanner", dbOpenDynaset)
Set REC3 = db.OpenRecordset("PoolBookings", dbOpenDynaset)
Set REC4 = db.OpenRecordset("ImportEquipment", dbOpenDynaset)
REC3.AddNew
REC3("CodeNo") = Me.BarcodeValue
REC3("DateOut") = Date - 1
REC3("TimeOut") = Time()
REC3("DateIn") = Date
REC3("TimeIn") = Time()
REC3("BookedOutBy") = "Not Recorded"
REC3("DepartmentBookingOutNotInList") = "Not Recorded"
REC3("BookedInBy") = DLookup("FullName", "Login", "[TempVars]![tvarUser]=[Username]")
REC3("DepartmentBookingIn") = "Equipment Pool"
REC3.Update
REC2.Edit
REC2("Status") = "Processed"
REC2.Update
REC3.MoveNext
REC2.MoveNext

End If

Next n


End Sub

Private Sub Command51_Click()



Dim db As DAO.Database
Dim REC, REC2, REC3, REC4 As Recordset
Dim strSql As String
Dim TotalRecords As Integer
Dim TotalExistingRecords As Integer
Dim n As Integer
Dim LastID As Integer
Dim YesOrNo As Integer
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("QueryBarcodeScannerReturns")
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF

YesOrNo = DCount("BookingID ", "QueryPoolBookingsDateInIsNull", "[CodeNo] = '" & Me.BarcodeValue & "'")

If YesOrNo > 0 Then

LastID = Nz(DMax("BookingID ", "PoolBookings", "[CodeNo] = '" & Me.BarcodeValue & "'"))

'Update PoolBookings with Returns Information
strSql = "SELECT * FROM [PoolBookings] WHERE [BookingID] =  " & LastID & ""
Set db = CurrentDb()
Set REC = db.OpenRecordset(strSql, dbOpenDynaset)
Set REC2 = db.OpenRecordset("ReturningEquipmentScanner", dbOpenDynaset)
REC.Edit
REC("DateIn") = Date
REC("TimeIn") = Time()
REC("BookedInBy") = "Test"
REC("DepartmentBookingIn") = "Test"
REC.Update
REC2.Edit
REC2("Status") = "Processed"
REC2.Update
REC.MoveNext
REC2.MoveNext

Else

'Update - No History of Equipment being booked out, so new record added to PoolBookings
Set db = CurrentDb()
Set REC2 = db.OpenRecordset("ReturningEquipmentScanner", dbOpenDynaset)
Set REC3 = db.OpenRecordset("PoolBookings", dbOpenDynaset)
Set REC4 = db.OpenRecordset("ImportEquipment", dbOpenDynaset)
REC3.AddNew
REC3("CodeNo") = Me.BarcodeValue
REC3("DateOut") = Date - 1
REC3("TimeOut") = Time()
REC3("DateIn") = Date
REC3("TimeIn") = Time()
REC3("BookedOutBy") = "Not Recorded"
REC3("DepartmentBookingOutNotInList") = "Not Recorded"
REC3("BookedInBy") = DLookup("FullName", "Login", "[TempVars]![tvarUser]=[Username]")
REC3("DepartmentBookingIn") = "Equipment Pool"
REC3.Update
REC2.Edit
REC2("Status") = "Processed"
REC2.Update
REC3.MoveNext
REC2.MoveNext

End If

Loop

rs.Close

So this code is looking at a query that contains a list of barcodes that have been scanned, with the field name [BarcodeValue].

It is then going to check if this [BarcodeValue] already exists in the [CodeNo] field in the already exists in the 'PoolBookings' table. To do this I have the added YesOrNo. If the value already exists, then it will find the last record (LastID) for this value and update some information on this record.

If this value does not exist in the 'PoolBookings' table, it will add a new record in to the 'PoolBookings' table.

I want this to happen for each record in 'QueryBarcodeScannerReturns'.

As I said, the Loop keeps on creating the same record over and over. I want this instead to move to the next record in the 'QueryBarcodeScannerReturns', and only create one new record, per record.

Hopefully this makes sense for anybody who can help.

Thanks

 

[ebs17]

For n = 1 To TotalRecords

TotalRecords is determined from a completely different recordset than the recordsets you are working with.
Counting is unnecessary anyway if you use the following loop construction (or an equivalent one):

Do While Not rs.EOF
   ' ...
   rs.MoveNext
Loop

This means that every record in this recordset is accessed. If EOF occurs, the loop does not start at all, so there is no need for an explicit termination.

 

[arnelgp]

what does Me.BarcodeValue, means?
that does mean that there are records on the Form and you want to Loop through that record (Form recordset)?

 

[kevnaff]

For n = 1 To TotalRecords

TotalRecords is determined from a completely different recordset than the recordsets you are working with.
Counting is unnecessary anyway if you use the following loop construction (or an equivalent one):

Do While Not rs.EOF
   ' ...
   rs.MoveNext
Loop

This means that every record in this recordset is accessed. If EOF occurs, the loop does not start at all, so there is no need for an explicit termination.

Thanks Ebs.

I have removed the TotalRecords part of the code and introduced the Do While Not rs.EOF and rs.MoveNext. This now loops better as it doesn't create infinite records.

At the moment I have 4 test records in 'QueryBarcodeScannerReturns'. The [BarcodeValue] of each record is 00001, 00002, 00003 and 00004. None of these BarcodeValues exist in the PoolBookings table, so I want 4 new records creating in this table, one for each of 00001, 00002, 00003 and 00004.

When I run the below code, I get 4 new records, but they all have the same [BarcodeValue], e.g all four records have 00001. So it is as if the code is not moving to the next record.

The code currently looks like this....

Dim db As DAO.Database
Dim REC, REC2, REC3, REC4 As Recordset
Dim strSql As String
Dim TotalRecords As Integer
Dim TotalExistingRecords As Integer
Dim n As Integer
Dim LastID As Integer
Dim YesOrNo As Integer
Dim rs As DAO.Recordset


Set rs = CurrentDb.OpenRecordset("QueryBarcodeScannerReturns")
Do While Not rs.EOF

YesOrNo = DCount("BookingID ", "QueryPoolBookingsDateInIsNull", "[CodeNo] = '" & Me.BarcodeValue & "'")

If YesOrNo > 0 Then

LastID = Nz(DMax("BookingID ", "PoolBookings", "[CodeNo] = '" & Me.BarcodeValue & "'"))

'Update PoolBookings with Returns Information
strSql = "SELECT * FROM [PoolBookings] WHERE [BookingID] =  " & LastID & ""
Set db = CurrentDb()
Set REC = db.OpenRecordset(strSql, dbOpenDynaset)
Set REC2 = db.OpenRecordset("ReturningEquipmentScanner", dbOpenDynaset)
REC.Edit
REC("DateIn") = Date
REC("TimeIn") = Time()
REC("BookedInBy") = "Test"
REC("DepartmentBookingIn") = "Test"
REC.Update
Me.Status = "Processed"

Else

'Update - No History of Equipment being booked out, so new record added to PoolBookings
Set db = CurrentDb()
Set REC2 = db.OpenRecordset("ReturningEquipmentScanner", dbOpenDynaset)
Set REC3 = db.OpenRecordset("PoolBookings", dbOpenDynaset)
Set REC4 = db.OpenRecordset("ImportEquipment", dbOpenDynaset)
REC3.AddNew
REC3("CodeNo") = Me.BarcodeValue
REC3("DateOut") = Date - 1
REC3("TimeOut") = Time()
REC3("DateIn") = Date
REC3("TimeIn") = Time()
REC3("BookedOutBy") = "Not Recorded"
REC3("DepartmentBookingOutNotInList") = "Not Recorded"
REC3("BookedInBy") = DLookup("FullName", "Login", "[TempVars]![tvarUser]=[Username]")
REC3("DepartmentBookingIn") = "Equipment Pool"
REC3.Update
Me.Status = "Processed"


End If

rs.MoveNext

Loop

what does Me.BarcodeValue, means?
that does mean that there are records on the Form and you want to Loop through that record (Form recordset)?

Me.BarcodeValue is meant to refer to the text in the barcode value textbox in the current record. Maybe this should this instead just be [BarcodeValue].

 

[ebs17]

The use of recordsets could be improved. But comparing two tables is actually work for queries.
First an update query to update existing records. Second, an append query to append the new records.
Since you are working with more than two tables and record sets, I don't understand your plan well enough to write a suggestion.
If several tables need to be added, the two queries mentioned above will be used for each table.

As approaches:

Solved - How to update the temporal table with an insert query

Dear all good evening! I have created an insert query in Ms Access for the purpose of updating the temporal table with equal fields. The data is supposed to be updated from tblProduct to tblProductTemple, the update here is supposed to only write new addition only not over writing the existing...

www.access-programmers.co.uk

update => #7
insert => #4

 

[Gasman]

When I run the below code, I get 4 new records, but they all have the same [BarcodeValue], e.g all four records have 00001. So it is as if the code is not moving to the next record.

Or you are setting that value in the incorrect place?
Walk your code line by line.

However now I can see you issue. You are doing the same as another member does here, and he has been playing with Access since 2005

You are walking recordsets, yet you are setting BarcodeValue from the form? hence always the same value.
You should be setting from whatever recordset that exists on.

Walking your code would have shown that.

 

[kevnaff]

Or you are setting that value in the incorrect place?
Walk your code line by line.

However now I can see you issue. You are doing the same as another member does here, and he has been playing with Access since 2005

You are walking recordsets, yet you are setting BarcodeValue from the form? hence always the same value.
You should be setting from whatever recordset that exists on.

Walking your code would have shown that.

I have set the recordset that I am getting the BarcodeValue from as REC2 in the code below. Is this what you mean by setting it from the Recordset?


The code with this change was still creating records for the same BarcodeValue. I added the DoCmd.Requery at the bottom of this code and it worked perfectly.

Dim db As DAO.Database
Dim REC, REC2, REC3, REC4 As Recordset
Dim strSql As String
Dim TotalRecords As Integer
Dim TotalExistingRecords As Integer
Dim n As Integer
Dim LastID As Integer
Dim YesOrNo As Integer
Dim rs As DAO.Recordset



Set rs = CurrentDb.OpenRecordset("QueryBarcodeScannerReturns")
'rs.MoveLast
'rs.MoveFirst


Do While Not rs.EOF

Set db = CurrentDb()
Set REC2 = db.OpenRecordset("QueryBarcodeScannerReturns", dbOpenDynaset)
Set REC3 = db.OpenRecordset("PoolBookings", dbOpenDynaset)
Set REC4 = db.OpenRecordset("ImportEquipment", dbOpenDynaset)
REC3.AddNew
REC3("CodeNo") = REC2.[BarcodeValue]
REC3("DateOut") = Date - 1
REC3("TimeOut") = Time()
REC3("DateIn") = Date
REC3("TimeIn") = Time()
REC3("BookedOutBy") = "Not Recorded"
REC3("DepartmentBookingOutNotInList") = "Not Recorded"
REC3("BookedInBy") = DLookup("FullName", "Login", "[TempVars]![tvarUser]=[Username]")
REC3("DepartmentBookingIn") = "Equipment Pool"
REC3.Update
Me.Status = "Processed"

DoCmd.Requery

rs.MoveNext

Loop

The only issue now is that I removed 'If YesOrNo > 0 Then' part of the code to try and walk through the code and identify the issue.

When I introduce the rest of my code back, I now received a message saying 'Loop without Do'. If I can get past this issue then I think I may have solved this issue. Below is the full code...

Dim db As DAO.Database
Dim REC, REC2, REC3, REC4 As Recordset
Dim strSql As String
Dim TotalRecords As Integer
Dim TotalExistingRecords As Integer
Dim n As Integer
Dim LastID As Integer
Dim YesOrNo As Integer
Dim rs As DAO.Recordset



Set rs = CurrentDb.OpenRecordset("QueryBarcodeScannerReturns")

Do While Not rs.EOF


YesOrNo = DCount("BookingID ", "QueryPoolBookingsDateInIsNull", "[CodeNo] = '" & Me.BarcodeValue & "'")

If YesOrNo > 0 Then

LastID = Nz(DMax("BookingID ", "PoolBookings", "[CodeNo] = '" & Me.BarcodeValue & "'"))

'Update PoolBookings with Returns Information
strSql = "SELECT * FROM [PoolBookings] WHERE [BookingID] =  " & LastID & ""
Set db = CurrentDb()
Set REC = db.OpenRecordset(strSql, dbOpenDynaset)
Set REC2 = db.OpenRecordset("ReturningEquipmentScanner", dbOpenDynaset)
REC.Edit
REC("DateIn") = Date
REC("TimeIn") = Time()
REC("BookedInBy") = "Test"
REC("DepartmentBookingIn") = "Test"
REC.Update
Me.Status = "Processed"

DoCmd.Requery

rs.MoveNext

Else

Set db = CurrentDb()
Set REC2 = db.OpenRecordset("QueryBarcodeScannerReturns", dbOpenDynaset)
Set REC3 = db.OpenRecordset("PoolBookings", dbOpenDynaset)
Set REC4 = db.OpenRecordset("ImportEquipment", dbOpenDynaset)
REC3.AddNew
REC3("CodeNo") = REC2.[BarcodeValue]
REC3("DateOut") = Date - 1
REC3("TimeOut") = Time()
REC3("DateIn") = Date
REC3("TimeIn") = Time()
REC3("BookedOutBy") = "Not Recorded"
REC3("DepartmentBookingOutNotInList") = "Not Recorded"
REC3("BookedInBy") = DLookup("FullName", "Login", "[TempVars]![tvarUser]=[Username]")
REC3("DepartmentBookingIn") = "Equipment Pool"
REC3.Update
Me.Status = "Processed"

DoCmd.Requery

rs.MoveNext

Loop

Can anybody spot why adding the rest of my code now gives me the 'Loop without Do'.

 

[arnelgp]

you have an If..Else, without the End If at the end.

and why are you Requering the Form? Requering will bring your recordset again on the first record of the form.
therefore an endless loop.

 

[kevnaff]

you have an If..Else, without the End If at the end.

and why are you Requering the Form? Requering will bring your recordset again on the first record of the form.
therefore an endless loop.

Thanks arnelgp.

I have added the End If and it works perfectly now!

Without the requery, it does not change the [BarcodeValue] on the new records it creates.

For example I have the following test data in my QueryBarcodeScannerReturns

Without the DoCmd.Requery

I get the following records created in my PoolBookings table...

With the DoCmd.Requery, I get the data that I want which looks like this...

Maybe this isn't the best way of doing it but it gets me the result at the moment.

 

[Gasman]

I would say that is still not working as you are adding the same value, just from Rec2 whose record pointer never gets moved?
The only record pointer that gets moved is rs?

 

[arnelgp]

i still i am following you.
does Me.BarcodeValue, means the BarcodeValue from QueryBarcodeScannerReturns query?

if so, change Me.BarcodeValue to rs.BarcodeValue, and remove the Requery.

 

[kevnaff]

i still i am following you.
does Me.BarcodeValue, means the BarcodeValue from QueryBarcodeScannerReturns query?

if so, change Me.BarcodeValue to rs.BarcodeValue, and remove the Requery.

Yes that's correct.

I changed the Me.BarcodeValue to rs.BarcodeValue and got a 'Method or data member not found' message.

 

[CJ_London]

You use rs!barcodevalue when referencing a recordset field

 

[kevnaff]

You use rs!barcodevalue when referencing a recordset field

I changed to rs!barcodevalue and I can now remove the DoCmd.Requery.

The snag now is that, previously it was using Me.Status = "Processed" to update the Status field in the query.

Now it only updates to "Processed" for the first record. I tried changing the Me.Status to rs!Status and got a message saying

I added rs.Edit before the rs!Status = "Processed"

The code runs through but now doesn't update any record's [Status] to "Processed".

The full code I currently have is...

Dim db As DAO.Database
Dim REC, REC2, REC3, REC4 As Recordset
Dim strSql As String
Dim TotalRecords As Integer
Dim TotalExistingRecords As Integer
Dim n As Integer
Dim LastID As Integer
Dim YesOrNo As Integer
Dim rs As DAO.Recordset



Set rs = CurrentDb.OpenRecordset("QueryBarcodeScannerReturns")

Do While Not rs.EOF


YesOrNo = DCount("BookingID ", "QueryPoolBookingsDateInIsNull", "[CodeNo] = '" & rs!BarcodeValue & "'")

If YesOrNo > 0 Then

LastID = Nz(DMax("BookingID ", "PoolBookings", "[CodeNo] = '" & rs!BarcodeValue & "'"))

'Update PoolBookings with Returns Information
strSql = "SELECT * FROM [PoolBookings] WHERE [BookingID] =  " & LastID & ""
Set db = CurrentDb()
Set REC = db.OpenRecordset(strSql, dbOpenDynaset)
Set REC2 = db.OpenRecordset("ReturningEquipmentScanner", dbOpenDynaset)
REC.Edit
REC("DateIn") = Date
REC("TimeIn") = Time()
REC("BookedInBy") = DLookup("FullName", "Login", "[TempVars]![tvarUser]=[Username]")
REC("DepartmentBookingIn") = "Equipment Pool"
REC.Update
rs.Edit
rs!Status = "Processed"


rs.MoveNext

Else

Set db = CurrentDb()
Set REC2 = db.OpenRecordset("QueryBarcodeScannerReturns", dbOpenDynaset)
Set REC3 = db.OpenRecordset("PoolBookings", dbOpenDynaset)
Set REC4 = db.OpenRecordset("ImportEquipment", dbOpenDynaset)
REC3.AddNew
REC3("CodeNo") = rs![BarcodeValue]
REC3("DateOut") = Date - 1
REC3("TimeOut") = Time()
REC3("DateIn") = Date
REC3("TimeIn") = Time()
REC3("BookedOutBy") = "Not Recorded"
REC3("DepartmentBookingOutNotInList") = "Not Recorded"
REC3("BookedInBy") = DLookup("FullName", "Login", "[TempVars]![tvarUser]=[Username]")
REC3("DepartmentBookingIn") = "Equipment Pool"
REC3.Update
rs.Edit
rs!Status = "Processed"


rs.MoveNext

End If

Loop