Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Mar 2008
    Posts
    21

    Unanswered: How to jump to a certain record in a form

    So, I've spent about an hour searching on this subject. I've come really close but am not getting all the way there...

    I'm doing a recipe database, and you guys have already helped me a ton with some delete queries and such. I have an ingredients form where you go through and select items you need from the store (besides the ones that are in your weekly recipes). The form is getting pretty long, so I wanted to put links or buttons at the top to jump to all of the letters of the alphabet. (An M that jumps straight to Mayonnaise, for example.)

    I know you can do it by filter, but I want it to be more user friendly than that.

    Thanks in advance!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    See the search form alphabetically in the code bank. It may give you some ideas.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sounds like you need to be using FIND instead of FILTER?

    Cheers

    ST
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well you could have say a combo box, or a toolbar strip in the form footer which sets the forms recordsource or filter to what you want.

    If you have a lot of records it may make sense to wake the form with the recordsource set to say like "ZQ*", so you don't load any records until the user specifies what records they want.

    You could get creative and use say a combo box, that allows the user to type in the first few letters and repopulate the combo rowsource with say the first 2..3 letters.

  5. #5
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Re

    See if you can find something in the help that
    explains something about: BOOKMARK
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  6. #6
    Join Date
    Mar 2008
    Posts
    21
    All great suggestions - thanks guys. Looks like one of these example databases is exactly what I want. I'm off to try and figure out the code and how to make it work for me. I may be back...

  7. #7
    Join Date
    Mar 2008
    Posts
    21
    Okay - I'm back. That didn't take long, huh? The example I found has the letters across the top of the form just like I wanted. Here's the code for the letter A:

    Me!FType = "A"
    Me.ListBusinesses.Requery
    Call recolor
    Me.F1.ForeColor = 255

    I'm not sure I understand the second line. For me, the field I'm worried about is Ingredients. Do I need to create a query to list ingredients that begin with the letter A? What's throwing me is that I don't see such a query in this example database....

    In the meantime, I'll look at some of your other suggestions.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no you shouldn't need to create a query for each ingredient
    you could put a filter on the cuurrent recordset in a form
    you could alter the forms recordsource to be a query
    eg select <mycolumn1>,<mycolumn2),....<mycolumnN> from <mytable> where <mycolumnx> like "A*" order by mysortorder

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The me.ListBusinesses.Requery in the example issues a requery command to the listbox on the form (the listbox on the form is called ListBusinesses.) If you look at the rowsource query of the ListBusinesses listbox, you'll see that it has criteria in where the BusinessName field is like Forms!FindBusiness!FType. The search form in the example has no recordsource for the form itself (i.e. again, see the rowsource for the listbox). Sorry if this is confusing. The search form is not meant to actually return data to the search form itself but instead open the data form once the ListBusinesses record is selected where the data form has a recordsource query with criteria that equals the ID of the listbox record selected from the search form. It's one of many ways to do what you want to do so please use the example as a suggestion only. There are some other search examples in the code bank to see other methods. I usually don't use the Filter command and instead use some type of query with criteria where I requery that query (or as healdem suggested set the recordsource of the data form to a query) verses a Filter command.

    To use the search form in your scenario where you have an "ingredients" field, you might replace the BusinessName field in the listbox rowsource with your ingredients field (and of course the dbo_Business table with your table) as the rowsource query for the ListBusinesses listbox. Again, use this as an example to design your search form (as obviously you may not want to have a listbox called ListBusinesses but instead something called ListIngredients and program the AfterUpdate event on ListIngredients listbox to go to the selected Ingredient.) (although it sounds more like you won't be going to a specific record as this search form is designed to do (ie. go to a specific business selected) but instead going to all the records with the selected ingredient.) If that's the case, you wouldn't have the ID field in the listbox query on the search form and once an ingredient is selected in the listbox, you might do something like open the data form and set the recordsource of the data form to something like "Select * from mytable where Ingredient Like '" & IngredientVariable & "'". Where IngredientVariable is a variable containing the ingredient string value selected on the search form listbox. I hope that makes sense.
    Last edited by pkstormy; 03-11-08 at 18:25.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Mar 2008
    Posts
    21
    pkstormy - So kind of you to write out an answer so specifically. I didn't get enough sleep last night and am exhausted now, but I'll read over it again tomorrow and give it a shot. Thanks so MUCH!

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    webee33 - I just uploaded a cardfile program in the code bank which you can also look at for alphabetical searching (last page - a new upload). This is a little different than the other alphabetical search but has the ABC buttons on the data entry form (no popup search form and queries the records on the form.) Try and take a look at it if you get a chance to get some more ideas (hold the shift key down when opening it to see the code.) I've tried to use simple and standard example methods but as you can tell, there's a little bit of learning and tweaking. Also, the Northwinds mdb that ships with MSAccess has an ABC example as well (the form is called: Customer Phone List in the Northwinds mdb). Please look at that also, although their example uses (*cough*, *cough*) - Macros and the Filter. If you have more questions, I'll try to answer them tomorrow but I can't give you a definate time I'll be online to answer them.
    Last edited by pkstormy; 03-12-08 at 00:05.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Mar 2008
    Posts
    21
    pkstormy - Okay, I've read through your post again, and I think I'm going to need some more help.

    I'm thinking the overall design of my form may not be right to do this. I do understand better the way you did yours. My concern is that the form exists so that I can update the quantities needed for certain ingredients, so at least the quantity and units fields can't be part of the list box. I need to be able to change them. The form is currently a tabular representation of the Ingredients table, so the ingredients field is just a text box listing each ingredient. I'm trying to get it so that I can click on an "M" at the top and it jumps me to the ingredients that start with M (milk, mayo, etc.) so that I don't have to scroll.

    So, I think I understand healdem's suggestion of altering the form's record source to be a query, but I'm missing the part of how I connect that to a button, because I need to be able to see the whole list again, too. Does that make sense at all?

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Sure webee33.

    If I read you correctly, here's what I think you're trying to accomplish (I hope this is what you're trying to accomplish but please let me know if I'm off track after you try this as there are different ways to do this depending on how you have it setup.) I attached 2 examples. 1 which shows an example with just an ingredients table. The other example is what I "think" you may have setup where you may have a "dish" table and then "ingredients" part of a dish (again, some different ways to set this up and this is an example which I quickly made so please bear with me.) The relational example is a little more advanced but I think it may be more like your setup. Also, in the attached examples I made it so the A, B, C and search box is all on the data form. There are essentially 2 basic ways to do searches. Either the search buttons/criteria text is on the data form, or you click a button and open a search type form. From your response it sounded like you wanted the search items on the same form as the data form.

    See the examples as you go through this...
    First thing to do on your form is make the recordsource and add the fields to the form. The form is going to be based off of the IngredientsTable table (where you want to search for ingredient names as well as update data from the 1 ingredients table.) The form will be a single form (Default View property of the form.) Again, I'm assuming here that you have 1 ingredients table which is the table you want to update. If this is not the case, disregard and see example 2 in the attachment (IngredientsExample2Relational) which also has an IngredientsTable but relational tables as well.

    Then in the design view of the form, click View -> Field List and add the fields to the form. Once this is done, you're going to change the recordsource to a query with criteria in it so that you show only records meeting the criteria (or all records if no criteria has been entered on the form.) What we'll do first is add an unbound text box on the form (say call the unbound text box something like FType - something I standardly use as a name for a search box). I usually like to make the background color of this field yellow to indicate that it's a search field (and isn't a data field) but you can make it blue or whatever color you like. I would just make it something other than a white background.

    Next edit the recordsource query of the form and let's make the SQL statement with the criteria. So the recordsource query might look like this - if you were to view it as a SQL view verses the query view:

    SELECT myIngredientsTable.*
    FROM myIngredientsTable
    WHERE (((myIngredientsTable.IngredientNameField) Like [Forms]![MyFormName]![FType] & "*"))

    Take a look and note that this select statement is a little different as shown in the relational example 2 (because it has a relational table in it.)

    When you make the recordsource query for the form, for the IngredientName field column's criteria row, you'll have Like Forms!MyFormName!FType & *. As healdem had previously suggested, you could also programatically set the recordsource to a SQL statement with the criteria (after the search field is updated or a button is clicked) verses having it static as the recordsource of the form.

    Make sense so far? Again, if your table structure is different, you may have this form designed different with different table/field names. Hopefully though the examples will help guide you to make your design. There's no tricks or complex coding in this design and it's basic stuff that you'll find very useful for your future programs. But essentially you are setting the recordsource of your form to retrieve all records where the Ingredients name begins with whatever the unbound text box (called FType or whatever name you choose.) The & "*" means that you could enter M in the FType field and you'll get all the records where the Ingredient names start with M. Still follow?

    Open the form and test that you can add new records or update the fields on the form since you are combining the search and data entry on 1 form. You want to make sure you can always update/add records on the data form (unless you intentionally don't want to do this.) If you can't update/add records, it means that you've created a recordsource which is not updateable or have set the AllowEdits property on the form to No! This is often the problem when designing these type of forms. If this happens, I've found it easiest to work backwards (ie. start with one simple table and no criteria, making sure the query is not set to show unique records only, then add in any relational tables if needed and the criteria.)

    Once this is done, what's left to do is put in the events so that when FType is updated (or an A, B, C...button is clicked), the recordsource of the form simply requerys (if the criteria is already in the recordsource as we've done - otherwise you again, programatically set the recordsource with criteria.)

    Thus, in the AfterUpdate event of FType, you'd have code like this:

    me.requery

    In the 1 table example, I added a list box which just shows a list of the ingredients and requeried the listbox to equal the criteria as well. The relational example has a listbox which always shows all the records in the "Spice" table.

    * Added note: Disregard (remove) the Me.ListIngredients.Requery in the relational example. I forgot to remove this when I copied the 1 table example to do the relational one.

    Now to put in the buttons and make it so when you click on the A button, FType get's populated with A and the recordsource requerys, when you click the B button, same thing, etc...etc... So put a command button on the form (call it something like FindA or F1 for simplicity and put A in as the Caption.) Then let's put some code in on the OnClick event of FindA:

    Private Sub FindA_Click()
    Me!FType = "A"
    me.requery
    End Sub

    and do the same thing for a B button, C button, etc..etc...

    Private Sub FindB_Click()
    Me!FType = "B"
    me.requery
    End Sub

    Private Sub FindC_Click()
    Me!FType = "C"
    me.requery
    End Sub

    That's it. You'll note that the alphabetical example you previously downloaded also called a function (recolor) which just changed the color of the button clicked (you can use that example to do the same thing.) Now open the form and try clicking on the A button, then the B button, etc...etc..or type something in the FType field. When you do this, it requerys the recordsource (based on criteria) and the form also has the data entry fields so you can update the record. And again, make sure you can update the data fields.

    I sincerely hope I haven't confused you here but it might be best for you to zip and upload an example of how you have things setup if you still need help since the form design all depends on how you have your tables setup and it could be different (but PLEASE give this an attempt to understand it.) I've made a lot of examples for users on this site and it's frustrating sometimes when they don't even attempt to look at the example or try to understand it and ask the same question again and again. I could understand this if the example had a lot of complex code but almost all my examples use simple, basic routines. (and keep in mind that I did these examples VERY quickly to show one of many ways to do it but I tried to add some comments in these examples to help you.)

    Crud...I completely forgot I'm supposed to help my parents (and I've spent the last couple of hours on this now - time flys)....gotta run....take care. I hope the examples help you.
    Attached Files Attached Files
    Last edited by pkstormy; 03-13-08 at 02:21.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    One thing I don't understand is why you don't just use a subform with a combo box to select the ingredient? That way when you type M the auto-complete nature of a combo box would immediately jump to, say, Mayonnaise. Then the user can just press TAB to enter the amount...
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I think it's the A, B, C buttons he's mostly interested in StarTrekker but you're right, a combobox is another easy way to do it and the way I usually do it if I'm not using the alphabetical search type.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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