Making Dispatch Notes

[harrytgs]

I have made a database for a repair workshop, where you book in and book out repairs.

I am having a problem as we need to make a Dispatch Note daily with a list of all booked out repairs for that day and for that branch.

This is all working so far. However, we need to generate a reference number using AutoNumber (Wshop0001, Wshop0002, etc) and keep a record of this - how can this be done with keeping ONE reference number per day per branch?

[June7]

An Autonumber datatype field will not accomplish this. Use VBA code to control the generation of this reference ID.

[rpeare]

You would have to have an expression to build your own unique identifier.

In your example your expression would be something like

Dim sMaxID As String

If DCount("[RefNo]", "Tbl_RefNo") = 0 Then
RefNo = "Wshop00001"
Else
sMaxID = DMax("[RefNo]", "Tbl_RefNo")
RefNo = "Wshop" & Right("0000" & CLng(Right(sMaxID, 5)) + 1, 5)
End If

For this example my table was named Tbl_RefNo, my reference number was named RefNo and I assumed that the length of your reference number was going to be static (five digits). If you have the potential to go more than 5 digits I would start with a longer string (0000000001 for instance) you would just have to modifiy the example to handle the longer string.

[harrytgs]

Thanks for your replies (and sorry it's taken so long for me to reply)

I have attached the database, not sure where I am meant to put the VBA code exactly and how the tables should be laid out...

[June7]

You have to decide whether to generate the reference ID when the comment is started or when it is saved. Probably best to do it when saved so if user changes mind and doesn't want to make comment, they can back out without having generated an ID.

Put this code in some event, perhaps a button click, label button "Save Comment". RefNo could be the field name. Slight change - to reference name of field included in the form's RecordSource, use Me!RefNo. This will set the value of this field. Then commit the record to table by closing form or moving to another record or DoCmd.RunCommand acCmdSaveRecord

You also want to prevent users from accidentally saving the record again and replacing the ID. So use something like:

If IsNull(Me!RefID) Then
'code to generate ID
End If

[harrytgs]

Thank you for your help - still completely unsure of what to do though.

I have made a button, and gone to VBA (on the Code Editor), I have then entered the following (the Table is called Bookings and the ref DispatchRef):


Private Sub Command38_Click()
Dim sMaxID As String

If DCount("[DispatchRef]", "Bookings") = 0 Then
DispatchRef = "Wshop00001"
Else
sMaxID = DMax("[DispatchRef]", "Bookings")
DispatchRef = "Wshop" & Right("0000" & CLng(Right(sMaxID, 5)) + 1, 5)
End If
End Sub


When I do this, nothing seems to happen.

Do you have any suggestions?

Many thanks

[June7]

This code is behind a form that is bound to the table and the form is open to a new record? Also, might help to qualify the fieldname with the form Me alias.

Me!DispatchRef =

[rpeare]

I do not have an example of your database so it's kind of impossible for me to tell you where to put it. If you are using an unbound form you could add it to the button or the event that saves your record. If you're using a bound form you can attach the code to any event on your form when a new record is defined (let's say you have 3 required fields on your form, you could have this event in the ON EXIT property of each of those required fields with some additional checks to make sure all three pieces of information are entered.

If you can upload a sample of your database with garbage data (access 2003 or earlier please) I can take a look.

In your adapted code you should have a TABLE named BOOKINGS, in that table there should be a field called DISPATCH REF (I would recommend you stay away from any spaces or special characters like #, &, etc in your table, query, field, form names, they cause more trouble than anything else). And there should also be a text box on your form named DISPATCH REF. If all of this is true your button should be populating the DISPATCH REF FIELD with a value.

[harrytgs]

Thanks for your replies.

@June7, I know Access pretty well but am very new to VBA. I need to know exactly what I am supposed to do!!

@rpeare - I attached an example a while ago (called db test). This is in Access 2007 format - sorry!

[June7]

Sorry, not pertinent, comment removed.

[harrytgs]

This is for a repair workshop ...

Any suggestions would be greatly appreciated!!

[rpeare]

I can't read access 2007+ format files for half the week so if you want to post another example in 2003 or prior I can look

[June7]

Replace the attachment with revised project and I will look at.

[harrytgs]

June7, will sort out later today - it currently has company info so need to remove!

[rpeare]

Because you're not using a system generated primary key I would be more inclined to do this with an unbound form, here's a copy of your database with an example.