Set Text box value in report using vb 1

[craigglenn]

Ok I have spend days trying to figure this out and have read post simular to my problem but can't quit get it.

I have a single report, off of one table, no joins.
I have and UB text box in the report header.

All I want to do using the code below found in the on open event of the report is the the select case rday section to set the value of the textbox to the relevant Day, ie "Monday" if case 1 is true.

Thanks in advance for your help.

Option Compare Database

Private Sub Report_NoData(Cancel As Integer)

MsgBox ("No records found for specified route.&quot
Cancel = True

End Sub

Private Sub Report_Open(Cancel As Integer)

Dim routeN As Integer
Dim rday As Integer
Dim theQuery As String
Dim dayField As String


GetRouteDay rday 'prompt for route day
GetRouteNum routeN 'prompt for route number

Select Case rday
Case 1
dayField = "PropSwpMonday"
Me.OrderBy = "[PropPriorityMonday]"
Case 2
dayField = "PropSwpTuesday"
Me.OrderBy = "[PropPriorityTuesday]"
Case 3
dayField = "PropSwpWednesday"
Me.OrderBy = "[PropPriorityWednesday]"
Case 4
dayField = "PropSwpThursday"
Me.OrderBy = "[PropPriorityThursday]"
Case 5
dayField = "PropSwpFriday"
Me.OrderBy = "[PropPriorityFriday]"
Case 6
dayField = "PropSwpSaturday"
Me.OrderBy = "[PropPrioritySaturday]"
Case 7
dayField = "PropSwpSunday"
Me.OrderBy = "[PropPrioritySunday]"
End Select
Me.OrderByOn = True

theQuery = "Select * From mainProperty Where " & dayField & "=" & routeN & ";"
Me.RecordSource = theQuery

End Sub

Private Function GetRouteNum(routeN) As Integer

On Error GoTo ErrorHandler
Dim message, title
message = "Enter Route Number:"
title = "Route Number"
routeN = InputBox(message, title)

Exit Function
ErrorHandler:
Select Case Err.Number
Case 13
MsgBox ("You must enter a valid route number&quot
Case Else
MsgBox ("You must enter a valid route number&quot
End Select
Resume


End Function


Private Function GetRouteDay(rday) As Integer

On Error GoTo ErrorHandler
Dim message, title
message = "Enter number for day of route:" + (Chr(13)) + "1. Monday" + (Chr(13)) + "2. Tuesday" + (Chr(13)) + "3. Wednesday" + (Chr(13)) + "4. Thursday" + (Chr(13)) + "5. Friday" + (Chr(13)) + "6. Saturday" + (Chr(13)) + "7. Sunday"
title = "Route day"

InvalidDay:

rday = InputBox(message, title)

Select Case rday
Case "1", "2", "3", "4", "5", "6", "7"
Exit Function
Case Else
MsgBox ("You must enter a valid Day Number&quot
GoTo InvalidDay
End Select

ErrorHandler:
Select Case Err.Number
Case 13
MsgBox ("You must enter a valid day&quot
Case Else
MsgBox ("You must enter a valid day&quot
End Select
Resume

End Function

 

[dhookom]

I don't see where you are setting any value of any text box. I would recommend that you use a form with combo boxes for the user to select the values prior to the report opening. This would be much cleaner. Also, I never use the OrderBy property. I prefer to modify the Control Source of the GroupLevels. Check Allen Browne's site

http://users.bigpond.net.au/abrowne1/ser-33.html.

BTW: I think you might confuse potential helpers when you use "UB". After some head scratchin' I figured you mean UnBound.

Duane
MS Access MVP

 

[craigglenn]

Thanks Duane,

I removed all of my failed attempts to set the value of the text box. I would rather not prompt the user for the same information twice. I ask for the day using getRouteDay function. I want the Day to appear at the top of the report in the header as well. Thought I could use a text box and assign it within the case select statement.

Rather than confuse everyone with all of my failed attempts, I was hoping for a directions to take.

I have tried using the me.(textboxname) = "Monday"
in the case select statement and of course it says that i can't assign a value to that object.

I have explored the control sourse approach for the textbox and can't figure it out.

Any suggestions would be great.

Thanks for the reply,
Craig

 

[dhookom]

I would grab both values from controls on a form. Then you can add a text box on your report and set the control source to:
=Forms!frmYourForm!cboDay

Duane
MS Access MVP

 

[craigglenn]

FYI

this did the trick!

Me.PropRouteNum.ControlSource = "=""Monday"""


and to set from a variable from my code I used...

Me.RNUM.ControlSource = "=" & routeN & ""

Hope this helps someone else.

Craig Glenn

 

[koresnordic]

Thanks Craig. this post hepled me a lot as well. Have a star on me.



Graham

 

[VRach02]

I think this may be what i am look for currently but am confused at the code given.

I would like the same thingi believe..
polulate a texbox ina report using code in a cmd button on a form.

slight difference my textbox names are variables that come from a loop

Me("txtSubHeading" & var) = CRTL.Column(4, VarItmSub)

i currently use this can it be changed ?> (it was placed in the detail section of the report.

Thanks