Loop Question 1

[Ammodog]

I have a table TblMsgBank with Login Id's. With this code how can I execute it for All the records in a table. It is a net send function and I want to be able to send out a message to all my users. Thank You

Shell ("net send " & User & " " & Message & "&quot







Christopher Abney
"There is no limit to the good you can do if you dont care who gets the credit"
[Afro]

 

[SeeJane]

Hi -

A for...next loop sould do the trick. I'd suggest fooling around a bit with loops before using this to reach all users, but here's the general idea...

This does assume your send command is OK...

Good luck - CJ

'---------------------------------------------------
Function Send

Dim rst as DAO.Recordset
Dim i as integer

'create a recordset from your table
Set rst = CurrentDb.OpenRecordset("tblMsgBank&quot

'create a counter for the loop (i)
'the index starts at 0 for the 1st record
'Start the loop
For i = 0 to rst.Recordcount - 1
Shell ("net send " & User & " " & Message & "&quot
rst.MoveNext
Next i

rst.Close
set rst = Nothing

End Function

 

[Ammodog]

This seems it will work I carried over the Net Send from a form where I can do this individually. The only problem I have is How do I get it to populat the Net Send with the LoginID and The Message from the Table? Like this


Shell ("net send " & LoginID & " " & Message & "&quot

Christopher Abney
"There is no limit to the good you can do if you dont care who gets the credit"
[Afro]

 

[GKProgrammer]

Hi AmmonDog,

Be sure to include that in your loop, something like this (I am simply adding on to the above code provided by SeeJane

Function Send

Dim rst as DAO.Recordset
Dim i as integer

'create a recordset from your table
Set rst = CurrentDb.OpenRecordset("tblMsgBank&quot

'create a counter for the loop (i)
'the index starts at 0 for the 1st record
'Start the loop
With rst
For i = 0 to .Recordcount - 1
Shell ("net send " & !LoginID & " " & !Message & "&quot
.MoveNext
Next i
End With
rst.Close
set rst = Nothing

End Function


Regards,
gkprogrammer

 

[Ammodog]

This Seems To be working! But for Some Reason it is only sending out the first three Messages and then it quits. Any Idea's?

Christopher Abney
"There is no limit to the good you can do if you dont care who gets the credit"
[Afro]

 

[GKProgrammer]

Yes I believe I see where the problem is coming from, try this:

Function Send

Dim rst as DAO.Recordset
Dim i as integer, NumRec as integer modified line

'create a recordset from your table
Set rst = CurrentDb.OpenRecordset("tblMsgBank&quot

'create a counter for the loop (i)
'the index starts at 0 for the 1st record
'Start the loop
With rst
.Movelast new line
NumRec = .Recordcount new line
.Movefirst new line
For i = 0 to NumRec - 1 modified line
Shell ("net send " & !LoginID & " " & !Message & "&quot
.MoveNext
Next i
End With
rst.Close
set rst = Nothing

End Function


The RecordCount property doesn't indicate how many records are contained in a dynaset-, snapshot-, or forward-only–type Recordset object until all records have been accessed. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast method on the Recordset object. Let me know if this helps.

Regards,
gkprogrammer

 

[Ammodog]

I tried this but then it gave me a Complile error: Wrong number of aurguments..... This is what I tried:


Function Send()
Dim rst As DAO.Recordset
Dim i As Integer, NumRec As Integer
'create a recordset from your table
Set rst = CurrentDb.OpenRecordset("tblMsgBank&quot

'create a counter for the loop (i)
'the index starts at 0 for the 1st record
'Start the loop
With rst
.MoveLast New Line
NumRec = .RecordCount
.MoveFirst New Line
For i = 0 To NumRec - 1
Shell ("net send " & !LoginID & " " & !Message & "&quot
.MoveNext
Next i
End With
rst.Close
Set rst = Nothing

End Function


Christopher Abney
"There is no limit to the good you can do if you dont care who gets the credit"
[Afro]

 

[GKProgrammer]

In the .Movelast and .Movefirst lines there you have left the new line flag that I used only to indicate that the line had changed from the previous post, delete the New Line text and the code should work properly.

Regards,
gkprogrammer

 

[Ammodog]

This seemed to work it is creating the MSDOS Prompts on the task bar but it still only sends 2 or 3 before it seems like it hangs up?

Christopher Abney
"There is no limit to the good you can do if you dont care who gets the credit"
[Afro]

 

[GKProgrammer]

Try placing a breakpoint on the .Movefirst line and check what value is being assigned to the NumRec variable and post your result.

Regards,
gkprogrammer

 

[Ammodog]

I set the break point but how do I check that value?


Christopher Abney
"There is no limit to the good you can do if you dont care who gets the credit"
[Afro]

 

[GKProgrammer]

You can simply place your cursor overtop of the variable and it should display. If not highlight the variable and click Shift + F9. Also try placing this method DoEvents after the Shell command line.

Regards,
gkprogrammer