[AccessD] Access problem with tables

Kim Wiggins kimjwiggins at yahoo.com
Wed Apr 12 12:37:25 CDT 2006


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.


More information about the AccessD mailing list