'Next n' Looping rather than moving to the next record

K

kevnaff

Member
Local time
Today, 12:17
Joined
Mar 25, 2021
Messages
174
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

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

' 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
 
Walk your code and see where it takes you.

FWIW why not just use the normal method
Code: 
Do While Not rs.EOF

Not sure I like this
Code: 
REC3.Edit
REC3.AddNew

You should be either editing or adding surely?
 
Last edited:
Gasman said:
Walk your code and see where it takes you.

FWIW why not just use the normal method
Code: 
Do While Not rs.EOF

Not sure I like this
Code: 
REC3.Edit
REC3.AddNew

You should be either editing or adding surely?
Click to expand...
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...

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

' 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
 
Code: 
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):
Code: 
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.
 
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)?
 
ebs17 said:
Code: 
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):
Code: 
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.
Click to expand...

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....

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"

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



arnelgp said:
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)?
Click to expand...

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].
 
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
 
Last edited:
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.
Click to expand...
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. :(
 
Gasman said:
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. :(
Click to expand...

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.


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")
'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...

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'.
 
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.
 
Last edited:
arnelgp said:
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.
Click to expand...

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

1720000549375.png


Without the DoCmd.Requery

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

1720000786937.png



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

1720000866379.png



Maybe this isn't the best way of doing it but it gets me the result at the moment.
 
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?
 
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.
 
arnelgp said:
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.
Click to expand...

Yes that's correct.

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

1720013389525.png
 
You use rs!barcodevalue when referencing a recordset field
 
CJ_London said:
You use rs!barcodevalue when referencing a recordset field
Click to expand...

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

1720014918490.png


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...

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] = '" & 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
 
i don't know if this is the complete code, but i made some changes.
please review the code.
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

'arnelgp
Dim bm As Variant

Set db = CurrentDb()

Set rs = db.OpenRecordset("QueryBarcodeScannerReturns")
Set REC = db.OpenRecordset("PoolBookings", dbOpenDynaset)

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 REC = db.OpenRecordset(strSql, dbOpenDynaset)
        'Set REC2 = db.OpenRecordset("ReturningEquipmentScanner", dbOpenDynaset)
       
        REC.FindFirst "[BookingID] = " & LastID
       
        If Not REC.NoMatch Then
            REC.Edit
            REC("DateIn") = Date
            REC("TimeIn") = Time()
            REC("BookedInBy") = DLookup("FullName", "Login", "[TempVars]![tvarUser]=[Username]")
            REC("DepartmentBookingIn") = "Equipment Pool"
            REC.Update
        End If
   
    Else
   
   
        'Set REC2 = db.OpenRecordset("QueryBarcodeScannerReturns", dbOpenDynaset)
        'Set REC3 = db.OpenRecordset("PoolBookings", dbOpenDynaset)
        'Set REC4 = db.OpenRecordset("ImportEquipment", dbOpenDynaset)
        REC.AddNew
        REC("CodeNo") = rs![BarcodeValue]
        REC("DateOut") = Date - 1
        REC("TimeOut") = Time()
        REC("DateIn") = Date
        REC("TimeIn") = Time()
        REC("BookedOutBy") = "Not Recorded"
        REC("DepartmentBookingOutNotInList") = "Not Recorded"
        REC("BookedInBy") = DLookup("FullName", "Login", "[TempVars]![tvarUser]=[Username]")
        REC("DepartmentBookingIn") = "Equipment Pool"
        REC.Update
   
   
   
    End If
   
    rs.Edit
    rs!Status = "Processed"
    rs.Update
   
    bm = rs.LastModified
    rs.Bookmark = bm
   
    rs.MoveNext

Loop
'Uncomment if this is the end of the code!
'Clean-up
'
'close them after using
'rs.Close
'ser REC = Nothing
'Set REC2 = Nothing
'Set REC3 = Nothing
'Set REC4 = Nothing
'Set rs = nothibng
'Set db = Nothing
 
Last edited:
You must log in or register to reply here.

Similar threads

K
strSql errors
2
Replies
26
Views
466
cheekybuddha
cheekybuddha
K
Solved strSql - Object Required
Replies
11
Views
312
Minty
Minty
K
DLookup to return different value on MoveNext
Replies
5
Views
207
ebs17
E
K
Solved Rec.MoveNext not working?
Replies
4
Views
216
kevnaff
K
K
No Current Record
Replies
8
Views
1,306
kevnaff
K

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom