Filtered Form Dmax

[olorin]

Hi people,
I understand the Dmax function, but I get an error if I open a form that is filtered.
When I click a buton on the continuous form "frmBookingsShipNote", it opens a single form "frmShipNote", and it has the link criteria of "pkBookingsID".
The booking may have been made a few days earlier and the records in the shipping table will have grown since. So the recordset of the frmShipNote is filtered to just the one record. How can I find the highest shipping note number in the entire recordset of tblShipments and add 1 to the ShipNoteNumber on the filtered form?
"ShipNoteNumber" is a number field.

 

[coyote]

If the ShipNoteNumber is a number then on the filtered data form have a command box and enter the following on the click event

Me.ShipNoteNumber = Format(Dmax("ShipNoteNumber","YourTable")+1)

Note: YourTable is the table where the ShipNoteNumber is stored

Cheers

 

[olorin]

Thanx so much for the advice.
If I get your drift, maybe I could put it on the "On Open" event of the form, or, if the form has to open using the present link criteria (where the ShipNoteNumber field is already 0), maybe I could put it on the tab exit etc.
I am at work now so I haven't tried it out yet.
I will try it later when I get home.
Thanx again for the advice.

 

[olorin]

Hi again,
I tried your advice and got it to work by attaching the code to a click event on a button.
Problem is, if I want to duplicate the record and change the details of the product shipped. i.e, add another item to be shipped to the same customer using the same ShipNoteNumber, but change the ProductShipped then the ShipNoteNumber sets to "1".
The code behind the button "btnAddItemSameNote" is;

Private Sub btn_AddItemSameNote_Click()
On Error GoTo Err_btn_AddItemSameNote_Click
Dim iresponse As Integer
    iresponse = MsgBox("DO YOU WANT TO ADD A NEW DETAILS?", vbYesNo + vbDefaultButton2)
        If iresponse = vbYes Then
    Me.Shipped.Value = -1
    Me.IncludeInBookings.Value = 0
    Me.ShipNoteDone.Value = -1
    Me.cbo_Customer.Enabled = True
    Me.cdoSelectShipAddy.Enabled = True
    Me.fkHaulierID.Enabled = True
    Me.TrailerNumber.Enabled = True
    Me.ShipDate.Enabled = True
    Me.DeliveryDate.Enabled = True
    Me.DateBooked.Enabled = True
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
    Me.cbo_Customer.Enabled = False
    Me.cdoSelectShipAddy.Enabled = False
    Me.fkHaulierID.Enabled = False
    Me.TrailerNumber.Enabled = False
    Me.ShipDate.Enabled = False
    Me.DeliveryDate.Enabled = False
    Me.DateBooked.Enabled = False
    Me.cbo_ProductShipped.SetFocus
    End If
        If iresponse = vbNo Then
    Me.btnClose.Visible = True
    Me.btnPrintShipNote.Visible = True
    Me.btnPrintShipNote.SetFocus
    End If
Exit_btn_AddItemSameNote_Click:
    Exit Sub
Err_btn_AddItemSameNote_Click:
    MsgBox Err.Description
    Resume Exit_btn_AddItemSameNote_Click
End Sub

the code on the trial button I did based on your advice is:
Private Sub Command124_Click()
Me.ShipNoteNumber.Enabled = True
Me.ShipNoteNumber.Locked = False
Me.ShipNoteNumber.Value = DMax("[ShipNoteNumber]", "tblShipments") + 1
Me.ShipNoteNumber.Locked = True
Me.ShipNoteNumber.Enabled = False
End Sub

I just keep getting a 1 in the ShipNoteNumber

I hope you can help.
Thanx