help me understand this code.

[RompStar]

I am in the learning stages on MS Access and VBA for access, know more
about some parts then others. But it dones't look that bad, the VBA,
because I am used to VBA for Excel, but I am not sure about this code,
esspecially the part of what is done after the tables are selected....

Would really appreciate it :- ) if someone would kindly help me out,
thanks ahead of time.
Thank you!!!
Option Compare Database
Option Explicit

Private Sub Command0_Click()
Dim SQL As String

On Error GoTo Err_Duplicate
ADOCurrentProjectConnect

SQL = ""
SQL = "SELECT a.field5 as rectime1, b.field8 as rectime2 " & _
"FROM receiving a, dockside b " & _
"WHERE a.field2 = b.field4 AND a.field5 IS NULL;"
rst2.Open SQL, cnn2, adOpenStatic, adLockOptimistic, adCmdText
If rst2.EOF = False Then
rst2.MoveFirst
End If
Do While Not (rst2.EOF)
rst2!rectime1 = rst2!rectime2
rst2.Update
rst2.MoveNext
Loop
MsgBox "Completed. Export MS_Market and Receiving Tables as *.txt files
to Desktop."
rst2.Close
CloseConnection
Exit Sub

Err_Duplicate:
If Err.Number = errorNumDup Or Err.Number = errorNumRange Or Err.Number
= errorType Then
Resume Next
End If
Err.Raise Err.Number
End Sub

[Jeff L]

Create an SQL Statement

SQL = ""
SQL = "SELECT a.field5 as rectime1, b.field8 as rectime2 " & _
"FROM receiving a, dockside b " & _
"WHERE a.field2 = b.field4 AND a.field5 IS NULL;"
Assign rst2 to the records returned by the SQL Statement
rst2.Open SQL, cnn2, adOpenStatic, adLockOptimistic, adCmdText
Check to see if rst2 has any records returned
If rst2.EOF = False Then
rst2.MoveFirst
End If
Loop through the records in rst2 until you get to the end
Do While Not (rst2.EOF)
Set the field rectime1 in rst2 = rectime2 in rst2 rst2!rectime1 = rst2!rectime2
Submit the change rst2.Update
Get the next record in rst2 rst2.MoveNext
Go back to Do while Statement until the last record in rst2 is
processed Loop
Message to user that the process is complete MsgBox "Completed. Export MS_Market and Receiving Tables as *.txt files
to Desktop."
Close the recordset rst2 rst2.Close
CloseConnection
Exit Sub
Code will jump here if there is an error.
Err_Duplicate:
If Err.Number = errorNumDup Or Err.Number = errorNumRange Or Err.Number
= errorType Then
Resume Next
End If
Err.Raise Err.Number
End Sub

[Rick Wannall]

A recordset is created based on records have field2 in one table equal to
field 4 in another table and field5 in one table empty.

the tables are receiving and dockside, aliased to a and b respectively

On every row selected into the recordset, field5 in table a is set equal to
the value in field8 in table b. The fields have aliases as well, so that
the names make some sense.

When it's all over, there is a messagebox to tell someone to export some
tables.

[RompStar]

Thanks!!! let me think about this :- )

Ray
Rick Wannall wrote:

A recordset is created based on records have field2 in one table equal to
field 4 in another table and field5 in one table empty.

the tables are receiving and dockside, aliased to a and b respectively

On every row selected into the recordset, field5 in table a is set equal to
the value in field8 in table b. The fields have aliases as well, so that
the names make some sense.

When it's all over, there is a messagebox to tell someone to export some
tables.

[John Mishefske]

RompStar wrote:

I am in the learning stages on MS Access and VBA for access, know more
about some parts then others. But it dones't look that bad, the VBA,
because I am used to VBA for Excel, but I am not sure about this code,
esspecially the part of what is done after the tables are selected....

Would really appreciate it :- ) if someone would kindly help me out,
thanks ahead of time.
Thank you!!!
Option Compare Database
Option Explicit

Private Sub Command0_Click()
Dim SQL As String

On Error GoTo Err_Duplicate
ADOCurrentProjectConnect

SQL = ""
SQL = "SELECT a.field5 as rectime1, b.field8 as rectime2 " & _
"FROM receiving a, dockside b " & _
"WHERE a.field2 = b.field4 AND a.field5 IS NULL;"
rst2.Open SQL, cnn2, adOpenStatic, adLockOptimistic, adCmdText
If rst2.EOF = False Then
rst2.MoveFirst
End If
Do While Not (rst2.EOF)
rst2!rectime1 = rst2!rectime2
rst2.Update
rst2.MoveNext
Loop
MsgBox "Completed. Export MS_Market and Receiving Tables as *.txt files
to Desktop."
rst2.Close
CloseConnection
Exit Sub

Err_Duplicate:
If Err.Number = errorNumDup Or Err.Number = errorNumRange Or Err.Number
= errorType Then
Resume Next
End If
Err.Raise Err.Number
End Sub

There is some room for improvement here. It would appear that a single SQL Update
statement might suffice instead of utilizing a recordset and 10-20 lines of code. Faster
too. Something like:
sSQL = "<copy your working update query SQL into this string>"
CurrentProject.Connection.Execute sSQL, , adExecuteNoRecords

There is no need to initialize the SQL variable to an empty string. (SQL = "")

rst2 has a wider scope than necessary. It should probably be defined within the proc.

The error handler should close the recordset before raising an error. (Err.Raise ...)

There may be some unknown (to me) reason the coder did it this way but I can't imagine
why. So when analyzing the proc it is OK to question whether the original coder did a good
or bad job...

--
'---------------
'John Mishefske
'---------------

[RompStar]

Yes you are right, I am just trying to understand the code, I got a
little hunged up on that SQL statement, but now I understand it, kinda
weird :- )

Am I thinking right for the SQL part ?

SQL = ""
SQL = "SELECT a.field5 as rectime1, b.field8 as rectime2 " & _
"FROM receiving a, dockside b " & _
"WHERE a.field2 = b.field4 AND a.field5 IS NULL;"

select field5 from receiving table and alias as rectime1
select field8 from dockside table and alsias as rectime2

then using the WHERE clause,

for every record WHERE field 2 (receivng table ) = fiend4 in (dockside
table)
AND
field 5 in (receiving table) = Null

If that WHERE condition is True, then grab every record row for each
field in the SELECT statement and return it. Am I thinking right ? if
not please correct using simple language, I am still learning.

Thank you.
John Mishefske wrote:

RompStar wrote:

I am in the learning stages on MS Access and VBA for access, know more
about some parts then others. But it dones't look that bad, the VBA,
because I am used to VBA for Excel, but I am not sure about this code,
esspecially the part of what is done after the tables are selected....

Would really appreciate it :- ) if someone would kindly help me out,
thanks ahead of time.
Thank you!!!
Option Compare Database
Option Explicit

Private Sub Command0_Click()
Dim SQL As String

On Error GoTo Err_Duplicate
ADOCurrentProjectConnect

SQL = ""
SQL = "SELECT a.field5 as rectime1, b.field8 as rectime2 " & _
"FROM receiving a, dockside b " & _
"WHERE a.field2 = b.field4 AND a.field5 IS NULL;"
rst2.Open SQL, cnn2, adOpenStatic, adLockOptimistic, adCmdText
If rst2.EOF = False Then
rst2.MoveFirst
End If
Do While Not (rst2.EOF)
rst2!rectime1 = rst2!rectime2
rst2.Update
rst2.MoveNext
Loop
MsgBox "Completed. Export MS_Market and Receiving Tables as *.txt files
to Desktop."
rst2.Close
CloseConnection
Exit Sub

Err_Duplicate:
If Err.Number = errorNumDup Or Err.Number = errorNumRange Or Err.Number
= errorType Then
Resume Next
End If
Err.Raise Err.Number
End Sub

There is some room for improvement here. It would appear that a single SQL Update
statement might suffice instead of utilizing a recordset and 10-20 lines of code. Faster
too. Something like:
sSQL = "<copy your working update query SQL into this string>"
CurrentProject.Connection.Execute sSQL, , adExecuteNoRecords

There is no need to initialize the SQL variable to an empty string. (SQL = "")

rst2 has a wider scope than necessary. It should probably be defined within the proc.

The error handler should close the recordset before raising an error. (Err.Raise ...)

There may be some unknown (to me) reason the coder did it this way but I can't imagine
why. So when analyzing the proc it is OK to question whether the original coder did a good
or bad job...

--
'---------------
'John Mishefske
'---------------

[Rick Wannall]

That's it.

[RompStar]

Ok, now this stuff is starting to make sense, lol..

Thanks!!!
Rick Wannall wrote:

That's it.

[Larry Linson]

"RompStar" <rm*******@comcast.net> wrote

Ok, now this stuff is starting to make sense, lol..

When it really seems to make sense is the time to run away, screaming, into
the night. <GRIN>