Check if value exists in combobox and give user a prompt if they try to change it after it has been set

[RFreund]

I'm trying to use a combobox to set the value of a subform control. A blank form is shown for a new record. However, after the user selects which subform should be displayed, I want a warning with a yes/no option displayed if they try to change it. I can't seem to get this to work correctly.

Thanks!

 

[Pat Hartman]

1. Why does a change in a main form record propagate to a subform record? Are you only intending on changing the single, visible record? The logic of this is suspect. It implies that your tables are not correctly defined and that the data field is in the wrong table.
2. Syntactically your code is incorrect. If the code is being run in the main form, then it is something like
Me.subformname.Form!cboSubformSelector = Me. ProposalTypeID
If the code is being run in the subform, then it could would be:
Me.CboSubformSelector = Me.Parent.ProposalTypeID

One side of the expression or the other needs to be qualified depending on where the code is running.

Remember - subformname = the NAME property of the subform control that is sitting on the main form. It may be different from the name for the subform that you see in the Navigation Pane.

 

[RFreund]

1. Why does a change in a main form record propagate to a subform record? Are you only intending on changing the single, visible record? The logic of this is suspect. It implies that your tables are not correctly defined and that the data field is in the wrong table.

I wouldn't be surprised if I'm doing something wrong. My intent is that the main form has general proposal information common to all proposals.
I then have different subforms which are specific to different types of proposals. I want to populate a subform located on the "proposal page of the tab with which ever proposal subform has been selected from the combobox. The idea is that this would start blank, the user selects a type, and completes the inputs. However, they may (either by mistake or intentionally) change the subform (i.e. the proposal type) and I'd like to prompt them to make sure they want to make that change.

Thanks for the other pointers. I will give it a few more tries.

It's possible that my entire approach to this is flawed, but the thought is something like this:
I have a proposal table that has data common to all "proposals". I then have a few "sub proposal tables" which capture data that is specific to the different proposal types. I don't want to create 10 different proposal forms which all have the same repeating information + the specific subform information. So I figured I would create one proposal form with a combobox that allows you to set the subform.

 

[June7]

How different is data for different types of proposals? Is it worth having multiple "sub proposal" tables instead of one that might have some empty fields? If user changes type, have to delete record from one table and then data must be input to another? What happens if you add another proposal type? Have to build another table and form, maybe modify existing main form and code.

If there were one "sub proposal" table, just distribute the various fields to different pages of Tab control. In this case, if user changes type, code would remove data from irrelevant fields. Either way, there is coding overhead.

It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works."

Would you care to provide db for analysis?

 

[RFreund]

Huh, yeah, I see what you are saying. Well said. I will share the database as all the data is fake at this point, but it's a bit messy. Let me try to clean it up a little and also add a couple more tables/forms so you get a better feel for number of proposal types and fields. Thanks again for the response. Much appreciated.

 

[The_Doc_Man]

Access controls don't have the inherent ability to do what you asked, so you would have to build your own mechanism to remember that a selection has been made. This mechanism would have to include that in the Form_Current routine, before you let the world see everything, you make a note that you have values in any controls that you wish to protect. Then, on the LostFocus event for a "protected" control, compare the value to the stored values for unbound controls. For bound controls, you have .OldValue for comparison to see if something is being reset, but that does not work for unbound controls AND .OldValue won't work even for bound controls before the first time they are saved.

It would be possible for you to ALSO simply lock the control once you set it -but then your "ask Y/N question before changing it" wouldn't work because a locked control can't be changed anyway.

 

[June7]

OldValue of bound control returns Null for a new record. That can be handled with the code. Or use condition of
If Not Me.NewRecord Then
before test for change of value.

 

[arnelgp]

after adding the record, just Lock the Control (see post #6).
you can do it on the Current Event of the form:

Private Sub Form_Current()
Me.TheTextboxToLock.Lock = Not (Me.NewRecord)
End Sub

 

[June7]

Me.TheTextboxToLock.Locked = Not Me.NewRecord

 

[RFreund]

So backing up for a minute and going back to what June7 mentioned in post #4...
Here are my tables, Main proposal table, and then 7 sub-tables. Does it seem better just to jam it all in one table or keep them separate?

Also attached is the database. I know it's in pretty rough shape. Don't fault you for opening it up and immediately dumping it in the trash. If interested check out the ProposalF:

 

[RFreund]

Also clarification on "locking" the control...
What triggers the locking? I noticed that as long as I don't change records the control remains unlocked. I can save it, refresh it, input text in other fields, none of that seems to impact that lock.

I take back the question, since it is in the current sub. Then it would only be when the record becomes current again, correct? This might be what I want. If I go this route.

 

[GaP42]

@RFreund - preliminary observations and questions:
- some typos in naming fields - hopefully not in the real table: ProposalTypeID, ProjectCountry
- does the Main Proposal Table have a ProposalID PK (autonumber?)
- The type tables provide further detail specific to the type. Is ProposalTypeID a coded link to the specific record in a proposal type table? or an indicator of the Type of proposal only : eg 1 = RetainingWalls table, 2 = .. etc
- Can a Proposal consist of more than one of the types? If so, and more flexibly the table structure should be changed to have the ProposalID (see point 2) as a FK in each of the Type tables. (Removing the ProposalTypeID from the Main Proposal Table.) Yuo could also have more than one ProposalDetail of the same type in the same project - eg two retaining walls for the project at ...
- ProposalTypeID if it refers to the latter in point 3 will be a lookup table the ProposalTypeID being found in each of the ProposalDetail tables

Do you have the relationships between the tables defined in the relationship window? If so post a picture, if not you need to do so - perhaps after revisions suggested...

Re combining each of the specific project type tables: there may be common elements which may be generalised in the table field names and made specific in the context of the subform for the ProposalTypeID (probably better named as the PropsalDetailTypeID) - eg ManufacturedID, SystemID, ForensicID, however there appear to be quite a few specific to each type.
Do each of the type tables have a PK - autonumber to refer to the specific records?

 

[June7]

If it were one table, and Forensic was selected, there would be 29 empty attribute fields - so what! Does every feature selected always have data for all of its attributes? 7 tables or 1 table, structure could still be viewed as not 100% normalized. Adding an attribute for any feature means redesign of table, queries, forms, reports, code. If structure seldom if ever changes then this is not a big concern and tables are "normalized" to the point they need to be. Would 1 table make life easier for you - only you can decide.

I have a db for tracking laboratory sample testing with tables like your 7 but in my case there are almost 200 tests so yes, they are separate tables (main table has 1-to-1 relationship with each test table and a sample can have several associated tests - even in each test table not all fields are filled).

Further normalization would entail an entity/attribute/value model.

 

[RFreund]

Thank you both for the responses.

@June7 - Would you create a separate form for each "type" i.e. 7 different forms for entry. Or just create tabs like you said earlier?
I'm torn on this decision. On one hand I really like the idea of having separate tables because it keeps things "cleaner" in my mind. However, I'm not sure that my DB skills are up to the task of managing that many tables correctly.
Things should rarely change, so I suppose it's not that big of a deal either way once it's setup.

1 Table Pro's / Con's:
Pro's:

1. Easier to setup.
2. Probably easier to "fix" things on the backend (i.e. go into the table and correct) if something is entered incorrectly.
3. Probably easier to run queries.

Con's:

1. If I use the tab idea, it would be easy to put data into fields that shouldn't have data (really only want one proposal type to have data)

Multiple Table Pro's / Con's:
Pro's:

1. Feels more "professional/correct" in my mind.
2. Easier to make changes to specific tables later on.
3. Potentially keeps proposal data "cleaner" (no random data in other tables).

Cons:

1. Difficult for me to code correctly.
2. Potentially creating unnecessary work for myself.
3. May end up feeling "buggy" if not done correctly.

Am I missing anything??

 

[RFreund]

@RFreund - preliminary observations and questions:
- some typos in naming fields - hopefully not in the real table: ProposalTypeID, ProjectCountry
- does the Main Proposal Table have a ProposalID PK (autonumber?)
- The type tables provide further detail specific to the type. Is ProposalTypeID a coded link to the specific record in a proposal type table? or an indicator of the Type of proposal only : eg 1 = RetainingWalls table, 2 = .. etc
- Can a Proposal consist of more than one of the types? If so, and more flexibly the table structure should be changed to have the ProposalID (see point 2) as a FK in each of the Type tables. (Removing the ProposalTypeID from the Main Proposal Table.) Yuo could also have more than one ProposalDetail of the same type in the same project - eg two retaining walls for the project at ...
- ProposalTypeID if it refers to the latter in point 3 will be a lookup table the ProposalTypeID being found in each of the ProposalDetail tables

Do you have the relationships between the tables defined in the relationship window? If so post a picture, if not you need to do so - perhaps after revisions suggested...

Re combining each of the specific project type tables: there may be common elements which may be generalised in the table field names and made specific in the context of the subform for the ProposalTypeID (probably better named as the PropsalDetailTypeID) - eg ManufacturedID, SystemID, ForensicID, however there appear to be quite a few specific to each type.
Do each of the type tables have a PK - autonumber to refer to the specific records?

A few responses:

1. Typos not in Database (at least I don't think they are)
2. Yes proposal table has PK called ProposalID. All tables have a PK and they all have ProposalID as a FK. I apologize for leaving all these off the table lists.
3. "Is ProposalTypeID a coded link to the specific record in a proposal type table?" - Yes it is, that table containes ProposalTypeID, ProposalTypeDescription, and ProposalSubFormName. I use this to set the subform control to the correct propsal type table.
4. "Can a Proposal consist of more than one of the types?" - No only one type.
5. "ProposalTypeID if it refers to the latter in point 3 will be a lookup table the ProposalTypeID - As described above, it is used to set the subform control.

"Do you have the relationships between the tables defined in the relationship window? If so post a picture, if not you need to do so - perhaps after revisions suggested..."

I actually don't have these setup, but it functions correctly. Is it considered good practice to set these up in the Database Tools / Relationships?

Do each of the type tables have a PK - autonumber to refer to the specific records?

Yes, Sorry again for not including these.

 

[June7]

I would probably have 1 table in this case.

I do not have relationships set up for the 200 test tables with the parent table partly because that exceeds the limit of links a table can have. I control data integrity with db design and code - lots of code. Relationships can be an aid or a nuisance. Links will be automatically set when tables are pulled into queries but then maybe that link is type you don't want for that particular query. I have seen dbs with complex relationships that won't work properly in query and links have to be removed in query design. Relationships can help with data integrity by setting enforcement properties. If setting relationships helps you understand and manage data, use them, otherwise don't bother. If you want to give someone a diagram of your schema, relationships builder is one way - or draw it on a napkin.

Code can manage visibility and therefore availability of pages of tab control depending on what Type user is entering data for (doesn't matter if tab pages hold textboxes/comboboxes or a subform). I have one form where I do this - keep in mind one page must always be visible but the entire control can be not visible until needed. Or trust your users to know which tab to utilize for data entry.

 

[RFreund]

Appreciate the feedback. I'm going to give single table a try.

 

[Pat Hartman]

Further normalization would entail an entity/attribute/value model.

I have a couple of applications that use this mode. One is an app that handles specs for parts. Different part types have different specs and different types of tests. New part types don't get added often but this app was intended to replace multiple existing apps and the client wanted the flexibility to add a new part type on the fly without having all the associated programming to create new tables and forms and queries. The other was an app to manage contract documents for an Insurance application. Again, adding a new insurance product doesn't happen frequently although, since the company was a start up and they only offered a few types of products initially, they knew they wanted to expand this capability.

The advantage of the entity/attribute/value model in these cases was primarily that they eliminated all the programming that was required to define a new part type or a new insurance policy type. If you want to see some of it, I can post some images. I can't post the database though. Or, if you would like a demo, I can set up a meeting where I can walk you through all the moving parts. Once you see how it works, it is not very difficult. There is a master tale of attributes. All of the fixed attributes are defined normally in the Product table. Then there is a child table which houses all the attributes for this particular product type. When you add a new policy record, after the form saves the fixed information, it runs an append query to copy all the variables for this product type. Because the records have to be created before you enter data, validation becomes tricky so the "required" part can't happen until you try to use the variable. The insurance app was also connected to thousands of Word documents since the whole point of the app was to ensure that the data fields specified in the policy were always the same in all the various policy docs.

My insurance app was a godsend to the client. It was taking their IT people 3-6 months to create all the forms/reports/docs, etc to add a new product type. With my app, a user could do it without my help in a day or two. Then a little longer depending on how many Word docs needed to be created and mapped.

So, if I knew I needed to expand the universe of types, I would strongly consider the entity/attribute/value model but if the types were static and not likely to be expanded, I would stick with a more typically normalized schema. Seven types isn't a lot, especially with only 29 variable attributes. The thing to keep in mind if you use the model you started with is that ALL tables relate to the entity table, NEVER to the type tables. I think that may be where June's test tables got out of hand.

 

[June7]

Pat, not sure what you mean by "got out of hand". My test tables are related to parent SampleLogIn table, not to a TestTypes table. Test tables store results of sample testing. There is a TestInfo table for metadata about each test (num, name, time, etc).

 

[Pat Hartman]

Sorry to sound critical, but there are 200 of them. That does seem excessive. Maybe I misunderstood.