Recent content by antonycat

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

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

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

Thanks PHV It worked a treat.

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

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

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

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

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

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

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

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

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

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

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