Visual Basic Question

[Kenshin17]

Ok here is the deal:

I am using Access 2003 and I want to code a little module to do the following:

I have a label at in the header of several reports that will change every week. Rather then go through and replace the label for each report every week I would like to code a module that will prompt the user for the data they want in the label, then take that input and change all the report header labels to that date. However I cannot figure out how to make the text of a label the variable value.

I tried label.text but that does not work in my version of VB. I tried label.caption, but that did not work either.

Code: Select all

I set it up as such (note I am using generic names)

Dim input as String

input = InputBox("Prompt","Title","Default value")

Dim label1 as Label

label1 = Generic_Report.ChangeLabel

label1.Caption = input


The above did not work

Any help would be much appreciated.

[TheMelodyMaker]

It's a silly thought on my part, but have you created the object "Generic_Report" before executing your code block above? It's the only solution I can think of that makes sense... ^_^;

[Kenshin17]

No no. I was just using generic names. I have a report, I have a label, I just wanna make it dynamically change the labels text to what the user inputs in the input box. Actually I have several reports and I want all the labels to update on all the reports based on a single input.

[Cap'n Nick]

Simple variable assignment won't create a usable control handle in VBA. Use "Set" before your assignment statement and things should work as expected.

The generic statement would look like this:

Code: Select all

Dim label1 As Label

[b]Set[/b] label1 = Generic_Report.ChangeLabel

Hopefully that will do it. Tell us if things work out.

[Kenshin17]

My version of VB does not allow the use of ChangeLabel command.

[Cap'n Nick]

Oh, sorry. I didn't meant ChangeLabel to act as a function. I thought you were using "ChangeLabel" as the name of the label you wanted to change. The idea is to set your variable to the label you want to mess with by using Set and then change its caption property. It would be something like:

Code: Select all

Dim labelToChange As Label
Dim inputText As String

inputText = InputBox("Prompt","Title","Default Value")

Set labelToChange = TheReport.labelToChange

labelToChange.Caption = inputText

In any case, the problem isn't the Caption property. That should work fine once you're actually working with the label in question. The problem is getting a proper reference to it, which doesn't happen when you use the equal sign alone to assign the label to a variable.

Do note that if the reports are not already open it may be difficult to change labels on them.

[Kenshin17]

I see. Ok I'll give it a shot.

Thanks

[Kenshin17]

Ok I tried it and it is as if VB ignores the code.

Code: Select all

Dim wdate As String
Dim aldate As Label

wdate = InputBox("Enter this weeks date range", "Week Dates", "03/03/03 to 03/10/03")

Set aldate = Report.accdate

aldate.Caption = wdate

[Cap'n Nick]

That code looks alright. I wouldn't be able to tell what's up without seeing the rest of the program. Sorry.

On a side note, though, if the dates you need will always be the beginning and end of the current week, it might be helpful to use the system date functions to generate the label automatically instead of asking the user to type. it.

[Kenshin17]

I wasn't sure how to do that. I need the date to be from Monday to Sunday.

[Cap'n Nick]

Ah, now that I can help with. You can get the label you need using the Date function to get the current date and the Weekday function to get an integer value representing the day of the week. Once you have these values you can get the dates for the beginning and end of the week using the DateAdd function and format it the way you need it with FormatDateTime. I just tested this code, where dateLabel is a Label element already present on the report. This code runs in the report's opening function.

Code: Select all

Dim currentDate As Date
Dim currentWeekDay As Integer
Dim firstDayOfWeek As Date
Dim lastDayOfWeek As Date
Dim dateText As String
   
currentDate = Date
currentWeekDay = Weekday(currentDate)
firstDayOfWeek = DateAdd("d", 1 - currentWeekDay, currentDate)
lastDayOfWeek = DateAdd("d", 7 - currentWeekDay, currentDate)
   
dateText = FormatDateTime(firstDayOfWeek, vbShortDate) _
             & " to " & FormatDateTime(lastDayOfWeek, vbShortDate)
   
dateLabel.Caption = dateText

[Kenshin17]

Thanks I'll give it a shot.