Help with vba code (1 Viewer)

Yianni

Registered User.
Local time
Today, 07:27
Joined
May 15, 2017
Messages
40
Hi,

I need some help with my code.

The following was copied from my screen when I opened the table directly

ID_fk PC ClientType
1 A REP
1 C
1 C
1 C
1 C
1 C
1 C
1 C
2 A REP
2 C
2 C
2 C
2 C
2 C
4 A REP
11 A REP
11 C
13 A REP
13 C
14 A REP
18 A REP
19 A REP
19 C
19 C
19 C
19 C


++++++++++++++++++++++++++++++++++++++

My code

Private Sub cmdLoop_Click()
On Error GoTo ErrorHandler
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "UniqueCustomersWithPickups"
Set rs = CurrentDb.OpenRecordset(strSQL)
'This line of code instantiates the recordset object!!!
'In English, this means that we have opened up a recordset
'and can access its values using the rs variable.

With rs


If Not .BOF And Not .EOF Then
'We don’t know if the recordset has any records,
'so we use this line of code to check. If there are no records
'we won’t execute any code in the if..end if statement.

.MoveLast
.MoveFirst

'It is not necessary to move to the last record and then back
'to the first one but it is good practice to do so.

While (Not .EOF)
'With this code, we are using a while loop to loop
'through the records. If we reach the end of the recordset, .EOF
'will return true and we will exit the while loop.

Debug.Print rs.Fields("ID_fk") & " " & rs.Fields("PC")
'prints info from fields to the immediate window

.MoveNext
'We need to ensure that we use .MoveNext,
'otherwise we will be stuck in a loop forever…
'(or at least until you press CTRL+Break)
Wend

End If

.Close
'Make sure you close the recordset...
End With

ExitSub:
Set rs = Nothing
'..and set it to nothing
Exit Sub
ErrorHandler:
Resume ExitSub

End Sub


+++++++++++++++++++++++++++++++

My results:

1 C
1 C
2 C
2 C
2 C
2 C
2 C
11 C
13 C
19 C
19 C
19 C
19 C
26 C
27 C
27 C
31 C
31 C
32 C
36 C
38 C
40 C
40 C
40 C
42 C
42 C
49 C
51 C
51 C
51 C
51 C
63 C
63 C
63 C
63 C
65 C
65 C

My question is : why am I missing all records with A in field PC and why do I picks up C only? Why is it not picking up all records? It missed all records with A in the field.

help will be appreciated. Thanks
 

moke123

AWF VIP
Local time
Today, 07:27
Joined
Jan 11, 2013
Messages
3,849
assuming "UniqueCustomersWithPickups" is a saved query, What is the SQL? Is there a value in the criteria field? I'm guessing you have "C" as the criteria or a wrong join to the client type.
 

Yianni

Registered User.
Local time
Today, 07:27
Joined
May 15, 2017
Messages
40
It is a table. ID_fk is a number and PC is text
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:27
Joined
May 7, 2009
Messages
19,169
You have filter on yiur query on PC is blank.
 

moke123

AWF VIP
Local time
Today, 07:27
Joined
Jan 11, 2013
Messages
3,849
try this

Code:
Private Sub cmdLoop_Click()

Dim strSQL As String
Dim rs As DAO.Recordset

   On Error GoTo cmdLoop_Click_Error

strSQL = "Select * from UniqueCustomersWithPickups"
Set rs = CurrentDb.OpenRecordset(strSQL)

If rs.BOF And rs.EOF Then Exit Sub



rs.MoveLast

Debug.Print "Rec Count = " & rs.RecordCount

rs.MoveFirst

Do Until rs.EOF

Debug.Print rs.Fields("ID_fk") & " " & rs.Fields("PC")


rs.MoveNext

Loop


rs.Close
Set rs = Nothing
Exit Sub

   On Error GoTo 0
   Exit Sub

cmdLoop_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdLoop_Click of Module Module1"

End Sub
 

Yianni

Registered User.
Local time
Today, 07:27
Joined
May 15, 2017
Messages
40
I am enclosing a screen clip of results. It certainly picked up the A and C as needed. However; why does it not start at the 1's? What did you do to pickup up the A's? You are on the right track!

168 A
171 A
174 A
176 A
176 C
178 A
178 C
178 C
179 A
179 C
179 C
179 C
181 A
181 C
181 C
181 C
182 A
182 C
182 C
182 C
182 C
182 C
184 A
184 C
187 A
188 A
195 A
195 C
198 A
198 C
198 C
203 A
204 A
204 C
204 C
204 C
204 C
206 A
208 A
208 C
209 A
211 A
211 C
212 A
212 C
212 C
212 C
212 C
214 A
214 C
214 C
214 C
214 C
214 C
216 A
219 A
219 C
220 A
223 A
224 A
224 C
227 A
229 A
229 C
229 C
229 C
229 C
230 A
233 A
233 C
235 A
238 A
238 C
238 C
239 A
241 A
245 A
246 A
246 C
249 A
249 C
256 A
256 C
257 A
257 C
257 C
257 C
258 A
258 C
258 C
258 C
265 A
265 C
267 A
268 A
271 A
271 C
271 C
271 C
274 A
279 A
282 A
282 C
282 C
283 A
283 C
283 C
283 C
283 C
283 C
284 A
284 C
285 A
289 A
289 C
297 A
299 A
299 C
299 C
299 C
299 C
299 C
308 A
308 C
308 C
308 C
309 A
309 C
310 A
310 C
311 A
311 C
311 C
311 C
311 C
312 A
312 C
313 A
313 C
315 A
315 C
315 C
315 C
315 C
315 C
324 A
324 C
327 A
327 C
327 C
329 A
329 C
330 A
330 C
334 A
334 C
339 A
344 A
345 A
345 C
345 C
346 A
346 C
346 C
346 C
346 C
347 A
347 C
347 C
347 C
347 C
348 A
348 C
349 A
350 A
351 A
352 A
352 C
352 C
352 C
353 A
353 C
353 C
354 A
354 C
355 A
356 A
356 C
356 C
357 A
358 A
358 C
358 C
359 A
360 A
360 C
361 A
361 C
361 C
 

Cronk

Registered User.
Local time
Today, 22:27
Joined
Jul 4, 2013
Messages
2,770
My suggestion is to add an autonumber key field to the table and print that out too.

Could the table have a (cursed) multi-value field.

BTW, you don't need to post all of your output. I dare say that most readers can get the drift from a sample of output.
 

moke123

AWF VIP
Local time
Today, 07:27
Joined
Jan 11, 2013
Messages
3,849
why does it not start at the 1's?
since the record count is not showing in your results I'm guessing you have more than 200 records and i think the Immediate Window is limited to around 200 lines.
 

Users who are viewing this thread

Top Bottom