Solved Query And Form Design (1 Viewer)

smtazulislam

Member
Local time
Today, 13:13
Joined
Mar 27, 2020
Messages
806
Hey access all experts,
I have a database but before I have to entry Payroll Or Salary Transfer in an excel sheet, now I like to update data in access database.
So, I needs your help that, these table how can create easily query and form for easy to entry data and search,

NOTE: if any expert want to modify allowed.
Many Thanks

tblAbsentDates
tblAllowanceData
tblContract
tblDeductionProcedure
tblPayroll
tblStopSalary
tblBankTransfer

===================================
in this tblTransactionWorkers little criteria like :
TransferDate- If same EmpleeID will transfer another company then status text should change automatic Transfer And LeaveDate field - change last date of transfer
Please see here tblTransactionWorkers
 

Attachments

  • ExamplePayRoll.accdb
    1.8 MB · Views: 85

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:13
Joined
May 7, 2009
Messages
19,175
open tblTransactionWorkers and add new record (same EmployeeID to transfer).
see the table in design view, and look at the After Insert Data Macro.
 

Attachments

  • ExamplePayRoll.zip
    312.9 KB · Views: 94

smtazulislam

Member
Local time
Today, 13:13
Joined
Mar 27, 2020
Messages
806
open tblTransactionWorkers and add new record (same EmployeeID to transfer).
see the table in design view, and look at the After Insert Data Macro.
Thank you for reply, I appreciate you have done my before thread.
I want automatic insert transferdate field as same date in LeaveDate field, if same EmployeeID
Exp:
ID -EmplyeeID- TransferDate- Company-Branch-BranchType- LeaveDate- Status
1 - 3-----------01/06/2020 - A comp. - HO----Main------- 10/06/2020- Transfer. (Here LeaveDate and Status insert Automatic, bcz Same EmployeeID
2 - 3-----------10/06/2020 - B comp. -HO----Main ------- -Current

I try this code. but it is insert same field in the row, not I want to previous row insert .... HOW ?
Code:
Private Sub TransferDate_AfterUpdate()
    On Error GoTo ErrorHandler
    Dim Chg As String
    Chg = Me.TransferDate
   
    If Not IsNull(Chg) Then
        Me.LeaveDate = Chg
    End If
   
Exit_Procedure:
    Exit Sub
       
ErrorHandler:
    MsgBox Err.Number, Err.Description
    Resume Exit_Procedure
    Resume
End Sub

I thing you understand me what I mean or I want.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:13
Joined
May 7, 2009
Messages
19,175
View the After Insert data macro of your table while in design view (of your table).
There is a portion that Setvalue to [TransferDate] -1.
Remove the -1.

You also remove the transferdate_afterupdate since we are already doing it through data macro.
 

smtazulislam

Member
Local time
Today, 13:13
Joined
Mar 27, 2020
Messages
806
View the After Insert data macro of your table while in design view (of your table).
There is a portion that Setvalue to [TransferDate] -1.
Remove the -1.

You also remove the transferdate_afterupdate since we are already doing it through data macro.
I remove -1, but not insert Automatic LeaveDate.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:13
Joined
May 7, 2009
Messages
19,175
i forget to make the After Update macro on the table.
 

Attachments

  • ExamplePayRoll.zip
    312 KB · Views: 98

smtazulislam

Member
Local time
Today, 13:13
Joined
Mar 27, 2020
Messages
806
arnelgp can I get help for another table on this thread. I appreciate... Thank you.
 
Last edited:

smtazulislam

Member
Local time
Today, 13:13
Joined
Mar 27, 2020
Messages
806
which thread?
Thanks for quick replies. this thread,
for below list tables need to advice for design form and Query, I again explain you that, this table create in excel file. but I like to access database, and it was almost complete... Now I need also add salary information activate in my database.
tblAbsentDates
tblAllowanceData
tblContract
tblDeductionProcedure
tblPayroll
tblStopSalary
tblBankTransfer


Thank you so much,
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:13
Joined
May 7, 2009
Messages
19,175
i am afraid i cannot if you want the Whole program/package.
it will take lot of time and code.
show the Excel file and someone, perhaps will convert it to Access.
 

Rene vK

Member
Local time
Today, 11:13
Joined
Mar 3, 2013
Messages
123
Thanks for quick replies. this thread,
for below list tables need to advice for design form and Query, I again explain you that, this table create in excel file. but I like to access database, and it was almost complete... Now I need also add salary information activate in my database.
tblAbsentDates
tblAllowanceData
tblContract
tblDeductionProcedure
tblPayroll
tblStopSalary
tblBankTransfer


Thank you so much,

That is a lot to ask.
 

smtazulislam

Member
Local time
Today, 13:13
Joined
Mar 27, 2020
Messages
806
i am afraid i cannot if you want the Whole program/package.
it will take lot of time and code.
show the Excel file and someone, perhaps will convert it to Access.
thanks for reply. Sorry for late reply.
I dont want to code for Form and Form field design, Just need to give advice or method how to work these table, Bcoz, these table are linked PK & FK.
Also advice me how many Form or Sub-form should I created.

thanks you so much.
 

smtazulislam

Member
Local time
Today, 13:13
Joined
Mar 27, 2020
Messages
806
That is a lot to ask.
Hi Rene VK,
I am sorry, I dont know how much time to this work, bcoz, I am totally new for access, before my all of the work in the excel file. Alhumdullillah, I know better an excel. And now I study access everyday 4-6 hours from few months. AWF every post I watching. InShaaAllah, shortly I will be know well.
 

Rene vK

Member
Local time
Today, 11:13
Joined
Mar 3, 2013
Messages
123
Hi Rene VK,
I am sorry, I dont know how much time to this work, bcoz, I am totally new for access, before my all of the work in the excel file. Alhumdullillah, I know better an excel. And now I study access everyday 4-6 hours from few months. AWF every post I watching. InShaaAllah, shortly I will be know well.
I can only say that study will help you on your way. A good way to start developing your db is to make a demo on paper, create tables and imagine how a user would like to use your application. You will make mistakes, things will not work and then you will know exactly what to ask :) . Good luck!
 

smtazulislam

Member
Local time
Today, 13:13
Joined
Mar 27, 2020
Messages
806
I can only say that study will help you on your way. A good way to start developing your db is to make a demo on paper, create tables and imagine how a user would like to use your application. You will make mistakes, things will not work and then you will know exactly what to ask :) . Good luck!
Thanks for your good advice.....
 

smtazulislam

Member
Local time
Today, 13:13
Joined
Mar 27, 2020
Messages
806
Hello,
I updated my db, I would like to check this 3 forms.
I appreciate someone help. Thank you so much.
 

Attachments

  • ExamplePayRoll.accdb
    3 MB · Views: 97

mike60smart

Registered User.
Local time
Today, 10:13
Joined
Aug 6, 2017
Messages
1,899
Hi

You need to stop and go back to basic table design.

In most of your tables you are using Lookup Fields which should NOT be used.

On your Data Input Form you would use a Combobox to obtain these values.
 

smtazulislam

Member
Local time
Today, 13:13
Joined
Mar 27, 2020
Messages
806
Hi

You need to stop and go back to basic table design.

In most of your tables you are using Lookup Fields which should NOT be used.

On your Data Input Form you would use a Combobox to obtain these values.

Hello, Thanks for your reply,
All of LOOKUP table is hide, And in this db is available... Please check
 

Users who are viewing this thread

Top Bottom