Assign Value to Text Box

[ParyGoal]

Hello everyone.
I have a Main Form with several subform. There is a direct Parent Child relationship between the main Form and the all the subform. On the main Form I have a combo box from which user select employee name. I want to fk empid in all the subforms to be set the value of the employee selected from the combo box. I am sure it must be sample. Right everytime I enter a record the value that gets stored in the empid of the child table is 0.
My subforms are not linked to the main form simply because It have them nested and access won;t let me create the link between them.
I will appreciate any help
Thank you
ParyGoal

 

[Remou]

You can type Child and Master fields into the subform control. You can have more than one Child and Master field.
Have you tried setting the Master field to the name of the Combobox and the Child field to EmpID?

 

[ParyGoal]

Thank you Remou for the reply. The problem is I have nested subforms. Do you think I can still define the relationship between the subform going down levels deep?
Thank you
ParyGoal

 

[Remou]

Yes, you can. You can set the master field to say,

[tt]Forms!frmForm!cboCombo[/tt]

And the Child to EmpID.

You will need to include a Me.Refresh in the change event of the combo.

 

[ParyGoal]

Thank you so much Remou. I think the code is starting to make some sense to my application. The only problem is two of field are not bieng stored in the table. These two are foreign keys in the detail table that my subform is based on. The ID that comes from the Parent Form and the EmployeeID that gets selected from the Combo Box. How can stored these two values in my child table?
Thank you
ParyGoal

 

[Remou]

If I understand you, I think that you should be able to use, say:

[tt]Master: Forms!frmForm!cboCombo, ID
Child: EmpID, ID[/tt]

Master fields do not have to refer to tables, any control can be used.

As long as the SQL string that your subform is based on includes all the fields you want to use, the fields do not have to be put on the form.

 

[ParyGoal]

Hi Remou
If am sorry I really could not understand the statements.
What I want to store the value of the selected employee in the Child table? It's not working for me.

 

[Remou]

Have you set the child field to the name of the field that you want to store EmpID in? Have you included this field on the subform?

 

[ParyGoal]

The way I have my database structured, will not allow me to link the forms using those Keys. The employeeCombo box is actually an unbound control on the main form.
I hope this makes sense

 

[Remou]

It is the fields on the subform that are important. What is the Recordsource for the subform? What are the link child and link master fields for the subform control?

You can set the link master fields to refer to a combobox, so Access should allow you to do this. When you say it will not allow you to use these Keys as links, what do you mean?

If you mean that you cannot choose these keys using the wizard, that is not important. You can edit the subform control and type in link child and link master fields.

It can be difficult to select the subform control, but you can choose it from the drop-down list on the Formatting toolbar or you can use the tab key to select it.

Link child and link master fields are properties of the subform control, not of the form contained by the control.

 

[ParyGoal]

When I set the link master field to combobox, I am able to view the detail information pertaining to the selected employee. I use the following syntax:Forms!frmManagementTraineePRogram2!cboEmployee Master Fields.
ID Child Link
ID is nothing an autonumber in the Parent table.
Now what it is really important for me is to be able to store EmployeeID value in the Child Table.
My Child Table Structure is a follow

ID
SafetyID
EmployeeID
ManagerID
Factor
Date

I will end up with something like this: let's employeeID selected from the combo box is 10

SafetyID EmployeeID ManagerID Factor Date
1 10 2 1 Now()
2 10 2 2 Now()
3 10 2 1 Now()

I want the values EmployeeID and ManagerID as well as Date to repeat for each SafetyID selection that is done from the subform

Thank you

 

[Remou]

This:
[tt]Forms!frmManagementTraineePRogram2!cboEmployee Master Fields
ID Child Link[/tt]

Means that Access will try to store the value in cboEmployee in the ID field, what you need is something more like:

[tt]ID, Forms!frmManagementTraineePRogram2!cboEmployee Master Fields
ID, EmployeeID Child Link[/tt]

However, as you want to set several fields to specific values, you may wish to set the default value for the subform in the After Update event of the controls on the main form. For example:

Me.[Name of First subform control].Form.[Name of Second subform control].Form.ManagerID.DefaultValue = Me.cboManager

The difference comes in what records you want to display in the subform, if you want only to display records with the same employee and manager, use link fields; if you want to display more records, use a default value. I am sorry if I am over-explaining, but I am trying not to leave bits out.

 

[ParyGoal]

Thank you Remou. You Deserve several Stars.
Actually I got the MainForm and Subform to syncronize and store the value of the empid in the child table.
The manager combo box, however, is located on the subform. I would store it in the child table just like the EmpID value.
I tried using Me.[Name of First subform control].Form.[Name of Second subform control].Form.ManagerID.DefaultValue = Me.cboManager. I am getting an error "access can;t find the field referred to in your expression".

 

[Remou]

Thank you.

You need to change the names to exactly match your subform controls. You may wish to look at:

http://www.mvps.org/access/forms/frm0031.htm

 

[DSTR3]

I have the following code to set the value of unbound checkboxes to value of a yes/no field in a table. It doesn't work. It sets all of the checkboxes to either -1 or 0. I think the problem is the referencing of field in the table. The field is called Active. Any help appreciated.
Thanks
DS





Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM SecurityDetails WHERE SecurityDetails.SecurityID = " & Me.TxtID & "", dbOpenDynaset)

With rst
If .EOF = False And .BOF = False Then
Dim Ctl As Control
For Each Ctl In Me.Controls
If Ctl.Tag = "9" Then
If !Active.Value = -1 Then
Ctl = -1
ElseIf !Active.Value = 0 Then
Ctl = 0
End If
End If
Next Ctl
End If
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

 

[Remou]

DSTR3
It would be much better to create your own thread. It is not fair to ParyGoal to use this thread and it is not fair to you, because it is possible that your post will be ignored or overlooked. That being said: YesNo fields are boolean, and are never really equal to Yes and No, these are just words displayed to the user.
Yes is -1
No is 0
Look at the way you field is set-up in the table. Make sure that the Data Type is Yes/No and, if you want Yes or No displayed, choose a Yes/No Format from the drop-down list.
If this does not work for you, create a new thread and reference this one.

 

[DSTR3]

Sorry I accidently hit the submit button. I would not intentionally start a new thread in someone else post! Accidents do happen. Can't this be deleted?
Thanks
DS