Hi,

I’m trying to return a value from VB 6 to Access 2007 report and the unbound textbox remains empty. It seems that actually the problem is not coming from the coding itself, but due to connectivity between the two ends. In fact, I tried to enter a Beep instruction as a diagnostic in the code and this return no sound at all. I tried this with different methods, like _Print, _Format, _OnLoad... but to no avail!!! It’s really frustrating.

The strange thing is that this same program used to work perfectly on Access 2003 version, but the pc I am using has got the vista version. I even tried to work on a new database as a trial and the same happened. I can’t understand how to go about this problem. If anyone knows how to solve this out I would for sure appreciate your help.

Thanks.
Regards,
EuroStud

Hi EuroStud,

Try using the report's _onOpen() event so that when you open your reports your code will execute automatically. Also you can use a breakpoints on your code to see what is actually happening during runtime.

As for displaying the value of the code on the report you will need to set the Control Source of the unbound text box using the vb code in the _onOpen event.

Here is the way I have done it:

Private Sub Report_Open(Cancel As Integer)
'code that runs when the form is opened

On Error GoTo errorHandler

        text1.ControlSource = addLine(strPresent) 
    
endSub:
    Exit Sub
    
errorHandler:
    'code to handle any errors that occur
    Resume endSub
   
End Sub

In the code above I set the control source of my unbound textbox to the value of the addLine() function I created. Make sure that the value of your vb code is in the correct format for the control source property e.g. if you want to count the number of fields in a table and display it when the report open your code will need to look similar to this:

Private Sub Report_Open(Cancel As Integer)
'code that runs when the form is opened

On Error GoTo errorHandler

        text1.ControlSource = "=DCount(" & Chr(34) & "[ID]" & Chr(34) & "," &   Chr(34) & "tableName" & Chr(34) & ")" 
    
endSub:
    Exit Sub
    
errorHandler:
    'code to handle any errors that occur
    Resume endSub
   
End Sub

so when the report is open the Control Source property of text1 is set to =DCount("[ID]", "tableName").

Useful ASCII Characters

  • Chr(34) - "
  • Chr(38) - &
  • Chr(39) - '

If you need any more ASCII characters click here

I hope this helps. If not, please let me know.

tmcpolin

Thanks tmcpolin. That was very informative and it encouraged me to try my luck again.

I adopted your code with slight changes to fit a trial model and then on the real one. Now what's happening is that at first it reamins stable for a number of report generation trials and then strangely enough the _open event won't be called anymore, even though I quit and launch again the db.

Just to make things clear both my Win Vista OS and Office Vista are genuine and I've made all updates available by microsoft.

I just can't understand why this is happening :/

Any ideas are much appreciated.

Thanks.
EuroStud

EuroStud,

I haven't come across this problem before as any time I open a report the _onOpen event is working fine. I don't think the version of the OS or Office would have an effect on it as I am currently running Office 2007 in XP.

In your post you said:

Now what's happening is that at first it reamins stable for a number of report generation trials and then strangely enough the _open event won't be called anymore, even though I quit and launch again the db.

Try using breakpoints so that you can see where your code is failing so that you can have a better understanding of what is happening. Are you printing multiple reports at once? Also can you post your code so I can see for myself what is happening?

tmcpolin

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.