Well, okay, you got me... DLookup has some update problems to the
subforms
and is a little slow. I've got an update query that I'm designing to do
the
job instead, but my design is not quite right. Can you take a look?
UPDATE [Order Entry5] SET [Order Entry5].BatchNumber = Format("Date","m")
&
"-" & ParseWord(DMax("[BatchNumber]","Order Entry5"),2,"-")+"1" & "-" &
Format("Date","yy")
WHERE ((([Order Entry5].[Order Number])=[forms]![frmInvoice]![Order
Number]));
I'm using Allen Browne's ParseWord function. I'll post it here so you can
duplicate the results.
Option Compare Database
Option Explicit
Function ParseWord(varPhrase As Variant, ByVal iWordNum As Integer,
Optional
strDelimiter As String = " ", _
Optional bRemoveLeadingDelimiters As Boolean, Optional
bIgnoreDoubleDelimiters As Boolean) As Variant
On Error GoTo Err_Handler
'Purpose: Return the iWordNum-th word from a phrase.
'Return: The word, or Null if not found.
'Arguments: varPhrase = the phrase to search.
' iWordNum = 1 for first word, 2 for second, ...
' Negative values for words form the right: -1 = last
word; -2 = second last word, ...
' (Entire phrase returned if iWordNum is zero.)
' strDelimiter = the separator between words. Defaults to a
space.
' bRemoveLeadingDelimiters: If True, leading delimiters are
stripped.
' Otherwise the first word is returned as null.
' bIgnoreDoubleDelimiters: If true, double-spaces are treated
as one space.
' Otherwise the word between spaces is returned as null.
'Author: Allen Browne.
http://allenbrowne.com. June 2006.
Dim varArray As Variant 'The phrase is parsed into a variant array.
Dim strPhrase As String 'varPhrase converted to a string.
Dim strResult As String 'The result to be returned.
Dim lngLen As Long 'Length of the string.
Dim lngLenDelimiter As Long 'Length of the delimiter.
Dim bCancel As Boolean 'Flag to cancel this operation.
'*************************************
'Validate the arguments
'*************************************
'Cancel if the phrase (a variant) is error, null, or a zero-length
string.
If IsError(varPhrase) Then
bCancel = True
Else
strPhrase = Nz(varPhrase, vbNullString)
If strPhrase = vbNullString Then
bCancel = True
End If
End If
'If word number is zero, return the whole thing and quit processing.
If iWordNum = 0 And Not bCancel Then
strResult = strPhrase
bCancel = True
End If
'Delimiter cannot be zero-length.
If Not bCancel Then
lngLenDelimiter = Len(strDelimiter)
If lngLenDelimiter = 0& Then
bCancel = True
End If
End If
'*************************************
'Process the string
'*************************************
If Not bCancel Then
strPhrase = varPhrase
'Remove leading delimiters?
If bRemoveLeadingDelimiters Then
strPhrase = Nz(varPhrase, vbNullString)
Do While Left$(strPhrase, lngLenDelimiter) = strDelimiter
strPhrase = Mid(strPhrase, lngLenDelimiter + 1&)
Loop
End If
'Ignore doubled-up delimiters?
If bIgnoreDoubleDelimiters Then
Do
lngLen = Len(strPhrase)
strPhrase = Replace(strPhrase, strDelimiter & strDelimiter,
strDelimiter)
Loop Until Len(strPhrase) = lngLen
End If
'Cancel if there's no phrase left to work with
If Len(strPhrase) = 0& Then
bCancel = True
End If
End If
'*************************************
'Parse the word from the string.
'*************************************
If Not bCancel Then
varArray = Split(strPhrase, strDelimiter)
If UBound(varArray) >= 0 Then
If iWordNum > 0 Then 'Positive: count words from the
left.
iWordNum = iWordNum - 1 'Adjust for zero-based
array.
If iWordNum <= UBound(varArray) Then
strResult = varArray(iWordNum)
End If
Else 'Negative: count words from the
right.
iWordNum = UBound(varArray) + iWordNum + 1
If iWordNum >= 0 Then
strResult = varArray(iWordNum)
End If
End If
End If
End If
'*************************************
'Return the result, or a null if it is a zero-length string.
'*************************************
If strResult <> vbNullString Then
ParseWord = strResult
Else
ParseWord = Null
End If
Exit_Handler:
Exit Function
Err_Handler:
On Error Resume Next
Resume Exit_Handler
End Function
I hope I'm not violating netiquette by doing this. Without my table, you
can't really reproduce results anyway. What happens is, I get the Records
from Order Entry5 that I want by making the Order Number textbox in the
header of my form a criteria for the query. You can see the formula I
used
for the update. But only the first record from the recordset is changed.
I
haven't run into this yet.
