Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-11-2018, 04:57 PM   #1
Yianni
Newly Registered User
 
Join Date: May 2017
Posts: 26
Thanks: 7
Thanked 0 Times in 0 Posts
Yianni is on a distinguished road
Question Help with vba code

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

Yianni is offline   Reply With Quote
Old 03-11-2018, 05:05 PM   #2
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 765
Thanks: 3
Thanked 232 Times in 218 Posts
moke123 will become famous soon enough
Re: Help with vba code

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.
moke123 is offline   Reply With Quote
Old 03-11-2018, 05:33 PM   #3
Yianni
Newly Registered User
 
Join Date: May 2017
Posts: 26
Thanks: 7
Thanked 0 Times in 0 Posts
Yianni is on a distinguished road
Re: Help with vba code

It is a table. ID_fk is a number and PC is text

Yianni is offline   Reply With Quote
Old 03-11-2018, 05:34 PM   #4
Yianni
Newly Registered User
 
Join Date: May 2017
Posts: 26
Thanks: 7
Thanked 0 Times in 0 Posts
Yianni is on a distinguished road
Re: Help with vba code

A single table no joins.
Yianni is offline   Reply With Quote
Old 03-11-2018, 05:59 PM   #5
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Help with vba code

You have filter on yiur query on PC is blank.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 03-11-2018, 06:14 PM   #6
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 765
Thanks: 3
Thanked 232 Times in 218 Posts
moke123 will become famous soon enough
Re: Help with vba code

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
moke123 is offline   Reply With Quote
Old 03-11-2018, 06:38 PM   #7
Yianni
Newly Registered User
 
Join Date: May 2017
Posts: 26
Thanks: 7
Thanked 0 Times in 0 Posts
Yianni is on a distinguished road
Re: Help with vba code

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

Yianni is offline   Reply With Quote
Old 03-11-2018, 08:03 PM   #8
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Help with vba code

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.
Cronk is offline   Reply With Quote
Old 03-12-2018, 12:00 AM   #9
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,022
Thanks: 114
Thanked 3,015 Times in 2,742 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Help with vba code

Cross posted with answers at http://www.accessforums.net/showthread.php?t=71006

Please follow forum guidelines regarding cross posting to prevent people wasting time. Always say you have done so and provide the link yourself
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns – the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 03-12-2018, 02:04 AM   #10
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 765
Thanks: 3
Thanked 232 Times in 218 Posts
moke123 will become famous soon enough
Re: Help with vba code

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

moke123 is offline   Reply With Quote
Reply

Tags
loop , table

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Idletimedetected Code not quitting Access due to Save changes code on dirty form. a_eriley Forms 18 06-12-2014 04:17 AM
[SOLVED] Break Points in Code Module fail to work - code works - any ideas? Rx_ General 3 10-22-2013 09:36 AM
Code to divide results by units based on Site Code. jereece Reports 5 11-28-2011 02:21 PM
Access Code & Module 32bit to 64bit system - Browse For File Code Thorope Modules & VBA 2 04-04-2011 09:13 AM
Code errors on converting 97 to 2003 see pasted code Scolds Modules & VBA 2 12-09-2005 03:34 AM




All times are GMT -8. The time now is 09:37 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World