Passing Data From One Form To Another

[mtagliaferri]

I have a fully functional DB with a few falls, one of these is a form where I enter numeric data as a known staff number which would populate employees details in other text boxes in the form, when the staff number is not known clicking on another text box would open another “Help” form (Default View: Continuous Form – Records Selector: No) with a search box to search the employees and display relative staff number once this is displayed the hep form is closed and the staff number is typed in from memory.
I would like to take it to the next step by passing data from the frmCrewMemberSearch (Double clicking on Staff Number) to the sfrmCrewAssigned Staff Number text box.

I have the following NOT WORKING code in the help form

Private Sub StaffNumber_DblClick(Cancel As Integer)
    Me.Refresh
    If Not IsNull(StaffNumber) Then
    Forms![sfrmCrewAssigned].Form.StaffNumber = Me.StaffNumber
End If
    DoCmd.Close
End Sub

 

[pbaldy]

Your syntax for a subform is incorrect:

http://www.theaccessweb.com/forms/frm0031.htm

 

[mtagliaferri]

Hey Paul,
I am pretty basic with VBA:banghead:
By looking at the link I think I am eveno more in the deep end....
I Have a main form where there are various TABS, in one TAB there is a form within this form there is a subform where I input the staff number if the staff number is not know I open the form "help".
In the form help from the search box I search the employee which will narrow down the number of records according on the name searched, I now want to click on the staff number on the help form and have this copied to the subform staff number text box.

So I changed the code to

Private Sub StaffNumber_DblClick(Cancel As Integer)
    Me.Refresh
    If Not IsNull(StaffNumber) Then
    Forms!sfrmFlight!sfrmCrewAssigned.Form.StaffNumber = Me.StaffNumber
    'Forms!sfrmCrewAssigned].Form.StaffNumber = Me.StaffNumber
End If
    DoCmd.Close
End Sub

not working...

 

[pbaldy]

Neither of those starts with the main form, presuming the prefixes are valid.

 

[mtagliaferri]

Hi Paul,
let me get this right, would the main form be the one that open up as help form or the one i am trying to write the value in?

Private Sub StaffNumber_DblClick(Cancel As Integer)
    Me.Refresh
    If Not IsNull(StaffNumber) Then
    Me.frmDuty.Form!sfrmCrewAssigned.From!StaffNumber
End If
    DoCmd.Close
End Sub

 

[pbaldy]

It looks like the one you're trying to write to. In other words, the form containing the subform container that you're writing to.

 

[Cronk]

Assuming the main form is called frmMain, which in turn has a sub form whose control name is sfrmFlight. which in turn has another sub form whose control in the parent sub form is sfrmCrewAssigned, and if the control (StaffNumber) that is being double clicked is on the parent form (frmMain), the code would be

me.sfrmFlight.form.sfrmCrewAssigned.form.StaffNumber = Me.StaffNumber

 

[mtagliaferri]

I am loosing the will to live .....:banghead:

I have a very basic knowledge and have built this with the help of everyone on this forum over the years....
I am slowly improving this as a hobby and I am realising about the flows this database has, I have just realised that the Crew member in the sfrm is a dropdown mask where it is limited just to type the staff number (Capture 2 for the properties...Don't ask why it is this way as it was done years ago...)
In the Capture 1 there is a proper idea of what I am trying to get to..

 

[pbaldy]

So from the link:

Forms!Mainform!Subform1.Form!Subform2.Form!ControlName

for you:

Forms!frmDuty!sfrmCrewAssigned.Form!WhoKnows.Form!IDCrewMember

with the bit in red replaced by the second subform control's name.

 

[MajP]

If I understand this correctly for a staff member they have an IDcrewMember and a StaffNumber.
When you do your search, I would assume on the search form you are getting a found record which should have the StaffNumber as well as the IDCrewMember. It may not be shown in your form, but probably in the recordsource. If not you can easily add it to the search forms recordsource. Then return that instead of the staff number.

Forms!frmDuty!sfrmCrewAssigned.Form!WhoKnows.Form!IDCrewMember

The combo is bound to IDCrewMember, but it is displaying a second field. I assume that is staffnumber. So if you set the correct IDcrewMember it will display the correct

 

[mtagliaferri]

My dullness in endless...

Private Sub StaffNumber_DblClick(Cancel As Integer)
    Me.Refresh
    If Not IsNull(StaffNumber) Then
        Forms!frmDuty!sfrmCrewAssigned.Form!frmCrweMemberSearch! = IDCrewMember

End If
    DoCmd.Close
End Sub

Still not working

 

[pbaldy]

Mine (adjusted for the second subform) and yours:

Forms!frmDuty!sfrmCrewAssigned.Form!frmCrweMemberSearch.Form!IDCrewMember
Forms!frmDuty!sfrmCrewAssigned.Form!frmCrweMemberSearch!

 

[mtagliaferri]

Still getting an error plus it is asking the "="

 

[JHB]

Post you database with some sample data, then it seems you can't get it, because the reference is still wrong!

 

[pbaldy]

What I posted is only the left side of the equation. You've still left it incomplete.

 

[mtagliaferri]

Post you database with some sample data, then it seems you can't get it, because the reference is still wrong!

Here it is attached a shrink version of it: Shift Click to open it, launch frmMain, double click on a Trip Date then Flight Details tab then expand the record. Here is my drama.... click on the Crew Type to open the frmCrewMemberSearch what I am traying to achieve here is once the crew member has been found through the search box I would like to doble click on the staff number and copy the value in the Staff Number on the form behind and automatically close frmCrewMemberSearch.

Now the cherry on the cake would be instead of clicking on the Crew Type to open frmCrewMemberSearch I would like to double click on the on the Staff Number on the Flight Details Tab and if there is no value it will open the frmCrewMemberSearch if there is already a value it will open the frmCrwMember with relative record (As it currently does)

 

[JHB]

One of the problem is/was that you've renamed the controls in the form.
The correct reference is:

Forms!frmDuty!sfrmFlight![SM Crew Assigned].Form!StaffNumber = Me.StaffNumber

Now you'll get another problem with duplicate entries, so actually what are you trying to active, besides to get the staff number?
Why are you not picking the staff from a combobox?
What table is the staff number going to be inserted in?

 

[mtagliaferri]

One of the problem is/was that you've renamed the controls in the form.
The correct reference is:
Now you'll get another problem with duplicate entries, so actually what are you trying to active, besides to get the staff number?
Why are you not picking the staff from a combobox?
What table is the staff number going to be inserted in?

the combo ox refers to the staff number and I cannot search by name,
I think I am slowly getting your point, which now confuses me further....
the staff number does not go in any table. when typing the staff number in the combo box it works and writes the IDCrewMember in the tblCrewAssigned....

 

[JHB]

Okay, I think I got what you're trying to archive, database attached.
Problem is still that you rename the controls.
If you do so, then keep it in mind that you've done it, if you can't that, then write it down or else don't rename the controls!
Reference is:

    Forms!frmDuty!sfrmFlight![SM Crew Assigned].Form!IDCrewMember = Me.Position