|
 |
access thread: Re: Just looking for a bit of advice...
Message #1 by "Darin Wray" <darin@r...> on Thu, 8 Nov 2001 10:58:54 -0600
|
|
Hey all,
I had posted a message to the vba_access forum about some help using ado
recordsets. I didn't get much response (which is fine, I learned alot while
doing this), but after a couple of days, I managed to scrap some code
together to make what I wanted work the way I wanted. In it's entirity it
copies a record in one table based on a sql statement in a combobox/lookup
box. Then the code deletes the record in another table based on the value
that was entered in the combo box. This is my first attempt at using ado
recordsets. If you guys can see where I might be able to be more efficient,
I'd appreciate the advice (I have little experience in programming, let
alone programming with ado)....except for the naming conventions and stuff
:) This is experimental code..hehe.
Thanks in advance,
Darin
Private Sub Text2_AfterUpdate()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This code is used to take an inventory record from the
'tblAccessoryInventory table and copy/paste it into the the
'tblAccessorySold Table.
'The last part of the code takes the value from text2
'and finds the control number in the recordset that is similar. Each
'control number is unique in the tblAccessoryInventory table.
'It finds that record, then deletes it.
'Finally we refresh everything, and set focus for data entry
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim mydb As ADODB.Connection
Dim MyTable As ADODB.Recordset
Dim rstblah As ADODB.Recordset
Dim strcnn As String
strcnn = "Provider=sqloledb;" & _
"Data Source=101.101.101.101;Initial
Catalog=cellular;UserId=sa;Password=; "
Set mydb = New ADODB.Connection
mydb.Open strcnn
Set MyTable = New ADODB.Recordset
MyTable.CursorType = adOpenKeyset
MyTable.LockType = adLockOptimistic
MyTable.Open "tblAccessorySold", mydb, , , adCmdTable
'This code used to update records in the tblAccessorySold table.
'It get's the value from the SQL statement that is behind the
'dropdown box. We will need to rename Text2 to something more
'meaningful later :) Among other things...
MyTable.AddNew
MyTable!ControlNumber = Me!Text2.Column(0)
MyTable!ShipmentReceived = Me!Text2.Column(1)
MyTable!SentOffice = Me!Text2.Column(2)
MyTable!OfficeID = Me!Text2.Column(3)
MyTable!InvoiceNumber = Me!Text2.Column(4)
MyTable!StockNumber = Me!Text2.Column(5)
MyTable!AccessoryCost = Me!Text2.Column(6)
MyTable!AccessoryItem = Me!Text2.Column(9)
MyTable!RetailPrice = Me!Text2.Column(10)
MyTable.Update
MyTable.Requery
MyTable.Close
'Now that the accessory has been "copied" to the sold file, we
'can now delete it from tblAccessoryInventory.
Set rstblah = New ADODB.Recordset
rstblah.CursorType = adOpenKeyset
rstblah.LockType = adLockOptimistic
rstblah.Open "tblAccessoryInventory", strcnn, , , adCmdTable
rstblah.Find ("ControlNumber = '" & CStr(Me.Text2) & "'")
rstblah.Delete 'Delete the record for who's ControlNumber we
just found
rstblah.Close 'Close Recordset
mydb.Close 'Close Connection
On Error Resume Next
DoCmd.SetWarnings False
DoCmd.ShowAllRecords 'this code works to get
'records to finally refresh
and update
'on our screen..without
going to the menu
'and selecting
records/refresh. Couldn't find a better way
Me.Repaint 'repaint form so we
can SetFocus
Forms!test!Child6.SetFocus 'must setfocus on subform
before on control
Forms!test!Child6!SalesPrice.SetFocus 'to setfocus on control for data
entry
End Sub
Message #2 by "Pardee, Roy E" <roy.e.pardee@l...> on Thu, 08 Nov 2001 09:31:33 -0800
|
|
If I understand the code correctly, you're pulling two entire SQL server
tables (tblAccessorySold and tblAccessoryInventory) over the wire to your
client and doing the add/deletes there. This causes quite a lot of network
traffic--most of it unnecessary I daresay. Compare how it would work if you
just sent SQL statements to the server to be executed there--you're sending
little more than the text of the SQL statement to the server. Using a
stored procedure would be even better--you could send just the identifier
for the item being sold, and have the sp use that to move the record from
one table to the other.
Also, consider wrapping these operations up in a transaction (see the
.BeginTrans method of the ado Connection object), so that you don't wind up
having your delete fail after your insert succeeds & start showing things as
both sold and in inventory.
HTH,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: Darin Wray [mailto:darin@r...]
Sent: Thursday, November 08, 2001 8:59 AM
To: Access
Subject: [access] Re: Just looking for a bit of advice...
Hey all,
I had posted a message to the vba_access forum about some help using ado
recordsets. I didn't get much response (which is fine, I learned alot while
doing this), but after a couple of days, I managed to scrap some code
together to make what I wanted work the way I wanted. In it's entirity it
copies a record in one table based on a sql statement in a combobox/lookup
box. Then the code deletes the record in another table based on the value
that was entered in the combo box. This is my first attempt at using ado
recordsets. If you guys can see where I might be able to be more efficient,
I'd appreciate the advice (I have little experience in programming, let
alone programming with ado)....except for the naming conventions and stuff
:) This is experimental code..hehe.
Thanks in advance,
Darin
Private Sub Text2_AfterUpdate()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This code is used to take an inventory record from the
'tblAccessoryInventory table and copy/paste it into the the
'tblAccessorySold Table.
'The last part of the code takes the value from text2
'and finds the control number in the recordset that is similar. Each
'control number is unique in the tblAccessoryInventory table.
'It finds that record, then deletes it.
'Finally we refresh everything, and set focus for data entry
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim mydb As ADODB.Connection
Dim MyTable As ADODB.Recordset
Dim rstblah As ADODB.Recordset
Dim strcnn As String
strcnn = "Provider=sqloledb;" & _
"Data Source=101.101.101.101;Initial
Catalog=cellular;UserId=sa;Password=; "
Set mydb = New ADODB.Connection
mydb.Open strcnn
Set MyTable = New ADODB.Recordset
MyTable.CursorType = adOpenKeyset
MyTable.LockType = adLockOptimistic
MyTable.Open "tblAccessorySold", mydb, , , adCmdTable
'This code used to update records in the tblAccessorySold table.
'It get's the value from the SQL statement that is behind the
'dropdown box. We will need to rename Text2 to something more
'meaningful later :) Among other things...
MyTable.AddNew
MyTable!ControlNumber = Me!Text2.Column(0)
MyTable!ShipmentReceived = Me!Text2.Column(1)
MyTable!SentOffice = Me!Text2.Column(2)
MyTable!OfficeID = Me!Text2.Column(3)
MyTable!InvoiceNumber = Me!Text2.Column(4)
MyTable!StockNumber = Me!Text2.Column(5)
MyTable!AccessoryCost = Me!Text2.Column(6)
MyTable!AccessoryItem = Me!Text2.Column(9)
MyTable!RetailPrice = Me!Text2.Column(10)
MyTable.Update
MyTable.Requery
MyTable.Close
'Now that the accessory has been "copied" to the sold file, we
'can now delete it from tblAccessoryInventory.
Set rstblah = New ADODB.Recordset
rstblah.CursorType = adOpenKeyset
rstblah.LockType = adLockOptimistic
rstblah.Open "tblAccessoryInventory", strcnn, , , adCmdTable
rstblah.Find ("ControlNumber = '" & CStr(Me.Text2) & "'")
rstblah.Delete 'Delete the record for who's ControlNumber we
just found
rstblah.Close 'Close Recordset
mydb.Close 'Close Connection
On Error Resume Next
DoCmd.SetWarnings False
DoCmd.ShowAllRecords 'this code works to get
'records to finally refresh
and update
'on our screen..without
going to the menu
'and selecting
records/refresh. Couldn't find a better way
Me.Repaint 'repaint form so we
can SetFocus
Forms!test!Child6.SetFocus 'must setfocus on subform
before on control
Forms!test!Child6!SalesPrice.SetFocus 'to setfocus on control for data
entry
End Sub
Message #3 by "Darin Wray" <darin@r...> on Thu, 8 Nov 2001 11:52:21 -0600
|
|
Thanks Roy,
This is the type of advice I was wanting to hear.
As far as sending an sql statement...does this mean that the ado recordset
is unnecessary? and, what's a simple example of how to call an sp on the
sql server from code?
Thanks,
Darin
----- Original Message -----
From: "Pardee, Roy E" <roy.e.pardee@l...>
To: "Access" <access@p...>
Sent: Thursday, November 08, 2001 11:31 AM
Subject: [access] Re: Just looking for a bit of advice...
> If I understand the code correctly, you're pulling two entire SQL server
> tables (tblAccessorySold and tblAccessoryInventory) over the wire to your
> client and doing the add/deletes there. This causes quite a lot of
network
> traffic--most of it unnecessary I daresay. Compare how it would work if
you
> just sent SQL statements to the server to be executed there--you're
sending
> little more than the text of the SQL statement to the server. Using a
> stored procedure would be even better--you could send just the identifier
> for the item being sold, and have the sp use that to move the record from
> one table to the other.
>
> Also, consider wrapping these operations up in a transaction (see the
> .BeginTrans method of the ado Connection object), so that you don't wind
up
> having your delete fail after your insert succeeds & start showing things
as
> both sold and in inventory.
>
> HTH,
>
> -Roy
>
> Roy Pardee
> Programmer/Analyst
> SWFPAC Lockheed Martin IT
> Extension 8487
>
> -----Original Message-----
> From: Darin Wray [mailto:darin@r...]
> Sent: Thursday, November 08, 2001 8:59 AM
> To: Access
> Subject: [access] Re: Just looking for a bit of advice...
>
>
> Hey all,
>
> I had posted a message to the vba_access forum about some help using ado
> recordsets. I didn't get much response (which is fine, I learned alot
while
> doing this), but after a couple of days, I managed to scrap some code
> together to make what I wanted work the way I wanted. In it's entirity it
> copies a record in one table based on a sql statement in a combobox/lookup
> box. Then the code deletes the record in another table based on the value
> that was entered in the combo box. This is my first attempt at using ado
> recordsets. If you guys can see where I might be able to be more
efficient,
> I'd appreciate the advice (I have little experience in programming, let
> alone programming with ado)....except for the naming conventions and stuff
> :) This is experimental code..hehe.
>
> Thanks in advance,
> Darin
>
>
> Private Sub Text2_AfterUpdate()
> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> 'This code is used to take an inventory record from the
> 'tblAccessoryInventory table and copy/paste it into the the
> 'tblAccessorySold Table.
> 'The last part of the code takes the value from text2
> 'and finds the control number in the recordset that is similar. Each
> 'control number is unique in the tblAccessoryInventory table.
> 'It finds that record, then deletes it.
> 'Finally we refresh everything, and set focus for data entry
> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>
> Dim mydb As ADODB.Connection
> Dim MyTable As ADODB.Recordset
> Dim rstblah As ADODB.Recordset
> Dim strcnn As String
>
> strcnn = "Provider=sqloledb;" & _
> "Data Source=101.101.101.101;Initial
> Catalog=cellular;UserId=sa;Password=; "
> Set mydb = New ADODB.Connection
> mydb.Open strcnn
>
>
> Set MyTable = New ADODB.Recordset
> MyTable.CursorType = adOpenKeyset
> MyTable.LockType = adLockOptimistic
> MyTable.Open "tblAccessorySold", mydb, , , adCmdTable
>
>
> 'This code used to update records in the tblAccessorySold table.
> 'It get's the value from the SQL statement that is behind the
> 'dropdown box. We will need to rename Text2 to something more
> 'meaningful later :) Among other things...
>
> MyTable.AddNew
> MyTable!ControlNumber = Me!Text2.Column(0)
> MyTable!ShipmentReceived = Me!Text2.Column(1)
> MyTable!SentOffice = Me!Text2.Column(2)
> MyTable!OfficeID = Me!Text2.Column(3)
> MyTable!InvoiceNumber = Me!Text2.Column(4)
> MyTable!StockNumber = Me!Text2.Column(5)
> MyTable!AccessoryCost = Me!Text2.Column(6)
> MyTable!AccessoryItem = Me!Text2.Column(9)
> MyTable!RetailPrice = Me!Text2.Column(10)
> MyTable.Update
> MyTable.Requery
> MyTable.Close
>
>
> 'Now that the accessory has been "copied" to the sold file, we
> 'can now delete it from tblAccessoryInventory.
>
> Set rstblah = New ADODB.Recordset
> rstblah.CursorType = adOpenKeyset
> rstblah.LockType = adLockOptimistic
> rstblah.Open "tblAccessoryInventory", strcnn, , , adCmdTable
> rstblah.Find ("ControlNumber = '" & CStr(Me.Text2) & "'")
> rstblah.Delete 'Delete the record for who's ControlNumber we
> just found
> rstblah.Close 'Close Recordset
> mydb.Close 'Close Connection
>
> On Error Resume Next
>
> DoCmd.SetWarnings False
> DoCmd.ShowAllRecords 'this code works to get
> 'records to finally
refresh
> and update
> 'on our screen..without
> going to the menu
> 'and selecting
> records/refresh. Couldn't find a better way
>
>
> Me.Repaint 'repaint form so
we
> can SetFocus
> Forms!test!Child6.SetFocus 'must setfocus on subform
> before on control
> Forms!test!Child6!SalesPrice.SetFocus 'to setfocus on control for data
> entry
>
> End Sub
>
>
>
>
Message #4 by "Pardee, Roy E" <roy.e.pardee@l...> on Thu, 08 Nov 2001 10:17:26 -0800
|
|
Yep--the recordsets are unnecessary. As for an example, here's some air
code. Say you write a stored procedure called sp_RecordSale, which takes
two parameters--an input text parameter called ControlNumber, and an output
integer called ResultCode. You could use code like so:
===============================================
Dim cn as ADODB.Connection
Dim cmd as ADODB.Command
Dim prm as ADODB.Parameter
Dim intRC as Integer
Set cn = New ADODB.Connection
With cn
.ConnectionString = "<<your connect string here>>"
.Open
End With
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "sp_RecordSale"
Set prm = .CreateParameter("ControlNumber" _
, adVarChar _
, adParamInput _
, 25)
.Parameters.Append prm
Set prm = .CreateParameter("ResultCode" _
, adVarChar _
, adParamOutput)
.Parameters.Append prm
.Parameters("ControlNumber").Value = Me.Controls("Text2").Value
.Parameters("ResultCode").Value = intRC
.Execute
End With
If intRC = 0 Then
MsgBox "Item " & Me.Controls("Text2").Value & " has been recorded as
sold"
Else
MsgBox "Error " & intRC & " has occurred--item " &
Me.Controls("Text2").Value & " has not been recorded as sold"
End If
Set prm = Nothing
Set cmd = Nothing
cn.Close
Set cn = Nothing
===============================================
HTH,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: Darin Wray [mailto:darin@r...]
Sent: Thursday, November 08, 2001 9:52 AM
To: Access
Subject: [access] Re: Just looking for a bit of advice...
Thanks Roy,
This is the type of advice I was wanting to hear.
As far as sending an sql statement...does this mean that the ado recordset
is unnecessary? and, what's a simple example of how to call an sp on the
sql server from code?
Thanks,
Darin
----- Original Message -----
From: "Pardee, Roy E" <roy.e.pardee@l...>
To: "Access" <access@p...>
Sent: Thursday, November 08, 2001 11:31 AM
Subject: [access] Re: Just looking for a bit of advice...
> If I understand the code correctly, you're pulling two entire SQL server
> tables (tblAccessorySold and tblAccessoryInventory) over the wire to your
> client and doing the add/deletes there. This causes quite a lot of
network
> traffic--most of it unnecessary I daresay. Compare how it would work if
you
> just sent SQL statements to the server to be executed there--you're
sending
> little more than the text of the SQL statement to the server. Using a
> stored procedure would be even better--you could send just the identifier
> for the item being sold, and have the sp use that to move the record from
> one table to the other.
>
> Also, consider wrapping these operations up in a transaction (see the
> .BeginTrans method of the ado Connection object), so that you don't wind
up
> having your delete fail after your insert succeeds & start showing things
as
> both sold and in inventory.
>
> HTH,
>
> -Roy
>
> Roy Pardee
> Programmer/Analyst
> SWFPAC Lockheed Martin IT
> Extension 8487
>
> -----Original Message-----
> From: Darin Wray [mailto:darin@r...]
> Sent: Thursday, November 08, 2001 8:59 AM
> To: Access
> Subject: [access] Re: Just looking for a bit of advice...
>
>
> Hey all,
>
> I had posted a message to the vba_access forum about some help using ado
> recordsets. I didn't get much response (which is fine, I learned alot
while
> doing this), but after a couple of days, I managed to scrap some code
> together to make what I wanted work the way I wanted. In it's entirity it
> copies a record in one table based on a sql statement in a combobox/lookup
> box. Then the code deletes the record in another table based on the value
> that was entered in the combo box. This is my first attempt at using ado
> recordsets. If you guys can see where I might be able to be more
efficient,
> I'd appreciate the advice (I have little experience in programming, let
> alone programming with ado)....except for the naming conventions and stuff
> :) This is experimental code..hehe.
>
> Thanks in advance,
> Darin
>
>
> Private Sub Text2_AfterUpdate()
> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> 'This code is used to take an inventory record from the
> 'tblAccessoryInventory table and copy/paste it into the the
> 'tblAccessorySold Table.
> 'The last part of the code takes the value from text2
> 'and finds the control number in the recordset that is similar. Each
> 'control number is unique in the tblAccessoryInventory table.
> 'It finds that record, then deletes it.
> 'Finally we refresh everything, and set focus for data entry
> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>
> Dim mydb As ADODB.Connection
> Dim MyTable As ADODB.Recordset
> Dim rstblah As ADODB.Recordset
> Dim strcnn As String
>
> strcnn = "Provider=sqloledb;" & _
> "Data Source=101.101.101.101;Initial
> Catalog=cellular;UserId=sa;Password=; "
> Set mydb = New ADODB.Connection
> mydb.Open strcnn
>
>
> Set MyTable = New ADODB.Recordset
> MyTable.CursorType = adOpenKeyset
> MyTable.LockType = adLockOptimistic
> MyTable.Open "tblAccessorySold", mydb, , , adCmdTable
>
>
> 'This code used to update records in the tblAccessorySold table.
> 'It get's the value from the SQL statement that is behind the
> 'dropdown box. We will need to rename Text2 to something more
> 'meaningful later :) Among other things...
>
> MyTable.AddNew
> MyTable!ControlNumber = Me!Text2.Column(0)
> MyTable!ShipmentReceived = Me!Text2.Column(1)
> MyTable!SentOffice = Me!Text2.Column(2)
> MyTable!OfficeID = Me!Text2.Column(3)
> MyTable!InvoiceNumber = Me!Text2.Column(4)
> MyTable!StockNumber = Me!Text2.Column(5)
> MyTable!AccessoryCost = Me!Text2.Column(6)
> MyTable!AccessoryItem = Me!Text2.Column(9)
> MyTable!RetailPrice = Me!Text2.Column(10)
> MyTable.Update
> MyTable.Requery
> MyTable.Close
>
>
> 'Now that the accessory has been "copied" to the sold file, we
> 'can now delete it from tblAccessoryInventory.
>
> Set rstblah = New ADODB.Recordset
> rstblah.CursorType = adOpenKeyset
> rstblah.LockType = adLockOptimistic
> rstblah.Open "tblAccessoryInventory", strcnn, , , adCmdTable
> rstblah.Find ("ControlNumber = '" & CStr(Me.Text2) & "'")
> rstblah.Delete 'Delete the record for who's ControlNumber we
> just found
> rstblah.Close 'Close Recordset
> mydb.Close 'Close Connection
>
> On Error Resume Next
>
> DoCmd.SetWarnings False
> DoCmd.ShowAllRecords 'this code works to get
> 'records to finally
refresh
> and update
> 'on our screen..without
> going to the menu
> 'and selecting
> records/refresh. Couldn't find a better way
>
>
> Me.Repaint 'repaint form so
we
> can SetFocus
> Forms!test!Child6.SetFocus 'must setfocus on subform
> before on control
> Forms!test!Child6!SalesPrice.SetFocus 'to setfocus on control for data
> entry
>
> End Sub
>
>
>
>
Message #5 by "Darin Wray" <darin@r...> on Fri, 9 Nov 2001 10:11:48 -0600
|
|
Hrmm...
Thanks Roy...this will give me something else to go learn about *grin*
Darin
----- Original Message -----
From: "Pardee, Roy E" <roy.e.pardee@l...>
To: "Access" <access@p...>
Sent: Thursday, November 08, 2001 12:17 PM
Subject: [access] Re: Just looking for a bit of advice...
> Yep--the recordsets are unnecessary. As for an example, here's some air
> code. Say you write a stored procedure called sp_RecordSale, which takes
> two parameters--an input text parameter called ControlNumber, and an
output
> integer called ResultCode. You could use code like so:
>
> ===============================================
> Dim cn as ADODB.Connection
> Dim cmd as ADODB.Command
> Dim prm as ADODB.Parameter
> Dim intRC as Integer
>
> Set cn = New ADODB.Connection
> With cn
> .ConnectionString = "<<your connect string here>>"
> .Open
> End With
>
> Set cmd = New ADODB.Command
> With cmd
> Set .ActiveConnection = cn
> .CommandType = adCmdStoredProc
> .CommandText = "sp_RecordSale"
> Set prm = .CreateParameter("ControlNumber" _
> , adVarChar _
> , adParamInput _
> , 25)
> .Parameters.Append prm
> Set prm = .CreateParameter("ResultCode" _
> , adVarChar _
> , adParamOutput)
> .Parameters.Append prm
> .Parameters("ControlNumber").Value = Me.Controls("Text2").Value
> .Parameters("ResultCode").Value = intRC
> .Execute
> End With
>
> If intRC = 0 Then
> MsgBox "Item " & Me.Controls("Text2").Value & " has been recorded as
> sold"
> Else
> MsgBox "Error " & intRC & " has occurred--item " &
> Me.Controls("Text2").Value & " has not been recorded as sold"
> End If
>
> Set prm = Nothing
> Set cmd = Nothing
>
> cn.Close
> Set cn = Nothing
> ===============================================
>
> HTH,
>
> -Roy
>
> Roy Pardee
> Programmer/Analyst
> SWFPAC Lockheed Martin IT
> Extension 8487
>
> -----Original Message-----
> From: Darin Wray [mailto:darin@r...]
> Sent: Thursday, November 08, 2001 9:52 AM
> To: Access
> Subject: [access] Re: Just looking for a bit of advice...
>
>
> Thanks Roy,
>
> This is the type of advice I was wanting to hear.
>
> As far as sending an sql statement...does this mean that the ado recordset
> is unnecessary? and, what's a simple example of how to call an sp on the
> sql server from code?
>
> Thanks,
> Darin
>
> ----- Original Message -----
> From: "Pardee, Roy E" <roy.e.pardee@l...>
> To: "Access" <access@p...>
> Sent: Thursday, November 08, 2001 11:31 AM
> Subject: [access] Re: Just looking for a bit of advice...
>
>
> > If I understand the code correctly, you're pulling two entire SQL server
> > tables (tblAccessorySold and tblAccessoryInventory) over the wire to
your
> > client and doing the add/deletes there. This causes quite a lot of
> network
> > traffic--most of it unnecessary I daresay. Compare how it would work if
> you
> > just sent SQL statements to the server to be executed there--you're
> sending
> > little more than the text of the SQL statement to the server. Using a
> > stored procedure would be even better--you could send just the
identifier
> > for the item being sold, and have the sp use that to move the record
from
> > one table to the other.
> >
> > Also, consider wrapping these operations up in a transaction (see the
> > .BeginTrans method of the ado Connection object), so that you don't wind
> up
> > having your delete fail after your insert succeeds & start showing
things
> as
> > both sold and in inventory.
> >
> > HTH,
> >
> > -Roy
> >
> > Roy Pardee
> > Programmer/Analyst
> > SWFPAC Lockheed Martin IT
> > Extension 8487
> >
> > -----Original Message-----
> > From: Darin Wray [mailto:darin@r...]
> > Sent: Thursday, November 08, 2001 8:59 AM
> > To: Access
> > Subject: [access] Re: Just looking for a bit of advice...
> >
> >
> > Hey all,
> >
> > I had posted a message to the vba_access forum about some help using ado
> > recordsets. I didn't get much response (which is fine, I learned alot
> while
> > doing this), but after a couple of days, I managed to scrap some code
> > together to make what I wanted work the way I wanted. In it's entirity
it
> > copies a record in one table based on a sql statement in a
combobox/lookup
> > box. Then the code deletes the record in another table based on the
value
> > that was entered in the combo box. This is my first attempt at using ado
> > recordsets. If you guys can see where I might be able to be more
> efficient,
> > I'd appreciate the advice (I have little experience in programming, let
> > alone programming with ado)....except for the naming conventions and
stuff
> > :) This is experimental code..hehe.
> >
> > Thanks in advance,
> > Darin
> >
> >
> > Private Sub Text2_AfterUpdate()
> >
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> > 'This code is used to take an inventory record from the
> > 'tblAccessoryInventory table and copy/paste it into the the
> > 'tblAccessorySold Table.
> > 'The last part of the code takes the value from text2
> > 'and finds the control number in the recordset that is similar. Each
> > 'control number is unique in the tblAccessoryInventory table.
> > 'It finds that record, then deletes it.
> > 'Finally we refresh everything, and set focus for data entry
> >
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> >
> > Dim mydb As ADODB.Connection
> > Dim MyTable As ADODB.Recordset
> > Dim rstblah As ADODB.Recordset
> > Dim strcnn As String
> >
> > strcnn = "Provider=sqloledb;" & _
> > "Data Source=101.101.101.101;Initial
> > Catalog=cellular;UserId=sa;Password=; "
> > Set mydb = New ADODB.Connection
> > mydb.Open strcnn
> >
> >
> > Set MyTable = New ADODB.Recordset
> > MyTable.CursorType = adOpenKeyset
> > MyTable.LockType = adLockOptimistic
> > MyTable.Open "tblAccessorySold", mydb, , , adCmdTable
> >
> >
> > 'This code used to update records in the tblAccessorySold table.
> > 'It get's the value from the SQL statement that is behind the
> > 'dropdown box. We will need to rename Text2 to something more
> > 'meaningful later :) Among other things...
> >
> > MyTable.AddNew
> > MyTable!ControlNumber = Me!Text2.Column(0)
> > MyTable!ShipmentReceived = Me!Text2.Column(1)
> > MyTable!SentOffice = Me!Text2.Column(2)
> > MyTable!OfficeID = Me!Text2.Column(3)
> > MyTable!InvoiceNumber = Me!Text2.Column(4)
> > MyTable!StockNumber = Me!Text2.Column(5)
> > MyTable!AccessoryCost = Me!Text2.Column(6)
> > MyTable!AccessoryItem = Me!Text2.Column(9)
> > MyTable!RetailPrice = Me!Text2.Column(10)
> > MyTable.Update
> > MyTable.Requery
> > MyTable.Close
> >
> >
> > 'Now that the accessory has been "copied" to the sold file, we
> > 'can now delete it from tblAccessoryInventory.
> >
> > Set rstblah = New ADODB.Recordset
> > rstblah.CursorType = adOpenKeyset
> > rstblah.LockType = adLockOptimistic
> > rstblah.Open "tblAccessoryInventory", strcnn, , , adCmdTable
> > rstblah.Find ("ControlNumber = '" & CStr(Me.Text2) & "'")
> > rstblah.Delete 'Delete the record for who's ControlNumber
we
> > just found
> > rstblah.Close 'Close Recordset
> > mydb.Close 'Close Connection
> >
> > On Error Resume Next
> >
> > DoCmd.SetWarnings False
> > DoCmd.ShowAllRecords 'this code works to get
> > 'records to finally
> refresh
> > and update
> > 'on our screen..without
> > going to the menu
> > 'and selecting
> > records/refresh. Couldn't find a better way
> >
> >
> > Me.Repaint 'repaint form
so
> we
> > can SetFocus
> > Forms!test!Child6.SetFocus 'must setfocus on
subform
> > before on control
> > Forms!test!Child6!SalesPrice.SetFocus 'to setfocus on control for
data
> > entry
> >
> > End Sub
> >
> >
> >
> >
>
>
>
>
|
|
|
View Cart
