Combine Fields into One and put and before last

[replyrpatil]

I have tblProductName ( ProductNumber and TradeMark )

Sample

ProductNumber TradeMark

123 T1
123 T2
234 T4
234 T1
123 T6
456 T3
123 T7




I have different table tblMainProductInfo ( ProductNumber and TradeMarkCombo)

ProductNumber TradeMarkCombo
123 T1,T2 , T6 and T7 are registered trademark of XYZ corp.

234 T4 and T1 are registered trademark of XYZ corp.

456 T3 is registered trademark of XYZ corp.





I have Main form based on main table “tblMainProductInfo” that has subform based on “tblProductName”


I need to create a code on subform such that if user add or delete a trademark from the subform it should update the relevant TradeMarkCombo filed on the main form.


e.g. say user is working on product code 123 ( subform will show T1 , T2 , T6 and T7 on separate rows) and the user deletes the row that has T2 , the main form TradeMarkCombo should be updated to

T1, T6 and T7 are registered trademark of XYZ corp.



Does anyone know how to write code for this

Thanks

 

[PHV]

Perhaps something like this in the subform's Current event procedure:
Me.Parent.Form![TradeMarkCombo].Requery

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

. . . or:

[blue]   Forms![purple][b]MainFormName[/b][/purple]!TradeMarkCombo.Requery[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[replyrpatil]

Private Sub TradeMark_AfterUpdate()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim StrProductNum As String
Dim StrTrademark As String


If (Forms![FrmMAINform]![cmbProductCode] & "" = "") Then Exit Sub

Set dbs = CurrentDb()

StrProductNum = Forms![FrmMAINform]![cmbProductCode]


Set rst = dbs.OpenRecordset("SELECT * FROM tblProductName WHERE ProductNumber = '" & StrProductNum & "'")

If rst.RecordCount > 0 Then

‘ After user updated subform trade mark filed ( they can add / delete / edit trade mark from the sub form)

‘clear the TradeMarkCOMO filed on Main form

Forms![FrmMAINform]![cmbTradeMarkCOMBO] = vbNullString

‘ if there are more than 1 trade mark associated to the product code

If rst.RecordCount > 1 Then

HERE I NEED A CODE THAT WILL COMBINE TRADEMARK Numbers such that it will put comma , after each trademark and will put “and” before the last trademark.

e.g. if there are two trade marks say T5 ,T6 in the subform after update .. in the Main form I need to have “T5 and T6 are register trade marks of XYZ corp”

e.g. if there are three or more say T7 , T8 , T9 , T11 in the subform after update … in the Main form I need to have “T7, T8 , T9 and T11 are register trade marks of XYZ corp”


SOME code that will Go to the first record

rst.Move 1 of rst! TradeMark

‘ loop Until second last record to add comma after each and then at the last record put “and” instead of comma

loop from rst first to second last by putting comma after each record

strTrademarCOMBO = rst1 & “,” & rst2 ………. Till rst N-1

then for the last record rst.EOF

strTrademarCOMBO = strTrademarCOMBO & “and” rstN


then

Forms![FrmMAINform]![cmbTradeMarkCOMBO] = = strTrademarCOMBO & “ are register trade marks of XYZ corp”








Else

‘ if there is only one record

Forms![FrmMAINform]![cmbTradeMarkCOMBO] = rst!trademark & “ is registered trade mark of XYZ corp”
End if

Else
Msgbox “ There are no Trade Marks associated to the selected Product Code”
End if


rst.Close
Set rst = Nothing


'turn dialogs back on
DoCmd.SetWarnings (True)


End Sub

 

[MajP]

Public Function getTradeMarks(varProdName As Variant) As String
  Dim rs As DAO.Recordset
  Dim intRecords As Integer
  Dim strTradeMarks As String
  Dim strSql As String
  
 'strSql = "Select distinct [trademark] from tblTrademarks where [productName] = " & varProdName
  strSql = "Select distinct [trademark] from tblTrademarks where [productName] = '" & varProdName & "'"
  Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  
  Do While Not rs.EOF
    intRecords = intRecords + 1
    If rs.AbsolutePosition = 0 Then
       strTradeMarks = rs.Fields("trademark")
    Else
       strTradeMarks = strTradeMarks & ", " & rs.Fields("trademark")
    End If
    rs.MoveNext
  Loop
  If intRecords = 1 Then
     strTradeMarks = strTradeMarks & " is a registered trademark of company " & varProdName
  Else
       strTradeMarks = Left(strTradeMarks, InStrRev(strTradeMarks, ",") - 1) & " and" & Mid(strTradeMarks, InStrRev(strTradeMarks, ",") + 1) & " are registered trademarks of " & varProdName
  End If
  getTradeMarks = strTradeMarks
End Function

Could be used in a query or a procedure.

SELECT DISTINCTROW tblTrademarks.productName, getTradeMarks([productName]) AS TradeMarks
FROM tblTrademarks;

my input:

ID	productName	trademark
1	123	T1
2	123	T2
3	123	T3
4	234	T4
5	234	T1
6	123	T6
7	456	T7
8	789	T8

my output:

productName	TradeMarks
123	T1, T2, T3 and T6 are registered trademarks of 123
234	T1 and T4 are registered trademarks of 234
456	T7 is a registered trademark of company 456
789	T8 is a registered trademark of company 789

 

[MajP]

Also on your main form you can simply put this in a calculated control
=getTradeMarks([productname])

If the subforms is the trademark table just encsure to recalc the main form after an update to the subform.