look up value and insert in form

[vb_Dumb]

I have two forms the main form has a text box for a record number it
also has a command button to open the second form the second form has
a text box that generates a record number with this code in the form's
before update event i need code or something that when i click to open
the second form it needs to look at the last number of the text box
and add one and insert it back in the first forms text box thanks in
advance.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.DMRnumbertxt = "1"
Else
Me.DMRnumbertxt = Nz(DMax("[DMR Number]", "[ETP Table]"), 0) + 1
End If
End If
End Sub

 

[banem2]

I have two forms the main form has a text box for a record number it
also has a command button to open the second form the second form has
a text box that generates a record number with this code in the form's
before update event i need code or something that when i click to open
the second form it needs to look at the last number of the text box
and add one and insert it back in the first forms text box thanks in
advance.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
 If RecordsetClone.RecordCount = 0 Then
  Me.DMRnumbertxt = "1"
 Else
    Me.DMRnumbertxt = Nz(DMax("[DMR Number]", "[ETP Table]"), 0) + 1
End If
End If
End Sub

So you are using second form just to add next number? Or you do
something more with second form?

In case you are using it just to add next number, then you can use
following code:

Sub sAddNextNumber()
On Error GoTo ErrHandler
Dim strSQL As String
strSQL = "INSERT INTO [ETP Table] ( [DMR Number] ) " & _
SELECT NZ(DMax('[DMR Number]','ETP Table'),0)+1 AS NextNumber"
CurrentDb.Execute strSQL, dbFailOnError
Requery
Exit Sub
ErrHandler:
MsgBox Err.Description
End Sub

Or, you can do it with:

Sub sAddNextNumber()
On Error GoTo ErrHandler
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("ETP Table")
With rst
.AddNew
.Fields(field number) = NZ(DMax('[DMR Number]','ETP Table'),0)+1
.Update
End With
rst.Close
Set rst = Nothing
Requery
Exit Sub
ErrHandler:
MsgBox Err.Description
End Sub

Also try not to use space in object naming, it causes too much
additional work in code, queries, forms, reports... (hint: google for
article "Hungarian Notation").

Regards,
Branislav Mihaljev
Microsoft Access MVP