Posts by Addy

    Interactive List

    I am looking to extract data from a database and put this into a list. Once in the list, it will only list a small potion of the data, but if they double click on the line, i would like it to be taken to another form where all the data will be place ready for editing. I can get the data, but not sure really what to put it in, IE. a text box etc, and not sure how i would make it know, whatever you double click on it will know the ID (record numb) and get all the info.

    and ideas or advice would be hugely helpful. Thanks in advance.

    Re: Searching Sql Database To Find Match

    I would like it so, whatever box you complete, it will return records with the information you put in the boxes. example A

    road = hudson close
    city = Bristol

    I would like it to return all the records where road, has hudson close and bristol under the city, not one or the other, but needs both in one record to be returned.

    Example B

    If you only no the first part of the post code, you could enter:

    post1 = cv21

    road = happy road

    it will return all the records that hold cv21 in the post column and happy road in the road column, but both must be in a record for that record to be returned.

    Hope thats better to understand.... :-s

    Thanks for your help :)

    I am having a huge problem in making a search from a form in excel to a SQL database. Here is the code that searches:

    strSQL = "SELECT [road], post1, post2, [town], [city], [plant], tzone FROM [SouthWest] WHERE (((post1) Like '" & _
        formPost1 & "') and ((post2) Like '" & formPost2 & "') OR (([road]) like '" & formRoad & "') and (([city]) Like '" & formCity & "')" & _
        " and (([town]) Like '" & formTown & "'))  ;"

    Now, if an address in the database, has a road, town and city and i complete the fields in the form, it will return, but if only has a road town, and put in to the road and town in the form it will return No Address, because the city is blank.... very odd to me how the AND and OR works. I would like it so if all the fields are complete, it searches on them, and if some are miss out, then it miss's them out and only returns records which hold the critria data.

    Not sure if that makes sence... if the database was small, i could show you, but it's huge... any ideas ?

    Thanks in advance.

    Re: Return Mutiple Results From Access Dao

    yeah, thats what i thought, so if had

    strSQL = "SELECT [post1], [post2], [road], [town], [city], [plant], [tzone] FROM [SouthWest] WHERE [post1] Like '" & formPost1 & "' AND [road] Like '" & formRoad & "' ;"

    So it would only return matching post1 and the road, but it did not do that. It just game all the post1 and all the roads A - Z. This is what i dont get :-S

    Re: Return Mutiple Results From Access Dao

    Hi, yesh, I have sorted that bit out, but its still not searching right, an address which has 8 columns in the database, and I have 5 in the form, The AND or the OR do not seem to work well, when I have it look at the results... need to play with that abit more i think.

    I am now having probs with INSERT INTO statment and got a new book which like cost £30... thats on another post thou.

    The code for the search however is this:

    strSQL = "SELECT [post1], [post2], [road], [town], [city], [plant], [tzone] FROM [SouthWest] WHERE ([post1] Like '" & formPost1 & "' AND [post2] Like '" & formPost2 & "' AND [road] Like '" & formRoad & "' AND [town] Like '" & formTown & "' AND [city] Like '" & formCity & "'));"

    It cannot seem to see past the post1 to road, as the results are all addres begining with the first part of the postcode....

    Re: Return Mutiple Results From Access Dao

    Hiya, I get an error, Item not found in this collection

    and it falls on this line:

    Cells(r, 1).Value = ![ID] & " " & ![post1] & " " & ![post2]

    Any ideas?

    EDIT *******************************

    Not sure, but i think the error happens if the field in the database is empty?

    The next stage of this, is I need to know how many results it's found before it starts moving the record to a sheet?[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Right... think im getting the hang of the code bit now.... I think!

    It is searching each field. But I am really looking for it to match whats on the form to a record. as an example:

    If i search post1 and road it will return all post1's and roads, rather than return only records that hold both post1 and road in the same record? does this make sense.. lol!!

    Anyway, i wanted it to ready the top 10 results that match the form critria and at the mo, because of how its searching im getting alot more.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Right, I have been doing a lot of messing, and I now have this:

    But it is still not searching how i would like it to. If i put in the form:

    post1 = BS21


    road = close

    I would want it bring up, only records that have Close and BS21 in them, but it's not doing that. I have tried this:

    '  SQL statement to query the database with
    strSQL = "SELECT [post1], [post2], [road], [town], [city], [plant], [tzone] FROM [SouthWest] WHERE ([post1] Like '" & formPost1 & "' AND [post2] Like '" & formPost2 & "' AND [road] Like '" & formRoad & "' AND [town] Like '" & formTown & "' AND [city] Like '" & formCity & "') ORDER BY ([road] Like '" & formRoad & "') DESC;"

    But this does not bring up a single record.... I am abit lost now, tried searching the web and the help files, but nothing like I am trying to do seems to be about. I even as you can see put a ORDER BY so I only get the 'Close' matching records at the top, and that don't work either....

    Please... any ideas ?

    Thanks in advance

    Re: Return Mutiple Results From Access Dao

    hr]*[/hr] Auto Merged Post;[dl]*[/dl]

    Can I just ask, so I understand:

    What do the brackets mean, or do ?

    What does the wildcard do within the quotes ?

    What does the word 'Like' mean ?

    Thanks, ill try that too what you have done.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

    Quote from zimitry

    See if this helps out a little...

    strSQL = "SELECT [post1], [post2], [road], [city] FROM [SouthWest] WHERE ((([post1]) Like '"* & formPost1 &* "') OR (( [post2]) Like '" & formPost2 & "') OR (( [road]) Like '" & formRoad & "') OR(( [town]) Like '" & formTown & "') OR (([city]) Like '" & formCity & "')) ;"


    I seem to get a syntax error with this code, i have looked at it, and can't seem to find an error as such, not that i would know what im looking for!!

    Re: Return Mutiple Results From Access Dao

    Great, would that give me partial results, or exact ? partial would be better, just incase a lane or road is not there, but a street is for example ?

    thank you again so much for this![hr]*[/hr] Auto Merged Post;[dl]*[/dl]I seem to get errors, worked few them, but still there, what am i missing please :

    strSQL = "SELECT [post1], [post2], [road], [street], [city], [county] FROM [SouthWest] WHERE [post1] = '" & formPost1 & "' ;, & _
        & [post2] = '" & formPost2 & "' ;, [road] = '" & formRoad & "' ;,[street] = '" & formStreet & "' ;, [town] = '" & formTown & "' ;, & _
        & [city] = '" & formCity & "' ;, [county] = '" & formCounty & "' ;"

    Thanks[hr]*[/hr] Auto Merged Post;[dl]*[/dl]This is the code, seems to work better than the one above, but i still get an error:

    "Characters found after end of SQL statement"

    The code is here:

    '  SQL statement to query the database with
        strSQL = "SELECT [post1], [post2], [road], [city] FROM [SouthWest] WHERE [post1] = '" & formPost1 & "' ;, [post2] = '" & formPost2 & "' ;, [road] = '" & formRoad & "' ;, [town] = '" & formTown & "' ;,[city] = '" & formCity & "' ;"
         'Initiate new DAO session, connect to the given database
         'and open the above SQL query results
         'MS Access will NOT be visible
        Set objEngine = New DBEngine
        Set objDb = objEngine.OpenDatabase(STR_DB_NAME)
        Set objRs = objDb.OpenRecordset(strSQL)

    thank you again for your help :)

    Re: Return Mutiple Results From Access Dao

    okay, that makes scense. I have this, which works:

    You will notice from my next question, im still very new to database stuff, but learning by do bits at a time. So i have one field working, and it takes what i need.

    Now, I need it to look at a whole record, which is 10 fields, but only search the first 8 and return the records, including the last two. The critria however is from a address form?

    So has post1 post2 for the postcode first and second part, road, street, town, city, county

    could i do this in one query and have it pick the closest match per record ?[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Would the query need to look like this:

    strSQL = "SELECT * FROM [SouthWest] WHERE [post1], [post2], [road], [street], [city] = '" & formPost1 & "','" & formPost2 & "','" & road & "' ;"  etc

    etc ? or am i way off ?

    Re: Return Mutiple Results From Access Dao

    not sure what the .movenext or movefirst means ?

    I did what you said, but not sure what to put on the movenext bit ? I know there would be about 50 + records matching that critria.

    Thank you for your help in this too.

    I have the following code, which connects to a database, searchs postcodes, well the first part of it. But the code i have only returns the first one it finds, and I need it to carry on until there are no more matchs, not sure how do this, as I am very new, and shocked I have made what i have to work:

    Any help would be great!!!

    Re: Open Access And Search Record And Return Results

    Quote from Parsnip

    OK, what I believe you want to do is execute an SQL statement on your database to return any/all records that match the criteria you wish to set. If this is the case, then please provide what your matching criteria are, and what your database name is, the database path and the relevant field names. Then I/someone else will have a chance to provide you with a workable solution.


    Hi Richard, thank you for this, im egar to learn it.

    Right, i have moved all the info from the excel to a new Access DB.

    The file is called : data_sw.mdb

    The Table is called : SouthWest

    The Fields are as follows:

    ID post1 post2 Road Street Town City County Plant tzone

    It needs to search all the fields of every record, which there are 165,000 and return any matching fields.

    The form on excels are on userform: searchlist

    thats it has the same fields, same name of the moment, but if needs be, that can change. Now it does not need to search for the plant, or tzone, but will need to return it with the rest of the record of a possible match. Excel will then need to sort with the closest match at the top. The returning results will be in a sheet called : results starting at Row 1 Column A.

    Hope that helps? thank you again for this.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]With the code above, this line:

    Set objRs = objDb.OpenRecordset(strSQL)

    I get a error '3061 Too few parameters. I can see more can go in, like, "Type", etc, but i am not sure whats needed ?

    Any Ideas ?

    Hi, I have found the following code which I think will open a database and search a field, I am looking to see if we can search per-field:

    any ideas on how it would open a database, look in a record, which may have 8 fields and then search them all, and return a match or close match?

    thanks in advance

    Re: Return Corresponding Data Chosen From ComboBox

    i attached that before i used your code, as it's not searching correctly. I think I need to use it via a database, such as Access ? I do not know how to do that, or if it will will search a whole record, if a cell at a time etc. plus, how do i connect to a access database to search, I can see how to extract info, but not search it?

    Re: Return Corresponding Data Chosen From ComboBox

    like i said, the one with the address is over 35mb, with the added search code and fronts, its over 40mb thats way to big a file to have open on a agents PC, so they can run the smaller file all day, and only need the address when needed

    Re: Return Corresponding Data Chosen From ComboBox

    In that file, there are two files, Data1 is the one holding data about 130,000 address with postcodes, in this example, only about 40 lines to keep the size down.

    The second file, you will find if you goto VB window, there are two userformes:


    and the code is within them.

    searchfile's code, opens data1 and moves the info from data1 to searchfile, because I cant have one file with all of it on, as the files about 40mb!!! to much. Once the code moves the part of the postcode given in the search, it then sieves through looking for the road name, and then thats the main results giving userform addresslist the the ones that match closest.

    Re: Sharing Spreadsheets With Multiple Users

    I was thinking of that, but i do not have any books on how to access databases in VB code. I think it would be faster, just very unsure... any good tutorials or books I can by which concentrate on this?