Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    132

    Unanswered: how to compile this table?

    Table ‘studentlectures’ is comprised:
    Code:
    ID   studentname    lectureplace       lecturesubject
    1         jack           A             politics    
    2         steve          A             politics    
    3         jack           B             math    
    4         steve          A             politics    
    5         jack           B             politics    
    6         joe            A             politics    
    .
    .
    154       Jane          B               politics
    I have created a form named “students” and a blank on this form named “studentname”, from where I can select a student name and put [forms]![Students]![studentname ] in the criteria of a query named "query1", to show the records for a specific student. So far, so good.
    Because of a reason, I need a table to be compiled with the result of the above query. I don’t need a query but a table. The table should be compiled to show the records of a specific student whose name is selected from the above mentioned form. Is this possible by VBA or a Query. I am so novice and understand only basics of access. I would appreciate if you could provide the VBA or SQL code to make this table created.
    Thank you
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Yes, it is possible, but it will probably lead to your database file bloating with time, as you will need to clear the destination table down each time before you run the query to repopulate it.
    What can you do with a table that you can't do with a query, just out of interest?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agree with weejas
    creating a (temnporary) table to hold this is silly, a query is the way to go. thats one of the advantages of using a relational db. if you MUST go down this route then you MUST have a separate front end for each user, and that also means bloating the db. not clever not smart (the blaoting bit). in a multi user environment you should already be using a split db and separate front ends

    could you expand on why you need a table not a query?
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2012
    Posts
    132
    Thank you very much for the replies,
    i agree with you, but it seems to me that i have no other choices!
    There is a long story and you might be bored. Here is a short version.
    I was instructed to use a VBA that populates and opens a table with the data I had wanted on a post here:
    http://www.dbforums.com/microsoft-ac...art-query.html
    First, a query named MonthlyLectures_sub was created to put records in the correct order to be used by VBA. Now i need to limit this query to a specific student name. I directly put the student name in the criteria of the query and everything ran fine. Then, i put [forms]![Students]![studentname] in the criteria, in order to get the studentname from a form, instead of writing it directly. Now, if i click the query directly it still runs fine, but the VBA can no longer use the query and shows an error and says: “run time error ‘3061’: too few parameters. Expected 1” . And by debugging, it points to this part of the VBA:
    Code:
    Set rsfrom = CurrentDb.OpenRecordset("ApprovedVisit_sub")
    So it seems to me in no way a query can help me to limit the records and I need to make a table first on which this query will run. Please let me know if I shall post the VBA which I was using.
    Thank you for your time
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    So it seems to me in no way a query can help me to limit the records and I need to make a table first on which this query will run.
    Er, no. It means that you need to have the relevant form open and populated before you invoke the query. Alternatively, you can take the SQL statement that Access has generated for your parameter query, use it to populate a string variable in SQL and replace the parameter call with the relevant value (assuming that this is available on the form from which you are invoking the current troublesome VBA).
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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