DMax Function

[mdbBound]

Need help on Dmax please! Critical

I have the following scenario

Table: testWLog.

WLngNo field: has W001,W002, W003, W004 and W005
WStrNm field: has W001, W002, W003, W004 and W005
PreparedBy field: Default =environ("Username") has UserA (for rows W001 to W003). It Also has UserB (for rows W004 and W005).

Everytime I run my code I get W004 for UserA. At this time I should now get W006 for UserA because W005 and W006 has been taken by UserB.

Here is the following Module, Function, On-click Events
Please look at it.

Module:
Option Compare Database
Public MyDmax As Long

Public Function testDmax()
MyDmax = Nz(DMax("[WLngNo]", "testWLog", "Preparedby='" & Environ("Username") & "'"), 0) + 1
End Function
*********************
On my Form:
Option Compare Database
Dim WNext As Long
Dim ULocalWS As String ' for later use

Private Sub Command0_Click()

Call testDmax()
WNext = MyDmax
txtIAssignWS.SetFocus
txtIAssignWS.Text = "W" & Format(CStr(WNext), "000")
UmyDmaxWS = txtIAssignWS.Text

End Sub
***********************

The Value of MyDmax for UserA is 4. It should be 6 because UserB used 4 and 5 already.

Please help me. Thanks

 

[DALeffler]

Hi, mdbBound...

Is the WLngNo field in the testWLog table an autonumber field?

If WLngNo field is an autonumber, that is not what you're saying when you post this:

Table: testWLog.

WLngNo field: has W001,W002, W003, W004 and W005

The above is telling me that WLngNo field is a text field. And if it is a text field, the DMax function posted will not work: it'll cause a type mismatch error every time because the statement is trying to add a number to a string.

Forgive me for my presumption, but what I think you meant to post (from reading previous posts from you) is this:

Table: testWLog.

WLngNo field: Autonumber
WStrNm field: has W001, W002, W003, W004 and W005
PreparedBy field: Default =environ("Username") has UserA (for rows W001 to W003). It Also has UserB (for rows W004 and W005).

Am I correct or do I need to remove my foot from my mouth? (and don't worry: while my foot and mouth aren't exactly friends, they are getting used to each other... )?

 

[mdbBound]

Doug, thanks

WTable: testWLog.
WLngNo field: has W001,W002, W003, W004 and W005

WLngNo is an autonumber that is formatted to come out this way.
WStrNm field is the text version of WLngNo so I could join it with other tables later.

This is the process:
1. There is a form. On the form, the user will click a button to get the next available number using this function:
On my MODULE
Option Compare Database
Public MyDmax As Long

Public Function testDmax()
MyDmax = Nz(DMax("[WLngNo]", "testWLog", "Preparedby='" & Environ("Username") & "'"), 0) + 1
End Function

On my FORM (Where the user clicks the button)
Option Compare Database
Dim WNext As Long
Dim ULocalWS As String ' for later use

Private Sub Command0_Click()

Call testDmax()
WNext = MyDmax
txtIAssignWS.SetFocus
txtIAssignWS.Text = "W" & Format(CStr(WNext), "000")
UmyDmaxWS = txtIAssignWS.Text

End Sub

2. Whatever is the value of the Global/Public variable UmyDmaxWS should append to the WStrNm field of testWLog table, that is if I can get to the correct row which is the MyDmax = Nz(DMax("[WLngNo]", "testWLog", "Preparedby='" & Environ("Username") & "'"), 0) + 1 I still do not know how to do the append portion.

The problem right now is: Everytime I run my code I get W004 for UserA. At this time I should now get W006 for UserA because W005 and W006 has been taken by UserB. I can't get to the correct row.

If you need any other info, please let me know. Thanks for helping me.

 

[adam_fleck]

Dmax

Maybe I've got the wrong end of the stick here, but it could be that

MyDmax = Nz(DMax("[WLngNo]", "testWLog", "Preparedby='" & Environ("Username") & "'"), 0) + 1

should be replaced by

MyDmax = Nz(DMax("[WLngNo]", "testWLog"), 0) + 1

i.e. take out

"Preparedby='" & Environ("Username") & "'"

as this condition is restricting DMax to records posted by the current user. This is why you're getting W004 for UserA, I think.

You could also take a whole different approach.

To start a new record in testWLog try the following code:

Private Sub Command0_Click()
On Error Resume Next

Dim wRex as Recordset, n_WLngNo as Long

'Open the table testWLog

Set wRex = CurrentDB.OpenRecordset("testWLog",dbOpenTable)

'Add a new record to the table

wRex.AddNew
n_WLngNo = wRex("WLngNo")
wRex("WstrNm") = "W" & format$(n_WLngNo,"000")
wRex.Update

wRex.Close

'Requery the form so that the new record is visible

Me.Requery

'And navigate to the new record

Set wRex = Me.RecordSet.Clone
wRex.FindFirst "WLngNo=" & n_WLngNo
Me.Bookmark = wRex.Bookmark

Set wRex = Nothing

End Sub

You may need to set a reference to Microsoft DAO 3.6 Object Library before this code will work (in the VB editor, choose Tools, References... then scroll down the probably very long list of dlls and tick the box for this library).