DWUTKA at marlow.com
DWUTKA at marlow.com
Wed Apr 12 14:21:20 CDT 2006
No, save the first, before you open the second. And I also close it,
destroy it (set it to nothing) and recreate it if I'm going to reuse it.
Drew
-----Original Message-----
From: Kim Wiggins [mailto:kimjwiggins at yahoo.com]
Sent: Wednesday, April 12, 2006 12:37 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access problem with tables
Thanks Drew, I will try just using one recordset instead of three. What I
thought is that using one recordset would create a problem when saving.
DWUTKA at marlow.com wrote: Just use one recordset.
So it should be like this:
rs.Open "tblAircraftWO", oConn, adOpenKeyset, adLockOptimistic
rs.AddNew
rs("work_order_no").Value = IIf(txtWorkOrderNo = "", vbNullString,
txtWorkOrderNo.Text)
.....assignments for all the other entries for this table
Rs.update
Rs.close
Set rs=nothing
Set rs = New ADODB.Recordset
rs.Open "tblAircraftPartsReplaced", oConn, adOpenKeyset, adLockOptimistic
rs.AddNew
rs("work_order_no").Value = IIf(txtWorkOrderNo = "", vbNullString,
txtWorkOrderNo.Text)
.....assignments for all the other entries for this table
Rs.update
Rs.close
Set rs=nothing
Drew
(Also, a little comment, IIF is useful in SQL because you can't use If Then,
in code, you can, and If Then's are more flexible then IIF statements...)
-----Original Message-----
From: Kim Wiggins [mailto:kimjwiggins at yahoo.com]
Sent: Wednesday, April 12, 2006 7:35 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access problem with tables
I apologize, the code was really so simple, I was embarrassed to post it. I
am new to this so I am learning. But here it is:
Set oConn = New ADODB.Connection
oConn.Open frmSplash.sConn
Set rs = New ADODB.Recordset
rs.Open "tblAircraftWO", oConn, adOpenKeyset, adLockOptimistic
rs.AddNew
rs("work_order_no").Value = IIf(txtWorkOrderNo = "", vbNullString,
txtWorkOrderNo.Text)
.....assignments for all the other entries for this table
Set rs1 = New ADODB.Recordset
rs1.Open "tblAircraftPartsReplaced", oConn, adOpenKeyset, adLockOptimistic
rs1.AddNew
rs1("work_order_no").Value = IIf(txtWorkOrderNo = "", vbNullString,
txtWorkOrderNo.Text)
.....assignments for all the other entries for this table
Set rs2 = New ADODB.Recordset
rs2.Open "tblAircraftPartsReplaced", oConn, adOpenKeyset, adLockOptimistic
rs2.AddNew
rs2("work_order_no").Value = IIf(txtWorkOrderNo = "", vbNullString,
txtWorkOrderNo.Text)
.....assignments for all the other entries for this table
rs.Update
rs.Close
rs1.Update
rs1.Close
rs2.Update
rs2.Close
Set rs = Nothing
Set rs1 = Nothing
Set rs2 = Nothing
oConn.Close
That's it. This works but the only problem is that because it is saving
to many tables some of the times the entries are created correctly and other
times it misses entries from 2 of the columns that must be there in all
three tables.
Thanks
Stuart McLachlan wrote:
On 11 Apr 2006 at 19:09, Kim Wiggins wrote:
> I have three Access tables that update using VB code. When I click
> the Save button, they all save at the same time. All three tables
> (table A, B and C) each have 2 columns (columns 1 and 2) that are
> identical. When it saves, it is adding a new record to columns 1
> and 2 in all three tables. The problem is, sometimes it saves to
> all three of the tables just fine and other times it may just save
> to 2 or 1 of them instead of all three. What would cause it to do
> that? What would cause such inconsistency to occur when it is using
> the same code every time? Thanks Kim Wiggins
>
We really need more information.
How about posting the code in the OnClick event for the Save button.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
---------------------------------
New Yahoo! Messenger with Voice. Call regular phones from your PC and save
big.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
---------------------------------
How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call
rates.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com