Problem with code

[Johnkl via AccessMonster.com]

Hi all

Can you please read the code below and tell me what I'm doing wrong??? I can
not make it work no matter what..
All I'm trying to do is transfer the data to two other tables and then change
the status of the line from pending(0) to ordered (1)

Please help.............................
Kind regards
John




Private Sub cmdSales_Click()
On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim db As Database
Dim rs As DAO.Recordset
Dim strSql As String 'SQL statement.
Dim strMsg As String
Dim lngID As Long 'Primary key value of the new record.
Dim frm As Form
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select or Insert Proposal to Post to Sales.", vbCritical,
gstrAppTitle
Else

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblSales")
With Me.RecordsetClone
rs.AddNew

rs![OrderDate] = [ArrivalDate]
rs![EmployeeID] = [EmployeeID]
rs![File] = [FileID]
rs![CustomerID] = [CustomerID]
rs![QuoteID] = [QuoteID]
'Save and edits first
rs.Update
rs.Bookmark = rs.LastModified

lngID = rs!SaleID
If Me.[frmQuoteDetailsSubform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblSales_Details] ( SaleID, Service, Price,
SupplierID, QuoteDetailID ) " & _
"SELECT " & lngID & " As SaleID, ServiceID, SupplierID,
SellPerItem, QuoteDetailID " & _
"FROM [tblQuoteDetails] WHERE (Quoted=True) AND (Status=0)
"
DBEngine(0)(0).Execute strSql, dbFailOnError

MsgBox "Succesfully Added Sales to This File!", vbInformation,
gstrAppTitle

THIS IS WHERE I HAVE THE PROBLEM Set rs = db.OpenRecordset
("tblQuoteDetails")
DBEngine(0)(0).Execute "UPDATE tblQuoteDetails SET Status=1 WHERE
QuoteDetailID = " & Me.frmQuoteDetailsSubform.Form.QuoteDetailID,
dbFailOnError ("This is where I Have a problem and I can not make it work -
ERROR 91-OBJECVT VARIABLE OR WITH NOT SET)

Set rs = Nothing
rs.Close
End If
End With



Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdSales_Click"
Resume Exit_Handler
End If
'End If
'End If
End Sub

 

[Jeff Boyce]

John

Before we take on the code, perhaps you could provide a bit more specific
description about why you are trying to do this? In a well-normalized
relational database design, I'm having some trouble visualizing what
business need would drive having data duplicated in more than one table.

I ask, not out of curiosity, but because the newsgroup readers may be able
to offer alternate approaches to solving the business need, if only we knew
what it was...

If you were to describe to an 80 year old grandmother what your
application/system/database allows you to do (i.e., solving some business
need?), how would you describe that need/issue/problem?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi all

Can you please read the code below and tell me what I'm doing wrong??? I can
not make it work no matter what..
All I'm trying to do is transfer the data to two other tables and then change
the status of the line from pending(0) to ordered (1)

Please help.............................
Kind regards
John




Private Sub cmdSales_Click()
On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim db As Database
Dim rs As DAO.Recordset
Dim strSql As String 'SQL statement.
Dim strMsg As String
Dim lngID As Long 'Primary key value of the new record.
Dim frm As Form
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select or Insert Proposal to Post to Sales.", vbCritical,
gstrAppTitle
Else

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblSales")
With Me.RecordsetClone
rs.AddNew

rs![OrderDate] = [ArrivalDate]
rs![EmployeeID] = [EmployeeID]
rs![File] = [FileID]
rs![CustomerID] = [CustomerID]
rs![QuoteID] = [QuoteID]
'Save and edits first
rs.Update
rs.Bookmark = rs.LastModified

lngID = rs!SaleID
If Me.[frmQuoteDetailsSubform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblSales_Details] ( SaleID, Service, Price,
SupplierID, QuoteDetailID ) " & _
"SELECT " & lngID & " As SaleID, ServiceID, SupplierID,
SellPerItem, QuoteDetailID " & _
"FROM [tblQuoteDetails] WHERE (Quoted=True) AND (Status=0)
"
DBEngine(0)(0).Execute strSql, dbFailOnError

MsgBox "Succesfully Added Sales to This File!", vbInformation,
gstrAppTitle

THIS IS WHERE I HAVE THE PROBLEM Set rs = db.OpenRecordset
("tblQuoteDetails")
DBEngine(0)(0).Execute "UPDATE tblQuoteDetails SET Status=1 WHERE
QuoteDetailID = " & Me.frmQuoteDetailsSubform.Form.QuoteDetailID,
dbFailOnError ("This is where I Have a problem and I can not make it work -
ERROR 91-OBJECVT VARIABLE OR WITH NOT SET)

Set rs = Nothing
rs.Close
End If
End With



Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdSales_Click"
Resume Exit_Handler
End If
'End If
'End If
End Sub

 

[Johnkl via AccessMonster.com]

Hi Jeff

Of course I can give you details.

Here goes
As far as the business is concerned I beleive that quotes (or proposal for
services ) is different that the actual order itself.

So the customer selects form various services whta he wants to order, and
thus I'm trying to transfer what the customer Ordered from Quotes to orders
or Sales.

My system - database is a system for DMC (Destination management) Agencies.

Hope that this is clear enough.
Kind regards
John

John

Before we take on the code, perhaps you could provide a bit more specific
description about why you are trying to do this? In a well-normalized
relational database design, I'm having some trouble visualizing what
business need would drive having data duplicated in more than one table.

I ask, not out of curiosity, but because the newsgroup readers may be able
to offer alternate approaches to solving the business need, if only we knew
what it was...

If you were to describe to an 80 year old grandmother what your
application/system/database allows you to do (i.e., solving some business
need?), how would you describe that need/issue/problem?

Hi all

[quoted text clipped - 76 lines]

'End If
End Sub

 

[Jeff Boyce]

Thanks for the clarification, John.

Another way to consider your situation would be that the items/services
selected by the customer are the basis of your "quote" until the customer
accepts the terms/conditions/prices your "quote" represents, then are
(?!magically?!) transformed into an official "Order". This doesn't require
storing two copies, or "moving" the data, since, if I'm understanding
correctly, the only thing that changes is the "status" of that shopping cart
full of items/services.

You could simply add a field to the underlying table to indicate either THAT
the customer accepted (a Yes/No field), or WHEN the customer accepted (a
Date/Time field).

JOPO (just one person's opinion)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi Jeff

Of course I can give you details.

Here goes
As far as the business is concerned I beleive that quotes (or proposal for
services ) is different that the actual order itself.

So the customer selects form various services whta he wants to order, and
thus I'm trying to transfer what the customer Ordered from Quotes to orders
or Sales.

My system - database is a system for DMC (Destination management) Agencies.

Hope that this is clear enough.
Kind regards
John

John

Before we take on the code, perhaps you could provide a bit more specific
description about why you are trying to do this? In a well-normalized
relational database design, I'm having some trouble visualizing what
business need would drive having data duplicated in more than one table.

I ask, not out of curiosity, but because the newsgroup readers may be able
to offer alternate approaches to solving the business need, if only we knew
what it was...

If you were to describe to an 80 year old grandmother what your
application/system/database allows you to do (i.e., solving some business
need?), how would you describe that need/issue/problem?

Hi all

[quoted text clipped - 76 lines]

'End If
End Sub