Auto Update VBA code HELP

[Hauler2me]

Hi all,
I need some help with coding to update a separate table using a form.
Be gentle, I did this on the fly with no prior education on VBA.

The database is used as a "check-out" station for multiple computer items within a business.
Basically I have one table that contains information for each item with the following info: (TABLE FORM)

• Make
• Model
• Type of item
• S/N
• Asset Number
• Technician
• Deployed date
• Deployed status (This is a combo box with a source type "Table/Query")
• Reference number

Then I have another table with fields: (EASY SCAN OUT FORM)

• Technician
• Deployed Date
• Reference Number
• Item 1
• Item 2
• Item 3
• Item 4
• Item 5

and stops at Item 20


I have created a form for both tables to make it easier inputting the data but the techs like the second table form better since it is quicker to fill in. So, with that said, I would like the following actions to take place:

1. Data to be updated automatically in the 1st table as they are scanning items in the 2nd table form.
2. Give the prompt saying "Contact Depot Employee" if an item that is being scanned out has the status of "Deployed", "Loaned", "Warranty Out", or "Warranty Pending".
3. Automatically input the technicians name in the 2nd table form

I can provide a sample of the Database if needed to get a full feel of what i am working with.

Thank you in advance for any help that is provided.

 

[Uncle Gizmo]

• Technician
• Deployed Date
• Reference Number
• Item 1
• Item 2
• Item 3
• Item 4
• Item 5

and stops at Item 20

These "Items" should be in a separate table. See this link for more info.

https://sites.google.com/site/msaccess457966vmfjg/articles-by-toeny-hine-awf-vip/excel-in-access

 

[plog]

To expand on what Gizmo hit on: your tables aren't set up properly. Nor is your understanding of how databases are to work.

I know creating input forms is the sexy part of creating a database and everyone wants to start there. But that's not how the process is to work. This is the workflow:

1. Tables - to set up the proper foundation for your database
2. Reports/Queries - to generate the output you want from your database
3. Forms - to create the user interface for your database

You need to set up your tables properly. No matter how you want to layout and format your Forms and Reports, they have no bearing on this process. The data itself drives the table structuring.

My advice is to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), try a few tutorials, then give structuring your tables another shot. Like Gizmo said, you definitely need another table for all those items. I wouldn't be surprised if you needed at least 2 other tables as well.

Lastly, "automatically populating data" isn't something that should be done. I believe I see what you are trying to accomplish and what needs to occur is using primary and foreign keys (https://en.wikipedia.org/wiki/Foreign_key). Those are used to relate data, not automatically populate data. Again, read up on normalization and that will help your understanding of all of this.

 

[Uncle Gizmo]

I think this previous post I answered a while back is somewhat similar to your problem:-

http://www.access-programmers.co.uk/forums/showthread.php?p=1458792#post1458792

 

[Hauler2me]

Main table is "tbl_Depot Inventory Campus"
Check-out table is "Scan Out Table"

I hope this helps to understand what I am trying to do.

Here you will see everything.

I want fields from "Scan Out Table" to update fields in "tbl_Depot Inventory Campus" after data has been put in. As the data it is being scanned in I would like it to search through the main table and find the record that contains the serial number or Asset number and update it accordingly. All it will need is the "Technician Name", "Deployed Date", "Reference Number", and "Deployment Status" updated in the main table.

 

[Uncle Gizmo]

I hope this helps to understand what I am trying to do.

It is obvious you have not taken any notice of any of the advice in this thread. I don't believe you've actually read anything at the links provided!

I recently received this comment from another poster:-

THANK YOU VERRY MUCH for all of your help and patience.



He started out in the same way you, after 125 odd posts, that's when he thanked me. He started to listen and I started helping... you're not listening!!!

It's true I had to be a bit abrupt with him before he would listen. This is the post that I believe made him realise I was serious about what I was saying. It might pay you to read it... HERE:-

 

[Hauler2me]

Thank you Gizmo and plog. I will take some time to read and retrain on this. Sorry if I sounded like i came up to you impatiently. I will let you know how it all turns out.

 

[Uncle Gizmo]

I am aware how difficult it is to see this subtle difference between MS Access and Excel because it was difficult for me when I was learning MS Access. It's a trap and no one likes a trap, no one will let anyone fall into a trap if they can help it. That's how I feel about it, I don't want you to get stuck in the same trap that I did, so I shout whoa! don't go that way...