Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2013
    Posts
    22

    Unanswered: Auto insert of value

    Attached herewith manpower attendance system made by me but not able to develop further as I want wage rate should be inserted automatically instead of manually in attendance sheet. In labourwage form, wage rate will be fixed for labour type with wagedate as and when required. Once I add attendate in attendform, it should look-up the table labourwage and auto insert wage rate entry in to wagerate column for the respective month ie from the given wage date up to next given wage date of labourwage. I have tried much, but not succeeded and presently entering manually which is time wasting. This should be possible with writing some code with VB. I am having very little knowledge about VB, hence Iam requesting forum to help me in this regard. Thanks in advance.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2011
    Posts
    413

    Auto insert of value

    Not sure if this is what your after. I don't use macros so I converted everything into VBA.
    It should at least give you a starting point. Your db structure could use some work. Please research naming conventions and begin your coding experience learning vba.

    HTH
    Attached Files Attached Files

  3. #3
    Join Date
    Oct 2013
    Posts
    22

    Re: Auto insert of value

    Thanks for help, but i want like this...

    In table labourwage, wage is fixed as & when w.r.t labour type.. eg.. say skilled.. wage 200 fixed on 1-June-2013 and say after 2 months again waged increased and fixed 250 on 1-aug-2013. similarly for semi-skilled & highly skilled etc. when I inserted attendance date of particular employee in attendform say of month july-2013 (any date), it should lookup the wage table and compare the labour type & wage date ie after 1-June-2013 but before 1-aug-2013 & show me wage value as 200 in wagerate column of attendsubform which is bound to wagerate of attendtable..

    well I tried with this code..

    Code:
    Private Sub cboday_AfterUpdate()
    Dim varMax As Currency
    
        varMax = DLookup("wage", "labourwage", "lbid = " & Forms!attendform.LBID & " And wagedate <= # " & Me!ATTENDATE & "#")
       
        Me!WAGERATE = varMax
        
    End Sub
    It gives the rate but not in the range of wagerate fixed from the particular date onwards upt next date or onwards.. something more vba code required to return exact value. pl help with your valuable suggestion..thanx

  4. #4
    Join Date
    Nov 2011
    Posts
    413
    It seems that you have changed your original question and someone else also downloaded the db, so maybe they can assist you further.

    Good Luck With Your Project.

  5. #5
    Join Date
    Oct 2013
    Posts
    22

    Re: Auto insert of value

    Quote Originally Posted by Burrina View Post
    It seems that you have changed your original question and someone else also downloaded the db, so maybe they can assist you further.

    Good Luck With Your Project.
    Thanx for reply, my question is same as of earlier.. but here I explained in detail.. thanx for your effort in preparing DB for me..
    well.. one of user of other forum have helped me in getting me right code and I am very much thankfull to him. This works as i wanted..

    Here is the code ..

    Code:
    Private Sub cboday_AfterUpdate()
        Dim r As DAO.Recordset
        Dim ssQL As String
    
        ssQL = "SELECT TOP 1 LABOURWAGE.WAGE, LABOUR.LABOURTYPE, LABOURWAGE.WAGEDATE"
        ssQL = ssQL & " FROM LABOUR RIGHT JOIN LABOURWAGE ON LABOUR.LBID = LABOURWAGE.LBID"
        ssQL = ssQL & " WHERE LABOUR.LABOURTYPE = '" & Me.Parent.[TYPE] & "'"
        ssQL = ssQL & " AND LABOURWAGE.WAGEDATE <= #" & Me.cboday & "#"
        ssQL = ssQL & " ORDER BY LABOURWAGE.WAGEDATE DESC;"
        '        Debug.Print ssQL
    
        'open recordset
        Set r = CurrentDb.OpenRecordset(ssQL)
        'check for records
        If r.BOF And r.EOF Then
            ' no record
            Me.WAGERATE = 0
        Else
            ' found record
            Me.WAGERATE = r("Wage")
        End If
        r.Close
        Set r = Nothing
        Me.Requery
    End Sub
    Thanks lot ..
    putur
    Last edited by putur; 10-25-13 at 14:28.

  6. #6
    Join Date
    Oct 2013
    Posts
    22
    Issue is now solved, and thanks to all for help..

Posting Permissions

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