Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: List Box Question...

    I have Header form and sub form based on tables called FIXEDFIELD and LONATABLE with one-to-many relationship on ENO (Employee Number)

    In LOANTABLE other fields are as under

    Eno (FK) - Integer
    PDate - Date
    MonthlyInstallment - Double
    DebitAmt - Double
    CreditAmt - Double

    I will present here some entries to make it very clear.

    Eno Pdate MonthlyInst CreditAmt DebitAmt
    641 01-01-07 100 0 1400
    641 01-02-07 100 140 0
    388 01-01-07 100 0 1500
    388 01-02-07 100 150 0
    329 01-01-07 100 0 1000
    329 01-02-07 100 100 0


    I placed a Listbox (LoanList) to show that how many employees have taken loan THIS MONTH.

    Upon clicking a command button, how can I select only those employee numbers along with DebitAmt value from LOANTABLE who has been granted loan this month?

    I think Row Source of List5 should generate this but what will be the code? Also I need to display name of those employee along with their respective ENO.

    Can someone help me?

    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To add a second item to a listbox, select it within the row source with simple SQL.

    Big hint: (Try it without using this!) SELECT [table1].[field1], [table1].[field2] FROM [table1]:

    Why not try create the list box using the wizard? Compare the row sources from the wizard with what you've already got.

    - GeorgeV
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    I used the Row Source (wizard) and seleced 4 columns from 2 difffrerent table keeping criteria in grid. Entered Pdate criteria as Month(Now()). Saved it and in format tab entereed coloumn desired width. Also set column count to 4.

    But no record displayed.

    How about Control source of List box ?

    Regards,
    Ashfaque

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Ashfaque
    Pdate criteria as Month(Now()).
    This might be your problem
    Pdate: '01/01/07'
    Month(Now()) = 01
    '01/01/07' <> '01'

    If not - try these:
    Row Source Type: table/Query
    Row Source: SELECT [table1].[field1], [table1].[field2] FROM [table1]
    Bound Column: 1

    The bound column decides which field is stored.
    you could easily add a WHERE clause to the row source.
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Yeah...I did it..

    Thanks georgev again

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    No probs

    Perhaps post your solution so that other users can see!

    - GeorgeV
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    This is the Row Source Code for Listbox;

    SELECT LOANTABLE.SNO, LOANTABLE.PDate, LOANTABLE.Eno, LOANTABLE.Debit, FIXEDFIELD.ENAME, FIXEDFIELD.ENO FROM LOANTABLE INNER JOIN FIXEDFIELD ON LOANTABLE.Eno = FIXEDFIELD.ENO WHERE (((LOANTABLE.Debit)>0) AND ((DatePart('m',[PDate]))=DatePart('m',Now()))) ORDER BY LOANTABLE.PDate;

    I just picked up EName from Fixedfield table to display in list and hence I make inner joint.

    Bound column = 1
    Counmn Count = 5
    Column Width = 0";0.8";1";1";1.5"
    Row Source Type = Table/Query

    This bring only amount records in the list that has been given as loan in the CURRENT MONTH (according to current date set in computer)

    Regards,
    Ashfaque

Posting Permissions

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