Making Dispatch Notes

[harrytgs]

Thanks, seems to be much better.

Still problem though, the automatically generated reference number is NOT the Bransom reference. It is the reference. Bransom is our POS software which the shops use to generate reference numbers.

This reference number should also be WSHOP-0000001 not HO-0000001. The HO-x number is generated by Bransom.



Have attached the changes that I have made.

[rpeare]

You said two pages ago that you WSHOP-x field is not unique. How can you be using it as a primary key if it's not unique?

If you were mistaken in saying that the WSHOP value is not unique then you just have to change the bit of code in the ON EXIT of one of your fields (after you've chosen the location since that's where it is now) instead of counting records at that location, count the number of records in the table as a whole and add one to that. The same method I've given you will work you just have to modify the code.

If your POS software is assigning a value at each location why don't you just use that instead of generating your own? Is it possible for the POS software to issue the same bransomref number at multiple locations?

[harrytgs]

I will try to explain again, sorry for any confusion.

At the shop, when a repair is logged, Bransom generates a UNIQUE reference number with the first two letters being an acronym of the location (i.e. Holloway is HO). This ensures it doesn't clash with any other location.

The WSHOP-00001 field that I am trying to make will be unique TO EACH DAY and TO EACH BRANCH. This will therefore not be unique (as there may be other repairs on the same day and for the same branch), so this cannot be the primary key. This is the reference for the dispatch notes and is what needs to be automatic.

[rpeare]

Ok for the sake of argument let's say you have this come in from one of your branches
(london office)
LO-0000001 1/1/2011 ----> repair information
LO-0000002 1/1/2011 ----> repair information
LO-0000003 1/1/2011 ----> repair information


When these get entered in your system, assuming they are the first records you receive for that office you would want them all to receive the code in your database

WSHOP-0000001

is that correct? If so you just change the code to count the records not only by office but by service date, it's just adding a where clause to the dcount statement and change which field it's populating from the bransomref to the ref field. You will also need to add an autonumber field to your table so that you have a unique identifier for that table.

[harrytgs]

Almost correct!

The WSHOP-00000001 number is to go on the DISPATCH NOTE - so it needs to have the same dispatch date.

How would I go about making this ?

[rpeare]

you are really confusing the hell out of me, I don't understand your last post other than that you now want to have the WSHOP-XXXX text appear not in the REF field but in a different field. If that's the case just change the destination in the code, right now it's saying something like

me.ref = blah blah

just change it to

me.<fieldname> = blah blah