Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. antonycat

    Use trigger to number each order line

    I have an OrderLineID as the primary key but I need to use the LineNumber and OrderID fields to generate another reference for 3rd party software. Anyway this is what I have written so far but it does not work and I am not sure why. CREATE TRIGGER LineNumbering ON...
  2. antonycat

    Use trigger to number each order line

    I am wanting to set up a trigger on our SQL server 2000 to create a sequential order line number in the OrderLines table for OrderID. I will be inserting data from an OrderLinesAdd table into the OrderLines table but want the LineNumber field to be incremented by 1 for each group of orders...
  3. antonycat

    Increment each order line number by 1 for the same OrderID only

    Thanks HandsOnAccess I still don't seem to be able to get it to work. Maybe I need to give a trigger a go as PHV suggested but I am not sure how I would write the code for that ? Regards Antony
  4. antonycat

    Retrieve data with same values from different rows

    Thanks PHV It worked a treat.
  5. antonycat

    Code to run stored procedure returning Invalid use of property

    Thanks for pointing out the oversight on my part and it does work in our mdb. Regards Antony
  6. antonycat

    Increment each order line number by 1 for the same OrderID only

    I have just started using SQL so am not sure how I would write the code for the trigger but I assume Dmax would be used but am not sure how best to proceed ? Regards Antony
  7. antonycat

    Code to run stored procedure returning Invalid use of property

    We have an Access 2002 frontend and SQL Server 2000 backend. I am trying to get this function to work but am getting Invalid use of property, when the code gets to cmdSQL.CommandType Here is the code I have written: Public Function GetStoredProcedure() As String On Error GoTo...
  8. antonycat

    Increment each order line number by 1 for the same OrderID only

    INSERT INTO Transactions ( OrderID, JobOrderReference, StopID, SourceID, RoutingDate, OrderType, CommodityType, OrderSize, TrolleyShelves, LoadReference, Comments, BookingInReference ) SELECT [Forms]![frmOrdersAdd]![txtOrderID] AS tmpOrderID, OrderLinesAdd.JobOrderNumber, OrderLinesAdd.StopID...
  9. antonycat

    Increment each order line number by 1 for the same OrderID only

    Herewith the qappOrderLines SQL: INSERT INTO Transactions ( OrderID, JobOrderReference, StopID, SourceID, RoutingDate, OrderType, CommodityType, OrderSize, TrolleyShelves, LoadReference, Comments, BookingInReference ) SELECT [Forms]![frmOrdersAdd]![txtOrderID] AS tmpOrderID...
  10. antonycat

    Increment each order line number by 1 for the same OrderID only

    Thanks for the help so far. However I get the same LineNumber generated, which is not what I want. We are using Access 2002 frontend and Sql 2000 Server backend. Let me rephrase what I am trying to do. I have a temporary form for entering the OrderLines. When I press the save button, I need to...
  11. antonycat

    Increment each order line number by 1 for the same OrderID only

    I have the following set of example data: OrderID LineNo OrderQty 102 1 10 102 2 5 103 1 2 103 2 3 103 3 20 If I then add another OrderLine for example OrderID = 102, I would like to increment the LineNo...
  12. antonycat

    Retrieve data with same values from different rows

    MajP Thanks for the start but the corresponding F type may not be the next line so I was wanting to use the TransID and SourceID fields to link the 2 corresponding 'P' and 'F'type orders but obviously the linked data is on different rows. Thank you in advance. Regards Antony
  13. antonycat

    Retrieve data with same values from different rows

    I am needing to create order references for data that we are then going to pass into 3rd party software. So the structure of the order reference has to be in a specific format for their software. Here is some example data from the Access table 'Transactions' and the order reference that I need...
  14. antonycat

    Draytek Router to SBS2k server problem

    I am trying to setup a site to site VPN from an ADSL Draytek 2600 router (Remote site with Static Public IP Address, DHCP server for remote LAN) to our SBS2K server (Head Office with different Static Public IP Address). I have run the Local ISA VPN Server Wizard on the SBS and have set it up...
  15. antonycat

    Printing copies of pages that are customer specific

    I am producing a product check sheet for a date range for a number of different customers. Now the product check sheet may only be 1 page for 1 customer but >1 for another. 2 copies of each check sheet go on each customer's pallet that we send out. So I need to automate the printouts so that...
  16. antonycat

    If then Else Statement not correct

    That is correct. So should I put the dtmFinalDlyDate is null as the first statement in the code or leave it where it is at the moment ? or how do I solve this issue ? Regards Antony
  17. antonycat

    If then Else Statement not correct

    I have now put the dtmFinalDlyDate, dblMaxShelfLife and dblMinShelfLife into the function's arguments list but when I run the query using the function, on all the rows where the dtmFinalDlyDate is null, the DateCode is returned as an error ? This is the calculated DateCode field I have set up...
  18. antonycat

    If then Else Statement not correct

    Bob Thanks for the help. Here is the changed code: Public Function DateCode(dtmDlyDate As Date, chrCoDivision As String) As String '1)If chrCoDivision = TS, then DateCode = Format([dtmDlyDate]+[dblMaxShelfLife]) as ”dd mmm”) '2)If chrCoDivision = TM and dtmFinalDlyDate is null, 'then...
  19. antonycat

    If then Else Statement not correct

    Hi All This is my first attempt at any code but cannot get it to work. Please could someone help me correct the following code including the date formatting: Public Function DateCode() As String '1)If chrCoDivision = TS, then DateCode = Format([dtmDlyDate]+[dblMaxShelfLife]) as ”dd mmm”)...
  20. antonycat

    Union Query for predicted vs actual sales

    Great. Worked perfectly but now the null values. Obviously this query will generate null values but I need to calculate a PlannedSales - ActualSales balance. I have discovered the Nz function which I suppose i could use on the report's calculated field or would it be best to append the results...

Part and Inventory Search

Back
Top