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

From your responses and discussion, I would agree with @June7 that 1 table for all proposal types would seem appropriate for you. Pat's EAV suggestion could also work for you however you would have to deal with a more abstracted table design and underlying coding.

You still need to consider/explore any generalisations that may apply across the types in designing your 1 table for all proposals. The (sub)forms for each type can display meaningful labels for fields that are different for different proposal types but hold the same type of data.

I would also look to ensure you relationships are defined to ensure referential integrity. @June7 had to address a limitation in Access, and then had to deal with ensuring RI was maintained through coding.
 
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.
Thanks Pat. I do like the sound of this, but given my lake of access skill and not having that many different "types" I will give the one table approach a shot. However, I would like to understand what you mean about the last part. Are you just saying that each "type" table should have an FK that relates to the "master table"?
 
Sorry to sound critical, but there are 200 of them. That does seem excessive. Maybe I misunderstood.
It may seem excessive but there are 200 tests, each with their own attributes, some with over 100 fields in table. If I could have only 7 tests and 29 fields, that would have been nice but it wasn't the case. Considered and rejected EAV model as just made it harder to produce desired reports (I also inherited db halfway through development). This design was based on dBaseIV app that had been running for 20 years.
 
Any chance you can take mercy on my soul and point me in the right direction here... (This should probably be a separate post, so feel free to reject and tell me that as well.)

After the user creates a proposal, if it is accepted, they will want to "open a project". I'd like this button to open up a project form and set the ProposalID FK equal to the proposalID on the proposal ID (call this step 1). Ideally, I'd like the project form to display information from a linked Query (this would get the project name from the proposal ID, company name, contact, etc). However, I can't seem to get step one to work correctly let alone step 2. So I created 3 buttons...
Open New Project -> This should open ProjectF form and assign ProposalID = Me!ProposalID. However, this doesn't work.
Open Existing Project -> This opens the ProjectF linked to the proposal. Works Great.
Open Project Query -> This was my attempt to open the ProjectQF (form linked to the query) and display information from the proposal and the project. This doesn't work either. I'm fairly certain something is wrong with my query.

1738524053123.png


I've also attached the DB.
Many thanks, hopefully I haven't overstepped the intent of the forums.
 

Attachments

Need to open form to new record row and pass key value and populate field?

From the first form:
First, make sure first form record is saved to table.
If Me.Dirty Then Me.Dirty = False
Open form for adding new record and pass key value
DoCmd.OpenForm "myformname", , , , acFormAdd, , Me!keyfieldname
Code behind second form to populate field
If Me.NewRecord Then Me!foreignkeyfieldname = Me.OpenArgs
 
Need to open form to new record row and pass key value and populate field?

From the first form:
First, make sure first form record is saved to table.
If Me.Dirty Then Me.Dirty = False
Open form for adding new record and pass key value
DoCmd.OpenForm "myformname", , , , acFormAdd, , Me!keyfieldname
Code behind second form to populate field
If Me.NewRecord Then Me!foreignkeyfieldname = Me.OpenArgs
Thank you. That worked.
If I first want to check to see if there is already a "linked Proposal" should I use "DLookup"? I feel like I've read things where you want to stay away from DLookup, but I'm sure it has it's uses.

Code:
Private Sub OpenProjectForm_Click()
    Dim projectID As Variant
    
    ' Ensure the current record is saved
    If Me.Dirty Then Me.Dirty = False
    
    ' Check if there is an existing project linked to this proposal
    projectID = DLookup("ProjectID", "ProjectT", "ProposalID = " & Me!ProposalID)
    
    If IsNull(projectID) Then
        ' No existing project, open form to add new record
        DoCmd.OpenForm "ProjectF", , , , acFormAdd, , Me!ProposalID
    Else
        ' Existing project found, open form to that record
        DoCmd.OpenForm "ProjectF", , , "ProjectID = " & projectID
    End If
End Sub
 
Domain aggregate functions can cause slow performance with large datasets in queries and on forms. I have VBA code with probably 100+ DLookup and no issues. So yes, use DLookup before calling second form.

My understanding issue can arise if domain aggregate is repeated within a loop structure as each execution opens a link to data and there is a limit on number of links.
 
Note - as you have said, and require, that a Proposal exists and then, when accepted, becomes a Project (and a project can only have one proposal) then you may as well say a Project-Proposal is the one table and the proposal becomes a project when its status is changed to accepted.
That may be OK however you limit your flexibility if a Proposal only relates to one Proposal "element" (eg a retaining wall or an extension or ...). Your design would be better (more flexible for future change) if you allow a proposal to consist of one or more Proposal/Project Elements each of a type, allowing you to provide a proposal consisting of say Retaining Wall A and Facade Construction and Retaining Wall B). The generalisation of the various proposal types would be to create a table Project/Proposal Element. You can retain the business process to only have one Project Element to a Project - but you will have a capacity to easily change.

A quick review of your db schema: there appear to be quite few mis-steps. The design of a database requires subject area knowledge to determine what are the data items needed and how they are used and knowledge of normalisation techniques so that data that "naturally" belongs together is in the one table and are not held in multiple tables. A Project table stores data that you need to manage / describe each Project and nothing else - so a construction project will have one project manager, proposal acceptance date, a commencement date, an estimated completion date, etc . However a Project is generally composed of one or more sub-projects - components or elements. Each of these Project elements has data that describes that component (eg the sub-project commencement date, the expected completion date, the sub-project status/progress, the details of the sub-project etc). The Project and its related components fully describe the Project.

I do not have your subject knowledge expertise - I have reviewed and suggest the revised schema in the attached is reviewed by you in detail to confirm/add/remove/edit items as you require thinking deeply about what data you really need and where it belongs. A number of lookup tables were added - content of which you may edit. One in particular resulted from combining each of your BldgRoof/Wall/Floor references (eliminating the need for separate tables for each). The form for this will filter by the category and then the selection of the appropriate reference to the description in the ProjectElementT in multiple fields as appropriate.

I am sure when you review there will be further adjustments you will need to make: it is most important that your foundation is solid before you launch into construction based on that foundation (forms reports and supporting queries). Some iteration may be needed/expected.
(Note: Only those tables in the Relationships window need to be retained, in my view). No adjustments have been made to anything other than tables in the attached - your forms/queries are unchanged - and so are unlikely to work properly until the data sources are remade/reconfigured to this schema.

EDIT: I have remembered that I left off the ReferringCompanyID and ReferringContactID, because I was unsure what these were for -- however I think that this reference is where you (your company) is being sub-contracted for some work. I would suggest you only need ReferringContactID in the appropriate ProjectT. Add the item and then add a relationship between ContactT and ProjectT using ContactID-ReferringContactID.
 

Attachments

Last edited:
I'd like this button to open up a project form and set the ProposalID FK equal to the proposalID on the proposal ID (call this step 1).
Do not dirty the record before the user does. Use the BeforeInsert event to populate the FK. OR, in the form's open event, save the FK from the OpenArgs to the .DefaultValue property of the FK control and let Access populate the FK when the record is dirtied.
 
Note - as you have said, and require, that a Proposal exists and then, when accepted, becomes a Project (and a project can only have one proposal) then you may as well say a Project-Proposal is the one table and the proposal becomes a project when its status is changed to accepted.
That may be OK however you limit your flexibility if a Proposal only relates to one Proposal "element" (eg a retaining wall or an extension or ...). Your design would be better (more flexible for future change) if you allow a proposal to consist of one or more Proposal/Project Elements each of a type, allowing you to provide a proposal consisting of say Retaining Wall A and Facade Construction and Retaining Wall B). The generalisation of the various proposal types would be to create a table Project/Proposal Element. You can retain the business process to only have one Project Element to a Project - but you will have a capacity to easily change.

A quick review of your db schema: there appear to be quite few mis-steps. The design of a database requires subject area knowledge to determine what are the data items needed and how they are used and knowledge of normalisation techniques so that data that "naturally" belongs together is in the one table and are not held in multiple tables. A Project table stores data that you need to manage / describe each Project and nothing else - so a construction project will have one project manager, proposal acceptance date, a commencement date, an estimated completion date, etc . However a Project is generally composed of one or more sub-projects - components or elements. Each of these Project elements has data that describes that component (eg the sub-project commencement date, the expected completion date, the sub-project status/progress, the details of the sub-project etc). The Project and its related components fully describe the Project.

I do not have your subject knowledge expertise - I have reviewed and suggest the revised schema in the attached is reviewed by you in detail to confirm/add/remove/edit items as you require thinking deeply about what data you really need and where it belongs. A number of lookup tables were added - content of which you may edit. One in particular resulted from combining each of your BldgRoof/Wall/Floor references (eliminating the need for separate tables for each). The form for this will filter by the category and then the selection of the appropriate reference to the description in the ProjectElementT in multiple fields as appropriate.

I am sure when you review there will be further adjustments you will need to make: it is most important that your foundation is solid before you launch into construction based on that foundation (forms reports and supporting queries). Some iteration may be needed/expected.
(Note: Only those tables in the Relationships window need to be retained, in my view). No adjustments have been made to anything other than tables in the attached - your forms/queries are unchanged - and so are unlikely to work properly until the data sources are remade/reconfigured to this schema.

EDIT: I have remembered that I left off the ReferringCompanyID and ReferringContactID, because I was unsure what these were for -- however I think that this reference is where you (your company) is being sub-contracted for some work. I would suggest you only need ReferringContactID in the appropriate ProjectT. Add the item and then add a relationship between ContactT and ProjectT using ContactID-ReferringContactID.
Sorry for the delayed response. Thank you, I appreciate your input. I will take a look at this.
 
Do not dirty the record before the user does. Use the BeforeInsert event to populate the FK. OR, in the form's open event, save the FK from the OpenArgs to the .DefaultValue property of the FK control and let Access populate the FK when the record is dirtied.
Ahh, wise. thank you.
 

Users who are viewing this thread

Back
Top Bottom