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" <[EMAIL PROTECTED]> 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
>
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.
