1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access: novice needs help

Discussion in 'Business Applications' started by Vicente07, Feb 20, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Vicente07

    Vicente07 Thread Starter

    Joined:
    Feb 20, 2013
    Messages:
    6
    First off, I am a novice and trying to teach myself Access as I go along. I have picked up a few things here and there, but at a stand still right now. So I'll start by explaining what I am trying to achieve.

    I'm trying to create a database to clock volunteer hours and then in turn tell me later in the year how many hours per year, per month, per volunteer, etc. Seems simple enough to me, but I hit a speed bump. Here's what I've done for now:


    • Created two tables. First table is for the Volunteers (ID, FirstName, LastName). Second table is for the Hours (ID, Volunteer, Hours, Date, TaskDescription).
    • Created a relationship. This is the first confusing part and not sure I am doing this correct. I linked the ID from the Volunteers table to the volunteer in the Hours table. Is this correct?
    • Created a form....
    The form is where I am hitting the speed bump. I want a form that can search for the volunteers, like in a drop down list linked to the Volunteers table, and plug the hours in for them. But the catch is, if they are not in the drop down list, I want to be able to write a new volunteer in and hours for the new volunteer. So this would update both tables at the same time, if its a new volunteer.

    Hopefully I am making sense. If it doesn't, then say so and I'll try to explain more. I think what I need is a form with a subform. I have yet to try that so it'd be entirely new to me. I'm open to advice and help, like I said I am a novice and hoping this site can help me out.

    Thanks,

    Vicente
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Vicente07, welcome to the Forum.
    I have created a very similar database a few years ago which also was used to log in and log out the volunteers.
    The link between the tables should be Volunteers ID to Volunteer, which should be of the correct field Type, ie if the ID field is an Autonumber the volunteer field should be a type Long ( Integer).
    The correct way to input and update data is to use a Mainform for the Volunteer and a subform for the hours/task with the Master/Child link the ID/Volunteer.
     
  3. Vicente07

    Vicente07 Thread Starter

    Joined:
    Feb 20, 2013
    Messages:
    6
    I setup the tables, but still having difficulty with the form itself. I'll mess around with things to see how to get it going.

    Is it possible to get your email to send you the file? Could really use some help on it. Or send me an example maybe?
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I can provide you with an example, but it does a bit more than the one you are designing.
    I can provide an email, but you can add attachments to your thread using the Advanced and manage attachments buttons.
    Access files should be compacted & repaired and zipped to put them on though.

    Which version of Access are you using?
     
  5. Vicente07

    Vicente07 Thread Starter

    Joined:
    Feb 20, 2013
    Messages:
    6
    OBP,

    Sorry for the late response, Friday came around and then the weekend. :) But I'm working on Access 2007. Here's the file with the tables. Haven't started the form on it because I keep getting stuck at the point where I want the form to search the Volunteers table for a name and if its not there, then update the same table with that name. Then at the same time, be able to add hours for the volunteers.
     

    Attached Files:

  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Here is the first step with the forms and Find combo.
    The second part will be a bit harder as I am showing the full name not just the Last Name.
    Providing the user enters the name in the combo, first name, space and last name I can provide the VBA code to add it to the table.
     

    Attached Files:

  7. Vicente07

    Vicente07 Thread Starter

    Joined:
    Feb 20, 2013
    Messages:
    6
    Thanks OBP. I'll take a look at it and study what you did. I'm sure I'll be back in a couple of days to ask some more questions. But we'll see. Thanks again.
     
  8. Vicente07

    Vicente07 Thread Starter

    Joined:
    Feb 20, 2013
    Messages:
    6
    OBP,

    So I took a look at this and kind of the same problem I was running into when I experimented with the form I wanted. The combo box searches the Volunteer table and updates the text boxes if the name exists, but it does not update the table itself. If its a new volunteer, then have to use the text boxes to add the Volunteer to the table. Is this the correct thinking?

    Is there a way to make a combo box do double duty? I envisioned the combo box looking up names by last name and updating the first name text box if it exist. However, if it doesn't, then the same combo box can add the new last name to the Volunteer table and can write in a first name to go with it. Hopefully that makes sense. Is it possible though or am I dreaming too much?
     
  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    As I said in my previous post, it can be done using VBA code.
    It is easy enough to achieve if the user only enters the last name, but because the search needs to look for both names to ensure it does not exist that makes it harder.
    If you are satisfied with just entering a last name (which is not in the list) and having Access enter it for you that can be done OK.
    If you want to enter both names and have both update then the input has to be controlled so that the VBA code can separate the 2 names to put in the individual fields.
     
  10. Vicente07

    Vicente07 Thread Starter

    Joined:
    Feb 20, 2013
    Messages:
    6
    Oh, apologies for not understanding the previous post. This is definitely beyond me and I don't know much about the VBA coding and how to go about achieving that. Need to start teaching myself it.
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I will provide it.
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1090383

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice