Ok, I tested again without any condition and it still doesn't work. I still the firstname+lastname as the username in the email.

The query itself works, I tested them in phpMyAdmin and when I click the 'Approve' button in CB > User Management the username is updated to the membership number.

I have tried onUserActive, onBeforeUserActive and even before/after user sync but none worked.

I don't understand why it's a trial and error process. I know you have a list of triggers in your tutorial but there must be a developer documentation that tells exactly when each trigger fires.

Hmm .. with CB Fields I can't have complex query. I need to get the next member number first before assigning it to the username.

Anyway, I wanted to hide the username from the welcome email and use [EMAIL] and [PASSWORD] tags instead of [DETAILS]. However, the [PASSWORD] tag is not substituted in the email, it's literally shown as '[PASSWORD]'.

Require approval = Y
Require confirmation = N
Auto-generate password = Y