Subform/DLookup Multiple Value Concatenation Issue 2

[tennisguy]

I have a combo box named Account on a subform named RFP Expenses subform. That subform is on a main form named RFP Entry. There is a text box on RFP Entry named Account_To_Be_Charged. The main form and subform are linked via a field named RFP_Number. For each record created in the subform, I select an Account from the Account combo box. These numbers can be 1, 2, 4, 6, 7, or 8. For each RFP, I may create a number of expense records in the subform that use just one of those Account numbers or several. For example:

Doctor# Expense Code Acct Amt
101 3000 1 30.00
102 3001 4 15.00
103 3000 4 7.50
103 3002 7 50.00

Or like this:

Doctor# Expense Code Acct Amt
101 3000 1 30.00
102 3001 1 15.00
102 3001 1 20.00

In the Accounts table, we have:

Account_ID Account_Description
1 Operations
2 Professional Development
4 Operations
6 Operations
7 Professional Enrichment
8 Incentive/Development

What I would like to do is have the text box named Account_To_Be_Charged on the main form lookup the Account_Descriptions for the Account #s entered for the records in the subform. If there is only one Account # used then the Account_To_Be_Charged box would just display that Account's description. If there is more than one different Account number entered in the set of records in the subform for that particular RFP record displayed in the main form, then each Account's description should be strung together with slash characters in between. For example:

Operations / Professional Enrichment (for acct #s 1 and 7 or 4 and 7 or 6 and 7)
Or
Operations for acct #s 1, 4, or 6
Or
Professional Development / Division Incentive (for acct #s 2 and 8)

If Account #s 1, 4, or 6 occur together in the same set of records in the subform, then "Operations" should only print once in the Account_To_Be_Charged text box. So, if there are some records for acct #s 1, 2, 6, and 7 in the subform for that particular RFP main form record, then the Account_To_Be_Charged text box should say:

Operations / Professional Development / Professional Enrichment


I think I may have to use a DLookup to look up the Account_Description, and I think I may have to create an expression for the control source of the Account_To_Be_Charged text box that uses the & symbol to string the descriptions together with the slash characters. But, other than that, I'm not sure how to proceed.

Should you have any questions or require additional information, please let me know.

Thanks for your input,
Michael

 

[TheAceMan1]

How are ya tennisguy . . . .

I worked this up [blue]not knowing the data types[/blue] of fields posted, so if you have any problems [purple]post back the Actual Form/Table/field names, and data types of fields given . . . .[/purple]

In the ControlSource of [blue]Account_To_Be_Charged[/blue] copy/paste the following:

[blue] =ChargedBuild()[/blue]

Then in the code module for form [blue]RFP Entry[/blue] copy/paste the following:

[blue]Public Function ChargedBuild()
   Dim frm As Form, Build As String, DLK
   Dim Criteria As String, n As Integer
   
   Set frm = Forms![RFP Entry]
   Criteria = "[RFP_Number] = " & frm!RFPNumber & " And " & _
              "[Account_ID] = " & n & ";"
   
   For n = 1 To 7
      If n <> 3 And n <> 5 Then
         DLK = DLookup("[Account_Description]", "Accounts", Criteria)
         If Not IsNull(DLK) Then Build = Build & DLK & "/"
      End If
   Next
   
   If Len(Build & "") > 0 Then
      ChargedBuild = Left(Build, Len(Build) - 1)
   Else
      ChargedBuild = ""
   End If
         
End Function[/blue]

Thats it . . . give it a whirl and let me know . . . .

See Ya! . . . . . .

 

[tennisguy]

Thanks, AceMan!

I tried what you suggested. I set the control source of the Account_To_Be_Charged and placed the code into the RFP form module. ("General" is in the code window's Object drop down list at the top.) I noticed in one place in your code that you left out an underscore (RFPNumber instead of RFP_Number). In both cases (whether I fixed the underscore or not) I get #Error in the Account_To_Be_Charged box on the form.

I also noticed that there was no mention of the subform named "RFP Expenses subform." On that subform is where the combo box (named "Account") from which I choose the acct# is located.

Table/Query/Data Type info:

"Accounts" table:
Account_ID -- number data type (Key field)
Account_Description -- text data type

"Request for Payment General Info" table:
RFP_Number -- number data type (Key field)
(... other fields ...)
Account_To_Be_Charged -- text data type (since it will hold the string of words with slashes in between)

"Request for Payment Expenses" table:
Expense_ID -- autonumber data type (Key field)
RFP_Number -- number data type
Doctor_ID -- number data type
Expense_Code -- text data type
Account -- number data type
Amount -- currency data type

"RFP Entry" main form's record source is a query named "RFP Entry." All fields from the "Request for Payment General Info" table are in the query along with some fields from a couple of other tables.

"RFP Expenses subform" subform's record source is a query named "RFP Expenses." All fields from the "Request for Payment Expenses" table are in the query along with some fields from another table.

For each RFP record created in the main form (RFP Entry), there can be many expense records created in the linked subform (RFP Expenses subform). For each subform expense record, I specify Doctor_ID, Expense_Code, Account, and Amount. So, the Account #s are coming from the subform. And, it's for those Account #s that I would like to string together the Account_Descriptions.

Your code example may be on target or maybe I placed it in the wrong area, but please advise.

Thank you...

 

[TheAceMan1]

tennisguy . . . . .

[blue]For each record in your Main Form, you want to concatenate Account Descriptions from the Accounts Table, [purple]via lookup by the selections made in the Accounts Combobox on your subform. Each concatenation of an Description is to occur only once[/purple], no matter how many times it appears . . .[/blue]

I found a bigger problem with the criteria of the current method I'm using and couldn't get around it. So scrap/delete the main routine but keep the call [purple]=ChargedBuild()[/purple].

Now the following code requires the [purple]Microsoft DAO 3.6 Object Library.[/purple] So in any code module, click [blue]Tools[/blue] - [blue]References ...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then with the up arrow, [blue]push it up as high in prioirity as it will go.[/blue] Click OK.

Next . . . . in a module in the modules window copy/paste the following code (you substitute proper names in [purple]purple[/purple]):

[blue]Public Function ChargedBuild()
   Dim db As DAO.Database, rst As DAO.Recordset, CB As ComboBox
   Dim frm As Form, sfrm As Form, Build As String, DLK
   Dim Criteria As String, n As Integer
   
   Set db = CurrentDb()
   Set frm = Forms![[purple][b]MainFormName[/b][/purple]]
   Set sfrm = frm![[purple][b]subFormName[/b][/purple]]
   Set CB = sfrm![[purple][b]subFormComboboxName][/b][/purple]
   Set rst = sfrm.RecordsetClone
   Criteria = "[Account_ID] = " & [b]n[/b] & ";"
   
   For [b]n[/b] = 1 To 8 [green]'Loop thur all Accounts.[/green]
      If [b]n[/b] <> 3 And [b]n[/b] <> 5 Then 'Accounts 3 & 5 omitted.
         
         [green]'Look for Account n in subform.[/green]
         rst.FindFirst "[[purple][b]subFormComboboxName[/b][/purple]] = " & n
         
         If Not rst.NoMatch Then [green]'Account n exist if true[/green]
            DLK = DLookup("[Account_Description]", "Accounts", Criteria)
            If Not IsNull(DLK) Then Build = Build & DLK & "/"
         End If
      End If
   Next
   
   If Len(Build & "") > 0 Then
      ChargedBuild = Left(Build, Len(Build) - 1) [green]'remove last /[/green]
   Else
      ChargedBuild = "" [green]'Nothing Found[/green]
   End If
   
   Set rst = Nothing
   Set CB = Nothing
   Set sfrm = Nothing
   Set frm = Nothing
   Set db = Nothing

End Function[/blue]

The code basically loops thru all accounts and concatenates the first instance of those found in the subform.

Thats it . . . give it another whirl and let me know . . .

See Ya! . . . . . .

 

[tennisguy]

Thanks again...

I tried your suggestions. I activated and moved as high as I could the DAO 3.6. Also, I originally had your code placed in the code window for the RFP Entry form. (I didn't have a Module created on the Modules tab in the database window.) I have now created a module (named Module1), and I tried your code in the module as well as tried it in the code for the form.

When I try the code in the form's code window, I still get #Error in the Account_To_Be_Charged text box. When I try the code in Module 1, I get a data type mismatch error when trying to open the RFP Entry form. When I click Debug on the box, it highlights the line:

[highlight]Set sfrm = frm![RFP Expenses subform][/highlight]

"RFP Expenses subform" is the name of my subform in the database window and in the design view of the RFP Entry form.

 

[TheAceMan1]

tennisguy . . . . .

Do this everyday! . . . . . don't know how I missed it (probably rushing).

[blue]Change:
[b]Set sfrm = frm![RFP Expenses subform][/b]
To:
[b]Set sfrm = frm![RFP Expenses subform][purple].Form[/purple][/b][/blue]

The [blue]code should be run from the Module1.[/blue] Errors are prone if run from the forms code module on open. So remove it from the code module. Besides, can't have two public functions with the same name.

Now . . . I expect there may be some problem with the combobox, so if you still have problems, when you post back, post the layout of the combobox or : Field Names (left to right, including any fields with width set to zero), Bound Column property.

See Ya! . . . . . .

 

[tennisguy]

OK, made that correction. Now, upon opening the RFP Entry form I get "Run-time error '3075': Syntax error in query expression '[Account_ID]=0;' When I click Debug, it highlights the line:
[highlight]
DLK = DLookup("[Account_Description]", "Accounts", Criteria)
[/highlight]

The Accounts combo box in the subform has the following properties:

Name = Account
Control Source = Account (name of the field in the RFP Expenses table that I wish to store the Account # in after an expense record is created in this subform)
Row Source Type = Table/Query
Row Source = SELECT Accounts.Account_ID, Accounts.Account_Description FROM Accounts ORDER BY Accounts.Account_ID;
Column Count = 2
Column Heads = Yes
Column Widths = 0.5";1.5"
Bound Column = 1
List Rows = 8
List Width = 2"
Limit To List = Yes

If you need other properties, just let me know.

 

[PHV]

Anyway, this code:
Criteria = "[Account_ID] = " & n & ";"
must be INSIDE the For loop, ie Criteria must be rebuild for each n.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tennisguy]

Thanks, PHV...

With the code line you mentioned repositioned, the same syntax error appears, except with '[Account_ID]=1;' And, the same line is highlighted when I click Debug.

 

[PHV]

And what about this ?
DLK = DLookup("Account_Description", "Accounts", "Account_ID=" & n)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

OK tennisguy . . . . .

Had to move the Criteria line (wrong place). Try again.

[blue]Public Function ChargedBuild()
   Dim db As DAO.Database, rst As DAO.Recordset, CB As ComboBox
   Dim frm As Form, sfrm As Form, Build As String, DLK
   Dim Criteria As String, n As Integer
   
   Set db = CurrentDb()
   Set frm = Forms![MainFormName]
   Set sfrm = frm![subFormName]
   Set CB = sfrm![subFormComboboxName]
   Set rst = sfrm.RecordsetClone
   
   For n = 1 To 8 'Loop thur all Accounts.
      If n <> 3 And n <> 5 Then 'Accounts 3 & 5 omitted.
         
         'Look for Account n in subform.
         rst.FindFirst "[subFormComboboxName] = " & n
         
         If Not rst.NoMatch Then 'Account n exist if true
            [purple][b]Criteria = "[Account_ID] = " & n[/b][/purple]
            DLK = DLookup("[Account_Description]", "Accounts", Criteria)
            If Not IsNull(DLK) Then Build = Build & DLK & "/"
         End If
      End If
   Next
   
   If Len(Build & "") > 0 Then
      ChargedBuild = Left(Build, Len(Build) - 1) 'remove last /
   Else
      ChargedBuild = "" 'Nothing Found
   End If
   
   Set rst = Nothing
   Set CB = Nothing
   Set sfrm = Nothing
   Set frm = Nothing
   Set db = Nothing

End Function[/blue]

See Ya! . . . . . .

 

[tennisguy]

Working great now, except that when I have multiple records in the subform for Operations accounts (1, 4, 6), the box will print Operations for each time it sees each different number. For example, if I have 2 expense records for Account 1 (Operations), 2 account records for Account 2 (Professional Development), and 2 expense records for Account 4 (Operations), then the Account_To_Be_Charged box will read Operations/Professional Development/Operations

Recall that Operations is the only account to have multiple Account_ID numbers. Any way to have the second occurrence of Operations to stay away?

 

[TheAceMan1]

tennisguy . . . . .

Ah yes! . . . forgot about that criteria . . . .

Shouldn't take too much . . . I'll post back when its worked up!

Oh! and by the way . . . [purple]Excellent Targeting PHV![/purple]

See Ya! . . . . . .

 

[PHV]

Replace the For n ... Next loop with something like this:
Dim x
For Each x In Array("1,4,6", "2", "7", "8")
rst.FindFirst "[subFormComboboxName] In (" & x & ")"
If Not rst.NoMatch Then
DLK = DLookup("Account_Description", "Accounts", "Account_ID In (" & x & ")")
If Not IsNull(DLK) Then Build = Build & DLK & "/"
End If
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

Alternative replacement for [blue]For n ... Next loop.[/blue]

[blue]   For n = 1 To 8 'Loop thur all Accounts.
      If n <> 3 And n <> 5 Then 'Accounts 3 & 5 omitted.
         
         'Look for Account n in subform.
         rst.FindFirst "[subFormComboboxName] = " & n
         
         If Not rst.NoMatch Then 'Account n exist if true
            Criteria = "[Account_ID] = " & n
            DLK = DLookup("[Account_Description]", "Accounts", Criteria)
            If Not IsNull(DLK) Then
               [purple][b]If InStr(Build, DLK) = 0 Then Build = Build & DLK & "/"[/b][/purple]
            End If
         End If
      End If
   Next[/blue]

See Ya! . . . . . .

 

[tennisguy]

Terrific! You guys are amazing! Gazillion stars all around!!!