required data on subform? Please Help 1

[cristoffer]

hi,
greetings.,
i have a subform. but i want the user to nofify that its has a required data to be enter.
like sample,
position required docs
captain a,b,c,d
2 officer b,c,d,e
3 officer a,c,d,e

so if in the main form is the position is captian then the required subform to filled must contain a,b,c,d

like main form captain
subform
a
b
c
d
and they can add other but the said requirements must contain a,b,c,d

i would i preciate any help you could thanks in advanced.

 

[Remou]

I suggest you use a table for the position/document combinations:

[tt]tblRequiredDocs
DocID ) Key
Position )[/tt]

You could then append from this table when a new position is created.

 

[cristoffer]

thanks Remou for early reply, but im sorry for my late responce.

well i would state it cleary, i have table name crew. in crew its has a field namely,
crewID = Key
Name = text
Position = text
Status = text
then this table has relation with other table namely License,
Medical,Training,traveldocs i categorize then
in each table above has a field
like License
idlicense = key
crewId = number (same as crew table)
licenseID = number (combobox the link the name of license to other table)
date issue = Date
date expire = Date
and same again with medical,training,traveldocs table
so i made the crew was the mainform and license,medical and other are the subform linking crewID for each subform. ill continue....

 

[cristoffer]

continue..
then when the user enter the position on the mainform it will automatically fill the required doc on each subform on license, medical and others. so user are required to fill the other info like date issued in subform.
or when the user fill the position on the mainform when they they close it, will inform them that they have to fill other required doc on the subform.
i really appreciated the reply that you have done again thank you..

 

[Remou]

It is possible to run an append query in an event of the main form to add records to the subform. You can hard code the documents to be added for each type of crew member, or you can create a table to show that a captain must have document A and a ship's cat must have document R. Here is an example of coding:

Private Sub Form_AfterInsert()
'This code will run when the main form is saved
strSQL = "Insert Into Training (CrewID,DocID) Values ('" _
& Me.CrewID & "',"
Select Case Me.Position
   Case "Captain"
      strSQL = strSQL & "1)"
   Case "Ship's Cat"
      strSQL = strSQL & "9)"
   Case Else
      strSQL = strSQL & "3)"
End Select
CurrentDb.Execute strSQL, dbFailOnError
Me.Training_Subform.Form.Requery
End Sub

Here is an example of using a table.
The table:
[tt]DocID Position TableName
1 Captain Training
2 Captain Training
1 Captain Medical
2 Captain Medical[/tt]

Some code:

Private Sub Form_AfterInsert()
'This code will run when the main form is saved
strSQL = "INSERT INTO tblTraining ( DocID, CrewID ) " _
& "SELECT tblDocuments.DocID, " & Me.CrewID _
& " FROM tblDocuments " _
& "WHERE tblDocuments.Position='" & Me.Position _
& "' AND tblDocuments.TableName='Training'"

CurrentDb.Execute strSQL, dbFailOnError
Me.tblTraining_subform.Form.Requery
End Sub

 

[cristoffer]

well thanks remou but its seam that i don't well understand it. i hope could you stated clearly im not an expert in access and im new in programming in access hope you understand me.

well all i want is that when the user select a position in mainform combo then the subform automatically fill the required documents. i have one mainform and four subform that linking each other,subforms are Training, Medical,license,
and Travel Documents. each subform are automatically fill when the position is enterd.

hope im not such a burden in, but any i really appreciate the thing you done so far thank you very much.

 

[Remou]

You have not provided sufficient information for me to state this any more clearly. Backup your database, then take this code:

strSQL = "Insert Into Training (CrewID,DocID) Values ('" _
& Me.CrewID & "',"
Select Case Me.Position
   Case "Captain"
      strSQL = strSQL & "1)"
   Case "Officer 1"
      strSQL = strSQL & "9)"
   Case Else
      strSQL = strSQL & "3)"
End Select
CurrentDb.Execute strSQL, dbFailOnError
Me.Training_Subform.Form.Requery

And add it to the After Update event of your combobox. Change the names such as Training_Subform to the real names on your form. Run the form and then post back the code as changed by you and the errors that occurred. Code is by far the easiest way to communicate in these fora, it is a language we have in common.

 

[cristoffer]

hi sorry for not replying soon, well i just come back from my out of town trip. anyway i just read your reply seem i try it but the error occur. actually i don't have any code for this yet. so this the senario. i have a table called

tblCrewList
IDCRWLIST (Autonumber)PKey
Position (text) combo that link to tblRank (Intermediate) to Rank

tblCREWLIC
IDCREWLIC (autoNumber)PKey
IDCRWLIST (Number) Link in tblCrewList (1-M relationship)
IDLICENSE (Number) Link in tblLICENSE (Intermediate) Combobox

tblLICENSE
IDLICENSE (AutoNumber)PKey
LINCENSENAME (Text)

tblRANK
IDRANK (autoNumber)Pkey
RANK (Text) Name of the Position

required
IDLICENSE (Number) Link to tblLICENSE
RANK (text) Link to tblRANK (Intermediate)

then i made a mainform as recordsource is tblCrewList with
field textbox
IDCRWLIST
POSITION (Combobox that link in tblRANK,Rank)

then a subform 1 withrecordsource is tblCREWLIC with field textbox
IDCREWLIC (autoNumber)PKey
IDCRWLIST (that link in tblCrewList) and Child & Master
IDLICENSE (Number) Link in tblLICENSE Combobox

then subform 2 with recordsource is table required
IDLICENSE (Number) Combobox
RANK (text) Combobox
i just filled this subform with corresponding data like
IDLICENSE RANK
PRC CAPTAIN
GOC CAPTAIN
SIRB CAPTAIN
TESDA BOSUN
SIRB BOSUN
GOC BOSUN
and so on

so when i open the mainform the subform 2 will auto fill w/ the
corresponding data when a position is filled in mainform while the subform 1 is not filled with data. so the user manually filled the subform 1 with regards the position of required licensename. so when they try to save the whole mainform it will prompt then some message that it will required some data that matches in subform 2. if all data are match in subform 2 it will save but if not the missing IDLICENSE will prompt them in message saying "you can't save unless a required "the IDLICENSE" is entered in subform 1.

i hope you fully understand what mean to say. im hoping for your kind attention and understanding. sorry for being such a burden, anyway again thank you very much.

 

[cristoffer]

hey remou,
thank you for the valuable post that you give meit work fine now for i just figure it out and work here the code that work for me.

Dim strSQL As String
strSQL = "INSERT INTO tblCREWLIC ( IDLICENSE,IDCRWLIST ) " _
& "SELECT required.IDLICENSE, " & Me.IDCRWLIST _
& " FROM required " _
& "WHERE required.Rank = '" & Me.POSITION & "' "
CurrentDb.Execute strSQL, dbFailOnError
Me.tblCREWLIC_subform.Form.Requery

thanks you very much but this is just a far of the work that i must do and im thinking about it anyway thanks.