Adding Temp Record to Form (1 Viewer)

Weekleyba

Silver Supporter
Joined
Oct 10, 2013
Messages
248
I have sample DB for you to look at.
When a project is added, I want to add a TEMP contract number which I have done via, the "Add TEMP Contract Num" button.
It puts the temp number in the SF_Contract, cboContract.

What I need help with is two things:
1. Once I press the "Add TEMP Contract Num" button, I want the cboContract to not be dirty. I've tried a few things but keep getting errors.

2. If I press the button multiple times, it changes the same record in the SF_Contract. I want it to go to a new record and place a new temp contract number in it.

Can you guys help me out?
Thanks.
 

Attachments

plog

Banishment Pending
Joined
May 11, 2011
Messages
9,576
This seems like one of those issues where you are trying to get us to help you with one step along a path that may not be optimal. Let's discuss the entire path.

Could you explain what it is your organization does? No database jargon, just pretend its career day at an elementary school and you are telling us what you do. Then, explain what this database is for, a little database jargon is ok, but don't over do it. Just tell us what this database will help you do.

With all that said, your tables look off. Why do both T_Contract and TJ_ProjectContract have ContractID and ProjectID in them? Only 1 table should have that relationship. Also, it seems that ContractNumber is actually holding 3 pieces of data (TEMP-2020-002) is that true? Are you using the dashes to delimit data? If so, that's not how databases are to work. If "TEMP" represents seomthing it should be in its own field; if "2020" represents something it should be in its own field (or calculated from DateCreated since it seems to be a year); if 0002 represents something then it should be in its own field.

Again, I don't think helping you with the issue you posted about will help you if the whole path you have chosen is incorrect to begin with.
 

MajP

I wish I could go back in time... I'd take state
Joined
May 21, 2018
Messages
2,427
makes no sense to me either.
 

Weekleyba

Silver Supporter
Joined
Oct 10, 2013
Messages
248
Plog,
Absolutely I can do that and thanks for looking at this.

So I'm an engineer for the Indian Health Service. My department is charged with keeping the facilities (clinics and hospitals) fully functioning and compliant with their respective accreditation organization. A big part of this is developing projects for repair, renovation, or new construction.
This database is for the department of 14 engineers, to manage the projects from cradle to grave. The main part of the database will include, the project and associated data, and the respective contracts for the projects. It will also keep track of the payments to the contracts.

A project can have multiple contracts and multiple locations. And a contract can have multiple projects.
This is the reason for the TJ_ProjectContract. It may be more evident in the large database that I am working on. See attached.

The database will begin by the engineer clicking the "Add New Project" button.
This opens the F_Project and the engineer will enter data.
At some point in the project progression, the engineer will need to enter what we call a Non-Catalog request. This will be generated on the F_Contract and will get all its data for that Non-Catalog report from the F_Contract. The engineer will click the "Open" button by the TEMP contract number to open the F_Contract.

After the Non-Catalog request is made, the Contracting Office will eventually award a contract to a contractor and an actual contract number will be generate.
But since I don't have the actual contract number at first, I've decided to create the "Add TEMP Contract Num", so I can get to the F_Contract and generate the Non-Catalog report.

When awarded, the engineer will revise the Contract Number on the F_Contract with the actual number, eliminating the TEMP number.
The TEMP-2020-0001 is just a temporary number and will probably not grow past 100. I could break it all apart and store separately but, I honestly don't see a need to do that, for this case.

Anyway, this is probably enough info for now.
I attached my full database ( still in the works, not complete).

What do you think of my approach?
All advise is appreciated!
 

Attachments

plog

Banishment Pending
Joined
May 11, 2011
Messages
9,576
It would be helpful to you and others if you completed the Relationship Tool in access. Bring in your tables, connect them and then that helps understand the relationships among them. But what you have is enough for now.

1. ProjectID shouldn't be in T_Contract. If a contract can go to multiple projects and vice versa then the only table that should have both a ProjectID and ContractID is TJ_ProjectContract.

2. If there's no ContractNumber, just leave it blank. I don't understand what creating a temp one does for you.

3. T_CAN_Crosswalk and T_Location_addresses have Location fields which relates to T_Locations.Location, instead they should hold the value in T_Locations.LocationID. When connecting tables you use the primary key of the table you are relating, not another field.

4. Can a contract have multiple contract info records? Since you have a seperate table that is how you have it set up.

5. T_ContractLineItem shouldn't have ContractID in it. Since it has ContractInfoID and ContractInfoID relates to just a single ContractID in T_ContractInfo you know which ContractID goes to it.

6. Dates stored as text. You should store dates in a Date/Time field. I see a bunch stored as Short Text (e.g. DateCreated)

7. Put all your people in 1 table with a new field to describe their role. All the data in T_Engineer, T_Requestor, T_ContractingOfficer and any other people tables should all go to the same table.
 

Weekleyba

Silver Supporter
Joined
Oct 10, 2013
Messages
248
Thanks Plog!
I do have a couple questions corresponding to your numbers.

1. I didn't have this until I tried the TEMP number approach. I will try to just open a the F_Contract from the F_Project and have it put in a TEMP number there. This would eliminate the whole TEMP form from the Project form. I'm a little stumped on how the F_Contract will tie to the ProjectID when I just click the "Open" button in the SF_Contract on the F_Project form.

2. I need to have a Contract Number so it will show up in the opening form for search purposes. If contract is created with no Contract Number it will not show in F_SearchMulti, so I'm trying to ensure that it is put in, even if it's a temporary number.

3. T_Location_addresses is not used and will be eliminated. T_CAN_Crosswalk is imported from an excel file that is generated at the main office. I use the Location field to link it to the T_Locations when choosing a CAN (funding code) in the F_Contract. So I don't think I can use the LocationID in this case. Every years a new T_CAN_Crosswalk is generated and I have to import the new updated table. Does that make sense?

4. Yes a contract can have multiple contract info records. These would be modifications to the contract and is done often.

5. I don't recall why I have that. Some of is this is from my older database that I put together and it has payments in it, so it might be related to that..? None the less, I'll get rid of it and work from there.

6. Changed.

7. Will do. That will clean it up a bit.
 

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,540
In addition to what plog suggested:

Do NOT use a button to add the temp contract number. Instead, add code in the FORM's BeforeUpdate event to determine if a contract number is present. If one isn't present, either automatically generate a temp number or ask the user if he wants to generate a temp number. Other validation also belongs in the FORM's BeforeUpdate event so you would NEVER want to save a record with only a temp contract number. There must be other required fields. Saving an "empty" record should not be allowed and makes no logical sense. By putting your validation code into the correct event (FORM's BeforeUpdate), you can control what records get saved and prevent incomplete records from being saved.
 

Weekleyba

Silver Supporter
Joined
Oct 10, 2013
Messages
248
Ok...I've been working on this and cannot quite get it to work. :banghead:
The user can add a project by clicking the button.
There is some validation of control in the before update...works good.
The user can then select the 'Open Contract' button.
There is some validation of control in the before update...works good.

Two problems:

1. When you click the close button in F_Contract, you have to click it twice. I see why this is happening in my code but, I don't know how to fix it. I simply want to click the button once and close. How do I fix this code and still get the validation of control to work?

Code:
Private Sub ButtonClose_F_Contract_Click()
    On Error Resume Next
    
    If Me.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
        'This trys to save but the BeforeUpdate event fires which has the Public Function in it.
       Else
        DoCmd.Close
        
    End If
End Sub
2. When the F_Contract is closed, I cannot get the combo box, cboContract to land on the TEMP contract number that was generated in F_Contract.
If I choose …..listcount -1, it gives me the next to last in the list. Which is expected. But if I choose ….listcount , (with no "- 1" at the end) then it gives me a blank. How do I correct this?

Code:
    Dim LastRow As Integer
    LastRow = Forms!F_Project!SF_Contract!cboContract.ListCount
    ctlCombo.Value = Forms!F_Project!SF_Contract!cboContract.ItemData(LastRow)
See attached sample database.
Thanks for any help.
 

Attachments

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,540
The FORM's BeforeUpdate event is ALWAYS the LAST event to fire whenever a record is being saved. It makes absolutely no difference what caused Access to save the record. The BeforeUpdate event is like the flapper at the end of the funnel. Your validation code either allows the data to slip through and be saved or to shut the flapper tight and prevent invalid data from being saved.

You only posted some of the code so I can't tell what is causing procedures to execute multiple times.
 

Weekleyba

Silver Supporter
Joined
Oct 10, 2013
Messages
248
Thanks Pat
I started a new thread on this and marked this one solved.
The new thread I have a sample database attached.
Hope that’s the correct forum etiquette.
And by the way, I still could use some help on the new thread.
Really struggling with this issue today.
 

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,540
Do you now understand how the BeforeUpdate event works and that it is executed whenever a record is saved whether automatically by access or by your command?
 

Weekleyba

Silver Supporter
Joined
Oct 10, 2013
Messages
248
I believe I do. Now I just need to remember to implement it where it’s required.
It really is an important concept when developing a database.
Thanks for the lesson on this.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom