Find and Append New Record Using Form

[westbandits]

Dear All,

Hello, I'm from Malaysia. I need advice and guide here. Currently with my new job, involves a lot of data entry records. I'm a beginner in Access 2007,since I'm not very good with VBA, I would be appreciated if anybody could assist me in the following scenario;

I have 6 Tables, I need to use a form, first I need to search the Employee name in Table A (by key in instead of pull down combo boxes), then Access will show me the record I need to see in Table A, B and C. After record have shown, I need to key in the date and time in Table D. Each employee will have a unique ID in Table A with relation to Table A, B, C, D and E. Only in the Table D, the employee ID is not a primary Key but it does have a sequence entry ID which set to primary key. Table D will store all the working date and hours of each employee records. How to go about it using the simple form design instead of VBA.

How to store a value to a table which have been calculated in the Query table??

 

[wiklendt]

hi westbandits, welcome to the forums.

first off, to create a form you can use the form wizard. make your main form based off the employee data, including the EmployeeID. then make a separate form one each for the data in tables B, C, D, E - make sure to include the EmployeeID in each of these.

then go back to your first employee form, and use the 'subform' control to place the other forms you made onto the first employee form (these other forms are now known as subforms, because they are dependent on the main form for information - that is, the EmployeeID). depending on the size of these forms, you may decide to use a 'tab control' to place each subform on your parent form. When the subform control wizard asks you for relationship info, select to define your own relationships, and just select your EmployeeID in both those combo boxes.

lastly, to search your records, i suggest you don't 'key in' the employee name, but i recommend placing a listbox on the main form, which lists your employees. when you create this list, access ought to give you three options about how to handle the data - choose the last one, where it says something to the effect of: "find a record in the form based on the selection".

this is all so far without needing to know any VBA - access automatically makes things work for you the way it should.

the reason i don't recommend a 'key in' is because many people don't know how to spell other people's names, or there may be two people with similar names etc... so 'keying in' could be problematic. i think 'keying in' also may require some VBA...

having said that... if you have MANY MANY MANY employees and you don't really want a bit long list in a listbox, then you can try some VERY BASIC vba (literally about 4 lines of copy/paste kind of VBA) by creating a simple search filter for the listbox, as i've previously described - make sure you have a look at the attached image in that post too.

the listbox/filter method would be a much prefered method and if you were to HAVE to use VBA, i'd chose the 'listbox and filter' method over 'key in' method any day.

how does all that sit with you? post back if you have more questions.

 

[westbandits]

Thanks Agnieska for the info. However, as a beginner I'm still blur with what your have explained. I attached the Database, if you dun mind please provide step by step of how to go about it.

Been trying for a few times still not succeed,

 

[wiklendt]

Thanks Agnieska for the info. However, as a beginner I'm still blur with what your have explained. I attached the Database, if you dun mind please provide step by step of how to go about it.

Been trying for a few times still not succeed,

ok, step by step is pretty much my first post.

can you explain what you've tried so far? and can you explain what didn't work, what did you expect and what did you actually get?

i'll have a look at your DB later - for now, it's dinner time, then it will be bed time.

 

[wiklendt]

as some more basic instructions:

click on the first table and go to the create ribbon. click on the button that says "form" (not "form design", just "form) - it should be about the 5th button from the left on that ribbon. save the form with a sensible name. i recommend "frmEmployees" the "frm" part will make it easier for you know what is what. i also recommend you change your table names to have the prefix "tbl"

repeat for all your other tables.

now, open frmEmployees and, in the ribbon, click on the word "view" and select "design view" from the drop-down that appears.


now click on the subform control button in the 'controls' segment in the design tab. here's a picture to help you find it:

when you put this on your form (just click and drag and area to fit), it will ask you what you want in there. select "use and existing form" and choose one from the list.

access will want to know how each form (parent and subform) relates to each other. when you get to it, select "define my own" and then select EmployeeID on both sides (or whatever you called your unique ID primary key for your employees).

congratulations, you have a subform.

repeat for the other forms you made - put them into this main form. to make this prettier, you can use the 'tab' control to put your subforms onto your main form. this control is selected one up, and four across from the 'subform' control.

HTH - dinner time!

 

[Acelink]

I made a sample database that you can reference. It has a few things in it. Multi-Search, Double-Click Listbox->Open related form and example of a combo box.

I would recommend not creating fields for AM/PM/Overtime if possible. Just keep it basic. Two fields: ClockIn/ClockOut.
I don't know how to code it but create a query. ClockIn-ClockOut = range. Hours between 6am-12pm are counted as AM hours. Hours between 12pm-6pm are counted as PM hours. If range is > 10 hours then Range-10=Amount of overtime. (Same idea for week/weekends)

I think that would help a lot... Not only would it help the data entree process it would also lighten up the database.


Hope the sample helps... I'm still learning myself and I created this example to review what I've learned so far.

 

[westbandits]

Dear Agnieszka,

Thanks for the prompt response. How come you didnt invite me for dinner?? just joke. Ok, lets get back what I started. Attach is the form design which I believe you will understand what I'm trying to do here.



FYI,

1) tblEmployee: I will create a form to input each respective employee.
2) tblEmp_work : It will be a sub-form and store to tblEmployee when I key in a new employee record.
3) tblEmp_Work_Hour : This is a dilemma for me, cause if I need to key in each employee I do not know how to instruct Access to pull out those data I need and then get Access to perform calculations and store those value I need for future preview of that records. Now, we cant expect all the employees to come to work every single day and always work on time. (Payroll system shall be implement in later database). In that case, I shall need to key in the working date and hours. Thier pay is different on Weekday and weekday OT, and weekend Normal hour and Ot Hour.

Hope this enlighten what I'm trying to implement here.

 

[Acelink]

I would spend a lot more time into considering what you want your database to do and work from there. Setting up the tables should be your first priority, not creating forms.

I really recommend changing your Emp_Work_Hours table. All you really need for imputing times are Date/StartTime/EndTime.

Take a look at the example db I attached above. That might give you some insight for #3 on your list. I didn't index or have a primary key for giving the people hours. (If I was going to it would be an arbitrary autonumber value)

When you do look at the sample database I attached... Start from Frm_Search_Worker. From there you can open up an overview of the patient you searched/selected and look at their hours logged. From there you can add more hours (Button up top) or look at a detailed version of the hours logged (Double click the list box).
*NOTE: You may have to F9 on Frm_Test after adding hours or changing records. I forgot to add in the refresh events*

 

[westbandits]

I would spend a lot more time into considering what you want your database to do and work from there. Setting up the tables should be your first priority, not creating forms.

I really recommend changing your Emp_Work_Hours table. All you really need for imputing times are Date/StartTime/EndTime.

Take a look at the example db I attached above. That might give you some insight for #3 on your list. I didn't index or have a primary key for giving the people hours. (If I was going to it would be an arbitrary autonumber value)

There are some complex calculations on taxes and bonuses based on working time, date and day. Working time are flexible and is not fix. Employee can some come to work in the morning and skip the afternoon but work on OT. So I try to make it flexible. Your method is good but not flexible. If you in a payroll system, then you will understand what I'm trying to say when comes to argument functions using Access. I'm able to do it using excel but is only spreadsheet which does not meet the criteria in storing database efficiently.

 

[Acelink]

There are some complex calculations on taxes and bonuses based on working time, date and day. Working time are flexible and is not fix. Employee can some come to work in the morning and skip the afternoon but work on OT. So I try to make it flexible. Your method is good but not flexible. If you in a payroll system, then you will understand what I'm trying to say when comes to argument functions using Access. I'm able to do it using excel but is only spreadsheet which does not meet the criteria in storing database efficiently.

If someone comes in and works and the morning then skips afternoon and works the night. Those two work periods should be seperate and get their own record. imo

I've never done payroll. I just know a majority of systems work on conditional statements. If Bob works past the 10hour point in a day/period, he is paid overtime for any additional time. If Bob works past 30hours in a week, he is paid overtime for any additional time.

It isn't hard to add manual overtime too... Create a checkbox saying OT. When that checkbox is markked, have a text field appear where you enter the amount of overtime out of the total time.

John: ClockIn:8AM ClockOut: 6PM Time:10 hours. OT: Yes OTtime: 3hours
John wored 10 hours between 8am and 6pm. Of those 10 hours, 3 hours were overtime.


I'm just trying to lessen the amount of fields you need.

 

[Acelink]

Here is an updated version of that sample database I linked earlier. You navigate around via search. Search allows you to open up worker overview. Overview allows you to View Detailed Worklog (double click on list), add to the worklog or go back to search. (I should of added detailed employee form/add button)

When you add a record to the work log you manually add overtime.

If I knew how to code things correctly, I would make conditions where things would be partially automated. If total time > 10 hours, Overtime = yes. OTtime = Total time - 10 hours. You will always have the option to add overtime manually.



As for the tax thing. Night and day differentiation can be given via queries. If time is between 6am-2pm then = am. If time is between 2pm-10pm then = pm.

 

[wiklendt]

westbandints, i think the best thing for you to do at this stage is hop over to your nearest bookstore and pick up a book on access basics.

make sure the book is on access 2007 as previous versions are much different.

also look out for books with lots of pictures, as one of the hardest parts of learning access (or re-learning 2007 if you've just upgraded) is knowing where all the buttons and menu items are.

once you have the basics (like creating a form and a subform, proper object nomenclature, etc) then this forum can help with the more complex things (like your OT calculations - which are, in fact, quite complex).

a few notes:

* of course, even with the basics we here are happy to help, but you will find it much easier to help yourself if you have some sort of physical reference to look at or some knowledge on HOW to describe what you want or what you have done.

* calculated fields should never be stored. if it can be calculated once, it can be calculated again (via saved query). storing data takes valuable harddisk space, storing a formula for calculation does not take as much.

 

[westbandits]

If someone comes in and works and the morning then skips afternoon and works the night. Those two work periods should be seperate and get their own record. imo

I've never done payroll. I just know a majority of systems work on conditional statements. If Bob works past the 10hour point in a day/period, he is paid overtime for any additional time. If Bob works past 30hours in a week, he is paid overtime for any additional time.

It isn't hard to add manual overtime too... Create a checkbox saying OT. When that checkbox is markked, have a text field appear where you enter the amount of overtime out of the total time.

John: ClockIn:8AM ClockOut: 6PM Time:10 hours. OT: Yes OTtime: 3hours
John wored 10 hours between 8am and 6pm. Of those 10 hours, 3 hours were overtime.


I'm just trying to lessen the amount of fields you need.

Ok, now let me share with you,
Scenario 1 : let say John comes to work punctually in a 1 week full day. on week day he work every day for 8 hours of normal time and 4 hours of OT. And on Saturday he work normal time for 4 hours and 8 hours is OT. Then on Sunday he got 12 hours of OT hours.

Scenario 2: Lulu, come to work same on a week job. However, she has a sick leave on wednesday Afternoon and Saturday Morning.

Now, I do not want to complicate my own database, I want access to determine automatically which day is weekday and weekend base upon date entry, which have been solved with the operator arguments.

 

[westbandits]

westbandints, i think the best thing for you to do at this stage is hop over to your nearest bookstore and pick up a book on access basics.

make sure the book is on access 2007 as previous versions are much different.

also look out for books with lots of pictures, as one of the hardest parts of learning access (or re-learning 2007 if you've just upgraded) is knowing where all the buttons and menu items are.

once you have the basics (like creating a form and a subform, proper object nomenclature, etc) then this forum can help with the more complex things (like your OT calculations - which are, in fact, quite complex).

a few notes:

* of course, even with the basics we here are happy to help, but you will find it much easier to help yourself if you have some sort of physical reference to look at or some knowledge on HOW to describe what you want or what you have done.

* calculated fields should never be stored. if it can be calculated once, it can be calculated again (via saved query). storing data takes valuable harddisk space, storing a formula for calculation does not take as much.

Agnieszka,

I understand what is form and what is sub-form, what I need is on how to search the record without using VBA code, cause I not very good with programming unless is Dbase. Maybe, you don't understand what I'm trying to do here, but thanks for your help. For the complex arguments, I'm not so worry about it, I have knowledge in Excel formulas. As I mentioned early on, excel is spreadsheet and it does not meet database storage criteria, thus you have to link multiple worksheet and sometime I may overlook and key in the wrong data if in a rush time or sleep hours .

Now, I can go to record using the navigation record on the bottom, but I prefer not to use it and most probably if can, I shall disable it. Why?, to prevent data entry error. Hope you understand.

 

[wiklendt]

Agnieszka,

I understand what is form and what is sub-form, what I need is on how to search the record without using VBA code, cause I not very good with programming unless is Dbase.

sorry, that's my bad - on this forum it can be assumed when someone says they're new to Access, they usually mean they bought their first computer yesterday... sorry for the misunderstanding! (i was impress with your normalisation, though!)

Now, I can go to record using the navigation record on the bottom, but I prefer not to use it and most probably if can, I shall disable it. Why?, to prevent data entry error. Hope you understand.

i almost always disable the record navigation at the bottom of the form - it's very ugly and not really user (or even data entry) friendly, like you say.

one word of caution with formulas...

just because both excel and access are microsoft does NOT mean the formulas work the same.

a quick example to write a & b & c & (z or x), you have:

in excel a AND b AND c OR (x,z)

in access (a AND b AND c AND x) OR (a AND b AND c AND z)

took me ages to realise this, so i write it here just to save you some future frustration!

 

[westbandits]

sorry, that's my bad - on this forum it can be assumed when someone says they're new to Access, they usually mean they bought their first computer yesterday... sorry for the misunderstanding! (i was impress with your normalisation, though!)



i almost always disable the record navigation at the bottom of the form - it's very ugly and not really user (or even data entry) friendly, like you say.

one word of caution with formulas...

just because both excel and access are microsoft does NOT mean the formulas work the same.

a quick example to write a & b & c & (z or x), you have:

in excel a AND b AND c OR (x,z)

in access (a AND b AND c AND x) OR (a AND b AND c AND z)

took me ages to realise this, so i write it here just to save you some future frustration!

Gees.....thanks my dear, how sweet of you. I will buy you lunch if possible . Anyway, I'm still frustrated with the forms I created in how to search the damn data. Maybe I'm wrong in the steps of creating the form or I really have to back to basic.

Too bad, basic don't teach this kind of things. I hope you still can help me to fix the method I need. I'm sure some user in this forum love to learn such tricks.

Btw, how do I disable the record navigation on the screen bottom?

FYI, I used to be a Dbase and Foxpro advance programmer in the early 90's when I was very young, age 17 then and now 37. So after more than 10 years that I totally lost touch with database programming, basically I have knowledge of some programming law and procedure on how to execute a command and perform the task, I can understand the process logic.

But, what I don't understand is Access, is a bit frustrated when you are rusted

 

[wiklendt]

Gees.....thanks my dear, how sweet of you. I will buy you lunch if possible . Anyway, I'm still frustrated with the forms I created in how to search the damn data. Maybe I'm wrong in the steps of creating the form or I really have to back to basic.

Too bad, basic don't teach this kind of things. I hope you still can help me to fix the method I need. I'm sure some user in this forum love to learn such tricks.

Btw, how do I disable the record navigation on the screen bottom?

FYI, I used to be a Dbase and Foxpro advance programmer in the early 90's when I was very young, age 17 then and now 37. So after more than 10 years that I totally lost touch with database programming, basically I have knowledge of some programming law and procedure on how to execute a command and perform the task, I can understand the process logic.

But, what I don't understand is Access, is a bit frustrated when you are rusted

like i said, the easiest way to navigate records in a form (for me, anyway!) is to create a listbox and then have access find your record based on your selection.

as for navigation buttons - and also i usually remove record selectors unless i'm using datasheet view - is show below. select form first, then select "no" next to "navigation buttons":

HTH

 

[wiklendt]

no VBA required:

just add a listbox control to your form. access will show this

you select "Find a record on my form based on the value I selected in my listbox" and then click next.

access asks which field you want to search, select the ID and the Employee name and then click next.

access shows you what the listbox will display, it should have the box "hide key column (recommended)" selected. click next.

label your listbox sensibly: "lstEmployees" is probably best if you're searching a list of employees! click finish.

you can now select any employee in the list and access will find the record that it is associated with (including your subforms).

edit: it is this sort of 'basics' that i mean when i say a book is great.