Data Entry for one field changed based on another field's data

[dmakson17]

There are two parts to my question. I appreciate any help I can get, I am a rookie learning as I go. I am designing a DB for my store that tracks customer special request orders. The orders fall into two categories, next available or future set date.

I have tblWhenNeeded with 'Next Available' and 'Future Date' as the choices. Also, the field in tblOrderDataEntry 'When Needed' is a combo box. What I am trying to accomplish is having the data for the field 'OrderOn' in tblOrderDataEntry be a calculated field if "next Available' is selected or be user entered if 'Future Date' is selected.

Secondly, if I use the Date() function in my calculations, wouldn't the dates based on that calculation change depending on when the order is opened in the future? I'm sure I'm overthinking a lot of this, my brain is just jumbled as I'm learning.

Thanks in advance!!!

[ArviLaanemets]

Don't use tables for regular data entry!
Don't use default values in tables!
Don't use Lookup feature in tables, combo boxes included!

Design tables for your database accordingly your business model and following basic database rules, such as avoiding saving same info multiple times and avoiding saving calculated values into tables unless it is inevitable. Practically you add fields to tables, set data types for them, define primary and foreign indexes, and optionally define table relationships with referential integrity. It's all you need!

When you have at least basic table structure created, you can start with designing main elements of user interface - forms. User doesn't use tables. A best practice is hide all tables from user, so he/she can't mess with them. All info is entered and edited on forms. When you need to have a default value for some field, you set this default value for form control, so you don't get orphan entries in your tables when inserting new data is canceled by user or by application. To select some codes having meaningful text for selection and later for display, you use forms combobox controls. Etc., etc. ...

[dmakson17]

Oh wow! Ok thank you. I need to rethink how I did this I guess. I was planning on hiding all tables from users, but I thought that the table had to carry all the data. That being said, when I have the Data Entry form, how would I have one field be dependent on the other field, either being calculated, or entered?

[ArviLaanemets]

About Date function. Yes, all formulas where you use it can return different value on next day. And again, when you use forms for data entry instead of tables, you can p.e. have an unbound text box (may be hidden) with =Date as source. You can read a current date value form this text box, use it in your calculations, and save the result, when you want it. Or you save the date to your table, and whenever you want to calculate something based on this date, you read the date from your table.

[ArviLaanemets]

when I have the Data Entry form, how would I have one field be dependent on the other field, either being calculated, or entered?

It depends what you mean by "being dependent".

When it is some calculation based on data saved in your tables, then usually it is better not to save it all. You can calculate it instead whenever you need it. In case you however want to save it, you must have a control on form linked to according table field and disabled for user. And p.e. in forms BeforeUpdate event you save calculated value into control, so it will be saved along with other data.

When you mean depending tables, like people from tblPeoples table having none or many phone numbers registered in table tblPhones, usually a form (fPeoples, single form) with child form (fPhones, contionous form) in subform (sfPhones) is used. You set LinkMasterFiled and LinkChildField properties for subform, and whenever you add a new record into subform, chield field controls in subform are filled with values from parent field controls in parent form.

When you have p.e. 2 combos on form, and the selections for one are depending at selection made in another, you have to write AfterUpdate events for one or both combos, where you modify properties for depending combo, and check integrity of data.