Help!!!!!!!!!!! Update Query and DLookup question 
Author Message
 Help!!!!!!!!!!! Update Query and DLookup question

I am being asked to fix the foll. in an application created by someone else.
A subform displays data based on data selected on the main form. Whenever
the
user changes info in cboStatusID(on the subform) and clicks the update
button, the table
(OrderDetails) linked to the query that controls the form is updated with
the the new StatusID.  However the user would like another table
(Resource)to reflect this change in data as well.  I have tried using a
macro which executes a query on cboStatusID_Click() but only OrderDetails is
updated.  I've also tried several variations of VB code but I do not know
enough to get it working correctly.  I am including the update query as well
as the latest version of code.  This last version of code was suggested by
someone on this forum however I am getting a run time 13 Type mismatch
error.  I am wondering if this is because ResourceID is a numeric field and
the DLookup expects text.   I would appreciate any help I can get on either
alternative. Thanks.

Janice

Code:
Private Sub cboStatusID_Click()
Dim dbsJAWAcquisitions As Database
Dim strUpdate As String
Dim resid As Variant

resid = DLookup("[ResourceID]", "OrderDetails", "[OrderID]= " & txtOrderID &
" And [InvoiceNumber] = '" & txtInvoiceNumber & _
              "'" And [StandardNumber] = "'" & txtStandardNumber & "'")

strUpdate = "UPDATE Resource " & _
            "SET StatusID= " & cboStatusID & _
            "WHERE ResourceID = " & resid

Set dbsJAWAcquisitions = CurrentDb()
dbsJAWAcquisitions.Execute strUpdate

End Sub

Update Query SQL view:
UPDATE Resource INNER JOIN OrderDetails ON Resource.ResourceID =
OrderDetails.ResourceID SET Resource.StatusID = [forms]![frmInvoice
Maintenance]![cboStatusID]
WHERE (((Resource.ResourceID)=[orderdetails].[resourceid]) AND
((OrderDetails.OrderID)=[forms]![frmInvoice Maintenance]![cboOrderID]) AND
((OrderDetails.InvoiceNumber)=[forms]![frmInvoice Maintenance]![cboInvoice])
AND ((Resource.StandardNumber)=[forms]![frmInvoice
Maintenance]![txtStandardNumber]));

NB: frmInvoice Maintenance =main form
       subform also has text boxes that displays invoice number and order id



Sun, 02 Nov 2003 05:39:13 GMT  
 Help!!!!!!!!!!! Update Query and DLookup question
Janice,

I think you have a few too many double quotes. Try the following:

resid = DLookup("[ResourceID]", "OrderDetails", _
              "[OrderID]= " & txtOrderID & _
              " And [InvoiceNumber] = '" & txtInvoiceNumber & _
              "' And [StandardNumber] = '" & txtStandardNumber & "'")

If OrderID is also a string, as implied by txtOrderID, it will also require
single quotes:

resid = DLookup("[ResourceID]", "OrderDetails", _
              "[OrderID]= '" & txtOrderID & _
              "' And [InvoiceNumber] = '" & txtInvoiceNumber & _
              "' And [StandardNumber] = '" & txtStandardNumber & "'")

You should copy the above to a module to see the single and double quotes
more clearly,
--

John Green - Excel MVP
Sydney
Australia


Quote:
> I am being asked to fix the foll. in an application created by someone
else.
> A subform displays data based on data selected on the main form. Whenever
> the
> user changes info in cboStatusID(on the subform) and clicks the update
> button, the table
> (OrderDetails) linked to the query that controls the form is updated with
> the the new StatusID.  However the user would like another table
> (Resource)to reflect this change in data as well.  I have tried using a
> macro which executes a query on cboStatusID_Click() but only OrderDetails
is
> updated.  I've also tried several variations of VB code but I do not know
> enough to get it working correctly.  I am including the update query as
well
> as the latest version of code.  This last version of code was suggested by
> someone on this forum however I am getting a run time 13 Type mismatch
> error.  I am wondering if this is because ResourceID is a numeric field
and
> the DLookup expects text.   I would appreciate any help I can get on
either
> alternative. Thanks.

> Janice

> Code:
> Private Sub cboStatusID_Click()
> Dim dbsJAWAcquisitions As Database
> Dim strUpdate As String
> Dim resid As Variant

> resid = DLookup("[ResourceID]", "OrderDetails", "[OrderID]= " & txtOrderID
&
> " And [InvoiceNumber] = '" & txtInvoiceNumber & _
>               "'" And [StandardNumber] = "'" & txtStandardNumber & "'")

> strUpdate = "UPDATE Resource " & _
>             "SET StatusID= " & cboStatusID & _
>             "WHERE ResourceID = " & resid

> Set dbsJAWAcquisitions = CurrentDb()
> dbsJAWAcquisitions.Execute strUpdate

> End Sub

> Update Query SQL view:
> UPDATE Resource INNER JOIN OrderDetails ON Resource.ResourceID =
> OrderDetails.ResourceID SET Resource.StatusID = [forms]![frmInvoice
> Maintenance]![cboStatusID]
> WHERE (((Resource.ResourceID)=[orderdetails].[resourceid]) AND
> ((OrderDetails.OrderID)=[forms]![frmInvoice Maintenance]![cboOrderID]) AND
> ((OrderDetails.InvoiceNumber)=[forms]![frmInvoice

Maintenance]![cboInvoice])

- Show quoted text -

Quote:
> AND ((Resource.StandardNumber)=[forms]![frmInvoice
> Maintenance]![txtStandardNumber]));

> NB: frmInvoice Maintenance =main form
>        subform also has text boxes that displays invoice number and order
id



Sun, 02 Nov 2003 06:46:51 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. DLookup function used as criteria in query

2. dlookup or rst update?

3. Tell a DLookup text box to update?

4. DLookup In Query for CR

5. SQL Update Query Question

6. Question on dlookup

7. Dlookup question

8. DLookUp Simple Question

9. Question regarding DLookUp/Subforms!!!

10. Dialog box displayed during query insert or query update

11. VB6,Access97,ADO - Update query doesn't update all records

12. UPDATE query not updating records

 

 
Powered by phpBB® Forum Software