Change table field text based on field in other table

arcaidius

Board Regular
Joined
Dec 4, 2018
Messages
78
(MS ACCESS)

Good afternoon, I am a complete noob on access. I do understand the basics, table set up, queries and forms and such. It's the code and functions that's getting me.

So lets take a crack at it, I have one table that will not receive any new entries, the primary key is auto numbered 1 to 86 representing the item number. The next column will be whether the item is in stock or missing, (Item will be replaced if missing with the same number) manually entered. The next column is whether the item is Issued, Available, or on Hold that I would like to automate based on Issued and returned dates from another table. If the item has an issue date only then it should be "Issued", If it has a returned date then "HOLD" until 6 months after the return date then "Available". The table with the dates will have many entries so it is a one to many relationship.

Is this possible? I have been trying to find it on my own with no luck.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,180
Office Version
  1. 365
Platform
  1. Windows
First thing is to establish a connection to your data. Easiest is to link the table(s) to your front end (fe) database that will contain your queries, forms and reports (and any code/macros). Then create a query using the table(s) that allows you to edit the fields manually. If you can't do the edit manually, it cannot be done programmatically. Then you can create a form that displays the information and either automatically update fields or prompt a user to "allow" the update, or you can have the updates happen when the fe opens. However, either will require that the fe is opened, either by a user or by a scheduling app such as Task Scheduler. The point is, if neither occurs until 7 months after the return date, then that's when the update happens.

What you can do inside of tables themselves when it comes to updating fields is very limited. I'm pretty sure that altering the value of a field based on a field from another table is not one of them, thus the need for forms and queries. The way some of your first post reads, I suspect you need to review

and perhaps
and/or

and maybe even Table and PK design tips
 
Solution

arcaidius

Board Regular
Joined
Dec 4, 2018
Messages
78
Thanks,
I will check out those links and do some more self training. (If my boss only knew how much time I waste messing around with Access and Excel).
 

Forum statistics

Threads
1,136,731
Messages
5,677,420
Members
419,692
Latest member
Also_Confused

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top