Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91

    use query to open form

    Hi all,

    I'm trying to use the results of a query to open a form with the record that is on top of the query.
    in the attached access database, there is a main form customers and inside I have a subform that is based on a query.
    I would like to use the button to run the query and have the first record that is on top passed to the form.

    I have been trying for a week and I simply cant find the solution :-(

    Any chance some one know how to deal with this?

    Greetings.FindOldestLetter.mdb

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    do you want the sub form to show the 1st record, ?
    or
    do you want the button to OPEN a query sorted by 1st record?

    cant you just do a sort of index , where client = txtBoxID?
    "select * from table where [client] = " & me.txtBoxID

  3. #3
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi ranman256,

    I'm actually want to open it in the same form. So the query should run in the background. I'm just green when it gets to more complicated things.

    Greetings.

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    build your query to pull the data,
    Normally, i use TABS, this allows the user to change the sub form query by clickin the tab...phones, dependants, etc.
    but you can use a button.
    on the click event
    set the sub form property:
    sub.sourceobject = query.qs1UserPhones
    or
    sub.sourceobject = query.qs1UserDepends

  5. #5
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi,
    I tried the above code, but no results when I tried to use it directly on the form.
    I did put a button on the main form (where I also have tabs) and then tried, but I get that it can't find the source object qry_next_case or it cant find the object 'sub'

    Im lost in all of this. can you maybe use the sample database that I attached in previous post and show me by example how it works?

    Greetings.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @megatronixs,

    I'm trying to use the results of a query to open a form with the record that is on top of the query.........I have been trying for a week and I simply cant find the solution :-(...............
    I'm actually want to open it in the same form. So the query should run in the background. I'm just green when it gets to more complicated things......
    Just a few comments for your consideration. My recommendation for designing/developing any database is to get a clear description of exactly what you are trying to accomplish. Start with the 30,000 foot overview of the things involved and how they relate. Gradually add detail to remove any ambiguity.

    Your post does not describe the business issue - you have described an approach to a solution ( use the results of a query to open a form) - but there may be other options for HOW to do/achieve whatever you're trying to do.

    Perhaps your current issue is a lack of familiarity with Forms, Events and Queries as you are suggesting. Maybe there are some options to the Form design.

    When I look at your database, I see 2 entities --Customers and Letters --but only 1 table.

    Have you tried describing the purpose of the Form/SubForm you are having difficulty with?
    Sometimes, jumping into Access form design mode and trying to design as you go is not an efficient strategy.
    I'm not saying it can't be done, nor that it shouldn't be done, I'm saying that it can be a long and tortuous route especially if you are just learning.

    I recommend a few tutorials on database design, and some youtube videos on Queries, Forms, and Events.

    You may find these free videos useful:
    http://www.datapigtechnologies.com/f...subforms1.html
    http://www.datapigtechnologies.com/f.../subform2.html

    Good luck.

  7. #7
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi Orange,

    Thanks for you info.
    In the attached database was only for testing, no intention to creae a full database there, but to have something to look at.
    On my current job, I was just selected to maintain a database that was created by some one who left. Now they asked me to create a functionality to prevent people cherry picking up cases. The idea is to have a button on the form that when pressed, it will select the oldest case in database and upen up in the database (the same form).
    I managed to get the query working to get the oldest case (letters) based on some formulas in the query to calculate the days and then descount the days that the case was waiting on clients side. The oldest should be on the top of the query. Now when I press on the button, should pick up the case and open it in the same form. As for now I managed to add the query as a subform in the main form, set it as datasheet view and than making white the background so people can't see the ID of the case and when they double click on it, it will open the case in the form. As i'm still green in Access automation with vba, it is still a "cheap" version that can be done (it just looks ugly on the form).

    So, I would really appriciate if some one could let me know what I try to do is possible and if I can get an example to learn from it, that would be great :-)
    (I don't want to get a complete solution as I will not learn from it, but sometimes I just get stuck as I did now and people are asking me on daily basis if I already finished it).

    Greetings and thanks.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly is the oldest case in database? Oldest StartDate? date_sent_question_client? date_answer_from_client?

    Please provide as much detail as necessary to differentiate these dates.

    From a business process view --what happens when the client doesn't reply?

    How many users on this system?
    Why can't you just select the oldest ?Date? and open the form? What exactly is the purpose of the subform?

  9. #9
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi Orange,

    Once I'm home, I will try to explain it as much as possible and will use the database example to illustrate what I mean.

    Thanks for you interest and willing to help :-)

    Greetings.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi all,

    I made some changes to the test database. It is just a simple one table, one from and one query. The oldest case/letter will be that has the oldest start date. If the case was sent for questions to the client and we get 2 days later the answer, than it will be discounted the 2 days it was out (for example it cold be 10 days old, but taking away the 2 days will make it 8 days old). The case status will be taken into consideration, so it needs to be "for checking" and that will be used to create the query (in the original query I forgot to add it). So that is the criteria I need. When I press the button "next case" the query should run and oldest case will appear on top. This is the one that should be passed to the form so the case can be checked and finalized.

    I hope this is a little bit more clear :-)

    Greetings.

    (This is only for learning purpose, that is why it is build very simple)
    Attached Files Attached Files

  12. #12
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi all,

    found a solution for this. I changed the query to have TOP 1 and now I get only the top result from the query. I had to make a second button to open the case as I'm not able to do this in one button.
    The next problem I have is that when the form opens, it should fill in the user name in the field [Checker_name], but it does not do it when it opens up. I use the below code:

    Code:
    Private Sub btn_next_check_Click()
        Dim stDocName As String
        Dim sSQL4 As String
        Dim varUserLock
        Dim stLinkCriteria As String
    On Error GoTo errr
        sSQL4 = "SELECT TOP 1 ID FROM qry_next_case"
        
        Me.frm_Reviews_subform.Form.RecordSource = sSQL4
        
        Me.frm_Reviews_subform.Form.RecordSource = Me.frm_Reviews_subform.Form.RecordSource
        
        Exit Sub
    errr:
        MsgBox Err.Description
        
        stDocName = "frm_Edit_Reviews"
        DoCmd.OpenForm stDocName, , , , , acWindowNormal
        Me.[Checker_name].Value = Environ("username")
        Me.Refresh
    End Sub
    Any idea how to solve it?

    Greetings.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How can you say found a solution for this.

    and then say Any idea how to solve it?

    Did you look at the subform videos I suggested earlier?

  14. #14
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi Orange,

    Videos are blocked at work and I had some problems with the laptop yesterday.
    maybe it was not 100% solution, but close to it. The only thing that I have that needs to work is to open the form and add the username in the field [Checker_name]. I was googeling and simply can't find nothing on it :-(

    Please, don't be that harsh on me, I'm trying to learn and there is still a long way :-(

    Greetings.

  15. #15
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    you could use Environ(UserName) with a dlookup function if you have a table of employees to autofill the current user into the box you want. Or you could have a simple login screen that your form would pull the username from.

    I would roll with the login screen if I was in your shoes.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-09-2014, 07:09 PM
  2. Replies: 1
    Last Post: 03-02-2014, 01:31 PM
  3. Replies: 2
    Last Post: 08-07-2013, 07:44 AM
  4. Help on report to open form to open query...
    By saseymour in forum Programming
    Replies: 13
    Last Post: 07-16-2013, 08:11 AM
  5. Replies: 1
    Last Post: 05-03-2012, 02:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums