Auto insert of value

[uk123]

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.

[ItsMe]

Have you done any research with DAO? Another route would be to create an UPDATE query using SQL. You can also combine the two options.

Here is a simple example of DAO

Dim rcd As DAO.Recordset
Set rcd = CurrentDb.OpenRecordset("TableName", dbOpenDynaset)
            rcd.FindFirst "FieldName = 0"
            
                If Not rcd.NoMatch Then
                
                    rcd.Edit
                    rcd![FieldName] = -1
                    rcd.Update
                    
                End If
            
Set rcd = Nothing

[uk123]

Thanks for reply.. it seems u have not seen my database as u have access-2003.. well let me clear..
I have tables -
1. employee (name, designation etc),
2. attend (emplID, attendate, wagerate)
3. Desig (desigID,desig,talbourtype)
4. labourwage(lbid, wage, wagedate)
5. labour( lbid, labourtype)

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 3 months again waged increased and fixed 250 on 1-sept-2013. similarly for semi-skilled & highly skilled etc. when I inserted attendance date of particular employee in attendform say of month August-2013 (any date), it should lookup the wage table and compare the labour type & wage date ie after 1-June-2013 but before 1-sept-2013 & show me wage value as 200 in next column of attendsubform which is bound to attendtable. Also attaching the object definition file..you can view and pl give your valuable suggestion.. thanks

[ItsMe]

I have not seen your DB because I have chosen not to download it. Your table structure, although it is relevant to the task, does not dictate the means.

You have a few options. I have explained a couple approaches in post #2. If you are not comfortable with these approaches I am not sure I can be of any assistance to you. It is up to you to determine if these approaches are suitable or not.

[uk123]

Pl find some images of attendance shhet, labour wage & designation screen

[ItsMe]

Pl find some images ......

You are missing my point. Looking at your DB or viewing screenshots is not going to cause me to approach the problem any differently. In my mind, there are only a couple approaches to updating records. I only use a couple different techniques. Your database's matrix does not affect how I would approach updating a record.

[uk123]

You are missing my point. Looking at your DB or viewing screenshots is not going to cause me to approach the problem any differently. In my mind, there are only a couple approaches to updating records. I only use a couple different techniques. Your database's matrix does not affect how I would approach updating a record.

Thanks for reply and please don't misunderstood me, actually i have little or zero knowledge about VBA codes and DAO etc.. whatever I know by trial method only and so by looking at your code i was not knowing where to put it and how i will get the result..

[ssanfu]

This is a confusing database. If you understand it, great. It took me over 3 hours to kind of understand what was going on.

IF I understood what you want, then this should work.

Open the database. Make sure that all objects are closed.
Type <ctl> G (hold the control key down and press the letter G)
In the left pane of the IDE, double click on "Form_ATTENDSUBFORM"
Paste in the following

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

In the debug menu, Select "Compile Database"
Close the IDE
Close any objects visible.

Open the form "ATTENDFORM"
Select a month, then add an "attenddate".



PS: "Type" is a reserved word in Access and shouldn't be used as an object name. See http://allenbrowne.com/AppIssueBadWord.html
You are using an autonumber as the employee id number. An autonumber should never be displayed or used as meaningful data.
See http://www.utteraccess.com/wiki/index.php/Autonumbers
After you have read the page, at the bottom of the page, note #1 and #3

[uk123]

This is a confusing database. If you understand it, great. It took me over 3 hours to kind of understand what was going on.

IF I understood what you want, then this should work.

Open the database. Make sure that all objects are closed.
Type <ctl> G (hold the control key down and press the letter G)
In the left pane of the IDE, double click on "Form_ATTENDSUBFORM"
Paste in the following

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

In the debug menu, Select "Compile Database"
Close the IDE
Close any objects visible.

Open the form "ATTENDFORM"
Select a month, then add an "attenddate".



PS: "Type" is a reserved word in Access and shouldn't be used as an object name. See http://allenbrowne.com/AppIssueBadWord.html
You are using an autonumber as the employee id number. An autonumber should never be displayed or used as meaningful data.
See http://www.utteraccess.com/wiki/index.php/Autonumbers
After you have read the page, at the bottom of the page, note #1 and #3

Thanx lot .. I have copied your given code to my DB and it really worked like what I wanted, .. as i have no knowledge about vba, i faced much difficulty in this .. you have spent your valuable time for me and seen my DB and written code for me.. thanx lot..
Further I am trying various other options and if help required I will surely ask you..

[ssanfu]

Great! So you ready to make this Solved?

[uk123]

Great! So you ready to make this Solved?

The code u have supplied is perfectly working fine on my PC, but it is having one small glitch.. there is nothing wrong with the code but with date format. When running on my office PC, it was showing wrong wagerate and zero etc .. on review thoroughly I came to know that it is due to date format. Here I have English-US format (mm/dd/yy) and at office English-Indian(dd/mm/yyyy). I can change the format, but it may give error/wrong result in other files/database etc.. Will u please insert some permanent date format in the code itself so that it can run on any pc with any format whatsoever. Thanks lot ..

[uk123]

Dear ssanfu

I also wanted to have requery & go to new record after entry of attendate.. I have added to your 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
        MsgBox ("No Wagerate found")
        Me.WAGERATE = 0
    Else
        ' found record
        Me.WAGERATE = r("Wage")
    End If
    r.Close
    Set r = Nothing
    Me.Requery
    DoCmd.GoToRecord , , acNewRec

End Sub

it works as needed but if duplicate record found it will generate error message and goes to code for debugging. to trap this error code I added following code

Private Sub Form_Error(DataErr As Integer, Response As Integer) If DataErr = 3022 Then
Response = acDataErrContinue
MsgBox "The record already exists. Please modify or delete your entry."
End If



End Sub

This also of no help.. becoz it only works fine and trap the error and shows the required message if I remove the earlier added code ie. Requery & DoCmd.GoToRecord. What i am missing?.where to put exact codes.. dont't know. Pl help. thanx in advance.

[ItsMe]

@Steve
Why do you use parenthesis and quotes to define your field name? Is it because you are using SQL? Just curious and trying to understand something I am not familiar with.

@uk123
For post # 11, maybe you should start another thread for that question. I don't know if that is very easy to solve.

As for your duplicate record thing, you should check your table where your control " Me.WAGERATE" is bound. It does not seem likely this field would not allow for duplicate values. However, this seems to be the only field that is receiving an update. you will probably need to evaluate your form's entire recordset and search for Key values that do not allow for duplicates.

[uk123]

@Steve
Why do you use parenthesis and quotes to define your field name? Is it because you are using SQL? Just curious and trying to understand something I am not familiar with.

@uk123
For post # 11, maybe you should start another thread for that question. I don't know if that is very easy to solve.

As for your duplicate record thing, you should check your table where your control " Me.WAGERATE" is bound. It does not seem likely this field would not allow for duplicate values. However, this seems to be the only field that is receiving an update. you will probably need to evaluate your form's entire recordset and search for Key values that do not allow for duplicates.

Thanks for your suggestions.. I have assigned indexing with no duplicates in table attend for EMPL ID + Attendate (caption name cboday on form)... wagerate is ok for duplicate values..
For post #11, can Me.cboday be made fixed formatted to mm/dd/yyyy in code itself.

[ItsMe]

You can switch dates between different international standards. It is not a straight forward thing to accomplish, especialy if you want to toggle back and forth from one standard to another.