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
>
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.
