Auto insert of value

U

usman948

Registered User.
Local time
Today, 14:37
Joined
Sep 5, 2013
Messages
18
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.
 

Attachments

I dont have your version of Access here so I cannot view your data.
 
Thanks for reply.. but I have access-2010 and it will not save in earlier versions due to some limitations of new features of 2010. Is there any other way, pl tell me.
 
I can't see your data either but if you are inserting from a form to a table you can use SQL INSERT/UPDATE. If you want a field updated through a form to a single record then you can use a button and code it with Me.FieldName = Rate. If you have to work out of the datasheet there is a datasheet form that will look like a table but you can program it. For example, I use a DS Form that checks for warranties by using code on the field to DLookup for previous entries. This way I can edit many records in one view as opposed to a single record at a time.
 
Thanks for reply.. it seems u have not seen my database as u may be having old access.. 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 & screen images.. you can see it and reply please.
 

Attachments

usman948 said:
Thanks for reply.. it seems u have not seen my database as u may be having old access.. 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 & screen images.. you can see it and reply please.
Click to expand...

I found that on these forums I get more help if I break down my goals into smaller issues. For example if a wage rate were to change on an employee because of a time period then you would need some code to run on opening of a form or database that would compare the Now() time with the time of attendance(Hire?). If you can break it down to something specific people will generate the code for you practically, but you have to know how to ask because there is so much involved. Look at my threads and how I get answers to my questions. Hope this helps a bit if at all.
 
Thanks Adam.. u have given good point.. yeah by this way I can get help and i will learn also instead of ready made provide by somebody.. thanx again..

well I tried 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.. pl help with your valuable suggestion..thanx
 
Try if the attached database works as you desire!
 

Attachments

Dear JHB.... Thanx and DB working as I wanted, really its great help.. as i have no knowledge about vba, i faced much difficulty in this and u have seen my DB and written code for me.. thanx lot.. further, if faced any vba issue in future i will ask for your help..
 
Good it works - luck with your project, and next time read my signature! :)
 
JHB said:
Good it works - luck with your project, and next time read my signature! :)
Click to expand...

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
 
Dear JHB...

I also wanted to have requery & go to new record after entry of attendate.. I have added to your code
Code: 
Private Sub cboday_AfterUpdate()
  Dim dbs As dao.Database, rst As Recordset
  Set dbs = CurrentDb
  
  Set rst = dbs.OpenRecordset("SELECT TOP 1 WAGE " _
  & "FROM ((EMPLOYEE INNER JOIN DESIG ON EMPLOYEE.DESIGNATION = DESIG.DESIG) " _
  & "INNER JOIN LABOUR ON DESIG.LABOURTYPE = LABOUR.LABOURTYPE) " _
  & "INNER JOIN LABOURWAGE ON LABOUR.LBID = LABOURWAGE.LBID " _
  & "WHERE EMPLID=" & Me.EMPL_ID & " And WAGEDATE<=#" & Me.cboday & "# " _
  & "ORDER BY LABOURWAGE.WAGEDATE DESC")
  If Not rst.EOF Then
    Me.WAGERATE = rst![Wage]
    [COLOR="red"]Me.Requery
    DoCmd.GoToRecord , , acNewRec[/COLOR]
  Else
    MsgBox ("No Wagerate found")
    Me.WAGERATE = 0
  End If
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
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.
 
usman948 said:
Dear JHB...
I also wanted to have requery & go to new record after entry of attendate.. I have added to your code
Code: 
Private Sub cboday_AfterUpdate()
[/QUOTE]
Why do you want that?
You need to explain exactly what you want to do, step by step.
Which new record after entry of attendate, do you want to go to?
Me.Requery, equerries all forms so you start by Empl. No. 1 again.
Click to expand...
 
JHB said:
Why do you want that?
You need to explain exactly what you want to do, step by step.
Which new record after entry of attendate, do you want to go to?
Me.Requery, equerries all forms so you start by Empl. No. 1 again.
Click to expand...

I am entering daily attendance of an employee in attendate column and it will provide rate of wages in wagerate column.. it is working fine.. Thanks to your code..
1) Next I wanted that after one entry of record it should go to next record for entry for same employee in subform datasheet. 2) and also requery/refresh to arrange in order.

If at all above two is not required in the code, than also if I entered duplicate entry of date it will sure give message for duplicate value, primarykey etc.. as I have assigned indexing with no duplicates in table attend for EMPL ID + Attendate (caption name cboday on form).

Kindly see if it can be implement in the code... also pl see and give solution to post # 13.. thanks lot..
 
Put in the below code in the cboday before update event
Code: 
Private Sub cboday_BeforeUpdate(Cancel As Integer)
  Dim dbs As dao.Database, rst As Recordset
  Set dbs = CurrentDb
  
  Set rst = dbs.OpenRecordset("SELECT [EMPL ID], ATTENDATE " _
  & "FROM ATTEND WHERE [EMPL ID]=" & Me.EMPL_ID & " AND cdate(ATTENDATE)=#" & Format(Me.cboday, "mm/dd/yyyy") & "#")
  If Not rst.EOF Then
    MsgBox ("Date already insert")
    Cancel = True
  End If
End Sub
And replace what you have in the after update event, with the below code.
Code: 
Private Sub cboday_AfterUpdate()
  Dim dbs As dao.Database, rst As Recordset
  Set dbs = CurrentDb
  
  Set rst = dbs.OpenRecordset("SELECT TOP 1 WAGE " _
  & "FROM ((EMPLOYEE INNER JOIN DESIG ON EMPLOYEE.DESIGNATION = DESIG.DESIG) " _
  & "INNER JOIN LABOUR ON DESIG.LABOURTYPE = LABOUR.LABOURTYPE) " _
  & "INNER JOIN LABOURWAGE ON LABOUR.LBID = LABOURWAGE.LBID " _
  & "WHERE EMPLID=" & Me.EMPL_ID & " And WAGEDATE<=#" & Me.cboday & "# " _
  & "ORDER BY LABOURWAGE.WAGEDATE DESC")
  If Not rst.EOF Then
    Me.WAGERATE = rst![Wage]
    Me.Requery
    DoCmd.GoToRecord , , acNewRec
  Else
    MsgBox ("No Wagerate found")
    Me.WAGERATE = 0
    Me.Requery
    DoCmd.GoToRecord , , acNewRec
  End If
End Sub
 
JHB said:
Put in the below code in the cboday before update event
Code: 
Private Sub cboday_BeforeUpdate(Cancel As Integer)
  Dim dbs As dao.Database, rst As Recordset
  Set dbs = CurrentDb
  
  Set rst = dbs.OpenRecordset("SELECT [EMPL ID], ATTENDATE " _
  & "FROM ATTEND WHERE [EMPL ID]=" & Me.EMPL_ID & " AND cdate(ATTENDATE)=#" & Format(Me.cboday, "mm/dd/yyyy") & "#")
  If Not rst.EOF Then
    MsgBox ("Date already insert")
    Cancel = True
  End If
End Sub
And replace what you have in the after update event, with the below code.
Code: 
Private Sub cboday_AfterUpdate()
  Dim dbs As dao.Database, rst As Recordset
  Set dbs = CurrentDb
  
  Set rst = dbs.OpenRecordset("SELECT TOP 1 WAGE " _
  & "FROM ((EMPLOYEE INNER JOIN DESIG ON EMPLOYEE.DESIGNATION = DESIG.DESIG) " _
  & "INNER JOIN LABOUR ON DESIG.LABOURTYPE = LABOUR.LABOURTYPE) " _
  & "INNER JOIN LABOURWAGE ON LABOUR.LBID = LABOURWAGE.LBID " _
  & "WHERE EMPLID=" & Me.EMPL_ID & " And WAGEDATE<=#" & Me.cboday & "# " _
  & "ORDER BY LABOURWAGE.WAGEDATE DESC")
  If Not rst.EOF Then
    Me.WAGERATE = rst![Wage]
    Me.Requery
    DoCmd.GoToRecord , , acNewRec
  Else
    MsgBox ("No Wagerate found")
    Me.WAGERATE = 0
    Me.Requery
    DoCmd.GoToRecord , , acNewRec
  End If
End Sub
Click to expand...

Thanks for reply and As u said i have copied & pasted in the form.. & it really worked as i wanted.. it is of great help to me and motivated me to develop further.. Thanks lot..

Also Please reply if there is any solution to my post no#13 of this thread.. thanks in advance...
 
Try the replace it with the below code, (it isn't tested because I haven't you setup).
Code: 
... And WAGEDATE<=#" & Format(Me.cboday, "mm/dd/yyyy") & "#")
 
Dear JHB..

Your correction in code is working absolutely fine as I wanted ie both in indian & US format... Thank you very much for all of your help..

Now I am happy to announce that this thread is now closed and my problem is well answered in this forum with all help from experts like you.. problem is solved as I needed.

Iam working on other DBs also.. if problem faced, will call for help in this forum and I am also watching, studying the VBA codes etc.,

In fact this was my first thread in this forum/any forum, I learnt lot from here and if possible will help to others also.. Thanks to all and good bye..
 
You must log in or register to reply here.

Similar threads

A
Inserting numeric values into a Table, using SQL : What's the correct VBA code to use in the VALUES part of the code ?
Replies
8
Views
2,690
ebs17
E
M
Vba code to insert dates to table not working
Replies
11
Views
1,048
ebs17
E
S
Permission Denied while trying to save a back-up copy through VBA code
Replies
5
Views
1,704
SachAccess
S
S
VBA command to create new records in tables and copy value of one field
Replies
15
Views
1,949
sheckay
S
W
CSV or XLSX Bank Statement to Access module (need to import via VBA and then write back to Excel with imported flag)
Replies
14
Views
2,837
Cotswold
Cotswold

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom