Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Populate controls on a form from a query

    Got a bit of a problem. I've created a form based on a table, the form also has combo boxes linked to a query.

    What I would like to know is how can I populate the rest of the controls on the form with the information from the query, after a selection is made from the one of the combo boxes?

    This is part of the code I have been using, but I can't seem to get it to work.

    Code:
    Private Sub cmboStudent_AfterUpdate()
    Dim db As Database
    Dim rst As DAO.Recordset
    
    Set rst = db.OpenRecordset("qryGet_Student_Details")
    rst![First_Name] = Me.[First_Name]
    
    End Sub
    It throws up Run-time error '91' when running the form, and I'm using Access 2003.

    Can anyone help.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Wotcha Kev

    You need to instantiate your database object (SET db = ....).

    Also, you have got

    rst![First_Name] = Me.[First_Name]

    the wrong way round

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Just done what you mentioned, and now I'm getting a different run-time error:

    Run-time error '3061': Too few parameters. Expected 1.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    That can imply a couple of different things - could you post your query SQL please. Does it, by any chance, refer to controls on the form? If so - do you know how to add parameters to a form?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2005
    Posts
    240
    Sure, here's the query SQL:

    SELECT QUERCUS_PERSON.ID_NUMBER AS Student_ID, QUERCUS_PERSON.FIRST_NAME AS First_Name, QUERCUS_PERSON.SURNAME AS surname
    FROM QUERCUS_PERSON
    WHERE (((QUERCUS_PERSON.ID_NUMBER)=[forms]![frmStudent_Placment]![cmboStudent]));
    And the cmboStudent above refers to the combo box control on the form.

    I'm not really sure what you mean when you say 'parameters'.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I presume this works fine if you run the query in the query window?

    If so, change the SQL to:
    Code:
     
    PARAMETERS [Forms]![MyForm]![MyCombo] Short;
    SELECT QUERCUS_PERSON.ID_NUMBER AS Student_ID, QUERCUS_PERSON.FIRST_NAME AS First_Name, QUERCUS_PERSON.SURNAME AS surname
    FROM QUERCUS_PERSON
    WHERE QUERCUS_PERSON.ID_NUMBER=[forms]![frmStudent_Placment]![cmboStudent];
    Short is just an integer. You can edit the data type easily by clicking (in the query grid) Query--> Parameters. Just make sure it macthes the data type of QUERCUS_PERSON.ID_NUMBER

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2005
    Posts
    240
    Now it comes up with:

    Run-time error '3061': Too few parameters. Expected 2.
    Grrr, Run-time errors

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you confirm you can run the query from the database window ok and that this message box comes up if you run the code with everything else in the database the same (in particular that form is open and the combo box is populated)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Sep 2005
    Posts
    240
    Yeah I can run the query fine, and the combo box on the form is populated.
    Code is the same as before, haven't changed anything.
    Just as a note, don't know if this affects it. But the query contains a linked tables from an Oracle database.
    And also I looked at the tables design and it said that the data type was Decimal for the ID Number (don't ask me why, I didn't create it), and when trying to set the parameter to Decimal in SQL mode it comes back with a SYNTAX error, and through in Design mode ---> Query ---> Parameters it keeps changing it from Decimal to Value.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Tell you what - try this:

    Code:
     
    
    Private Sub cmboStudent_AfterUpdate()
    Dim db As Database Dim rst As DAO.Recordset Dim sSQL as string
    sSQL = "SELECT ID_NUMBER AS Student_ID, FIRST_NAME AS First_Name, " & _
    " SURNAME AS surname FROM QUERCUS_PERSON " & _
    " WHERE ID_NUMBER= " & Me.cmboStudent
    Set rst = db.OpenRecordset(sSQL)
    Me.[First_Name] = rst![First_Name] End Sub
    and bypass the query entirely
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Sep 2005
    Posts
    240
    Guess what, another run-time error.
    I'm back to:

    Run-time error '91': Object variable or With block variable not set
    edit: You forgot the Set db=....

    Now I've got this:

    Run-time error '3103': Circular reference caused by alias 'First_Name' in query's definition's SELECT list.
    Last edited by KevCB226; 09-29-05 at 11:13.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yep - forgot the instantiation

    Ok - that explains the earlier message - quite often with DAO recordsets you get that error when there is a syntax error in the sql.

    Code:
     
    
    Private Sub cmboStudent_AfterUpdate()
    Dim db As Database
    Dim rst As DAO.Recordset Dim sSQL as string Set db = Application.Currentdb
    sSQL = "SELECT ID_NUMBER AS Student_ID, FIRST_NAME, " & _
    " SURNAME FROM QUERCUS_PERSON " & _
    " WHERE ID_NUMBER= " & Me.cmboStudent
    Set rst = db.OpenRecordset(sSQL) Me.[First_Name] = rst![First_Name] End Sub
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Sep 2005
    Posts
    240
    Managed to get that to work with the SQL, wonder why the query didn't work

    Anyway, I've managed to get one of the controls working, now just got to get the rest of them working, problem is they are in other tables. You've been a great help so far, but I don't know if you'll want to help me after you see this

    Anyway, I have the tables linked in another query and here is it's SQL:

    SELECT DISTINCTROW
    FROM QUERCUS_PERSON INNER JOIN ((((QUERCUS_STUDENT_COURSE_DETAIL INNER JOIN QUERCUS_COURSE_INSTANCE ON QUERCUS_STUDENT_COURSE_DETAIL.COURSE_INSTANCE = QUERCUS_COURSE_INSTANCE.OBJECT_ID) INNER JOIN QUERCUS_MODE_OF_STUDY ON QUERCUS_COURSE_INSTANCE.MODE_OF_STUDY = QUERCUS_MODE_OF_STUDY.OBJECT_ID) INNER JOIN QUERCUS_COURSE ON QUERCUS_COURSE_INSTANCE.COURSE = QUERCUS_COURSE.OBJECT_ID) INNER JOIN QUERCUS_STATUS ON QUERCUS_STUDENT_COURSE_DETAIL.STATUS = QUERCUS_STATUS.OBJECT_ID) ON QUERCUS_PERSON.OBJECT_ID = QUERCUS_STUDENT_COURSE_DETAIL.PERSON
    WHERE (((QUERCUS_COURSE_INSTANCE.ACADEMIC_YEAR)=[forms]![frmMenu]![AcademicYear]) AND ((QUERCUS_MODE_OF_STUDY.MODE_OF_STUDY) In ("FULL-TIME_FULL_Y","SANDWICH","FULL-TIME_LESS_T","FTS","04")) AND ((QUERCUS_STATUS.STATUS)="R") AND ((QUERCUS_COURSE.COURSE)=[forms]![frmStudent_Placement]![cmboCourse]) AND ((QUERCUS_COURSE_INSTANCE.COURSE_YEAR)=[forms]![frmStudent_Placement]![cmboCourse_Year]));
    A lot isn't it, anyway the fields I need are as follows:

    QUERCUS_PERSON.TITLE
    QUERCUS_PERSON.FIRST_NAME
    QUERCUS_PERSON.MIDDLE_NAME
    QUERCUS_PERSON.SURNAME

    QUERCUS_COURSE.DESCRIPTION

    QUERCUS_COURSE_INSTANCE.ACADEMIC_YEAR

    So what I've done is copy and paste the SQL so I have the joins, and placed it in the vba script, ran it but it came up with the following message when selecting from the combo box:

    Query must have at least one desitination field.
    This is the SQL script in vba:

    Private Sub cmboStudent_AfterUpdate()
    Dim db As Database
    Dim rst As DAO.Recordset
    Dim sSQL As String

    Set db = CurrentDb()
    sSQL = "SELECT QUERCUS_PERSON.ID_NUMBER AS Student_ID, QUERCUS_PERSON.TITLE," & _
    " QUERCUS_PERSON.FIRST_NAME, QUERCUS_PERSON.MIDDLE_NAME " & _
    " QUERCUS_PERSON.SURNAME, QUERCUS_COURSE.DESCRIPTION, QUERCUS_COURSE_INSTANCE.ACADEMIC_YEAR " & _
    " FROM QUERCUS_PERSON INNER JOIN ((((QUERCUS_STUDENT_COURSE_DETAIL INNER JOIN QUERCUS_COURSE_INSTANCE ON QUERCUS_STUDENT_COURSE_DETAIL.COURSE_INSTANCE = QUERCUS_COURSE_INSTANCE.OBJECT_ID)" & _
    " INNER JOIN QUERCUS_COURSE ON QUERCUS_COURSE_INSTANCE.COURSE = QUERCUS_COURSE.OBJECT_ID)" & _
    " INNER JOIN QUERCUS_STATUS ON QUERCUS_STUDENT_COURSE_DETAIL.STATUS = QUERCUS_STATUS.OBJECT_ID) ON QUERCUS_PERSON.OBJECT_ID = QUERCUS_STUDENT_COURSE_DETAIL.PERSON))))" & _
    " WHERE QUERCUS_PERSON.ID_NUMBER= " & Me.cmboStudent

    Set rst = db.OpenRecordset(sSQL)

    Me.[Title] = rst![Title]
    Me.[First_Name] = rst![First_Name]
    Me.[Middle_Name] = rst![Middle_Name]
    Me.[Surname] = rst![Surname]
    Me.[Course_Description] = rst![DESCRIPTION]
    Me.[Academic_Year] = rst![Academic_Year]

    End Sub
    Can you help?

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Could you pop

    Code:
    Debug.Print sSQL
    in just before

    Code:
     Set rst = db.OpenRecordset(sSQL)
    please. This will print the SQL statement to the immediate window (View--> Immediate Window). Could you post the SQL?

    Cheers
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Sep 2005
    Posts
    240
    Just found it, but I can't get it to run the script to displaying something?

    Update:
    When I run the form and switch to the Immediate Window, nothing is displayed.
    Last edited by KevCB226; 09-30-05 at 06:21.

Posting Permissions

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