Making Dispatch Notes

[harrytgs]

please ignore

[harrytgs]

Hi rpeare,

What you've done looks great - definitely better than what I could have done

However, it doesn't seem to work! Every reference (even with different dispatch dates) is set to be WSHOP--1. As this is the primary key, I can't save anything!

Also, we have two ref numbers - one from the shop (always unique) and one dispatch ref (the WSHOP--1, which isn't unique as more than one repair can be done per branch per day). I have tried adding the other ref, which allows me to enter information and it appears on the table but doesn't help the WSHOP--1 number to count.

Finally, I need there to be a form ONLY showing the records which don't have a date dispatched, to make it easier to book an item out.

I hope this make sense - please let me know if it doesn't!

I don't expect you to do this - but I would appreciate if you could steer me in the right direction.

Thank you very much for your help!

[harrytgs]

@June7 - what exactly do you need ?

[June7]

You said you had attempted some coding as suggested by rpeare after attaching the file. I was suggesting if you provide modified file, can analyse the attempt. Looks like rpeare has offered you a sample db as guideline. I have not yet examined it and don't know how it is not accomplishing what you want. Don't really understand what you are asking for.

[harrytgs]

Hi June7,

Will try and explain below:

I work for a company which has 12 branches and a repair workshop.

The branches give in jewellery to be repaired by this workshop. Every time a repair is "created" by the branch, the POS software generates a unique reference number, and this is put with the jewellery and sent to the workshop.

At the workshop, they are currently using pen & paper, but they want a database as they need to Book In and Out.

So, when it arrives, they want to enter the POS reference, date received, branch from and description. (may be a few lines)

And, when it is finished, they want to find the correct repair (already booked in, preferably only the ones which haven't yet been booked out) and enter the date dispatched, who dispatched and a dispatch note reference should be automatically generated. This should be unique to each branch and each day (so, there will be a different reference number for one repair on 1/1/11 at Branch A and one repair on 2/1/11 at Branch A or one repair on 1/1/11 at Branch B).

Then, they want to print out a dispatch note, with the reference, date and branch in the title.

There should then be a breakdown of each item, description and POS reference. If possible, there should also be a count on the amount of repairs returned.

This then means that, should a branch need to trace an old repair, they can contact the workshop and they can see the dispatch note ref with which they sent the jewellery with.

Hope this makes sense!

Harry

[June7]

Branches submit item for repair for which they generate a POS reference that is unique for the issuing branch but not for the branches as a whole?

The workshop generates a dispatch ID when the repair is completed? No ID is generated when the item is received?

Any ID generated by workshop should be unique to the workshop and need not be dependent on which branch submitted the item nor the date of issuance. The submitter and date and POS ID are information in the record. This info can always be used in any search of historical records. No need to construct an ID from these attributes. At least that is the approach I would take. Don't see any real need for a dispatch ID. Record the date of dispatch in the repair record and who did the work.

I tried rpeare's sample project but the AddRecord is not working for me. My approach in generating a custom unique ID does use a bound form.

[harrytgs]

The reference begins with the initials of the store (e.g. London would be L or LO).

No ID is REQUIRED to be generated when the item is received.

They have requested that a dispatch ID is made, just so that people don't have to look for the right date and branch...

[rpeare]

Can you provide a sample of what you have modified your database to (after the example I posted) just strip out any information that's private and put some fake data in if you can.

[harrytgs]

Attached, thanks

[rpeare]

This is exactly the same database I gave you as far as I can tell, I don't see any modifications at all, so what is the issue? You just want the first two letters of the branch to appear in the REF field? If so change the code of the ON EXIT from the branch field to this

Dim sMaxRef

sMaxRef = DMax("[Ref]", "Bookings", "([BranchID]) = " & Me.BranchID)

If Not IsNull(BranchID) Then
    If IsNull(Ref) Then
        Ref = UCase(Left(BranchID.Column(1), 2)) & "-0000001"
    Else
        Ref = UCase(Left(BranchID.Column(1), 2)) & CLng(Right(sMaxRef, Len(sMaxRef) - 5))
    End If
End If

Also change the BRANCHID in your BOOKINGS table to NUMBER (long integer).

[harrytgs]

That ref is what is from the shop - NOT the dispatch note reference number!!!

The only change was to add the shop ref and make that the primary key as the DISPATCH NOTE reference is NOT unique!!!

[rpeare]

So what's the issue, instead of populating the REF you populate the dispatchnote instead it's just a change of field

[harrytgs]

The issue is that the dispatchnote code doesn't generate a new dispatchnote ref - WSHOP-000001 is made on EVERY repair added....

[rpeare]

ok, first of all you changed the reference of the tables so you have to change the code:

this is what should go in the ON EXIT property of the BRANCHID field now

Dim sMaxRef

sMaxRef = DMax("[Bransom Ref]", "Bookings", "([BranchID]) = " & Me.BranchID)

If Not IsNull(BranchID) Then
    If IsNull(sMaxRef) Then
        Bransomref = UCase(Left(BranchID.Column(1), 2)) & "-0000001"
    Else
        Bransomref = UCase(Left(BranchID.Column(1), 2)) & "-" & Right("000000" & CLng(Right(sMaxRef, Len(sMaxRef) - 3)) + 1, 6)
    End If
End If

add a field and hope it will work :P

NOTE, you also did not change the TAG property of your BRANSOMID field on your form to DE. If you don't the code I have that clears fields after data entry is complete will not work you have to change that TAG property to DE

[rpeare]

One more note, because you're limiting generating the ID and limiting it to six digits you will start to have problems if ever get to 10,000,000 records, it shouldn't be an issue but it's something to be aware of.