Results 1 to 9 of 9

Thread: query from code

  1. #1
    Join Date
    Feb 2007
    Posts
    17

    Unanswered: query from code

    hello
    i have a form that is not bound to any table, i want to use vbscript to query the db and fill some textboxes with the values returned, i want to do this in the most 'native' way possible, i've seen examples using DAO and ADO, but i'm not sure if they are the best option, i just want to query the db using the most simple way

    thanks for your advise

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    if you only have a few text boxes, and the table/tables you need some answers from (the query) then simple "Domain Functions" (DLookup, DMax, DMin, etc.) would be quite easy. But, if you have a lot of records, I would suggest an actual query, then either bind the fields in the query to the text boxes on the form, or you will have to end up using the "Domain Functions" against the query.

    This answer is just a stab in the dark. You really have not provided enough information to really address your issue.
    HTH,

  3. #3
    Join Date
    Feb 2007
    Posts
    17
    hello GolferGuy, thanks for your reply, what i want to do is quite simple, i just want to query the db in response of a user event, then fill some textboxes with the values returned, my problem is that i don't know how to do this from code, i know how to create queries using the designer but i don't know how to get a recorset out of that queries, this can be resumed like this:

    Code:
    Private Sub mods_lista_Click() 'click event of a listbox
    
    'supposed code to execute query
    set rs=query("select field1,field2 from table where id=1")
    
    'then just fill the textboxes
    textbox1.text=rs!field1
    textbox2.text=rs!field2
    
    End Sub
    you see, nothing too hard, just need a straightforwar way to query the db, the most 'native' way possible

    thanks for your advise

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    OK, you want it as simple as possible, and as 'native' as possible. The query that you have written, make it the Record Source of the form, then bind the text boxes to the fields in the query that you want to display in each text box, and poof, you are finished! It really doesn't get any easier than that. If you want to make sure that your users can not update the data, then you can set the Data Properties for the form so the AllowEdit, etc. are set to False.
    HTH,

  5. #5
    Join Date
    Feb 2007
    Posts
    17
    well yes i was thinking about that but i want to do it from code, because the form has other controls that aren't so related to that textboxes and maybe i'll need to update their values from code too

    thanks for your advise

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Then use this syntax:
    me.Textbox1 = dlookup("fieldname", "QueryName", "Put the WHERE clause here")
    The WHERE clause does not include the word "WHERE". For more info, see DLookup in Access help.

  7. #7
    Join Date
    Feb 2005
    Posts
    333
    Is this what you're looking for?

    Code:
     
    Private Sub mods_lista_Click() 'click event of a listbox Dim ws As Workspace Dim db As Database Dim rs As Recordset Dim strsql as String Set ws = DBEngine.Workspaces(0) Set db = ws.Databases(0) strsql = "SELECT field1, field2" & _ "FROM myTable " & _ "WHERE id = 1" 'supposed code to execute query Set rs = db.OpenRecordset(strsql , dbOpenDynaset) 'then just fill the textboxes textbox1.text=rs!field1 textbox2.text=rs!field2 End Sub

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would suggest staying away from DLookup to populate you form. It is very inefficient and you can't be sure your application will stay small (if it is small).

    DAO and ADO are as native as you can get with Access. I would suggest ADO since DAO is an older technology.

    With ADO you need two things, one a connection (the default connection is CurrentProject.Connection) and second a RecordSet.

    You'll want to make sure that your SQL returns some results, so check to make sure the recordset's EOF is not set to True. Your code will look something like:

    Code:
        
        Dim rstSeedLists As New ADODB.Recordset
        Dim cnnTables As New ADODB.Connection
        Dim strSQL As String
        
        cnnTables.ConnectionString = CurrentProject.Connection
        cnnTables.Open
        
        strSQL = "SELECT * FROM tblSeedList WHERE SeedListID=" & lngSeedListID
    
        rstSeedLists.Open strSQL, cnnTables, adOpenForwardOnly, adLockReadOnly
        
        With rstSeedLists
        
            If Not .EOF Then
            
                LookupSeedListQuery = Nz(!QueryName, "")
                
            End If
            
            .Close
        
        End With
    
        Set rstSeedListQuery = Nothing
        Set cnnTables = Nothing

  9. #9
    Join Date
    Feb 2007
    Posts
    17
    thanks for your help guys, ill go with ado

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •