Recordset Data

[DMT Dave]

Hi Guy's this is a bit of a method issue i would imagine



If i tick 6 check boxes (AddToRoute), this means I have 6 postcodes to add to route

I am aware that because i have got my Shell command within the loop that it will open 6 x URL's

1: I guess i need to move myURL Command after the loop ?

2: How would i then add the looped postcodes to 1 x URL ?

3: Because each postcode requires a forward slash after the 1st one and before the next one etc...... how would I loop forward slashes within the recordset loop ?

Is this achievable ?

Dim myURL As String, OpenChrome As StringDim iQty As Integer
Dim pc1 As String, pc2 As String, pc3 As String, pc4 As String, pc5 As String
Dim rs As DAO.Recordset




Set rs = CurrentDb.OpenRecordset("Select * From tblRemovals WHERE AddToRoute = Yes")


Do Until rs.EOF


    myURL = "https://www.google.co.uk/maps/dir/" & Replace(rs.Fields("PostCode"), " ", "")
    OpenChrome = """C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"""
    Shell OpenChrome & myURL, vbMaximizedFocus


rs.MoveNext
Loop

[Welshgasman]

Concatenate the postcodes with a slash within the loop into a string variable. Then use that outside the loop.

[DMT Dave]

Hi Guy's I have since tried this to add the forward slashes, also moved the URL command outside the loop, now only getting 1 post code when i have checked 6 in recordset ?

I though that If i moved the URL outside of the loop would result in opening 1 x URL which is correct, just now need to rsPC string for all records checked ?

Dim myURL As String, OpenChrome As String, rsPC As StringDim iQty As Integer
Dim pc1 As String, pc2 As String, pc3 As String, pc4 As String, pc5 As String
Dim rs As DAO.Recordset




Set rs = CurrentDb.OpenRecordset("Select * From tblRemovals WHERE AddToRoute = Yes")


Do Until rs.EOF


rsPC = Replace(Replace(rs.Fields("PostCode"), " ", ""), rs.Fields("Postcode"), rs.Fields("PostCode") & "/")


rs.MoveNext
Loop


   ' myURL = "https://www.google.co.uk/maps/dir/" & Replace(Replace(rsPC, " ", ""), rsPC, rsPC & "/")
    myURL = "https://www.google.co.uk/maps/dir/" & rsPC
    
    OpenChrome = """C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"""
    Shell OpenChrome & myURL, vbMaximizedFocus

[Welshgasman]

You are not concatenating the postcodes though.
Walk through your code line by line with F8 and inspect the variables.
Use debug.print to see what you actually have, not what you think you have.

[DMT Dave]

Hi WGM, thank you, I know you have mentioned numerous times to use F8 and debug.print

Can i please admit, I am unsure, added debug.Print on this code and F8 is doing nothing, I have tried highlighting the code and pressing F8 again doing nothing

Dim myURL As String, OpenChrome As String, rsPC As StringDim iQty As Integer
Dim pc1 As String, pc2 As String, pc3 As String, pc4 As String, pc5 As String
Dim rs As DAO.Recordset




Set rs = CurrentDb.OpenRecordset("Select * From tblRemovals WHERE AddToRoute = Yes")


Do Until rs.EOF


rsPC = Replace(Replace(rs.Fields("PostCode"), " ", ""), rs.Fields("Postcode"), rs.Fields("PostCode") & "/")
Debug.Print
rs.MoveNext
Loop


   ' myURL = "https://www.google.co.uk/maps/dir/" & Replace(Replace(rsPC, " ", ""), rsPC, rsPC & "/")
    myURL = "https://www.google.co.uk/maps/dir/" & rsPC
    
    OpenChrome = """C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"""
    Shell OpenChrome & myURL, vbMaximizedFocus

[Welshgasman]

You need to set a breakpoint, if not executing from the code window.
Click in the column to the left of the code and a red dot should appear. Click again, or select from the menu 'remove breakpoints' to remove it.
Once you have a breakpoint, I would put it on the Do until line, then the code will stop at that line in the code window.
Now you press F8 to move to each line. At the same time, either hover over the variable/field to see it's contents, or add a debug.print variablename in a line of code.

All debug.prints output to the immediate window (Ctrl + G)

Now you are in control and can see where your code goes and what it produces, step by step.

Look at the link in my signature for visual details on how.

[DMT Dave]

Hi WGM, so I have done the breakpoint, when i ran the code, the debug window popped up, pressed F8 and kept pressing F8 in it highlighted the following lines and kept looping through this

So sorry i am not understanding the theory behind this, please forgive me

Also checked menus to find 'remove breakpoints'

When i get this and understand your method, will this allow me to run the looped postcodes through a single URL command out side of the loop ?

[DMT Dave]

Pressed Ctrl+G and nothing in the immediate window

[Gicu]

@ Dave,
You mention adding 6 post codes yet to Dim only 5 pc string variables and then don't use them. Can your recordset return more then six? Can it return Nulls?
In any case you need to add the rsPC (usually you name string variables to start with an "s") into the line inside the loop:

rsPC = rsPC & (Replace(Replace(rs("PostCode"), " ", "") & "/")

.
Now you have them all the post codes but the variable contains one extra "/? at the end. So you need to add an extra line outside the loop to remove it:

rsPC=Left(rsPC,Len(rsPC)-1)

Finally add your Shell line after this.
Learning how to step through code will help you tremendously in figuring out what's happening!

Cheers,

[Welshgasman]

The reason there is nothing in the immediate window is because you have no debug.prints?
As you press F8 and loop through the records, hover over rsPC and see what values you have each time.

Yes, when you grasp the logic required. This is meant to show you what you are doing wrong, and teach you some basics of debugging, which you really should know by now.

[DMT Dave]

HI WGM and Vlad, thank you for helping, now done debug.Print on rsPC and the immediate window shows the rest of the postcodes in the rs

The shell command now opens and adds just the single postcode when 3 are checked, the 3 are in immediate window so:

That is now telling me the loop is working

Will loop through on F8

[orange]

@DMT Dave,

For info on Debugging VBA by the late Chip Pearson.

A youtube by Steve Bishop Debugging and Error Handling.

[DMT Dave]

Just looked at the signature as suggested and watched the debug.Print video, will give it a shot now

[Gicu]

Did you make the changes I suggested in my previous post? It should look something like this (there is no checks or logic anywhere to see how many checkbox are checked, so the code relies on the recordset rs to contain the same number of records (not null for the post codes):

Dim myURL As String, OpenChrome As String, rsPC As StringDim iQty As Integer'Dim pc1 As String, pc2 As String, pc3 As String, pc4 As String, pc5 As String 'Vlad you don't use them so why have them?
Dim rs As DAO.Recordset








Set rs = CurrentDb.OpenRecordset("Select * From tblRemovals WHERE AddToRoute = Yes")




Do Until rs.EOF
    rsPC = rsPC & (Replace(Replace(rs("PostCode"), " ", "") & "/")
    Debug.Print rsPC 
rs.MoveNext
Loop


rsPC=Left(rsPC,Len(rsPC)-1) 'Vlad - remove the trailing /
Debug.Print rsPC 
   
 myURL = "https://www.google.co.uk/maps/dir/" & rsPC
    
OpenChrome = """C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"""
Shell OpenChrome & myURL, vbMaximizedFocus

Cheers,

[Welshgasman]

I would expect the single postcode you get, is the last selected?