I have two tables, One is a Data ““LST_ SS”” and other is the result ““MML””. I want to concatenate a field FAC of LST_ SS for each DN and update in Table ““MML”” , into field FAC. Here i am trying to get rid of it. Please assist how do control rsMML , there is error on rsMML update. Private Sub cmdMML_Click() DoCmd.OpenQuery ““qryDISTINCT””, acViewNormal Dim rsList As Recordset Dim rsDBList As Database Dim rsData As Recordset Dim rsDBData As Database Dim rsMML As Recordset Dim rsDBMML As Database Set rsDBList = CurrentDb Set rsList = rsDBList.OpenRecordset(““qryDisTINCT””) Set rsDBData = CurrentDb Set rsData = rsDBData.OpenRecordset(““LST_ SS””) Set rsDBMML = CurrentDb Set rsMML = rsDBMML.OpenRecordset(““MML””) Do While Not rsList.EOF With rsMML .AddNew !DN = rsData.Fields(““DN””) End With Do While rsList.Fields(““DN””) = rsData.Fields(““DN””) With rsMML .Edit !FAC = rsMML.Fields(““FAC””) & “”,“” & rsData.Fields(““FAC””) .Update End With rsData.MoveNext Loop rsList.MoveNext Loop End Sub
Excellent work, nicely works for my requirement. I am so glad. Thanks a lot Martin. I have no problem to complete my MACRO.
Thanks again
Rana Irfan Ahmad
@ U.A.E., Sharja
In Excel, if you have DN in column A and FAC in column B, then add two columns.
In C2, enter: =IF(A2=A1,CONCATENATE(C1,“&”,B2),B2)
In D2, enter: =IF(A2=A3,“”,CONCATENATE(“MOD VSBR: DN=”,A2," , FAC=“,C2,”;"))
Do a copy down of the formulas, and column D will have one row for each DN.
Slightly off-list, but it should work.
Thanks to Martin and Michael , for guidance and help.
It solves my problem,
In fact I am creating a macro in excel to do the same . I was getting problem to concatenate my rows, that is why i uses MIcrosoft access.
I wish to complete my MACRO in excel so that my users can use one tool , not two.
Below is my sheet data.
I want to generate in to MML for each DN ,
like for example the out put row would be
MOD VSBR: DN=749001 , FAC=6&CBA-1&CFU-1&CFB-1&CIDCW-1;
LST_SS Sheet
DN FAC
7490001 6
7490001 CBA-1
7490001 CFU-1
7490001 CFB-1
7490001 CIDCW-1
7490005 6
7490005 CFU-1
7490005 CW-1
7490005 CIDCW-1
7490006 6
7490006 CW-1
7490007 6
7490007 CFU-1
7490007 CW-1
Hi,
The best way to do this is to first concatenate the FAC fields for each DN and then update the MML table. Use a query otherwise you’ll be looping round the main table for each record of DN. As Michael said, you don’t need to keep opening the DB, use just one connection thus:
Private Sub cmdMML_Click()
Dim rsList As Recordset, rsData As Recordset, rsMML As Recordset
Dim SQLStr As String, TStr As String
Set rsList = CurrentDB.Openrecordset(“qryDisTINCT”)
Set rsMML = CurrentDB.Openrecordset(“MML”)
Do While Not rsList.EOF
SQLStr = “Select * from LST_SS where dn = '” & rsList(“dn”) & “'”
Set rsData = CurrentDB.Openrecordset(SQLStr)
TStr = “”
Do While Not rsData.EOF
If TStr = “” Then
TStr = rsData(“FAC”)
Else
TStr = TStr & “,” & rsData(“FAC”)
End If
rsData.MoveNext
Loop
rsData.Close
With rsMML
.AddNew
!DN = rsList(“dn”)
!FAC = TStr
.Update
End With
rsList.MoveNext
Loop
rsList.Close
rsMML.Close
Set rsList = Nothing
Set rsMML = Nothing
Set rsData = Nothing
HTH
Martin
I have A Table LST_SS two fileds in it, DN and FAC , for each DN there are multiple rows of FAC, I want to combine (Concatinate )FAC in one string for each DN.
Rana Irfan Ahmad
@ U.A.E., Sharja
Irfan,
You don’t need to create separate object variables for the same database. Simply use CurrentDB.OpenRecordset() for each of the recordsetsYour code is moving through rsList, but you never select records for the other recordsets. At best, your code will examine the same (first) record in rsData repeatedly; at worst because you never selected a record, it will generate an error.Whenever you issue either the .AddNew or the .Edit method on a recordset, you must finish the operation with either a .Update or a .CancelUpdate method. You never complete the .AddNew on rsMML.When you open a recordset on a table, there is no order to the records. You are comparing the current record from rsList to a record from rsData. If you were actually advancing through the records or rsData, you would be falling out of the loop the first time you encountered a record that didn’t match the record from rsList, and missing any later records that did.
I suggest that, instead of trying to rework the code that you posted, you try to describe what you want to do. Please give us:
A description of each table: the name of the table; the names and data types of the fields, and any relationships between the fields of one table and the records of another table.A description of your goals: what changes do you want to make in the contents of each table? What are the rules for selecting records that match?
With that information, we will be able to give you much more help.
Michael S. Meyers-Jouan
