updating field based in another

[Francis]

Field IDBancos (combobox: cmbIDBancos) after having a value and thus
changed, is writen also in another field lancamento (textbox:
txtlancamento) a value of the increment as it would be an autonumber,
as following:

Private Sub cmbIDBancos_AfterUpdate()
If (Not IsNull(Me.cmbIDBancos)) Then
If IsNull(Me.txtlancamento.Value) Then
Me.txtlancamento.Value = DMax("[lancamento]",
"GestaoTesouraria_Table", "[IDBancos] = Me!cmbIDBancos.Value") + 1
End If
End If
End Sub

Both fields are in GestaoTesouraria_Table.

What is not working in here? and why doesn't txtlancamento updates with
an incremented integer after cmbIDBancos gets a value.

Thanks in advance.

Francis
(Portugal)

 

[Nikos Yannacopoulos]

Francis,

The problem is in the condition of the DMAx; the reference to
Me!cmbIDBancos must be outside the quotes, so VBA realizes it is a
reference and not a text string. Try this:

Me.txtlancamento.Value = DMax("[lancamento]", _
"GestaoTesouraria_Table", _
"[IDBancos] = " & Me!cmbIDBancos.Value) + 1

if IDBancos is numeric. In case it's text, use this instead:

Me.txtlancamento.Value = DMax("[lancamento]", _
"GestaoTesouraria_Table", _
"[IDBancos] = '" & Me!cmbIDBancos.Value & "'") + 1

HTH,
Nikos

 

[Francis]

Private Sub cmbIDBancos_AfterUpdate()
Dim dm
dm = DMax("[lancamento]", "GestaoTesouraria_Table", "[IDBancos] ="
& Forms!Cashflows!cmbIDBancos)
Forms!Cashflows!txtlancamento = IIf(IsNull(dm), 1, dm + 1)
End Sub

Was the solution i came up by myself, but it seems, you have also
replyed, thanks

 

[Douglas J Steele]

While not significantly different than your solution, the following would
also work:

Private Sub cmbIDBancos_AfterUpdate()

Forms!Cashflows!txtlancamento = Nz(DMax("[lancamento]", _
"GestaoTesouraria_Table", "[IDBancos] =" & _
Forms!Cashflows!cmbIDBancos), 0) + 1

End Sub

It uses the Nz function to set the result of the DMax to 0 if nothing is
found, and adds 1, all in a single step.

 

[Francis]

Nice and works

thanks