Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2007
    Posts
    40

    Unanswered: A form to generate a report?

    Hi, I am fairly new to this whole MS Access malarky (having previously had experience with MySQL/PHP programming). I am trying to generate a training database for a fairly large company. Currently I have 3 tables:

    [Table Employees]
    EmployeeID
    FirstName
    LastName
    ...

    [Table TrainingSessions]
    SessionID
    Name
    Date
    Location
    ...

    [Table Attendance]
    AttendanceID
    EmployeeID
    SessionID

    So what I am trying to do is to create a form where the user can enter an employee name and pull up all the sessions which that employee has attended, in the form of a report. I am using MS Access 2000. I presume, that I need to use Visual Basic in some form to do this. I am not familiar with this language at all, I know C++ and PHP and various other languages but have never had the need to use Visual Basic.

    Manually, it is quite possible for me to create a query which pulls up all the info on an employee, by simply entering the employee name in the criteria box under Employees_Name. However it is trying to automate this process which I am finding difficult. I need to create in essence a "temporary" query to store the information and then create a report based on the information in the temporary query.

    Any thoughts or ideas would be much appreciated. I am sure that this problem is not "hard" for someone advanced with Access/VBA, but I am a bit of a beginner.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You need to create a dynamic query using VBA.
    You say that you have knowledge of (my)SQL so I can assume that you can write a query to produce the result that you want for a single person.

    To make this query dynamic we simply want to change our query criteria (the employee name?) each time we run it.
    Here's a simplified example - note thie highlighted apostrophes - these are key.
    Code:
    Dim SQLstart, SQLwhere, SQLfull As String
    
    SQLstart = "SELECT .... FROM ... " '<<You need to fill this in
    SQLwhere = "WHERE LastName = '" & Me.txtLastName.Value & "'"
    '<<txtLastName is the name of the textbox your users are typing the criteria into
    
    SQLfull = SQLstart & SQLwhere
    MsgBox SQLFull
    Have a play with this and see if you can follow
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2007
    Posts
    348
    george,
    I have a dumb question (no surprise). Why did you break the Select and Where clauses into different variables.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nothing more than to make it easier to understand.
    Nice and simple.

    You're quite right though you could do this very easily in one variable but I thought it'd be best to break the problem down more visually.
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2007
    Posts
    348
    no problem, thanks for the explanation.
    I'm slowly learning the challenges of readability in code.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's much better if you ask when you don't understand something, rather than just take it as given. Here's 2 modifications using only one variable
    Code:
    Dim SQLfull As String
    
    SQLfull = "SELECT .... FROM ... WHERE LastName = '" & Me.txtLastName.Value & "'"
    MsgBox SQLFull
    
    SQLfull = ""
    SQLfull = SQLfull & "SELECT .... "
    SQLfull = SQLfull & "FROM ... "
    SQLfull = SQLfull & "WHERE LastName = '"
    SQLfull = SQLfull & Me.txtLastName.Value
    SQLfull = SQLfull & "'"
    MsgBox SQLFull
    Depending on context and complexity - breaking down the problem into steps can be very valuable. You can always combine them into one uber step later if you fancy.
    You've just got to consider if you'll follow it when you come back to it in 6months time
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2007
    Posts
    348
    Thanks george the fifth (GeorgeV). :-)

  8. #8
    Join Date
    Jun 2007
    Posts
    40
    Quote Originally Posted by georgev
    You need to create a dynamic query using VBA.
    You say that you have knowledge of (my)SQL so I can assume that you can write a query to produce the result that you want for a single person.

    To make this query dynamic we simply want to change our query criteria (the employee name?) each time we run it.
    Here's a simplified example - note thie highlighted apostrophes - these are key.
    Code:
    Dim SQLstart, SQLwhere, SQLfull As String
    
    SQLstart = "SELECT .... FROM ... " '<<You need to fill this in
    SQLwhere = "WHERE LastName = '" & Me.txtLastName.Value & "'"
    '<<txtLastName is the name of the textbox your users are typing the criteria into
    
    SQLfull = SQLstart & SQLwhere
    MsgBox SQLFull
    Have a play with this and see if you can follow
    Hi George, that's great. Looks very intuitive. So I would have some questions:

    1. How do you actually run this SQL query?
    2. Where are the results stored? For example, in PHP/MySQL that I am used to, the function mysql_query returns a pointer to a "result set" which you can then pull information from.
    3. How do you assemble a report from the result of the query?

    Thanks VERY much for your help so far.

  9. #9
    Join Date
    Jun 2007
    Posts
    40
    Hi, I have made some progress with this problem.

    I created a query called qrySpecific for the specific employee query. I made it look essentially the same as my manual query for looking up employee info, but in the Criteria box I typed [Forms]![frmSpecific]![txtName].[Text] where frmSpecific is the form which is meant to generate the query.

    Now when I run the query it says "Enter Parameter Value" and underneath "Forms!frmSpecific!txtName.Text". If I enter an employee name it does bring up the correct info.

    However, I am still stuck on how to make the frmSpecific form bring up the query automatically with the correct parameter value set. I have bound the button on the form to a macro which opens the query, but when I do that, the query is empty! Help!

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by kidburla
    Hi George, that's great. Looks very intuitive. So I would have some questions:

    1. How do you actually run this SQL query?
    2. Where are the results stored? For example, in PHP/MySQL that I am used to, the function mysql_query returns a pointer to a "result set" which you can then pull information from.
    3. How do you assemble a report from the result of the query?

    Thanks VERY much for your help so far.
    1. This is written in VBA which generally means it is event driven. You could put this code on the OnClick event of a button, for example.
    2. Results of a SELECT query are not stored. Think of it as a view if you must. What you describe sounds a lot like a cursor - one "equivalent" of this in Access is called a recordset.
    3. Can you build a report with a query attached? With the query you currently have - create a report that will give you the desired result for a single person (type "smith" for example).

    For a practical coded solution you may wish to look at another example I knocked up here
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And what happens if you change
    Code:
    [Forms]![frmSpecific]![txtName].[Text]
    To
    Code:
    [Forms]![frmSpecific]![txtName].Value
    Make sure that the form is open and the textbox is populated and give it a try.
    George
    Home | Blog

Posting Permissions

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