Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Unanswered: Command button coding

    I have an Employee Table that houses the employee name, shift, availability, hours scheduled etc. I want to add 2 additional items to that form. One being a combo box that lets the user select a particular job their employee worked and the other a command button that when clicked will take the employee, shift, job selected from the combo box and enter that information onto a new table called TblJobWorked and enter the date into a Date Worked field.

    I need it to create a new record every time that button is selected so the user can pull a report that shows employee a worked job b on this date to create a historical reference.

    If you know of an easier way to accomplish this please let me know. The Employee Table is built with approximately 46 employees so I thought it would be better to have the select information I need for the report pulled to a different table.

    Please help as I have inherited this DB to enhance and I am still in the learning stages.

    Thanks
    Angie

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    It sounds reasonable to me. Are you having a problem doing it? Simply create the combo box using the wizard. It should look up a value in a table (jobs) and retain the value. When you click the command button, have VBA code to add the various fields into the work table.

    It certainly makes sense to isolate the work table and jobs table from the employee table. It's called normalization of data, and is to be encouraged.

    Don't be afraid to use the Help file for explanation of any of the terms or steps I mentioned. If you are new to Access, and plan to use it for a while, it's better to familiarize yourself with these basic concepts earlier rather than later.

    Good luck,

    Sam

  3. #3
    Join Date
    Jun 2011
    Posts
    4
    I am getting familiar with some of the basic aspects of Access - I have been going thru the Help section of access and have been reviewing a couple of books, forums & web tutorials. I am however, having difficulty in coding the VBA. I am not well versed in that yet

    I have the combo box set to select the job that the employee worked. The supervisor will go into the employee - add their hours worked, select the job
    worked and then I would like them to hit the command button to pull that information and put it into the new table.

    How do I code the VBA to have the command box do the following:

    copy Employee from Employee Information Table to Employee field in TblJobWorked
    copy Shift from Employee Information Table to Shift field in TblJobWorked
    take the job selection from the combo box in Employee Information and put that information in the Job Worked field on TblJobWorked
    add a date stamp to Date Worked field on TblJobWorked (there is no date field on Employee Information table)

    Thanks again for taking the time to view and answer my questions.

    I added a zip file of the DB.
    Angie
    Attached Files Attached Files
    Last edited by paankadu; 06-29-11 at 18:05. Reason: password for file is coop

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Hi, Angie,

    How about if I give you a prose-y answer, and give you the satisfaction of sweating out the actual code.

    When the command button is clicked, you want VBA to open the TblJobWorked table as a table-type recordset. You want to add the values of the various variables, including the combo box value, into the table fields and save the record. You then want to close the table.

    You will want to use the following objects/methods/properties (I hope you know now the definitions of all three terms): Set, dbOpenTable, DoCmd, .Recordset, .Addnew, .Update, = (variable assignments), .Value

    You may use some more as well, but these are the main ones that come to mind.

    Tell you what. If the code doesn't work the way you expect, post it here and we'll critique it.

    Fair enough?

    Sam
    P.S. I didn't view the upload. I have a poor track record with db's uploaded to the forum. For some reason they don't unzip properly on my computer.

  5. #5
    Join Date
    Jun 2011
    Posts
    4
    Sam,
    I appreciate the advice you have given me thus far. I have been browsing thru different sites and books and have attempted some code. I am not well versed in putting the pieces together yet, but am learning different things - just not what I am looking for for this project.

    Here is some attempted code

    Private Sub Command223_Click()
    On Error GoTo Err_Command223_Click
    Dim db As Database
    Dim RS As Recordset
    DoCmd.GoToRecord , , acNewRec
    Set db = CurrentDb
    Set RS = db.OpenRecordset("TblJobWorked", dbOpenTable)
    RS.AddNew
    RS!Employee = "Employee"
    RS!Shift = "Shift"

    RS.Update
    RS.Close
    Exit_Command223_Click:
    Exit Sub

    Err_Command223_Click:
    MsgBox Err.Description
    Resume Exit_Command223_Click

    End Sub


    If I set the requirment for Job Worked to no it pastes the words Employee and Shift into the appropriate fields. It doesn't carry the actual name and shift of the employee over. I have tried various things to get the names to pull over but it keeps going to record 29 which is a blank new record for table 1. When I hit the add record button it advances the form (table 1) one record which blanks everything out and then says the field TblJobWorked.Job_Worked cannot contain a Null value because required property for the field is set to true.

    How do I get it to pull the name, shift and job worked (selected from the combo box) over and add a date to the new table?

    Thanks in advance
    Angie

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Angie,

    I wouldn't let a computer-generated date stamp rule the roost. What happens if the supervisor is on vacation and the entry is made a day or two later? I would allow for seperate date entry, a la
    Code:
    Me.WorkDate = InputBox("Please Enter the Work Date", ,Date())
    This provides the current date as the default, the correct date format, and allows the user to change the date as well.

    This is only an added suggestion. I said we'd critique. To start, you seem to get the concepts pretty well. You only have to tweak your methodology.

    For example, the "Shift" and "Employee" controls are not strings; they are controls, and as such, they should be used with the Me object, which denotes a control on a "Me", which in this case is the form, as in
    Code:
    RS!Shift = Me.Shift.
    You object naming is also not clear, such as Command223. That's only the name Access generates for the 223rd object to be added to the form, which happens to be a command button. It's up to you to change the name in the Property Sheet to something you'll understand later, such as "cmdAddRecord".

    I have yet to figure out why Access provides for the DoCmd.GoToRecord,,acNewRec command. Since the .AddNew method is mandatory, the other is completely redundant. I have never used it, and I never had a problem. As you saw, it causes incomprehensible error messages. My advice: don't use it. In fact, I never use any form of the GoTo method of the DoCmd object. If I need a particular record, I locate the record through logic, and glean whatever information I need from the record.

    Let me give you some sample code, and see if it will give you a better start on your needs. To begin with, let's say your combobox is called cmbJobs. Let's begin.

    Code:
    Private Sub cmdAddRecord_Click()
    
        Dim RS as DAO.Recordset, WorkDate As Date
    
        On Error GoTo Err_cmdAddRecord_Click
        
        Me.WorkDate = InputBox("Please Enter the Work Date", ,Date())
        Set RS = CurrentDb.OpenRecordset("TblJobWorked", dbOpenTable)
        With RS
            .AddNew
            !Employee = Me.Employee
            !WorkDate = Me.WorkDate  'I took the liberty of adding to the structure
            !Shift = Me.Shift  ' of the table
            !JobWorked = Me.cmbJobs.Value
            .Update
            .Close
        End With
    
    Exit_cmdAddRecord_Click:
        
        Exit Sub
    
    Err_cmdAddRecord_Click:
    
        MsgBox Err.Description
        Resume Exit_cmdAddRecord_Click
    
    End Sub
    If there is anything you don't understand about the code, see the Help file. If you still need help, we'll be there for you.

    Good luck,

    Sam
    Last edited by Sam Landy; 07-10-11 at 15:59. Reason: didn't finish

  7. #7
    Join Date
    Jun 2011
    Posts
    4

    Talking

    Sam,

    I was so close, yet so far. Thank you so much for your guidance. I liked your advice about the date so did implement that.

    It worked like a charm and I have a report set for the users to pull when needed.

    Thanks again!

    I truly appreciate the knowledge that you all are so willing to share.

    Angie

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Great! I'm happy it worked out for you. I find Access a barrel of fun. Really! I hope you will, too.

    Sam

Posting Permissions

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