Select Query to Update (1 Viewer)

[Jord]

Hi,

I'm a novice to al things access and know very little about sql & vba, so sorry If i dont make sense or confuse matters more.

I have this code in a select query:

SELECT TOP 10 Matrix_Export.[Departure Site], Matrix_Export.[Destination Site] AS [Store ID], Customers.CUSTNAME AS [Store Name], Round([Seconds]/3600,2) AS Hours, Matrix_Export.KMs
FROM Matrix_Export INNER JOIN Customers ON Matrix_Export.[Destination Site] = Customers.CUSTID
WHERE (((Matrix_Export.[Departure Site])=[Forms]![Top10 Closest]![Depart]) AND ((Matrix_Export.[Destination Site]) Like "S202*" Or (Matrix_Export.[Destination Site]) Like "S200*" Or (Matrix_Export.[Destination Site]) Like "D*") AND ((Matrix_Export.KMs)>0))
ORDER BY Matrix_Export.KMs;

I want to turn this into an update query to update a new table lets say "ClosestTable".

Any help would be much appreciated.

 

[sparks80]

Hi,

Firstly I would recommend you have a look at this tutorial, which explains the basics about update queries.

http://www.databasedev.co.uk/update_query.html

I'll have a look at your code and update this post in a minute with the code necessary to convert your existing SELECT query.

 

[sparks80]

Which fields are you looking to update, can you give me the table and the field names?

 

[Jord]

What I want really is just a table with the same results as the query but with an autonumber added so i can lookup the data onto a form i have, thats the only reason i need the table rather than just the query.

So the fields can be the same name or similar

Hope that makes sense

 

[Alansidman]

Why not have the form be bound to your query. Make the query your record source instead of a table?

 

[Jord]

Can I have a text box showing the results of the query using this method?

 

[sparks80]

Hi,

You could create a new table in the database with the same field names, and an additional field for your autonumber ID.

Then convert your existing query into an append query and associate each field in your query with the corresponding field in the table. The autonumber field will be populated automatically.

 

[sparks80]

If you use the results of the query as the bound recordset of your form, you can lay out the controls however you want.

 

[Jord]

So i know how to bind the form to the query but how can i put the results into text box's or something with a name so i can reference it within some vba that runs from the form?
The only solution I knew was Dlookup hence me wanting the table to use to lookup from.

*EDIT:

I have bound the query and quickly added one field "Store Name", but is it possible to have all ten visible? rather than arrowing through the records?

 

[Alansidman]

What happens when you change your form to a datasheet view. Does this get you the desired results.

 

[Jord]

I'm not on that pc at the moment but i have a browser window within the form which i guess would prevent that?
Basically the browser shows some points on Google maps, which is the reason i need to be able to reference the names.

 

[sparks80]

Hi,

To display the results of your query on the form you will need to create a control (textbox, comobobox, checkbox etc) for each field in your query. Then you need to change the "Control Source" for each control by selecting the appropriate field name.

From VBA you can get the value of the field either by referring to the textbox, or the underlying field name. So you do not necessarily need to display all the fields on the form to be able to access them from VBA. But you do need the fields to be included in the underlying query.

Lets say you have a field in the query called [Store Name], and you create a bound textbox (lets say it is called txtStoreName) on the form with it's control source set to this field.

From VBA you can get the value from this field for the currently selected record like this:

Dim strStoreName as String
strStoreName = Me![Store Name]

You can also get the value by referring to the value of the textbox

Dim strStoreName As String
strStoreName = Me!txtStoreName.Value

It is a good idea to name all your objects (tables, forms, textboxes etc) without using spaces. Either use an underscore (_) instead, or possibly separate words by using capitals, e.g StoreName.
One of the main reasons is to prevent having to enclose your field names using square brackets in queries and VBA code.

Hope that helps.

 

[Jord]

Thanks alot for your help so far.

I needed all the results to show in the form so what i did was add a continuous form as a sub form, bound to the query as you had suggested.

I now need the query results to form a url for use in the browser window in the main form. I have had a look all over Google and come up with the below but it's not working. I get "Run time error 3061, too few parameters expected 1"
With the line "Set rs = db.OpenRecordset("Top10 Closest", dbOpenDynaset)" highlighted in the debug screen.

Private Sub Map_Click()
    

    Dim db As Database
    Dim rs As Recordset
    Dim StrClosestID() As String
    i = 0

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Top10 Closest", dbOpenDynaset)

    If rs.RecordCount <> 0 Then
    With rs
    .MoveFirst
    Do While Not .EOF
    StrClosestID(i) = ![Store ID]
    .MoveNext
    i = i + 1
    Loop
    End With
    End If

    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing


    'Insert Lat and Longs for calls
    Strt = DLookup("[CUSTLAT]", "Customers", "[CUSTID] = StrClosest(0)") & "," & DLookup("[CUSTLONG]", "Customers", "[CUSTID] = Form![Start]") & "&"
    D1 = "&daddr=" & DLookup("[CUSTLAT]", "Customers", "[CUSTID] = StrClosest(1)") & "," & DLookup("[CUSTLONG]", "Customers", "[CUSTID] = StrClosest(1)")
    D2 = "+to:" & DLookup("[CUSTLAT]", "Customers", "[CUSTID] = StrClosest(2)") & "," & DLookup("[CUSTLONG]", "Customers", "[CUSTID] = StrClosest(2)")
    D3 = "+to:" & DLookup("[CUSTLAT]", "Customers", "[CUSTID] = StrClosest(3)") & "," & DLookup("[CUSTLONG]", "Customers", "[CUSTID] = StrClosest(3)")
    D4 = "+to:" & DLookup("[CUSTLAT]", "Customers", "[CUSTID] = StrClosest(4)") & "," & DLookup("[CUSTLONG]", "Customers", "[CUSTID] = StrClosest(4)")
    D5 = "+to:" & DLookup("[CUSTLAT]", "Customers", "[CUSTID] = StrClosest(5)") & "," & DLookup("[CUSTLONG]", "Customers", "[CUSTID] = StrClosest(5)")
    D6 = "+to:" & DLookup("[CUSTLAT]", "Customers", "[CUSTID] = StrClosest(6)") & "," & DLookup("[CUSTLONG]", "Customers", "[CUSTID] = StrClosest(6)")
    D7 = "+to:" & DLookup("[CUSTLAT]", "Customers", "[CUSTID] = StrClosest(7)") & "," & DLookup("[CUSTLONG]", "Customers", "[CUSTID] = StrClosest(7)")
    D8 = "+to:" & DLookup("[CUSTLAT]", "Customers", "[CUSTID] = StrClosest(8)") & "," & DLookup("[CUSTLONG]", "Customers", "[CUSTID] = StrClosest(8)")
    D9 = "+to:" & DLookup("[CUSTLAT]", "Customers", "[CUSTID] = StrClosest(9)") & "," & DLookup("[CUSTLONG]", "Customers", "[CUSTID] = StrClosest(9)")
    D10 = "+to:" & DLookup("[CUSTLAT]", "Customers", "[CUSTID] = StrClosest(10)") & "," & DLookup("[CUSTLONG]", "Customers", "[CUSTID] = StrClosest(10)")
    
    'Start & End of Google URL
    URLBeg = 
    URLEnd = 
    
    'Check if all calls are used
    If Len(Drop1) < 1 Or IsNull(Drop1) Then D1 = ""
    If Len(Drop2) < 1 Or IsNull(Drop2) Then D2 = ""
    If Len(Drop3) < 1 Or IsNull(Drop3) Then D3 = ""
    If Len(Drop4) < 1 Or IsNull(Drop4) Then D4 = ""
    If Len(Drop5) < 1 Or IsNull(Drop5) Then D5 = ""
    If Len(Drop6) < 1 Or IsNull(Drop6) Then D6 = ""
    If Len(Drop7) < 1 Or IsNull(Drop7) Then D7 = ""
    If Len(Drop8) < 1 Or IsNull(Drop8) Then D8 = ""
    If Len(Drop9) < 1 Or IsNull(Drop9) Then D9 = ""
    If Len(Drop10) < 1 Or IsNull(Drop10) Then D10 = ""
    
    'Form full URL
    URL = URLBeg & Strt & D1 & D2 & D3 & D4 & D5 & D6 & D7 & D8 & D9 & D10 & URLEnd

I'm convinced I'm doing stuff a long way round so again any help much apreciated.

*I had to to take the Start and End URL bits out because I'm only on 5 posts

 

[Jord]

Help, anyone?

 

[vbaInet]

First of all, you should not be saving calculated values.

If you want to display the results of that query simply use a subform with its Record Source set to that SQL statement.

 

[Jord]

Right, I have added the lat, long bits into the sql query now.
So I have a query which will give the name, lat, long etc but I need to be able to concatenate those results into a url to send to the ActiveX webbrowser.

Thanks in advance

 

[sparks80]

Hi,

can you possibly give me an example URL that you are trying to generate, and which parts of the URL are made up by the fields in the query (as well as the field names). Then I can show you how to concatenate the results to give the URL.

Thanks,

Mark

 

[Jord]

Hi Mark,

I basically want to create a file like the map.txt (should be.htm but i had to change to enable upload to the site)

The code.txt file has some code I was working on (without much succsess).
I have now added the "CUSTLAT" & "CUSTLONG" to the query so the dlookup's dont need to be there. The query name is "Top10 Closest".

 

[Jord]

Any thoughts would be appreciated.

 

[Jord]

Hi,

can you possibly give me an example URL that you are trying to generate, and which parts of the URL are made up by the fields in the query (as well as the field names). Then I can show you how to concatenate the results to give the URL.

Thanks,

Mark

Mark Can you be of any help?