First, you shouldn't be trying to specify those options in an assignment to
strSQL. And secondly, you don't need those parameters for an execute of an
"action" query. And finally, you probably should wrap all of this inside a
transaction so that either the order, the order details, and delete of the
offer all happen successfully or none of it does.
John Viescas, author
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
Running Microsoft Access 2000
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
For the inside scoop on Access 2007, see:
http://blogs.msdn.com/access/
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf
Of tyki9799
Sent: Saturday, May 06, 2006 4:35 AM
To: [email protected]
Subject: [ms_access] Re: Adding a new record
John, I understand that the breakpoint simply stops the code being
executed at that point. It's not a cause of errors.
If I move the "lngOrderID = rstO!OrderID" statement before the
"rstO.Update" statement, ie if code looks like this:
rstO!field3 = rstI!field3
.....
lngOrderID = rstO!OrderID
rstO.Update
I then get this error message: "Run-time error '94': Invalid use of Null."
It seems no OrderID (autonumber) value is been supplied by SQL Server
until the "rstO.Update" statement is proccessed.
I followed your istructions and added this piece of code:
Set rstI = db.OpenRecordset("SELECT @@IDENTITY As NewOrderID " & _
"FROM tblOrders;", dbOpenDynaset, dbSeeChanges)
lngOrderID = rstI!NewOrderID
rstI.Close
Set rstI = Nothing
At last the Offer Items are turned to Order Items with the correct
OrderID!
I'm really gratefull! Thanks very much!
One last thing: I have this SQL string to delete the Offer Items as
they are now turned into Order Items. The problem is that I get
something wrong in adding the "dbOpenDynaset, dbSeeChanges" parameter
so I get an error message that the string is wrong. I know it's the
"dbOpenDynaset, dbSeeChanges" parameter as the SQL string worked fine
in earlier versions. Any clue?
strSQL = "DELETE tblOfferDetails.* " & _
"FROM tblOfferDetails " & _
"WHERE InterestOfferID=" & Me.InterestOfferID & ";" &
", dbOpenDynaset, dbSeeChanges"
--- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote:
>
> Theodore-
>
> Adding a breakpoint shouldn't cause an error. Is it actually dying on:
>
> lngOrderID = rstO!OrderID
>
> ??
>
> Try moving that statement inside the rst.Update - anywhere after you've
> assigned some values to the new row. I know in a JET table, the new
> "autonumber" is available as soon as you dirty the row. If that doesn't
> work, then you'll have to open another recordset on "SELECT
@@IDENTITY As
> NewOrder" and use the value of the NewOrder column returned by that
query.
>
> John Viescas, author
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> Running Microsoft Access 2000
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
> For the inside scoop on Access 2007, see:
> http://blogs.msdn.com/access/
>
>
> -----Original Message-----
> From: [email protected] [mailto:[EMAIL PROTECTED]
On Behalf
> Of tyki9799
> Sent: Thursday, May 04, 2006 11:56 PM
> To: [email protected]
> Subject: [ms_access] Re: Adding a new record
>
> John, the "dbSeeChanges + dbAppendOnly" addition works fine!
>
> As about the second problem, OrderID is the primary key of tblOrders.
> It's an integer (int, identity, 4-bit long, ie an autonumber).
>
> If I place a breakpoint at the db.Execute strSQL command I get the
> following error message:
>
> "Run-time error '3021': No current record."
>
> My regards, Theodore
>
> --- In [email protected], "John Viescas" <JohnV@> wrote:
> >
> > For the first problem, do:
> >
> > Set rstO = db.OpenRecordset("SELECT * FROM tblOrders;", _
> > dbOpenDynaset, dbSeeChanges + dbAppendOnly)
> >
> > Do you have a valid value in lngOrderID? If not, you might need
to do a
> > SELECT @@Identity As NewOrder to get the correct order ID.
> >
> > You could also try putting a halt on the db.Execute, copy what's in
> strSQL
> > into the SQL View of a new query, and see if you can run the query
> from the
> > user interface.
> >
> > John Viescas, author
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > Running Microsoft Access 2000
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> > For the inside scoop on Access 2007, see:
> > http://blogs.msdn.com/access/
> >
> >
> > -----Original Message-----
> > From: [email protected] [mailto:[EMAIL PROTECTED]
> On Behalf
> > Of tyki9799
> > Sent: Thursday, May 04, 2006 12:01 AM
> > To: [email protected]
> > Subject: [ms_access] Re: Adding a new record
> >
> > Hi John! Thanks for your help.
> >
> > I followed your advice (adding dbOpenDynaset, dbAppendOnly) so I ended
> > up having:
> >
> > Set rstO = db.OpenRecordset("SELECT * FROM TableB;", _
> > dbOpenDynaset, dbAppendOnly)
> >
> > Then, unfortunately I faced the following error message.
> >
> > "Run-time error '3622': You must use the dbSeeChanges option with
> > OpenRecordset when accessing a SQL Server table that has an IDENTITY
> > column."
> >
> > Then I tried instead this (I didn't supply the "dbAppendOnly"
parameter:
> >
> > Set rstO = db.OpenRecordset("SELECT * FROM TableB;", _
> > dbOpenDynaset, dbSeeChanges)
> >
> > I tried it and it seems to be working OK though I'm not sure whether
> > by not supplying the "dbAppendOnly" parameter I'm getting into future
> > troubles...
> >
> > But then I'm not yet done!
> >
> > TableA is tblOffers and TableB is tblOrders. So what I do (worked fine
> > in Jet, prior to upsizing) is turning an offer into an order so that
> > the order hasn't to be created right from the start. Also the items
> > offered have to be turned into items ordered.
> >
> > This is done by getting the new OrderID and assign it to each of the
> > offered items being turned into ordered items.
> >
> > The problem is that when the INSERT INTO query is about to be executed
> > I get the following error message:
> >
> > "Run-time error '3001': ODBC--call failed."
> >
> > And then it points to "db.Execute strSQL, dbFailOnError" command after
> > the INSERT INTO query.
> >
> > So I end up with the offer turned into order alright but offered items
> > aren't turned to ordered items.
> >
> > Is there something wrong with the variable declaration or with the
> > query itself?
> >
> > Cheers, Theodore
> >
> > p.s. The whole code follows as it is now.
> >
> > Private Sub cmdAmendToOrder_Click()
> >
> > Dim db As DAO.Database, rstI As DAO.Recordset, rstO As
> DAO.Recordset
> > Dim lngOrderID As Long
> > Dim strSQL As String
> >
> > Set db = DBEngine(0)(0)
> >
> > Set rstI = db.OpenRecordset("SELECT * FROM tblOffers WHERE...
> > ;", dbOpenDynaset, dbSeeChanges)
> >
> > Set rstO = db.OpenRecordset("SELECT * FROM tblOrders;",
> > dbOpenDynaset, dbSeeChanges)
> >
> > rstO.AddNew
> >
> > rstO!field1 = rstI!field1
> > rstO!field2 = rstI!field2
> > rstO!field3 = rstI!field3
> > .....
> >
> > rstO.Update
> >
> > lngOrderID = rstO!OrderID
> >
> > rstI.Close
> > rstO.Close
> >
> > Set rstI = Nothing
> > Set rstO = Nothing
> >
> > strSQL = "INSERT INTO tblOrderDetails (OrderID, field2...)
" & _
> > "SELECT " & lngOrderID & " As OrderID, field2... " & _
> > "FROM tblOfferDetails " & _
> > "WHERE OfferID=" & Me.OfferID & ";"
> >
> > db.Execute strSQL, dbFailOnError
> >
> > End Sub
> >
> > --- In [email protected], "John Viescas" <JohnV@> wrote:
> > >
> > > You need dbSeeChanges only for a read/write navigable recordset.
> > For the
> > > second one, do:
> > >
> > > Set rstO = db.OpenRecordset("SELECT * FROM TableB;", _
> > > dbOpenDyanaset, dbAppendOnly)
> > >
> > > You had the dbAppendOnly in the wrong place.
> > >
> > > John Viescas, author
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > Running Microsoft Access 2000
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > > For the inside scoop on Access 2007, see:
> > > http://blogs.msdn.com/access/
> > >
> > >
> > > -----Original Message-----
> > > From: [email protected] [mailto:[EMAIL PROTECTED]
> > On Behalf
> > > Of tyki9799
> > > Sent: Wednesday, May 03, 2006 1:46 AM
> > > To: [email protected]
> > > Subject: [ms_access] Adding a new record
> > >
> > > Hi everyone!
> > >
> > > I recently upsized my Access database to SQL Server (MSDE sp4).
> > > I now have an .adp file which I use to manage the database (an
> > > interface to the MSDE backend) and an mdb file that holds the UI
> > > (forms, etc.). The .mdb file has the MSDE database tables linked
to it
> > > through ODBC. The application so far seems to run rather
smoothly but
> > > I get a few glitches.
> > >
> > > Here is one: I have this code for getting values from TableA and get
> > > them to a new record in TableB. It used to run fine prior to
upsizing
> > > but now a get an error message (the db.OpenRecordset "dbSeeChanges"
> > > parameter was added after the upsizing).
> > >
> > > Dim db As DAO.Database, rstI As DAO.Recordset, rstO As
> > DAO.Recordset
> > >
> > > Set db = DBEngine(0)(0)
> > >
> > > Set rstI = db.OpenRecordset("SELECT * FROM TableA WHERE... ;",
> > > dbOpenDynaset, dbSeeChanges)
> > >
> > > Set rstO = db.OpenRecordset("SELECT * FROM TableB;",
> > > dbAppendOnly, dbSeeChanges)
> > >
> > > rstO.AddNew
> > >
> > > rstO!field1 = rstI!field1
> > > rstO!field2 = rstI!field2
> > > rstO!field3 = rstI!field3
> > >
> > > rstO.Update
> > >
> > > rstI.Close
> > > rstO.Close
> > >
> > > Set rstI = Nothing
> > > Set rstO = Nothing
> > >
> > > When I run the code (through a command button) I get the following
> > > error message:
> > >
> > > "Run-time error '3251'. Operation is not supported for this type of
> > > object."
> > >
> > > The error is pointed to the "rstO.AddNew" statement.
> > >
> > > Any clue of what's wrong and how to fix it?
> > >
> > > Thanks in advance, Theodore
> > >
> > >
> > >
> > >
> > >
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> >
> >
> >
> > Yahoo! Groups Links
> >
>
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
Yahoo! Groups Links
SPONSORED LINKS
| Microsoft access database | Database development software | Database management software |
| Database software | Inventory database software | Membership database software |
YAHOO! GROUPS LINKS
- Visit your group "ms_access" on the web.
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
