lookup field value in a sub form

T

tina

Hi
I am going to try and make this as simple as I can (I hope). What I need is
to get value of subform form2 field month where its monthnumber is equal to a
value in textbox2 in form1
my sub form form2 has 2 fields month and monthnumber
I cannot make a link to form1 as textbox2 is a calculated field resulting in
information from combobox selection
is this possible
Thanks
Tina
 
S

Sharkbyte

Tina:

You would want to do something like this:

Set Subfrm2 Control Source to: select month, monthnumber from tblmonths
where monthnumber = forms![form1]![textbox2];

Because Textbox2 hasn't calculated until a selection, the subform returns
Null. So you will need to add "subfrm2.requery" to the After Update property
of Form1.ComboBox.

When you select from the combobox, textbox2 will calculate, and the requery
will rerun the subfrm2 query and return the proper month.

HTH

Sharkbyte
 
W

Wolfgang Kais

Hello "tina".

tina said:
I am going to try and make this as simple as I can (I hope). What I
need is to get value of subform form2 field month where its
monthnumber is equal to a value in textbox2 in form1 my sub form
form2 has 2 fields month and monthnumber I cannot make a link to
form1 as textbox2 is a calculated field resulting in information from
combobox selection is this possible
Click to expand...

Who said that you can't make a link? Set the LinkChieldFields property of
the subform control to monthnumber and set the LinkMasterFields property to
textbox2.
What happens?
 
T

tina

Thank you both for your help
I could not use link as monthnumber is a delete query type and textbox2 does
not appear in drop down list in wizard
I had lots of goes at with requery etc but was getting errors so found
following easier by not attaching sub form but opening form1 in code as
follows this may not be correct way or simplest but works for me

Private Sub Combo18_AfterUpdate()
If IsNull(Me.cbomoveto) Then
MsgBox "please select batch number first"
Me.Combo18 = Null
Exit Sub
Else
Me.Text20 = Me.cbomoveto
Me.Text22 = Me.Combo18.Column(0)
Me.Text29 = Me.Combo18.Column(2)
Me.Text33 = Me.Combo18.Column(1) + Me.MonthNo
DoCmd.OpenForm "form1", , , "[MonthNo]=" & Me![Text33]
Forms!form1.Visible = False
Me.Text35 = Forms!form1!MonthYear
End If
End Sub

Thanks again
 
W

Wolfgang Kais

Hello "tina".

Thank you both for your help
I could not use link as monthnumber is a delete query type and textbox2
does not appear in drop down list in wizard.
I had lots of goes at with requery etc but was getting errors so found.
following easier by not attaching sub form but opening form1 in code
as follows this may not be correct way or simplest but works for me.

Private Sub Combo18_AfterUpdate()
If IsNull(Me.cbomoveto) Then
MsgBox "please select batch number first"
Me.Combo18 = Null
Exit Sub
Else
Me.Text20 = Me.cbomoveto
Me.Text22 = Me.Combo18.Column(0)
Me.Text29 = Me.Combo18.Column(2)
Me.Text33 = Me.Combo18.Column(1) + Me.MonthNo
DoCmd.OpenForm "form1", , , "[MonthNo]=" & Me![Text33]
Forms!form1.Visible = False
Me.Text35 = Forms!form1!MonthYear
End If
End Sub
Click to expand...

Try to add the subform to the form again and configure the subform
control's data properties as follows:
LinkChildFields: [MonthNo]
LinkMasterFields: [Text33]
Then the form should work as desired, using the code that fills Text33.
 
Top