Concatenate string on main form from a value in subform 2

[skyline666]

Hi,

I have a bit of a puzzler. I have a form called Category (in form view), and a subform called Risk (in datasheet view) in an Access 2003 project called RiskAnalysis. In the subform I have a combo box field called WBS which gets its value from a stored procedure called usp_WBSChange. The stored procedure returns 4 fields; WBSID (int), WBS (int), WBSDesc (varchar) and Name (varchar). The combo box in the subform only shows the field Name. The code that creates name in the stored procedure is:

CAST(CES.dbo.tlbWBS.WBS AS varchar(5)) + ' - ' + CES.dbo.tlbWBS.WBSDescription AS Name

So a returned value could be "0 - Danger".

What I would like is to display the value for WBS (being name from stored procedure) on the main form in a text box (or label, whatever!) in the format "WBS : [WBS]", without the quotes and where [WBS] is the returned value and the rest is just normal text.

I have tried doing this already, but what I have done so far returns a error saying "Run-time error 2427: You entered an expression that has no value". The code I have used so far for this is:

Private Sub Form_Open(Cancel As Integer)

Me.txtWBS.ControlSource = "WBS : " & Me.Risk!WBS

'Me.Requery

End Sub

Help will, as always, be very much appreciated.

Many thanks,

Andrew

 

[TheAceMan1]

How are ya skyline666 . . .

How about:

[blue]Me.txtWBS.ControlSource = "WBS : [" & [Risk].form!WBS.Column(3) & "]"[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[skyline666]

Hi AceMan,

I tried what you suggested, there isn't an error message, but #Name? comes up instead in the text box. Any idea why?

Andrew

 

[skyline666]

Just a little bit of info, I put a breakpoint on the code line and on the Me.Requery line, and when I stepped to the requery line and hovered over the previous line, it said:

Me.txtWBS.ControlSource = "WBS : []"

Andrew

 

[PHV]

And this ?
Me!txtWBS.ControlSource = "WBS : ([Risk].Form!WBS.Column(3))"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skyline666]

Hi PHV,

No that hasn't worked either . I can't think what will make it work.

Andrew

 

[PHV]

In the Current event procedure of the subform and in the AfterUpdate event procedure of the combo:
Me.Parent.txtWBS = "WBS : [" & Me!WBS.Column(3) & "]"

BTW, get rid of the ControlSource property of txtWBS.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skyline666]

OK, i put the code in as you said, and commented out the control source on the form_open, and now the text box is blank, and you can type stuff into it if you click it, which I don't think you could before (and also isn't something I want!)

Sorry to be a pain!

Andrew

 

[skyline666]

Doh!! It did work, why did I say it didn't show up then, well because I hadn't actually chosen a record for it to show. What a dip!

Thanks PHV and AceMan, i'll give you a star each.

Andrew

 

[skyline666]

Just as an addition for anyone else who finds this useful, if you want to use this but have a standard field (ie: just a cell you enter information to), leave out the .Column(#). As an example I had two columns I wanted to have data shown for and did this (as described above in relation to where to put it):

Me.Parent.txtCriticality = "Criticality : [" & Me!Criticality & "]"

Also, I found that when I chose a record with data not there, the original data was still visible, so I put Requeries to refresh the text box. Note that in the below code as I have it now, I have put the Me.Parent... lines together and Requery lines together afterwards, if you put the Requery line after the relevant Me.Parent line, your form will continuously refresh and end up not responding, it did for me anyhow!

Me.Parent.txtWBS = "WBS : [" & Me!WBS.Column(3) & "]"
Me.Parent.txtOwner = "Owner : [" & Me!OwnerID.Column(1) & "]"
Me.Parent.txtRiskRating = "Risk Rating : [" & Me!RiskRating & "]"
Me.Parent.txtCriticality = "Criticality : [" & Me!Criticality & "]"
Forms!form1!txtWBS.Requery
Forms!form1!txtOwner.Requery
Forms!form1!txtRiskRating.Requery
Forms!form1!txtCriticality.Requery

Hope this helps,

Andrew