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
ADOCurrentProje ctConnect

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, adLockOptimisti c, 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]

Re: help me understand this code.

Create an SQL Statement[color=blue]
> 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;"[/color]

Assign rst2 to the records returned by the SQL Statement
[color=blue]
> rst2.Open SQL, cnn2, adOpenStatic, adLockOptimisti c, adCmdText[/color]

Check to see if rst2 has any records returned
[color=blue]
> If rst2.EOF = False Then
> rst2.MoveFirst
> End If[/color]

Loop through the records in rst2 until you get to the end
[color=blue]
> Do While Not (rst2.EOF)[/color]

Set the field rectime1 in rst2 = rectime2 in rst2[color=blue]
> rst2!rectime1 = rst2!rectime2[/color]

Submit the change[color=blue]
> rst2.Update[/color]

Get the next record in rst2[color=blue]
> rst2.MoveNext[/color]

Go back to Do while Statement until the last record in rst2 is
processed[color=blue]
> Loop[/color]

Message to user that the process is complete[color=blue]
> MsgBox "Completed. Export MS_Market and Receiving Tables as *.txt files
> to Desktop."[/color]

Close the recordset rst2[color=blue]
> rst2.Close
> CloseConnection
> Exit Sub[/color]

Code will jump here if there is an error.
[color=blue]
> 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[/color]

[Rick Wannall]

Re: help me understand this code.



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]

Re: help me understand this code.

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

Ray


Rick Wannall wrote:[color=blue]
> 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.[/color]

[John Mishefske]

Re: help me understand this code.

RompStar wrote:[color=blue]
> 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
> ADOCurrentProje ctConnect
>
> 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, adLockOptimisti c, 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
>[/color]

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.Exec ute sSQL, , adExecuteNoReco rds

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]

Re: help me understand this code.

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:[color=blue]
> RompStar wrote:[color=green]
> > 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
> > ADOCurrentProje ctConnect
> >
> > 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, adLockOptimisti c, 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
> >[/color]
>
> 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.Exec ute sSQL, , adExecuteNoReco rds
>
> 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
> '---------------[/color]

[Rick Wannall]

Re: help me understand this code.

That's it.

[RompStar]

Re: help me understand this code.

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

Thanks!!!


Rick Wannall wrote:[color=blue]
> That's it.[/color]

[Larry Linson]

Re: help me understand this code.

"RompStar" <rmiecznik@comc ast.net> wrote
[color=blue]
> Ok, now this stuff is starting to make sense, lol..[/color]


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