Passing a string to a public subrountine 3

[IanMWheeler]

Hi there

I am trying to develop a public sub routine to display messages to the users while long bits of code runs.
The idea is I that I declare the string of Text like "reviewing records..." etc in my private sub and pass it out to the public sub routine to display. I have used a sub routine as there is nothing to pass back to the private subroutine

I have tested the code without passing the message string ok, the form opens and returns (seen in debug.print) but now I can not pass the txt, I have googled but all refs seem to be on functions with the line
Call MsgFormSub(strTxt as string)
but when I try this I get error!

if I try - Call MsgFormSub(strTxt)I get a error for the wrong number of arguments!

I have a couple of real basic question also
1. How do I execute my private subroutine in Visual Basic window direct? To run my code I have to go back to access and press the button to trigger the code, this does not seem right.
2. Rather than declaring a rs and writting the txt message to a table there must be a better way of loading the txt message to a text box control direct, but how?

The code is as follows
from the private sub

Private Sub Command88_Click()
Dim strTxt As String
strTxt = "Test Send"
'Go to msgform sub
Debug.Print "goto msgform"
Call MsgFormSub(strTxt)
debug.Print "return msgform"

Public Routine
Public Sub MsgFormSub()

'This code should be passed the msgTxt from another rountine
'The msgTxt should be written to the table then displayed on the form to inform the user wht is going on while longer code runs

Dim stDocName As String
Dim strTxt As String
Dim dbmsg As DAO.Database
Dim msgrst As DAO.Recordset
Dim strmsgSql As String
Dim stLinkCriteria As String

Debug.Print "arrive at sub routine"
strTxt = "Sales Demand Table Written"
Debug.Print strTxt
stDocName = "SystemMessage"
strmsgSql = "SELECT Msgtxt.MsgTxt FROM Msgtxt"
Set dbmsg = CurrentDb()
Set msgrst = dbmsg.OpenRecordset(strmsgSql, dbOpenDynaset)
With msgrst
.Edit
!MsgTxt = strTxt
.Update
End With
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.RunCommand acCmdRefresh

' run a loop to display the message
i = 0
For i = 1 To 100000000
i = i + 1
Next


DoCmd.Close

Debug.Print "End of sub"

End Sub

Many thanks


A person new to crystal with very little training a part from Access based

Regards
Ian

 

[IanMWheeler]

Hey Andy thanks alot those are some pretty simple things to look at that I can see just by reading make things better / easier to understand

I will chew it over and let you know how I get on

by the way do I take it with a "Select Case" statement you can only set one variable at a time whereas I was trying to do 3 variable in the same case and to declare rst2 as a module variable how do I do that


many thanks again


A person new to crystal with very little training a part from Access based

Regards
Ian

 

[TheAceMan1]

IanMWheeler . . .

[blue]by the way do I take it with a "Select Case" statement you can only set one variable at a time ...[/blue]

No ... for any case that proves true, you do as much as you want. So you are fine with what you've done! However ... your [blue]Select Case[/blue] statements are logically wrong. Consider, you have

[blue]   For y = 1 To 8
      strStockFlag = ""
      
      Select Case AssignStock
         Case y = 1[/blue]

When you say [blue]Select Case [purple]AssignStock[/purple][/blue], you intend [blue]AssignStock[/blue] to be the reference for all following case statements. But since your case statements are of the type [blue]Case y = x[/blue] you completely ignore [blue]AssignStock[/blue]. Proper would be:

[blue]

  [COLOR=DarkGoldenRod][b]See Ya! .  .  .  .  .  .[/b][/color]

Be sure to see faq219-2884  [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886  [blue]Worthy Reading![/blue] [thumbsup2]

 

[IanMWheeler]

Hi AceMan1

Hi there, could you look at that post again as I think you only put my wrong code and not the correct version, many thanks

Also if we go back to the top of the post where you suggested the amend technique I have now tried it out, my comments are

The msgtxt in my mind was only for a moment in time, my amend of the msgtxt to the table will fill the system with a massive table (in one run of code I could get 1700 entries). The rest of the code is great and very useful. Is there an advantage to amending to the msgtxt table? If there is at the end the code could i delete all the records in that table to keep the table from growing too big or should I go back to overwriting the record?

The other point is just a visual one; the opening and shutting of the msgtxt form causes a flickering effect and that causes the users to complain! So I am thinking of declaring a second variable in the public function called intForm. If the value is set to 1 at the first call in the code, it will open the system message form, then for all other calls set it to 0 (leave the form open) and at the last call in the code set it to -1 (close the form), your comment or better suggestions welcome

Also, at the end of the code I have seen code that is

db = nothing

why do people do this there must be a reason?

Thanks for all the suggestions though they are very useful

A person new to crystal with very little training a part from Access based

Regards
Ian

 

[Andrzejek]

declare rst2 as a module variable how do I do that

You simply do:

Option Explicit[green]
'I hope you have the statement Option Explicit at the top 
'of all your Forms/Modules.  If not - you should have[/green]
[blue]Dim rst2 As DAO.Recordset[/blue]
...
Private Sub Command88_Click()
...
Set rst2 = db.OpenRecordset(strSql, dbOpenDynaset)
...
Call ABC

Call XYZ
[blue]
rst2.Close
Set rst2 = Nothing
[/blue]
End Sub

Your [tt]rst2[/tt] is now available in other procedures: in ABC and XYZ
BTW - you set the recordset to Nothing to release the object from memory. It is cleaning up after your code



Have fun.

---- Andy

 

[Andrzejek]

The msgtxt [...] will fill the system with a massive table (in one run of code I could get 1700 entries). [...]Is there an advantage to amending to the msgtxt table? If there is at the end the code could i delete all the records in that table to keep the table from growing too big or should I go back to overwriting the record?

Why would you want to even have this table? What's the point of writing into the table when you want to delete everything from it when you are done running a piece of the code? Looks like you have no way to see what's in that table anyway, and even if you could, going thru 1700 records and look for a problem is not something I would like to do on regular bases.

If you want to know if something goes wrong - have an error handler and catch the problems. And if you cannot fix them in the code - then write them into some error log file or a table so you can address the problem 'by hand'.

Have fun.

---- Andy

 

[IanMWheeler]

Hi Andy

What I am trying to achieve is a way of getting a message to the user to show that something is happening while the code is running for example "evaluating record 10 of 1700". Thus trying to stop them pressing buttons etc until the code is stopped

This was the best method I could think of at the time, if there is a slicker solution I am all for it

Thanks

A person new to crystal with very little training a part from Access based

Regards
Ian

 

[Andrzejek]

OK, that's the different story then....

I think it is OK and nice to show the user something is going on, like "evaluating record 10 of 1700" and the number (10) keeps getting high. You don't have to write it into the table in your database, just display it in a lable on the Form.

If you don't want user clicking on buttons, dis-able them for the duration of the proccess

Private Sub Command88_Click()

Command88.Enable = False
[green]
... do all your stuff here
[/green]
Command88.Enable = True

End Sub

If you want to dis-able whole bunch of controls on the Form, you can loop thru all of them. If you want to deal with only some of the controls, you can set the Tag property to something you can detect in your loop. Or you can dis-able just some type of controls (buttons, text boxes)

My choice - I would like to send a small electric shock to the mouse. Maybe in next Windows version.....

Have fun.

---- Andy

 

[IanMWheeler]

Hi Andy

Now we are talking - dis-able - sounds very good, that is going to get added!

I see what you mean about the label never thought of that, I will experiment, by the way to display the message I am using a for next loop to put in a pause, is that the best way?

My code is not looking anything like it started out much thanks, I am delivering the first look see report to the users tomorrow - now two weeks ahead of schedule - mainly down to the support and help of this forum, brilliant

Many thanks all


A person new to crystal with very little training a part from Access based

Regards
Ian

 

[Andrzejek]

to display the message I am using a for next loop [blue]to put in a pause[/blue], is that the best way?

I don't know what you mean by 'a pause'? Could you explain?

Also, you need to know if you use this approach to the message label in a loop (this is what I do):

For i = 1 To rst.RecordCount
    lblMsg.Caption = "Rec " & i & " of " & rst.RecordCount
    lblMsg.Refresh
    ....
    rst.MoveNext
Next i

This will slow your loop a little because of the display of the message to the user. I use .Refresh because otherwise the loop is so fast the label does not have a chance to display the caption and shows blank or black on the Form. Refresh fixes the problem, but slows the loop a little.

Have fun.

---- Andy