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.

Is there a way to have the excel window minimized while a userform is open so it looks tidy in VB????

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:

'  SQL statement to query the database with
strSQL = "SELECT [post1], [post2], [road], [town], [city], [plant], [tzone] FROM [SouthWest] WHERE ([post1] Like '" & formPost1 & "' OR [post2] Like '" & formPost2 & "' AND [road] Like '" & formRoad & "' OR [town] Like '" & formTown & "' OR [city] Like '" & formCity & "') ORDER BY ([road] Like '" & formRoad & "') DESC;"
     '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)
     
     'Takes the first result from the query and prints the fields
     'on to the XL spreadsheet
     r = 1
     i = 0
     
    'If an error goto msgbox
    'On Error GoTo errorHandler
        
    ' Search for the records with the above critira
    With objRs
    
    'No record was found, so stops looking and ends sub
    i = objRs.RecordCount
    If i = 0 Then
    MsgBox "No Address found!", , "CSOS Error"
    Set objRs = Nothing
    Set objDb = Nothing
    Set objEngine = Nothing
    searchform.disbox.Text = "Done!"
    searchform.Hide
    Exit Sub
    End If
    
        .MoveFirst ' find first record
        


        Worksheets("Front").Cells(r, 1).Value = ![post1] & " " & ![post2] & " " & ![road] & " " & ![town] & " " & ![city]
        Worksheets("Front").Cells(r, 2).Value = ![plant]
        Worksheets("Front").Cells(r, 3).Value = ![tzone]
        
        Do Until objRs.EOF  ' keeping searching and pulling records till the critria not meet
        
        r = r + 1
        
        Worksheets("Front").Cells(r, 1).Value = ![post1] & " " & ![post2] & " " & ![road] & " " & ![city]
        Worksheets("Front").Cells(r, 2).Value = ![plant]
        Worksheets("Front").Cells(r, 3).Value = ![tzone]
        .MoveNext
        
        Loop
        
    End With

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

post1 = BS21

and

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

Code

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 & "')) ;"

z

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:

r = 1
        
    With objRs
    
        .MoveFirst
        
        Cells(r, 1).Value = ![ID] & ![post1] & ![post2]
        
        Do Until r = 25
        
        r = r + 1


        .MoveNext
        
        Cells(r, 1).Value = ![ID] & ![post1] & ![post2]
        
        Loop
        
    End With

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:

Sub SearchAdd_test()
     
     'The  name of the database
    Const STR_DB_NAME As String = "C:\Documents and Settings\Andrew\My Documents\CSOS_ADD1.mdb"
     
     Dim objEngine As DBEngine
    Dim objDb As Database
    Dim objRs As Recordset
    Dim strSQL As String
    Dim recID As String
    Dim formPost1 As String
        
     'T to look for in the database
    formPost1 = searchform.post1
     
     '  SQL statement to query the database with
    strSQL = "SELECT * FROM [SouthWest] WHERE [post1] = '" & formPost1 & "' ;"
     
     '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)
     
     'Takes the first result from the query and prints the fields
     'on to the XL spreadsheet
    With objRs
        .MoveFirst
        Cells(1, 1).Value = ![ID]
        
         'If there is a 2nd  record .EOF will return False
        'If Not .EOF Then
             'MsgBox "More than 1 record has been found.", vbOKOnly
        'End If
        .MoveNext
    End With
     
     'Destroys the DAO/MS Access objects
    Set objRs = Nothing
    Set objDb = Nothing
    Set objEngine = Nothing
     
End Sub

Any help would be great!!!

Re: Open Access And Search Record And Return Results

Okay sorted this bit,

the problem was the access query string which was wrong.

thank you

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.

Richard

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:

Private Sub MemberDetailsToXL() 
     
     'The name of the database
    Const STR_DB_NAME As String = "C:\Path\MWCSDB.mdb" 
     
     'The DAO (3.51) object library is needed for these  objects
    Dim objEngine As DBEngine 
    Dim objDb As Database 
    Dim objRs As Recordset 
    Dim strSQL As String 
    Dim strTableName As String 
    Dim strFieldName As String 
    Dim lngMemNum As Long 
     
     'The name of the table/DataSource and field to look in
    strTableName = "tblName" 
    strFieldName = "[lngMemID]" 
     
     'The member number to look for in the database
    lngMemNum = CLng(ActiveCell.Value) 
     
     ' SQL statement to query the database with
    strSQL = "SELECT * FROM " & strTableName & _ 
    " WHERE " & strFieldName & " = " & lngMemNum & ";" 
     
     '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) 
     
     'Takes the first result from the query and prints the fields
     'on to the XL spreadsheet
    With objRs 
        .MoveFirst 
        ActiveCell.Offset(0, 1).Value = ![lngMemID] 
        ActiveCell.Offset(0, 2).Value = ![strName] 
        .MoveNext 
         'If there is a 2nd record .EOF will return False
        If Not .EOF Then 
             MsgBox "More than 1 record has been found.", vbOKOnly 
        End If 
    End With 
     
     'Destroys the DAO/MS Access objects
    Set objRs = Nothing 
    Set objDb = Nothing 
    Set objEngine = Nothing 
     
End Sub

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:

addresslist
searchlist

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?

Re: Return Corresponding Data Chosen From ComboBox

thats a better idea, it's not searching right at the moment, i have attached the files zipped with all of one set of postcode address.

All advice is great thank you.