Limit the status after one time (1 Viewer)

Talatraja

New member
Local time
Today, 12:58
Joined
May 19, 2021
Messages
8
hi i am new in access and vba sorry if i ask some thing wrong or if i not explained my question due to my language issue, i making database for project documents our propose is that we received a as built drawings for final projects ,we received sample submittal submittal only at starting project then its approved or reject after that contractor submit the complete package submittal, now the problem is that some contractor submit the sample submittal after the package we want to restrict contractor to submit the sample after package submittal ,as we need only sample first, i have one table is projects detail with primaty key project number and other one is for submittal datils with submittal number as primary key and project number as fo key, submittal typ ,submittal date, replly date and status, user enter data through form with same field including combo box of submittal type with sample and package,
just need to prevent entering the sample submittal after package submittal, thanks
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:58
Joined
Feb 19, 2013
Messages
16,553
moved from site suggestions to a more appropriate forum

Always better to show some example data with actual field names and data types as descriptions can be difficult to interpret and harder still to provide meaningful response

With regards the question

once the user has entered the project number
  • your type combo could exclude the sample submittal type from the rowsource if a complete package has already been submitted
  • or the type combo could be disabled if a complete package has been submitted
to determine if a complete package has been submitted you could use the dcount function (this assumes all values are numeric)

dcount("*","tblProjects","ProjectNumber=" & me.txtProjectNumber & " AND TypeFK= 1)

where 1 is the typePK for for 'complete package'

if dcount returns 0 then the complete package has not yet been submitted
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:58
Joined
May 7, 2009
Messages
19,169
how do you do it in your Excel worksheet?
i am sure you have some "monitoring" sheet of the As-Built drawings, eg:

Status: Engineering-for revision, date: ??/??/????
status: submitted to client for approval, date: ??/??/?????


basically the same with MSA, you Lookup the ProjectNum + DrawingNum
to see the as-built drawing status.
 

Talatraja

New member
Local time
Today, 12:58
Joined
May 19, 2021
Messages
8
Thanks you for your reply , but i cant reach, here i attached my files ,in this file one record related to contract 11121173 is troubling or wrong because it received the package first and after that received the sample , but another record related to contract 31131131 is all ok and sequence is fine ,
i attached image for form where submittal type combo box has two option sample and package , i want when i select contract number which has already package submittal the option for sample not show in submittal type combo box or it show some error msg
sorry for bad explanation and language
thanks
 

Attachments

  • PROJECTS SUBMITTAL.accdb
    1.3 MB · Views: 401
  • PIC1.jpg
    PIC1.jpg
    169.2 KB · Views: 408

Talatraja

New member
Local time
Today, 12:58
Joined
May 19, 2021
Messages
8
how do you do it in your Excel worksheet?
i am sure you have some "monitoring" sheet of the As-Built drawings, eg:

Status: Engineering-for revision, date: ??/??/????
status: submitted to client for approval, date: ??/??/?????


basically the same with MSA, you Lookup the ProjectNum + DrawingNum
to see the as-built drawing status.
actually the receiving user on multiple location and we using it with shard files i shared front end to receiving user and back end to my database ,an
 

Talatraja

New member
Local time
Today, 12:58
Joined
May 19, 2021
Messages
8
how do you do it in your Excel worksheet?
i am sure you have some "monitoring" sheet of the As-Built drawings, eg:

Status: Engineering-for revision, date: ??/??/????
status: submitted to client for approval, date: ??/??/?????


basically the same with MSA, you Lookup the ProjectNum + DrawingNum
to see the as-built drawing status.
here i attached my files ,in this file one record related to contract 11121173 is troubling or wrong because it received the package first and after that received the sample , but another record related to contract 31131131 is all ok and sequence is fine ,
i attached image for form where submittal type combo box has two option sample and package , i want when i select contract number which has already package submittal the option for sample not show in submittal type combo box or it show some error msg
sorry for bad explanation and language
 

Attachments

  • PIC1.jpg
    PIC1.jpg
    169.2 KB · Views: 391
  • PROJECTS SUBMITTAL.accdb
    1.3 MB · Views: 369

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:58
Joined
May 7, 2009
Messages
19,169
i think it is best to "see" all (or none) of all your transmittal.
 

Attachments

  • PROJECTS SUBMITTAL.accdb
    2.1 MB · Views: 408

Talatraja

New member
Local time
Today, 12:58
Joined
May 19, 2021
Messages
8
sorry i didn't explain you exact, in short if a project is already received as "package" after that no new submittal should be received as "sample"
the sample submittal only eligible before submitting package.
thanks
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:58
Joined
Feb 28, 2001
Messages
26,996
All we can say without seeing more of your environment is this:

If you have a project, how do you identify it? That was a rhetorical question... More specifically, how should your computer identify it?

You should have a table of submissions which tell you what you have on the project you have identified, perhaps by project number. On the form, you would do one of two things.

Option 1. When you identify the project to the computer, you can adjust your list of submissions to omit the "sample" option if you already have a package. How you would do this depends on what you use as the source for your submission-type selector.

Option 2. When you identify the project to the computer, you can test for the existence of the package and if that option is selected, issue an error message that disallows that particular submission.

In either case, you can use DCount( *, "tablename", "ProjectNumber = " & Me.ProjectNumber & " AND Submission = 'Package'" ) where I am assuming you have the project number already on the form because that was either entered or selected previously.

Without more information, it would be very hard for us to offer more of a solution.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:58
Joined
May 7, 2009
Messages
19,169
sorry i didn't explain you exact,
you did explain it correctly.
what i showed you is that you can see the "latest" status at a glance in the subform.
instead of adding code (dlookup or Whatever) just to check if a package has already been issued.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:58
Joined
May 7, 2009
Messages
19,169
here try adding "sample" to new record, it will reject if a package has been submitted.
 

Attachments

  • PROJECTS SUBMITTAL.accdb
    2.1 MB · Views: 403

Talatraja

New member
Local time
Today, 12:58
Joined
May 19, 2021
Messages
8
here try adding "sample" to new record, it will reject if a package has been submitted.
yes i understand but the problem is this form is related to front end user and i cant show the previous submittal record on user form ,user should only enter the submittal information ,
i just need that contractor should submit the sample submittal only before package submittal after complete package we want restrict them to submit the sample submittal, i am keep searching for this issue
but thanks you very much for your help
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:58
Joined
May 7, 2009
Messages
19,169
yes i understand but the problem is this form is related to front end user and i cant show the previous submittal record on user form ,user should only enter the submittal information
why is that? is the project confidential.
i don't think it is ever confidential.
anybody inputting should be aware of
what is going on (the status) of such package.
otherwise, higher management asked him of
the status, he cannot give the detail.
you are entering blindly.
btw i have been in construction (piping)(oil & gas/ power, refinery)
for more than 20 years.
 

Talatraja

New member
Local time
Today, 12:58
Joined
May 19, 2021
Messages
8
here try adding "sample" to new record, it will reject if a package has been submitted.
Thank you its good idea ,but here is one way i tried to get required result

Private Sub CONTRACT_Number_AfterUpdate()
Me.Submittal_No.SetFocus

If Me.CONTRACT_Number.Value = DLookup("contract_number", "submittal_detail", "contract_number = '" & Me.CONTRACT_Number.Value & "' And submittal_type = 'Package'") Then
'ComboBox Lock
Me.Submittal_Type.Locked = True
'submittal type value in locked combobox
Me.Submittal_Type.Value = "package"

MsgBox "The Project Already Received In Package Submittal", vbInformation, infromatin1
Else
End If
End Sub
 

mike60smart

Registered User.
Local time
Today, 09:58
Joined
Aug 6, 2017
Messages
1,899
Hi
You need to step back a bit and get your tables designed correctly.

All tables should have a Primark Key set as Autonumber.
You should not use a Lookup field to set a Foreign Key in a related table.
You should not use Lookup Fields at table level.
You should not Hard Code a List of Lookup values in your Table fields.
 

Users who are viewing this thread

Top Bottom