Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Dec 2008
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Unbound Search via Form

    I haven't programmed in access in a few years and when I did I wasn't proficient in it. I still have a very basic understanding. I'm using Access 2007. What I want to do is take my form which has 23 unbound textboxes. The names range from txt1 to txt23. I want to type in a 6 digit number into txt1 and pull information from my table (JMD_tbl), and have it auto-fill the remaining 22 boxes with the information from the table when I click the search button (cmdSearch). I believe I have to start with the following:
    Code:
     
    Private Sub cmdSearch_Click()
    Dim dbs As Database, Rs As Recordset
    Set Rs.ActiveConnection = CurrentProject.Conection
    Set Rs = dbs.OpenRecordset("SELECT * FROM JMD_tbl" WHERE JMD_tbl.RTN = txt1.Text)
     
    txt2.Value = "JMD_tbl.NAME"
    txt3.Value = "JMD_tbl.LOC"
     
    dbs.Close
    End Sub
    Please go gentle it's been awhile and I've lost all my old stuff to go off of.

  2. #2
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Hello -

    First, a quick word about the declaration of the recordset variable....I recommend that you always use an explict declaration for the type of recordset (DAO or ADO) that you want to use. Here is an article I wrote that discusses this in more detail:

    ADO and DAO Library References in Access Databases
    http://www.accessmvp.com/TWickerath/articles/adodao.htm

    I have some Query by Form examples available for download, which you are welcome to have a look at. These samples increase in complexity, with the Custom Dialog Box sample being the easiest, followed by the Elements sample (demonstrates how to iterate a multiselect listbox). The Chap08QBF sample came from chapter 8 of the book "Access 2000 Power Programming", written by F. Scott Barker, with a few enhancements that I added:

    http://www.accessmvp.com/TWickerath/...mdialogbox.zip
    http://www.accessmvp.com/TWickerath/...s/elements.zip
    http://www.accessmvp.com/TWickerath/.../Chap08QBF.zip

    http://www.seattleaccess.org/downloads.htm
    See the download "Query By Form"
    Tom Wickerath, February 12, 2008

    To help you get started with VBA programming, you might want to have a look first at this download on the Seattle Access site:

    DAO - Back To Basics Compilation/Demo by Tom Wickerath, Jan/Feb 2007


    Tom Wickerath
    Microsoft Access MVP

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Is there some good reason for using an unbound form with unbound controls? It would be much simpler if the form and controls were bound.

    You can then just use the combo box wizard to add an unbound Combo Box, and choose to Find a Record, and the wizard will write the simple bit of code needed.
    Regards
    John



  4. #4
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Quote Originally Posted by johnhutchison View Post
    Is there some good reason for using an unbound form with unbound controls? It would be much simpler if the form and controls were bound.

    You can then just use the combo box wizard to add an unbound Combo Box, and choose to Find a Record, and the wizard will write the simple bit of code needed.
    Hi John,

    Using a bound form is really only appropriate for fairly small databases, or if the database is located entirely on a user's local hard drive (no network involved), because you end up pulling more records over the network wire than are usually necessary. The Golden Rule for good performance of a multi-user Access application [or any application, for that matter] is to retrieve only the data needed. The same logic applies to a web page or other application that retrieves data from SQL Server, Oracle or whatever database you are using.

    For more information, check out my Multi-user Applications paper:

    Implementing a Successful Multiuser Access/JET Application
    http://www.accessmvp.com/TWickerath/.../multiuser.htm


    Tom Wickerath
    Microsoft Access MVP

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Thanks Tom, I understand that there can be very good reasons for using unbound forms. But it is also the case that people sometimes make things unnecessarily complicated for themselves by using unbound forms without a good reason.

    So I was just wondering what the reason was here.
    Regards
    John



  6. #6
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    The samples I posted use an unbound main form with a bound subform. Then, when you double-click on a record in the subform, you open a bound form to just that one record selected. So, I'm still enjoying the ease of development using bound forms, but, if the bound form would normally be tied to a large recordset, then I use the Unbound QBF form to help find the appropriate record to open. Does that make sense?

    Question for you as a moderator:
    I'm new to this group. Is there a way to set up email notifications? If so, can it be done without revealing one's real email address?

    Thanks,

    Tom Wickerath
    Microsoft Access MVP

    2006 - 2011

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    That makes sense.

    Go to Settings (up the top, next to My Profile) then to General Settings , then in Default Subscription Mode choose "instantly via email". You will then be automatically subscribed to any thread you start, or any thread that you have replied to. That means you will receive an email about any future replies to that thread.

    subscribe.gif
    Regards
    John



  8. The Following User Says Thank You to johnhutchison For This Useful Post:

    tgw7078 (2011-03-08)

  9. #8
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Thanks. I've been hunting around for that setting, but had not found it on my own.

    Tom

Posting Permissions

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