Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2006
    Location
    MI, USA
    Posts
    14

    Red face Unanswered: Table search, post and save - Solved

    OK, I read I don't know how many threads here and I can't find my answer.

    I am creating a database that checks students into a learning/tutoring center.

    Basically the student would slide their ID card throught a magnetic stip reader, that would pass their ID number to the database, no problems there.
    Next the staff person behind the counter would click a button and the database is suppose to find that students name (first and last) and would post info along with their ID number on another form that has drop down boxes, check boxes and other things for what type of tutoring they want that day.

    I don't want the information in the services_tbl to linked to the students_tbl because the students_tbl gets flushed every semester and a new list gets imported and they may not be on the new list if they leave the university. That is why I want to save the name and ID number in the services_tbl as that table will retain records for serveral years (government requirement).

    The problem I am having is I can't figure out now to search for that students specific information and then save some of it to a different table and then save that record.

    I may be over complicating this, but I just can't find the answer.

    PLEASE HELP!!!!!

    Access 2003

    Students_tbl
    studentID
    fname
    lname
    degree
    year
    phone
    address
    email

    Services_tbl
    serviceID
    studentID
    fname
    lname
    degree
    tutorID
    subject
    date/time
    Last edited by Ferris_Dude; 06-07-06 at 11:30. Reason: Problem was solved so I updated the title.

  2. #2
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    Right, i belive this sounds like another homework question!

    If im wrong then sorry but he is the help im willing to give

    Obviously the Magentic strip will contain the same unique id as the person being trained on that day so for that reason you need to grab that unique id.

    Put that into a variable

    On the form that you want to put the specifics about that person create a drop down box with that unique id (set that as the first field) and the users first name, last name etc.

    Get the information from the variable and set focus on the drop down, place the variable into the drop down and then set focus to the next control, this should bring up the user information.

    It is very hard to advise on this because i dont know how much VBA you know becuase there would be better coding ways to do with that require a bit more coding.

    The form with the drop down on would be databinded to the services table but the dropdown would have a databind from the services table but a lookup from the users table.
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  3. #3
    Join Date
    Jun 2006
    Location
    MI, USA
    Posts
    14
    Sorry, I forgot to tell you that this is for the Learning Center at the university where I work. This is not an assignment (well, I guess it is a work assignment, but not from a faculty member).
    If I were to use drop downs on the second form that will bring up the information, wouldn't that require a relationship with the students_tbl? And if there is a relationship with that table and the student gets deleted from it, wouldn't that cause a problem?

  4. #4
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    Basically in the table you want to write the information to you drag all the information from the combo box.

    If you use a lookup on the drop down to the original table and get the student name, id etc

    I presume you know about drop downs with lookups and stuff

    Now, when you take the focus from the drop down to the next control you have two more fields on the form that will take the first name and the second name and you trigger some code on the drop down "Lost Focus" event or the "After Update" thats goes something like this
    Code:
    With DropDown1
     me.id = .column(0) 'remember the array starts at 0 so this would be the ID field
    me.name = .column(1)
    me.surname = .column(2)
    end with
    If you have created two fields on the form you are working with that are name and surname and they are bound to the table that never gets flushed then that will put the name and surname in

    This also puts the ID in but i only did that to demostrate the first index of an array. Like i said before i presume you know how to work with a combo box and how to fill the combo box with another tables details, show more than one column etc.

    In this situation, you would bind the combobox to column 0, set the column widths to 0.1,4,4 (so that it does not show the id but you can still enter id's into it) and show three columns

    I hope this makes sense
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  5. #5
    Join Date
    Jun 2006
    Location
    MI, USA
    Posts
    14
    I will try to implement what you have described. I am not too familiar with editing combo boxes and list boxes, so I will have to pick that knowledge up.

    Thanks so far, I will let you know how it goes.

  6. #6
    Join Date
    Jun 2006
    Location
    MI, USA
    Posts
    14
    Chris,
    Hey, thanks a lot! That was the key to getting this database going.
    I did mine a little different, but I used your script example as the basis, with out it I would not have gotten this working.
    You have renewed my faith in Access!
    100 points to Chris!!!

  7. #7
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    No probs, glad you could get it working.

    Access is a great piece of kit, it just has two major flaws

    Its slow and its really slow!

    Its a shame really, i was convinced that on the next release of access (2003) that they would implement some kind of SQL based speed increase into the database. I know it would not make sense to introduce the full speed of SQL into access but at least some of the power.

    Access is great because i have started programming .net of late and having to manually bind all your controls to a datasource is a pain, i moved from access to .net and i have to say, if .net had the same ease to use a datasource and create forms as access then the world would be a better place!

    Anyway im rambling, glad the solution works
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  8. #8
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    Could you update your title for this question to read [Solved] and the question name, makes it easier for people to know what has been sorted and what hasnt.

    Cheers

    Chris
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

Posting Permissions

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