tyruss8
asked on
Need an Access VBA code loop
Hello,
I have a table with multiple columns and each time the columns can change from say 8 - 10 fields. My ask is I concatenate all columns in one field called [Combine]. I have created update individual queries.
But what I'm trying to do is create vba code with some SQL loop update, that does the same thing as my queries, even when the fields changes from 8-10...
Please see attached Access DB; table [Members]; and the 7 queries I've created...
Thanks!
Update-db.accdb
I have a table with multiple columns and each time the columns can change from say 8 - 10 fields. My ask is I concatenate all columns in one field called [Combine]. I have created update individual queries.
But what I'm trying to do is create vba code with some SQL loop update, that does the same thing as my queries, even when the fields changes from 8-10...
Please see attached Access DB; table [Members]; and the 7 queries I've created...
Thanks!
Update-db.accdb
You can combine all fields in a single query. Having to deal with nulls and adding a delimiter makes the statement a bit ugly but it's a copy+paste job in the SQL text mode window of the query designer.
update Members set Combine = iif(S01 is null, "", S01 & "; ") & iif(S02 is null, "", S02 & "; ") ...
update Members set Combine = iif(S01 is null, "", S01 & "; ") & iif(S02 is null, "", S02 & "; ") ...
If you want to do it in a code loop, it'd look like this.
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("select * from Members")
While Not rs.EOF
Dim i As Integer
Dim fld As String
Dim s As String
s = ""
For i = 1 To 12
fld = "S" & Right("0" & i, 2)
If Not IsNull(rs(fld)) Then s = s & rs(fld) & "; "
Next
rs.Edit
rs!Combine = s
rs.Update
rs.MoveNext
Wend
ASKER
Hi Crystal, for the suggestion and code I will look into it, but I'm afraid I may not understand the full code as my experience in vba is intermediate at best.
thanks
thanks
ASKER
Hi CraigYellick,
I've copied and pasted your suggested vba code and got an error "item not found in this collection"?
Thanks
I've copied and pasted your suggested vba code and got an error "item not found in this collection"?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks CraigYellick worked like charm appreciate it!
Open in new window