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

[RFreund]

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.

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

 

[June7]

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

 

[RFreund]

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.

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

 

[June7]

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.

 

[GaP42]

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.

 

[RFreund]

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.

 

[RFreund]

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.