Question regarding DLookup Text Boxes

[Minty]

Okay - that all sort of makes sense. As someone that works in the service industry, I think I would hopefully point you to a sensible bit of a restructure of your data, so that moving forwards you don't get stuck.
Engineer Notes
Office Notes
Status

All of these are things that have the following in common, that in your current structure you won't capture. Who did it, when they did it, and a history of those updates.

I would combine all three into one separate table with the following fields,
tblTracking
TrackingID - Autonumber Unique - PK
TaskID - Number - FK from your task List
StatusID - Number - FK from your Status list
EmpID - Number - FK from your soon to be created employee table
txtNote - Memo or text field depending on your needs.
TimeStamp - DateTime - Defaults to Now() so you can see when the status was update.

By recording who makes the note you don't really need an additional field for department (office, technical etc) as they should have a department assigned to them in their employee record.

This way you now have a history of what and when your Task was updated. You can always look up the latest Status on any given task to give you the tasks current status.

 

[Exxili]

Okay - that all sort of makes sense. As someone that works in the service industry, I think I would hopefully point you to a sensible bit of a restructure of your data, so that moving forwards you don't get stuck.
Engineer Notes
Office Notes
Status

All of these are things that have the following in common, that in your current structure you won't capture. Who did it, when they did it, and a history of those updates.

I would combine all three into one separate table with the following fields,
tblTracking
TrackingID - Autonumber Unique - PK
TaskID - Number - FK from your task List
StatusID - Number - FK from your Status list
EmpID - Number - FK from your soon to be created employee table
txtNote - Memo or text field depending on your needs.
TimeStamp - DateTime - Defaults to Now() so you can see when the status was update.

By recording who makes the note you don't really need an additional field for department (office, technical etc) as they should have a department assigned to them in their employee record.

This way you now have a history of what and when your Task was updated. You can always look up the latest Status on any given task to give you the tasks current status.

I see that helps a lot, I am looking to eventually work in this field as I find the challenges quite fun, and I am learning Access and Databases during my spare time! Thank you for looking over it! But then How does a User Assign a customer to the specific task that is being created/edited by selecting (double clicking) from the Subform TaskSrch ?

And Also if i did make that seperate tracking table for all the notes (status is being saved in the tasklist table linked to Taskstatus table) How would those notes be displayed on OfficeTaskAssignment Form?

Id also just like to say thanks for sticking to help me sort out this you have been a fantastic and knowledgeable help!

 

[Minty]

You probably need to have a look at some working database models and some videos demonstrating the functionality. Typically these involve forms with sub forms, displaying Master and Child records.

In your case you would probably have a few of these - off the top of my head you would probably want one to show all tasks for a customer, so Customer details as main form with a list of tasks as a continuous sub form. I would probably have a simple customer look up in the form header. And a filter to only show tasks that are either open , closed or all in the sub form.

You may want another one that lists all tasks as a continuous form with the task details as a single view sub form.

I think before you get any further have a really good study of this web site - http://www.rogersaccesslibrary.com/ and look at the normalisation, structure and then fianlly application design.

If you get the data storage wrong the rest becomes very difficult later. There are samples and videos to learn from.

Determine what you want out of the database, to determine what you need to store into it. Once you think you know what you want to store then look at how to store it. Only after all of that should you get stuck into form design.

 

[Exxili]

[Solved]Re: Question regarding DLookup Text Boxes

You probably need to have a look at some working database models and some videos demonstrating the functionality. Typically these involve forms with sub forms, displaying Master and Child records.

In your case you would probably have a few of these - off the top of my head you would probably want one to show all tasks for a customer, so Customer details as main form with a list of tasks as a continuous sub form. I would probably have a simple customer look up in the form header. And a filter to only show tasks that are either open , closed or all in the sub form.

You may want another one that lists all tasks as a continuous form with the task details as a single view sub form.

I think before you get any further have a really good study of this web site - http://www.rogersaccesslibrary.com/ and look at the normalisation, structure and then fianlly application design.

If you get the data storage wrong the rest becomes very difficult later. There are samples and videos to learn from.

Determine what you want out of the database, to determine what you need to store into it. Once you think you know what you want to store then look at how to store it. Only after all of that should you get stuck into form design.

Hey Minty, Would just like to say thank you, This post helped me on reading about my issue and learning how to solve it!, Fantastic Resource of information at that website, already bookmarked

 

[Minty]

Glad to assist. Always like to hear that someone is learning, rather than just being given code snippets.