Help with while ...loop Statements

B

Bre-x

Hi,

I have a table where there's a "R" field (Yes/No Type)
For every record where R equals to -1 I have a function that do "something"

I am able to accomplish this by creating a query with a function on it

But couldn't I accomplish this with a while..loop or for....each statement?

How to I read my table and go into each record and read the "R" Field?


Thank you All

Bre-x


'Here is the Query
SELECT SOL.SONUM, SOL.L, SOL.MS, SOL.R, SOL.RQTY,
doSHP([SONUM],[L],[R],[RQTY]) AS DoSHPFunction INTO Temp
FROM SOL;

'Here is the Function
Function doSHP(SONUM, SOLINE, R, RQTY)
If R = -1 Then
If RQTY = 0 Then
MsgBox "Sales Order: " & SONUM & " " & Chr(10) & "Line: " & SOLINE &
Chr(10) & "This line will not be process"
Exit Function
End If

RText = "Sales Order: " & SONUM & " " & Chr(10) & "Line: " & SOLINE &
Chr(10) & "Ready to Process?"
Responce = MsgBox(RText, vbInformation + vbYesNo, cianame())
If Responce = vbYes Then
sqlString = "INSERT INTO PS_HST ( SONUM, SOLINE, QTY, SHIPPER,
SHIPDATE, SHIPTIME ) VALUES (" & SONUM & "," & SOLINE & "," & RQTY & ", '" &
dhGetUserName() & "', CURDATE ( ), CURTIME ( ))"
INSERT_SHP (sqlString)
sqlString = "SELECT COUNT(SONUM) AS SO FROM PS_SHP WHERE SONUM = " &
SONUM & " AND SOLINE = " & SOLINE
CurrentDb.QueryDefs("Server_GETTSHP").SQL = sqlString
If DLookup("[SO]", "Server_GETTSHP") = 0 Then
sqlString = "INSERT INTO PS_SHP ( SONUM, SOLINE, TSHP ) VALUES
(" & SONUM & "," & SOLINE & "," & RQTY & " )"
INSERT_SHP (sqlString)
Else
sqlString = "SELECT TSHP FROM PS_SHP WHERE SONUM = " & SONUM & "
AND SOLINE = " & SOLINE
CurrentDb.QueryDefs("Server_GETTSHP").SQL = sqlString
NQ = RQTY + DLookup("[TSHP]", "Server_GETTSHP")
sqlString = "UPDATE PS_SHP SET TSHP = " & NQ & " WHERE SONUM = "
& SONUM & " AND SOLINE = " & SOLINE
INSERT_SHP (sqlString)
End If
Else
End If
Else
End If
End Function
 
B

Bre-x

Graham, thanks for answering my question

Yes, tt does not return a value, doesnt have to. But it accomplish what I
need.

It's partial shipment DB.

The user enter a Sales Order Number
Sales Order is displayed.
for each Sales Order Line that is marked (R Field)
my function send a insert and update statement to a Pervasive SQL Server.

I am been able to accomplish it by running a Make Table Query ( this query
has my function).

Believe it works fine.

But the only way I am able to find out what Sales Orders been marked to be
ship is by running this query.

I am pretty sure there is a way to read the Sales Order Line Table go into
each record, check the R Field
and every time it is marked (-1) send my Insert and Update sql scripts to
the sql server



Regards,

Bre-x




----- Original Message -----
From: "Graham R Seach" <[email protected]>
Newsgroups: microsoft.public.access.gettingstarted
Sent: Monday, November 29, 2004 6:39 PM
Subject: Re: Help with while ...loop Statements

doSHP() doesn't return a value, so it's use in the SELECT INTO query doesn't
quite do what you think it does.

What are you really trying to do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Bre-x said:
Hi,

I have a table where there's a "R" field (Yes/No Type)
For every record where R equals to -1 I have a function that do
"something"

I am able to accomplish this by creating a query with a function on it

But couldn't I accomplish this with a while..loop or for....each
statement?

How to I read my table and go into each record and read the "R" Field?


Thank you All

Bre-x


'Here is the Query
SELECT SOL.SONUM, SOL.L, SOL.MS, SOL.R, SOL.RQTY,
doSHP([SONUM],[L],[R],[RQTY]) AS DoSHPFunction INTO Temp
FROM SOL;

'Here is the Function
Function doSHP(SONUM, SOLINE, R, RQTY)
If R = -1 Then
If RQTY = 0 Then
MsgBox "Sales Order: " & SONUM & " " & Chr(10) & "Line: " & SOLINE &
Chr(10) & "This line will not be process"
Exit Function
End If

RText = "Sales Order: " & SONUM & " " & Chr(10) & "Line: " & SOLINE &
Chr(10) & "Ready to Process?"
Responce = MsgBox(RText, vbInformation + vbYesNo, cianame())
If Responce = vbYes Then
sqlString = "INSERT INTO PS_HST ( SONUM, SOLINE, QTY, SHIPPER,
SHIPDATE, SHIPTIME ) VALUES (" & SONUM & "," & SOLINE & "," & RQTY & ", '"
&
dhGetUserName() & "', CURDATE ( ), CURTIME ( ))"
INSERT_SHP (sqlString)
sqlString = "SELECT COUNT(SONUM) AS SO FROM PS_SHP WHERE SONUM = "
&
SONUM & " AND SOLINE = " & SOLINE
CurrentDb.QueryDefs("Server_GETTSHP").SQL = sqlString
If DLookup("[SO]", "Server_GETTSHP") = 0 Then
sqlString = "INSERT INTO PS_SHP ( SONUM, SOLINE, TSHP ) VALUES
(" & SONUM & "," & SOLINE & "," & RQTY & " )"
INSERT_SHP (sqlString)
Else
sqlString = "SELECT TSHP FROM PS_SHP WHERE SONUM = " & SONUM &
"
AND SOLINE = " & SOLINE
CurrentDb.QueryDefs("Server_GETTSHP").SQL = sqlString
NQ = RQTY + DLookup("[TSHP]", "Server_GETTSHP")
sqlString = "UPDATE PS_SHP SET TSHP = " & NQ & " WHERE SONUM =
"
& SONUM & " AND SOLINE = " & SOLINE
INSERT_SHP (sqlString)
End If
Else
End If
Else
End If
End Function
Click to expand...
Click to expand...
 
G

Graham R Seach

OK, but I find your code quite confusing. What I want to do is understand
exactly what you're trying to accomplish, because although I understand you
want to make your existing code work, we might be able to find a far simpler
way of doing it. So I need you to give me the business rules for this
activity - what do the business rules say is supposed to happen (in detail)
after the user enters a sales order number?

Also, what is INSERT_SHP (sqlString), and what does it do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Bre-x said:
Graham, thanks for answering my question

Yes, tt does not return a value, doesnt have to. But it accomplish what I
need.

It's partial shipment DB.

The user enter a Sales Order Number
Sales Order is displayed.
for each Sales Order Line that is marked (R Field)
my function send a insert and update statement to a Pervasive SQL Server.

I am been able to accomplish it by running a Make Table Query ( this query
has my function).

Believe it works fine.

But the only way I am able to find out what Sales Orders been marked to be
ship is by running this query.

I am pretty sure there is a way to read the Sales Order Line Table go into
each record, check the R Field
and every time it is marked (-1) send my Insert and Update sql scripts to
the sql server



Regards,

Bre-x




----- Original Message -----
From: "Graham R Seach" <[email protected]>
Newsgroups: microsoft.public.access.gettingstarted
Sent: Monday, November 29, 2004 6:39 PM
Subject: Re: Help with while ...loop Statements

doSHP() doesn't return a value, so it's use in the SELECT INTO query doesn't
quite do what you think it does.

What are you really trying to do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Bre-x said:
Hi,

I have a table where there's a "R" field (Yes/No Type)
For every record where R equals to -1 I have a function that do
"something"

I am able to accomplish this by creating a query with a function on it

But couldn't I accomplish this with a while..loop or for....each
statement?

How to I read my table and go into each record and read the "R" Field?


Thank you All

Bre-x


'Here is the Query
SELECT SOL.SONUM, SOL.L, SOL.MS, SOL.R, SOL.RQTY,
doSHP([SONUM],[L],[R],[RQTY]) AS DoSHPFunction INTO Temp
FROM SOL;

'Here is the Function
Function doSHP(SONUM, SOLINE, R, RQTY)
If R = -1 Then
If RQTY = 0 Then
MsgBox "Sales Order: " & SONUM & " " & Chr(10) & "Line: " &
SOLINE &
Chr(10) & "This line will not be process"
Exit Function
End If

RText = "Sales Order: " & SONUM & " " & Chr(10) & "Line: " & SOLINE
&
Chr(10) & "Ready to Process?"
Responce = MsgBox(RText, vbInformation + vbYesNo, cianame())
If Responce = vbYes Then
sqlString = "INSERT INTO PS_HST ( SONUM, SOLINE, QTY, SHIPPER,
SHIPDATE, SHIPTIME ) VALUES (" & SONUM & "," & SOLINE & "," & RQTY & ", '"
&
dhGetUserName() & "', CURDATE ( ), CURTIME ( ))"
INSERT_SHP (sqlString)
sqlString = "SELECT COUNT(SONUM) AS SO FROM PS_SHP WHERE SONUM = "
&
SONUM & " AND SOLINE = " & SOLINE
CurrentDb.QueryDefs("Server_GETTSHP").SQL = sqlString
If DLookup("[SO]", "Server_GETTSHP") = 0 Then
sqlString = "INSERT INTO PS_SHP ( SONUM, SOLINE, TSHP ) VALUES
(" & SONUM & "," & SOLINE & "," & RQTY & " )"
INSERT_SHP (sqlString)
Else
sqlString = "SELECT TSHP FROM PS_SHP WHERE SONUM = " & SONUM &
"
AND SOLINE = " & SOLINE
CurrentDb.QueryDefs("Server_GETTSHP").SQL = sqlString
NQ = RQTY + DLookup("[TSHP]", "Server_GETTSHP")
sqlString = "UPDATE PS_SHP SET TSHP = " & NQ & " WHERE SONUM =
"
& SONUM & " AND SOLINE = " & SOLINE
INSERT_SHP (sqlString)
End If
Else
End If
Else
End If
End Function
Click to expand...
Click to expand...
Click to expand...
 
B

Bre-x

Thanks Grahm,
Here it is:

My form display a Sales Order Line information

SONumber, SOLine, Part, Qty, MarktoShip (Yes/No Type Field), QtyToShip

The user will mark (MarktoShip Field) all the Sales Order Lines that he/she
wish to ship, then enter a quantity on QtyToShip Field

When he/she is done, I would like to have a button that when click it will
call a function that will:

open the Sales Order Lines Table check each record, and for each record that
is marked (Marktoship Field) run an sql statement.
that will store each Sales Order Line information on a table on my Pervasive
Sql Server.

If you wish to see the DB it is on: http://www.lasonrisalatina.ca/PS.zip
it is 180K


Thank you
Also, what is INSERT_SHP (sqlString), and what does it do?
Click to expand...
send a insert statement to Pervasive SQL

sqlString = "INSERT INTO PS_HST ( SONUM, SOLINE, QTY, SHIPPER, SHIPDATE,
SHIPTIME ) VALUES (" & SONUM & "," & SOLINE & "," & RQTY & ", '" &
dhGetUserName() & "', CURDATE ( ), CURTIME ( ))"

Function INSERT_SHP(sqlString As String)
'Declare all necessary ADO objects
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
'Dim sqlString As String
'Open connection
cn.ConnectionString = "DSN=SAccess"
cn.Open
'Set up Stored Procedure for execution
cmd.ActiveConnection = cn
cmd.CommandText = sqlString
cmd.Execute
cn.Close
Set cmd = Nothing
Set cn = Nothing
End Function
 
G

Graham R Seach

OK, then we can simplify it., but I think you should link the SQL Server
table into Access.

Assuming PS_HST is linked into Access, although I haven't tested it, the
following function should insert ALL the records found in [Sales Order
Lines] that have Marktoship<>0, into PS_HST.

Function INSERT_SHP()
Dim sUname As String

sUname = dhGetUserName()
sqlString = "INSERT INTO PS_HST (SONUM, SOLINE, QTY, " & _
"SHIPPER, SHIPDATE, SHIPTIME) " & _
"SELECT SONUM, SOLINE, QTY, """ & sUname & """, " & _
"#" & Date & "#, #" & Time() & "#) " & _
"FROM [Sales Order Lines] WHERE Marktoship <> 0"

On Error Resume Next
DBEngine(0)(0).Execute strSQL, dbFailOnError

If Err <> 0 Then
MsgBox Err.Number & vbCrLf & Err.Description
End If
End Function

You only need to call this function once when you're finished marking all
the fields and entering the quantities.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
G

Graham R Seach

Ignore the other post. I forgot to declare a variable.

Function INSERT_SHP()
Dim sUname As String
Dim sqlString As String

sUname = dhGetUserName()
sqlString = "INSERT INTO PS_HST (SONUM, SOLINE, QTY, " & _
"SHIPPER, SHIPDATE, SHIPTIME) " & _
"SELECT SONUM, SOLINE, QTY, """ & sUname & """, " & _
"#" & Date & "#, #" & Time() & "#) " & _
"FROM [Sales Order Lines] WHERE Marktoship <> 0"

On Error Resume Next
DBEngine(0)(0).Execute sqlString, dbFailOnError

If Err <> 0 Then
MsgBox Err.Number & vbCrLf & Err.Description
End If
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
B

Bre-x

Thanks Grahm
The Pervasive SQL that we use lock tables, it does not quite lock a record.
That is way I use the Function INSERT_SHP

But I will modify my db and give it a try.

Thank you once Again

Regards,

Bre-x



Graham R Seach said:
Ignore the other post. I forgot to declare a variable.

Function INSERT_SHP()
Dim sUname As String
Dim sqlString As String

sUname = dhGetUserName()
sqlString = "INSERT INTO PS_HST (SONUM, SOLINE, QTY, " & _
"SHIPPER, SHIPDATE, SHIPTIME) " & _
"SELECT SONUM, SOLINE, QTY, """ & sUname & """, " & _
"#" & Date & "#, #" & Time() & "#) " & _
"FROM [Sales Order Lines] WHERE Marktoship <> 0"

On Error Resume Next
DBEngine(0)(0).Execute sqlString, dbFailOnError

If Err <> 0 Then
MsgBox Err.Number & vbCrLf & Err.Description
End If
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Bre-x said:
Thanks Grahm,
Here it is:

My form display a Sales Order Line information

SONumber, SOLine, Part, Qty, MarktoShip (Yes/No Type Field), QtyToShip

The user will mark (MarktoShip Field) all the Sales Order Lines that
he/she
wish to ship, then enter a quantity on QtyToShip Field

When he/she is done, I would like to have a button that when click it will
call a function that will:

open the Sales Order Lines Table check each record, and for each record
that
is marked (Marktoship Field) run an sql statement.
that will store each Sales Order Line information on a table on my
Pervasive
Sql Server.

If you wish to see the DB it is on: http://www.lasonrisalatina.ca/PS.zip
it is 180K


Thank you

send a insert statement to Pervasive SQL

sqlString = "INSERT INTO PS_HST ( SONUM, SOLINE, QTY, SHIPPER, SHIPDATE,
SHIPTIME ) VALUES (" & SONUM & "," & SOLINE & "," & RQTY & ", '" &
dhGetUserName() & "', CURDATE ( ), CURTIME ( ))"

Function INSERT_SHP(sqlString As String)
'Declare all necessary ADO objects
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
'Dim sqlString As String
'Open connection
cn.ConnectionString = "DSN=SAccess"
cn.Open
'Set up Stored Procedure for execution
cmd.ActiveConnection = cn
cmd.CommandText = sqlString
cmd.Execute
cn.Close
Set cmd = Nothing
Set cn = Nothing
End Function
Click to expand...
Click to expand...
 
G

Graham R Seach

Don't forget to make a backup first!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Bre-x said:
Thanks Grahm
The Pervasive SQL that we use lock tables, it does not quite lock a
record.
That is way I use the Function INSERT_SHP

But I will modify my db and give it a try.

Thank you once Again

Regards,

Bre-x



Graham R Seach said:
Ignore the other post. I forgot to declare a variable.

Function INSERT_SHP()
Dim sUname As String
Dim sqlString As String

sUname = dhGetUserName()
sqlString = "INSERT INTO PS_HST (SONUM, SOLINE, QTY, " & _
"SHIPPER, SHIPDATE, SHIPTIME) " & _
"SELECT SONUM, SOLINE, QTY, """ & sUname & """, " &
_
"#" & Date & "#, #" & Time() & "#) " & _
"FROM [Sales Order Lines] WHERE Marktoship <> 0"

On Error Resume Next
DBEngine(0)(0).Execute sqlString, dbFailOnError

If Err <> 0 Then
MsgBox Err.Number & vbCrLf & Err.Description
End If
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Bre-x said:
Thanks Grahm,
Here it is:

My form display a Sales Order Line information

SONumber, SOLine, Part, Qty, MarktoShip (Yes/No Type Field), QtyToShip

The user will mark (MarktoShip Field) all the Sales Order Lines that
he/she
wish to ship, then enter a quantity on QtyToShip Field

When he/she is done, I would like to have a button that when click it will
call a function that will:

open the Sales Order Lines Table check each record, and for each record
that
is marked (Marktoship Field) run an sql statement.
that will store each Sales Order Line information on a table on my
Pervasive
Sql Server.

If you wish to see the DB it is on: http://www.lasonrisalatina.ca/PS.zip
it is 180K


Thank you

Also, what is INSERT_SHP (sqlString), and what does it do?
send a insert statement to Pervasive SQL

sqlString = "INSERT INTO PS_HST ( SONUM, SOLINE, QTY, SHIPPER, SHIPDATE,
SHIPTIME ) VALUES (" & SONUM & "," & SOLINE & "," & RQTY & ", '" &
dhGetUserName() & "', CURDATE ( ), CURTIME ( ))"

Function INSERT_SHP(sqlString As String)
'Declare all necessary ADO objects
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
'Dim sqlString As String
'Open connection
cn.ConnectionString = "DSN=SAccess"
cn.Open
'Set up Stored Procedure for execution
cmd.ActiveConnection = cn
cmd.CommandText = sqlString
cmd.Execute
cn.Close
Set cmd = Nothing
Set cn = Nothing
End Function





OK, but I find your code quite confusing. What I want to do is understand
exactly what you're trying to accomplish, because although I
understand
you
want to make your existing code work, we might be able to find a far
simpler
way of doing it. So I need you to give me the business rules for this
activity - what do the business rules say is supposed to happen (in
detail)
after the user enters a sales order number?

Also, what is INSERT_SHP (sqlString), and what does it do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Click to expand...
Click to expand...
Click to expand...
 
Top