Results 1 to 8 of 8
  1. #1
    alanl is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Location
    North East England
    Posts
    9

    Help with Query & Form

    Can anyone help with this please?
    I have a table containing data on contracts for various dates for various people
    I can run a query to find the ID for any contracts for a given person on a given date (no more than three contracts for a given person on any given date)
    I then want to use the ID to open a Contract Display Form
    How can I get the ID from the Query into the Macro or Code to open the corresponding Contract Display Form?
    Any help would greatly appreciated
    AlanL

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Reading your thread I understand you have the following requirements:

    1) Display contracts on the basis of Client and Date
    2) Not more than 3 Contracts for a particular client to be displayed. (You have not stated the condition by which to short the contracts displayed I used ContractID so the First three for a particular date for a paticular client will appear)
    3) Use ContractID to open the respective Contract Display form.

    This is What I have done:

    1) I table Contract: Fields:
    a) ContractID
    b)ClientName
    c)Con_Date
    d)Details
    e)Amount

    Client_Names entered: RuralGuy,Pbaldy,Cowboy
    Dates: RuralGuy:04/15/2010; Pbaldy: 04/17/2010; Cowboy: 04/16/2010

    I have entered the contracts for each of the clients for the following dates:

    Count of Contracts:
    RuralGuy:04/15/2010=4
    Cowboy:04/16/2010=2
    Pbaldy:04/17/2010=4

    A startup form called exp will open when you open the database. select a client name from the combobox: e.g. Ruralguy

    Then type the date(Follow the dates mentioned above)

    e.g. ClientName=RuralGuy, Date=04/15/2010

    Click on Load Data

    all thought 4 contracts are enlisted to this client on this date only the First three are displayed on the listbox.

    Select any one contract on the list box and Click on Open Contract Display Form.

    Codes used:

    Load Data Button:

    Private Sub Command2_Click()
    On Error GoTo Err_Command2_Click
    Dim strSQl As String

    strSQl = "SELECT TOP 3 ContractID,ClientName,Con_Date,Details,Amount From contract Where ClientName='" & Me.Combo3 & "' And Con_Date=#" & Me.Text5 & "# ORDER BY ContractID"
    Me.List0.RowSource = strSQl
    Me.List0.Requery
    Exit_Command2_Click:
    Exit Sub

    Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click

    End Sub

    Open Contract Display Form:

    Private Sub Command2_Click()
    On Error GoTo Err_Command2_Click
    Dim strSQl As String

    strSQl = "SELECT TOP 3 ContractID,ClientName,Con_Date,Details,Amount From contract Where ClientName='" & Me.Combo3 & "' And Con_Date=#" & Me.Text5 & "# ORDER BY ContractID"
    Me.List0.RowSource = strSQl
    Me.List0.Requery
    Exit_Command2_Click:
    Exit Sub

    Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click

    End Sub


    Explanation:

    I have used an SQL string to populate my ListBox and then selecting a contract on the Listbox I pass the value of the contract ID as a criteria to open my Contract display form.


    Note : Changing the Order By you can arrange the contracts according to Amount:

    strSQl = "SELECT TOP 3 ContractID,ClientName,Con_Date,Details,Amount From contract Where ClientName='" & Me.Combo3 & "' And Con_Date=#" & Me.Text5 & "# ORDER BY Amount"


    if this solves your problem mark this thread solved.

    refer to attached mdb

  3. #3
    alanl is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Location
    North East England
    Posts
    9

    Thank You & Help

    Hi Maximus
    Thank you so much for your code!
    I have adapted it to use my data & displays and it works great although I do have a problem with the dates 1 to 12 of each month excluding 4/4/2010, 5/5/2010, 6/6/2010, etc
    The contracts are not found as the programme seems to be looking USA dates and not UK dates when the day can be a month.
    If I look for contracts on April 7th 2010 (07/04/2010 in the UK) I would have to input July 4th 2010 (04/07/2010 in the UK) but I think 04/07/2010 gets the date April 7th 2010 in USA?
    I hope I have explained this clearly?
    Have you any thoughts / ideas on how I can remedy this?
    AlanL

  4. #4
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    One question is the date field in the table is Date/Time Type or text.

  5. #5
    alanl is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Location
    North East England
    Posts
    9
    Date/Time
    I tested it in your original mdb adding contracts for each day of April with the same result.
    AlanL

  6. #6
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Zip you database and upload let me see the problem. In access 2000 format.

  7. #7
    alanl is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Location
    North East England
    Posts
    9
    Hi attached as requested zip file
    The Main Menu will load from the autoexec
    Click the Act Button and hit return twice, you should have an Act called Magic loaded on a small form
    Click the Diary button on the Act Form and the double click a date with a contract in your exp will load with the Act Name & the Date you clicked on already loaded.
    In the exp I can't get any contracts to display with a date 1 to 12 of any month as per my previous post
    This project is still embryonic so forgive me if some or all of it is naive.
    My screen display is set to 1360 x 768 landscape
    Alanl
    Last edited by alanl; 04-20-2010 at 10:48 AM.

  8. #8
    alanl is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Location
    North East England
    Posts
    9

    Thank You

    Thank you for all your help Alan L

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query to only show a value in a form from a query
    By cwwaicw311 in forum Queries
    Replies: 28
    Last Post: 03-27-2010, 02:31 AM

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