Unbound TextBox; Define Control Source w/VBA

[sard0nicpan]

Ok, my problem is as follows:

I am helping a colleage debug the code behind his reports. Originally, his text boxes were bound to Stored Procedures. Unfortunately, this did not provide him with the control of his data he desired. So instead we call the procedures in VBA. Fine. However, his text boxes are a single control with multiple values. If we set his textbox as follows:

me.myTextBox = !UserID

as it loops thru it only overwrites the same text box. Now I know how to do it with a list box (and here's the complete code) as follows:

Function PopNow()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command


cn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Data Source=usw4250062;Initial Catalog=MyImagio;" & _
"Integrated Security = SSPI"

cn.Open

UBID1 = InputBox("Your Entry")

Set cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cmd.CommandText = "Exec dbo.spA1 " & UBID1

Set rs = cmd.Execute
 
UBID1 = ""

  
With rs
Do Until rs.EOF
    UBID1 = Str(!UBID) & ";" & UBID1
    rs.MoveNext
    Me.lboTest.RowSource = UBID1  ' I get a nice __ 
    Drop list
    
Loop
End With

cn.Close

End Function

My friend has about 12 reports in his project, all painstakingly formatted with text boxes; so switching to listboxes is not a preferred direction for him.

When his reports were printed with the bound controls his values would look exactly as if they were from a dropDown list. I'm not all that into programming reports so I'm stumped. I guess the way his textbox looked is as if it were a textbox on a continues form--the same object(textBox1) would be assigned many values, appearing on the report as a list.

I hope I'm clear. Anybody have an idea? I'm sure it's simple, however, with reports I really don't know what I'm doing . . .

Thanks in advance,

SP

 

[dhookom]

Is the report bound to any recordsource? Can you provide some sample values and desired output?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sard0nicpan]

No the report as it stands is unbound.

The format is something like this:

Project Type Budget Wedge etc . . .

Acquistion $1.00 $0.99
Completion
Facility Capital

So the values would be currency values. I have been hunting down threads, and I feel like I almost have something. However, I'm not getting it.

Say the stored procedure is spGetRevenue.
The textbox (let's just do one) is txtRev.
I dim an ADORecordset as above named rs.
I return about 5 or 6 values for ProjectAcquisition/Budget(see above). This recordset somehow has to be written to the textbox. If I simply do

with rs
do until rs.eof
  me.txtRev = !ReturnVal
Loop
End with

I end up writing over the same textbox 5-6 times. I think it involves setting up a value for the ControlSource like:

me.txtRev.ControlSource =  ???  'THis is the problem _
                                 I do not know how to _
                                 set this value to get _
                                 my desired output _

If it were a bound text box I would simply set the Control source in properties. However, to keep this story from geeting too complicated, I'll leave out why we need the text boxes to be unbound (plus it's irrelavent to this topic)

Any hope?

SP

 

[dhookom]

If you have an unbound text box, just use code like:
Me.txtRev = rs!YourField

I think you are making a mistake by attempting to create an unbound report. I trust you know what you are doing.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sard0nicpan]

with rs
do until rs.eof
  me.txtRev = !ReturnVal
Loop
End with

As noted above in my previous post, thats what I started with. Unfortunately, that just writes over the previous data each time I access a new value from the recordset

For example, the code leads to the following:

say MyVar in the rs has the following values:
1,2,3,4
AND they are accessed in ascending order,
the write to the text box starts with 1 then 2, but 2 writes over one, three writes over 2, and 4 writes over 3--so when I preview my report, I only see the textbox with the value 4 . . . 1 thru 3 are gone.

You may be right about the unbound thing, however, I do have one question for everyone:

Any idea of how to set the ControlSource to an array within your code? Like

me.myText.ControlSource = MyArray

I dimensioned my Array as a string, however when I tried to set the control source I received a type-mismatch error.

Thanks,

SP

 

[dhookom]

If you want to build the value of txtRev then try:

with rs
do until rs.eof
  me.txtRev = Me.txtRev & " - " & !ReturnVal
Loop
End with

I haven't seen any "desired output" from you, only code which doesn't tell us anything about what you would like to see as a result of your report.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sard0nicpan]

By the way, thanks for responses so far, however, I think I must have failed in communication.

Let's start over (and simplify things). I'll give you a simplified version of what I'm trying to do.

Situation: Report's record source:StoredProcedure

Report with two text fields: Text1, RevDate

StoredProcedure Values:

ID CurrencyVal RevDate
1 $2,000 4-22-2005
2 $3,000 4-23-2005
3 $4,000 5-01-2005

OutPut to Screen (textbox label eliminated):

$2,000 4-22-2005
$3,000 4-23-2005
$4,000 5-01-2005

Sort of like a continuous Form, right? I minimize the size of the report until it's no bigger than the text box, so while I have three records, they all show up on one page. (This is much simpler than what I'm doing, but it's at the core of it).

Now with the code you posted above , my output for the currency text box is:

$2,000 - $3,000 - $4,000 all in one box.

So there is something missing here. I need to take that textbox to have a one to one relationship with the records, that is, a new occurance of that textbox for each record (Not as you see above, all records stuffed into one occurance of the text box).

I hope that was a bit clearer.

Thanks,

SP

 

[dhookom]

I still don't see why you don't just bind your report to the stored procedure or a temporary table or other.

I'm sorry but I am leaving for a few days, so you are at the mercy of others who might read this thread and attempt to figure out what you have and what you need to resolve your problem.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sard0nicpan]

Hey no problem dhookum . . .

Actually I have been searching threads from several forums (other websites) and have seen this same scenario pop up a few times in the past. No one ever had this resolved due to the fact that its just not possible (oh well . . .)

Anyway, I am going back to binding it via the data tab (I'll still pass my parameters via code . . . that I can do). Its just curious that there is no way to create multiple iterations of the same text box in code just as you can when reports are generated from a bound data source.

Enough breath wasted on this . . .

Thanks,

SP

 

[sard0nicpan]

Last word . . .

It was much simpler than I thought. I just switched my front end from a regular Access front end to an Access project front, much simpler way to do it. Here's a scaled down version--I was making it much too complicated:

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "dbo.spA1"
Me.Text0.ControlSource = "UBID"

End Sub

Duh! I feel stupid! No connection string no, ADO needed

Sorry for the waste of time . . .

SP

 

[sard0nicpan]

My apologies to the moderators for trying to start a new thread based on the text below (it was removed from the Coding forum--sorry for the redundance, my bad!).

Anyway, I thought that the above post was the last word, unfortunately I was wrong. For some reason, when I add the "Exec" to the SP call, Access declares that the SP does not exist or cannot find it. Makes no sense at all!

Let me spell it out below:

I have an Access 2000(standard edition) project where I want to set my recordsource dynamically in VBA. I coded according to the following link:

http://support.microsoft.com/?kbid=300693

So you'd think it would work, right? No. My project cannot find the stored procedure. If I take away the "Exec" and the parameter part of the statement it works (and then prompts me for the parameter). See code:

Private Sub Report_Open(Cancel As Integer)  
Dim strRecordSource As String   

strRecordSource = "Exec dbo.spA1 'td962'"    
Me.RecordSource = strRecordSource

End Sub

Ok so I tried to see if I could set up a Northwind.ADP like in the link and got the "cannot find" statement also. This is even though I just cut and paste the same code from the link above.

Private Sub Report_Open(Cancel As Integer)    
Dim strRecordSource As String 
   
strRecordSource = "Exec [SalesByYear] '1/1/97','12/31/98'"
Me.RecordSource = strRecordSource

End Sub

So the only thing I can figure is that perhaps this code does not work with my edition of 2000.

Any ideas(?) . . .

By the way, I am the dbo, and I tried the above with dbo.spA1 and with plain spA1. The only way it finds it is if I eliminate the Exec AND the parameters.

This makes no sense to me because every link tells me that my code is just fine . . .

thanks in advance,
SP

 

[sard0nicpan]

Well I give up!

I just going with:

Report's Recordsource: spA1
Report's InputParamaters: @Dummy=Forms!frmDummy!txtDummy

Not elegant, but it works

Thanks to all who respnded,
SP