help forcing a record to have an alternating value

[installer69]

I have a simple table with data input via a simple form.

Each record consists of 'id'(autonumber) 'serial number' 'action date' 'actioned by' and 'action'

'serial number' is looked up from, and limited to, a stockholding table.

'action' has just two alternatives - 'issue' and 'return'

I have racked my brain, all my books and search engines to find the code to prevent a record with the same 'serial' being issued again until it has been returned.

Come to think of it, if you could help me to ensure that the same 'actioned by' returns it after being issued it that would be fantastic!

Your help is greatly apreciated.

 

[willir]

Can you explain this a little more...

'serial number' is looked up from, and limited to, a stockholding table.

Better yet, give a couple of examples.

 

[installer69]

Sure; I have another table - stockholding - consisting of 'stockid'(autonumber) 'serial number' and 'date in'

On the form that allows data entry to the other table (it's called stockmovement) is a list box 'serial number' whose row source is 'serial number' from stockholding.

I then have a relationship between the 'serial number' on the two tables.

It seems to work ok for limiting the available stock to issue/return to that which I have in stock.

'Serial number' is entered via barcode scanner.

 

[ALEKSJAY]

You can try doing a query retrieving the last value of 'action' based on the 'serial' you entered.

I'm assuming the table's name is BOOKS for this example:

SELECT books.serialnum, Last(books.action) AS LastAction
FROM books
GROUP BY books.serialnum;

Modify this one to include your serial number in the criteria

 

[dhookom]

Last does not work. You want to find stock where the Max value of ActionDate for the stock item matches an Issue value. Consider a query with a subquery to return all the SerialNumbers where the most recent action is an Issue.

SELECT StockMovement.SerialNumber, StockMovement.Action, StockMovement.ActionDate
FROM StockMovement
WHERE (((StockMovement.Action)="Issue") AND ((StockMovement.ActionDate)=(SELECT Max(ActionDate) from StockMovement m WHERE m.SerialNumber = StockMovement.SerialNumber)));


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[installer69]

Thanks for you time guys. Sometimes planting a seed and making you evaluate your own coding helps.

Up 'till 3am I finally sorted a way (maybe not the best or tidiest but it's only for my business!)

I used a listbox in a datasheet as a subform to select the serial number of the stock to be issued. I populated it with items from a query that excluded any stock item whose 'lastAction' was 'issue'. I then applied a 'Me.refresh' to the 'afterupdate' of the listbox so that it repopulated with the latest data and thus excluded the stock that I had just issued as an option for the next record.

The best bit is in 6 months when I need to change something I'll be back here again begging for help 'cos I've forgotten how I did it.......like when I turned off windows,status bars and menus at start up.........

Thanks as always. It's nice to know that some people are still willing to give their help in this selfish world.