Need help creating DLookup Query

[Madmax via AccessMonster.com]

I have taken over maintenance on an access system that I did not design or
code. I am not very knowlegable in Access coding. Over time changes were
made to the system that kind of work, but sometime don't really work well.
The problem I am trying to resolve now is as follows. There is a table [Test
Cases] that new entries are added into, there is a field in this table called
TestCaseNumber. It might have started out in life as a number, but now is
actually a string field. The field is composed of several form fields
concatenated together. The last part of the concatenated is a number that
distinguishes records with identical names to make them unique. When
creating a new entry I need to be able to determine the Next Available number
to use based on the records that already exist. List below is the query I
have created and the sub to process the query. Thanks in advance if you can
help me get this to work.

Query: Next Available Test Case Number2

SELECT [Test Cases].TestCaseNumber, [TestCaseNumber] & Max(Right("00" & Right
([TestCaseNumber],3)+1,3))
AS [Next Available]
FROM [Test Cases]
GROUP BY [Test Cases].TestCaseNumber;


After Update Event Procedure:

Private Sub TestTypeCode_AfterUpdate()

Dim nextnumber As Variant

Dim srchstring, TestTypeC As String
Dim NextTestCaseNumber As String

TestTypeC = TestTypeCode

' Build the test case name search query string
TestCaseNumber = Platform & "_TST_" & [FunctionalArea] & "_" & [Sub-
Functional Area] & "_" & TestTypeC & "*"

srchstring = "[TestCaseNumber] like """ & [TestCaseNumber] & """"

nextnumber = DLookup("[Next Available]", "Next Available Test Case
Number2", _
srchstring)

' Build the test case name String
[TestCaseNumber] = Platform & "_TST_" & [FunctionalArea] & "_" & [Sub-
Functional Area] & "_" & TestTypeC

If IsNull(nextnumber) Then
[TestCaseNumber] = [TestCaseNumber] & "_001"
Else
' Parse out the three digit Test Case Number - The last three digit
of the string
NextTestCaseNumber = Right$(nextnumber, 3)

'Append the Next Available Test Case number to the Test Case
[TestCaseNumber] = [TestCaseNumber] & "_" & NextTestCaseNumber
TestCaseSearch = [TestCaseNumber]
End If

'Turn off TTCode,Platform and Sub-functional Areas
TestTypeCode.Enabled = False
Platform.Enabled = False

[TestCaseDescription].Enabled = True
[TestCaseSearch].Enabled = True
[Test Case Conditions].Enabled = True
[Problem Logs].Enabled = True

End Sub

I have gone through numerous different senario trying to get this to work,
including another make query that that front ended the select query shown
here that extracted all the current records with the TestCaseNumber in
question and wrote them into a temp table and then used that table for the
DLookup. It did not work and I also had a concern about using the make query
with multiple users adding new records. After extensive searching on the web
I could not find anything that indicated temp tables in Access were like temp
#tables in SQL and that each user would get a unique table. I probably
should say that when I inherited maintenace on this system, it did create
unique numbers, but it was doing it based on only the [Functional Area]
column not on the concatenated [TestCaseNumber] field.

 

[Steve Schapel]

Madmax,

I have not been able to follow the full gist of what you have done here.
But it seems to me that the query is not correct. I amnot able to
comment on the expression you have used to return the number portion of
the TestCaseNumber, since you didn't give the details of the structure
of this field. But the GROUP BY clause on the TestCaseNumber field
itself isn't what you want, is it? You mentioned something about
"identical names", and whatever this is, I would imagine the query's
GROUP BY should be this.

--
Steve Schapel, Microsoft Access MVP

I have taken over maintenance on an access system that I did not design or
code. I am not very knowlegable in Access coding. Over time changes were
made to the system that kind of work, but sometime don't really work well.
The problem I am trying to resolve now is as follows. There is a table [Test
Cases] that new entries are added into, there is a field in this table called
TestCaseNumber. It might have started out in life as a number, but now is
actually a string field. The field is composed of several form fields
concatenated together. The last part of the concatenated is a number that
distinguishes records with identical names to make them unique. When
creating a new entry I need to be able to determine the Next Available number
to use based on the records that already exist. List below is the query I
have created and the sub to process the query. Thanks in advance if you can
help me get this to work.

Query: Next Available Test Case Number2

SELECT [Test Cases].TestCaseNumber, [TestCaseNumber] & Max(Right("00" & Right
([TestCaseNumber],3)+1,3))
AS [Next Available]
FROM [Test Cases]
GROUP BY [Test Cases].TestCaseNumber;


After Update Event Procedure:

Private Sub TestTypeCode_AfterUpdate()

Dim nextnumber As Variant

Dim srchstring, TestTypeC As String
Dim NextTestCaseNumber As String

TestTypeC = TestTypeCode

' Build the test case name search query string
TestCaseNumber = Platform & "_TST_" & [FunctionalArea] & "_" & [Sub-
Functional Area] & "_" & TestTypeC & "*"

srchstring = "[TestCaseNumber] like """ & [TestCaseNumber] & """"

nextnumber = DLookup("[Next Available]", "Next Available Test Case
Number2", _
srchstring)

' Build the test case name String
[TestCaseNumber] = Platform & "_TST_" & [FunctionalArea] & "_" & [Sub-
Functional Area] & "_" & TestTypeC

If IsNull(nextnumber) Then
[TestCaseNumber] = [TestCaseNumber] & "_001"
Else
' Parse out the three digit Test Case Number - The last three digit
of the string
NextTestCaseNumber = Right$(nextnumber, 3)

'Append the Next Available Test Case number to the Test Case
[TestCaseNumber] = [TestCaseNumber] & "_" & NextTestCaseNumber
TestCaseSearch = [TestCaseNumber]
End If

'Turn off TTCode,Platform and Sub-functional Areas
TestTypeCode.Enabled = False
Platform.Enabled = False

[TestCaseDescription].Enabled = True
[TestCaseSearch].Enabled = True
[Test Case Conditions].Enabled = True
[Problem Logs].Enabled = True

End Sub

I have gone through numerous different senario trying to get this to work,
including another make query that that front ended the select query shown
here that extracted all the current records with the TestCaseNumber in
question and wrote them into a temp table and then used that table for the
DLookup. It did not work and I also had a concern about using the make query
with multiple users adding new records. After extensive searching on the web
I could not find anything that indicated temp tables in Access were like temp
#tables in SQL and that each user would get a unique table. I probably
should say that when I inherited maintenace on this system, it did create
unique numbers, but it was doing it based on only the [Functional Area]
column not on the concatenated [TestCaseNumber] field.

 

[SteveS]

I'm also a little confused about what you are trying to do with the query.

You didn't provide any examples of what the "TestCaseNumber" looks like or
the data types of the fields involves, so I assumed they are all text fields.

The following code creates test numbers like this:

"PC_TST_MB_CPU_ALL_001"
"MAC_TST_Keyboard_All_Some_001"
"PC_TST_MB_CPU_ALL_003"
"MAC_TST_keyboard_All_Some_002"


Maybe this will help you move in the right direction.......
....Watch for line wrap...

'****** Beg code **********
Private Sub TestTypeCode_AfterUpdate()

Dim rst As DAO.Recordset
Dim strSQL As String
Dim strNextNumber As String
Dim srchString As String

' Build the test case name search query string
srchString = Me.[Platform] & "_TST_" & Me.FunctionalArea & "_" &
Me.[Sub-Functional Area] & "_" & Me.[TestTypeCode]

'make SQL string to search for [TestCaseNumber] records
strSQL = "Select Top 1 [TestCaseNumber]"
strSQL = strSQL & " From [Test Cases]"
strSQL = strSQL & " Where [TestCaseNumber] like '" & srchString & "_*' "
strSQL = strSQL & " ORDER BY [TestCaseNumber] DESC;"

'open recordset
Set rst = CurrentDb.OpenRecordset(strSQL)

'is there a record???
If rst.BOF And rst.EOF Then
'NO...
[TestCaseNumber] = srchString & "_001"
Else
'YES...
' Parse out the three digit Test Case Number - The last three digit
of the string
' convert to integer, add 1 and format it
strNextNumber = Format(CInt(Right(rst!TestCaseNumber, 3)) + 1, "_000")

'Append the Next Available Test Case number to the Test Case
[TestCaseNumber] = srchString & strNextNumber
End If

rst.Close
Set rst = Nothing

'Turn off TTCode,Platform and Sub-functional Areas
TestTypeCode.Enabled = False
Platform.Enabled = False

[TestCaseDescription].Enabled = True
[TestCaseSearch].Enabled = True
[Test Case Conditions].Enabled = True
[Problem Logs].Enabled = True
[TestCaseDescription].SetFocus
End Sub

'****** End code **********
--
Steve Sanford
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I have taken over maintenance on an access system that I did not design or
code. I am not very knowlegable in Access coding. Over time changes were
made to the system that kind of work, but sometime don't really work well.
The problem I am trying to resolve now is as follows. There is a table [Test
Cases] that new entries are added into, there is a field in this table called
TestCaseNumber. It might have started out in life as a number, but now is
actually a string field. The field is composed of several form fields
concatenated together. The last part of the concatenated is a number that
distinguishes records with identical names to make them unique. When
creating a new entry I need to be able to determine the Next Available number
to use based on the records that already exist. List below is the query I
have created and the sub to process the query. Thanks in advance if you can
help me get this to work.

Query: Next Available Test Case Number2

SELECT [Test Cases].TestCaseNumber, [TestCaseNumber] & Max(Right("00" & Right
([TestCaseNumber],3)+1,3))
AS [Next Available]
FROM [Test Cases]
GROUP BY [Test Cases].TestCaseNumber;


After Update Event Procedure:

Private Sub TestTypeCode_AfterUpdate()

Dim nextnumber As Variant

Dim srchstring, TestTypeC As String
Dim NextTestCaseNumber As String

TestTypeC = TestTypeCode

' Build the test case name search query string
TestCaseNumber = Platform & "_TST_" & [FunctionalArea] & "_" & [Sub-
Functional Area] & "_" & TestTypeC & "*"

srchstring = "[TestCaseNumber] like """ & [TestCaseNumber] & """"

nextnumber = DLookup("[Next Available]", "Next Available Test Case
Number2", _
srchstring)

' Build the test case name String
[TestCaseNumber] = Platform & "_TST_" & [FunctionalArea] & "_" & [Sub-
Functional Area] & "_" & TestTypeC

If IsNull(nextnumber) Then
[TestCaseNumber] = [TestCaseNumber] & "_001"
Else
' Parse out the three digit Test Case Number - The last three digit
of the string
NextTestCaseNumber = Right$(nextnumber, 3)

'Append the Next Available Test Case number to the Test Case
[TestCaseNumber] = [TestCaseNumber] & "_" & NextTestCaseNumber
TestCaseSearch = [TestCaseNumber]
End If

'Turn off TTCode,Platform and Sub-functional Areas
TestTypeCode.Enabled = False
Platform.Enabled = False

[TestCaseDescription].Enabled = True
[TestCaseSearch].Enabled = True
[Test Case Conditions].Enabled = True
[Problem Logs].Enabled = True

End Sub

I have gone through numerous different senario trying to get this to work,
including another make query that that front ended the select query shown
here that extracted all the current records with the TestCaseNumber in
question and wrote them into a temp table and then used that table for the
DLookup. It did not work and I also had a concern about using the make query
with multiple users adding new records. After extensive searching on the web
I could not find anything that indicated temp tables in Access were like temp
#tables in SQL and that each user would get a unique table. I probably
should say that when I inherited maintenace on this system, it did create
unique numbers, but it was doing it based on only the [Functional Area]
column not on the concatenated [TestCaseNumber] field.