looping through records

  • Thread starter Thread starter Bre-x
  • Start date Start date
B

Bre-x

How can I modify this function so that it will show what the record is?

Function scan_sku() As String
Dim rs As DAO.Recordset

Dim strSQL
strSQL = "SELECT BKAR_INVL_PCODE FROM BKARINVL"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

rs.MoveFirst

Do Until rs.EOF
MsgBox rs.BKAR_INVL_PCODE


rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Function
 
How can I modify this function so that it will show what the record is?

Function scan_sku() As String
Dim rs As DAO.Recordset

Dim strSQL
strSQL = "SELECT BKAR_INVL_PCODE FROM BKARINVL"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

rs.MoveFirst

Do Until rs.EOF
MsgBox rs.BKAR_INVL_PCODE

rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Function
Click to expand...

You need to use the bang (!) not the dot.

Do Until rs.EOF
MsgBox rs!BKAR_INVL_PCODE


rs.MoveNext
Loop
 
Function scan_sku() As String
Dim rs As DAO.Recordset
Dim strSQL

strSQL = "SELECT DISTINCT BKAR_INVL_PCODE FROM BKARINVL"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

rs.MoveFirst
Do Until rs.EOF
MsgBox rs.Fields(0).Value
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

End Function
 
How can I modify this function so that it will show what the record is?

Function scan_sku() As String
Dim rs As DAO.Recordset

Dim strSQL
strSQL = "SELECT BKAR_INVL_PCODE FROM BKARINVL"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

rs.MoveFirst

Do Until rs.EOF
MsgBox rs.BKAR_INVL_PCODE


rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Function
Click to expand...

Please explain two things:

1. What do you mean by "show what the record is"?
2. What's the purpose of this exercise? Surely you don't want the user to have
to respond to thousands of msgbox prompts if BKARINVL has thousands of
records? What are you trying to *accomplish* (rather than how you're trying to
accomplish it)?
 
First, thank you all for your responce

I was trying to get the SKU ID of each Sales Order Line (BKARINVL)


Function get_data_01(SO As Double)
Dim sql As String

'WORKORD
sql = "SELECT * FROM WORKORD WHERE MTWO_WIP_WOPRE = " & SO
CurrentDb.QueryDefs("DBA").sql = sql
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT DBA.* INTO WORKORD FROM DBA;"
DoCmd.SetWarnings True

'BKARINV
sql = "SELECT * FROM BKARINV WHERE BKAR_INV_NUM = " & SO
CurrentDb.QueryDefs("DBA").sql = sql
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT DBA.* INTO BKARINV FROM DBA;"
DoCmd.SetWarnings True

'BKARINVL
sql = "SELECT * FROM BKARINVL where BKAR_INVL_INVNM = " & SO & " AND
LENGTH(BKAR_INVL_PCODE) <> 0"
CurrentDb.QueryDefs("DBA").sql = sql
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT DBA.* INTO BKARINVL FROM DBA;"
DoCmd.SetWarnings True

'ROUTING
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM ROUTING;"
DoCmd.SetWarnings True
do_ROUTING

'BKICMSTR
CurrentDb.QueryDefs("DBA").sql = sql
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM BKICMSTR;"
DoCmd.SetWarnings True
do_BKICMSTR

'MTICMSTR
CurrentDb.QueryDefs("DBA").sql = sql
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM MTICMSTR;"
DoCmd.SetWarnings True
do_MTICMSTR


End Function


Function do_ROUTING()
Dim rs As DAO.Recordset
Dim strSQL
Dim sku As String

strSQL = "SELECT DISTINCT BKAR_INVL_PCODE FROM BKARINVL"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

rs.MoveFirst
Do Until rs.EOF
sku = rs.Fields(0).Value
sql = "select * from ROUTING WHERE MTRO_CODE = '" & sku & "'"
CurrentDb.QueryDefs("DBA").sql = sql
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT DBA.* INTO TEMP FROM DBA;"
DoCmd.RunSQL "INSERT INTO ROUTING SELECT TEMP.* FROM TEMP;"
DoCmd.SetWarnings True
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Function

Function do_BKICMSTR()
Dim rs As DAO.Recordset
Dim strSQL
Dim sku As String

strSQL = "SELECT DISTINCT BKAR_INVL_PCODE FROM BKARINVL"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

rs.MoveFirst
Do Until rs.EOF
sku = rs.Fields(0).Value
sql = "select * from BKICMSTR WHERE BKIC_PROD_CODE = '" & sku & "'"
CurrentDb.QueryDefs("DBA").sql = sql
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT DBA.* INTO TEMP FROM DBA;"
DoCmd.RunSQL "INSERT INTO BKICMSTR SELECT TEMP.* FROM TEMP;"
DoCmd.SetWarnings True
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Function

Function do_MTICMSTR()
Dim rs As DAO.Recordset
Dim strSQL
Dim sku As String

strSQL = "SELECT DISTINCT BKAR_INVL_PCODE FROM BKARINVL"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

rs.MoveFirst
Do Until rs.EOF
sku = rs.Fields(0).Value
sql = "SELECT * FROM MTICMSTR WHERE MTIC_PROD_CODE = '" & sku & "'"
CurrentDb.QueryDefs("DBA").sql = sql
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT DBA.* INTO TEMP FROM DBA;"
DoCmd.RunSQL "INSERT INTO MTICMSTR SELECT TEMP.* FROM TEMP;"
DoCmd.SetWarnings True
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Function
 
Back
Top