sqlString to create Public Sub

[Kiteknight]

I am trying to make multiple copies of a VBA on click events using a sqlstring (see below). ~10K copies


For the most part all is well. Although what I would like to happen is for the CreateCode2 mod to create only the Private Sub () when the cpt (5digitnumber) is listed in the tblValue.
What is happening: my variable i = 11001 to 11020 creates 20 Public Sub btn5ditignumber_Click()
What I want to happen: Create only the 5 Public Sub btn5didigitnumber_Click () based on the existing values in the cpt field in tblValues.

What I am hoping is an if statement or something else that would evaluate the i = 11001 to 11020 and therefore create on the 5 wanted Private Sub btn5digitnumber_Click ()

All suggestion would be appreciated.
and thanks very much in advance.



Two tables
tblValues 4 fields Id autoenter, CPT as number long, des as text and RVU as double
the cpt value is the 5digitnumber.

tblValue
ID	cpt	des	rvu
1	11001	this is text1	1.1
2	11003	this is text2	1.2
3	11005	this is text3	1.3
4	11010	this is text4	1.4
5	11020	this is text5	1.5

tblMemo 2 fields id number autoenter and a text field called Memo as text long

Below is the appearance of tblMemo after running CreateCode2

tblMemo
ID	Memo
1	Private Sub btn11002_Click() Code written here End Sub Private Sub btn11003_Click() Code written here End Sub Private Sub btn11004_Click() Code written here End Sub Private Sub btn11005_Click() Code written here End Sub Private Sub btn11006_Click() Code written here End Sub Private Sub btn11007_Click() Code written here End Sub Private Sub btn11008_Click() Code written here End Sub Private Sub btn11009_Click() Code written here End Sub Private Sub btn11010_Click() Code written here End Sub Private Sub btn11011_Click() Code written here End Sub Private Sub btn11012_Click() Code written here End Sub Private Sub btn11013_Click() Code written here End Sub Private Sub btn11014_Click() Code written here End Sub Private Sub btn11015_Click() Code written here End Sub Private Sub btn11016_Click() Code written here End Sub Private Sub btn11017_Click() Code written here End Sub Private Sub btn11018_Click() Code written here End Sub Private Sub btn11019_Click() Code written here End Sub Private Sub btn11020_Click() Code written here End Sub Private Sub btn11021_Click() Code written here End Sub

The name of the button will be btn5digitnumber_Click()

‘below is the mod that creates the code in the tblMemo

Public Sub CreateCode2()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long
Dim strMemo As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM tblMemo WHERE [ID]=1")
With rs

For i = 11001 To 11020
strMemo = strMemo & "Private Sub btn" & CStr(i + 1) _
& "_Click()" & vbNewLine _
& "Code written here" & vbNewLine _
& "End Sub" & vbNewLine & vbNewLine
Next i

.Edit
.Fields("Memo") = strMemo

.Update
.Close

End With

Set db = Nothing
Set rs = Nothing
End Sub

[ItsMe]

This is all very confusing to me. Are you trying to programmatically write VBA code into an existing module or are you trying to append/update a table?

[Kiteknight]

Correct programmatically write VBA code. Which would ultimately then be copied an paste into a module on a form.

[ItsMe]

Not sure why you are mentioning table and paste. And the idea of having thousands of click event handlers in a single module seems odd but..

Here is some code to create a control.. see the answer
http://bytes.com/topic/access/answer...-vba-ms-access

Here is a thread that has a discussion that seems relevant, along with a couple sample DB's
https://www.accessforums.net/access/...vba-15539.html

[Kiteknight]

sorry I am not clear and thanks for trying. Not really trying to explain the end results just the process to get there.
CreateCode2() just fine in doing what I ask it to do.
my For i = 11001 To 11020 this will create 20
private sub btn5digitnumber_Click ()
Code written here
End Sub

I would like to find a way to create code based only on (in my example) 11001, 11003, 11005 11010 and 11020. And NOT everything in between those number.

Again thanks for your interest and attempt in directing me to other posts very much appreciated.

this will generate btn11002_Click (), btn11003_Click().... All the way to btn11021_Click()

I was look for a way to NOT programmatically create the VBA code for the

[ItsMe]

You are going to need a .movenext when you are updating your records in your rs recordset.

Maybe...

 For i = 11001 To 11020
strMemo = ""
 strMemo = strMemo & "Private Sub btn" & CStr(i + 1) _
        & "_Click()" & vbNewLine _
        & "Code written here" & vbNewLine _
        & "End Sub" & vbNewLine & vbNewLine
        

    .Edit
    .Fields("Memo") = strMemo

    .Update

.movenext
Next i

    .Close

Although this line seems to suggest you only have one record within the recordset.
Set rs = db.OpenRecordset("Select * FROM tblMemo WHERE [ID]=1")

Like I said, not too clear on what you are trying to do and reading through your code without a clue what the objective is is difficult at the least.
.

[Kiteknight]

Look thanks ITSme again but I think you are trying to pick apart my CreateCode2()..... it works just fine for what I am trying to do. And yes I only want one record in tblMemo. and no I am not trying to append or update a table.
It is really a very simple example setup and maybe you should try it for yourself. Shift-F2 in the memo field from tblMemo and you will see the results (as posted above). My basic question remains is there away to change this line i=11001 to 11020 so that it only creates results on the 5 digit number from the cpt field in the tblValue.

[orange]

KiteKnight,
Can you tell us in plain English what you are trying to accomplish?
And why?
I'm not challenging your concept or approach, I'm curious.

[ItsMe]

...It is really a very simple example setup and maybe you should try it for yourself...

No thank you. I think I have had enough trying to decipher it.

[Kiteknight]

Orange thanks, I'll try... but maybe the answer is impossible. I'm very new to Access having only started working with it a couple of months ago and probably don't know how to ask the right questions.
Basically I am interested in writing lots of code quickly. I have figured out a fairly easy way using the strMemo to create the VBA code and attach it to a click buttons on a form.
My tblValue contain 10,601 records. First cpt value is 10021 and the last is 99607

So in short I would like to create 10,601 codes and not the ~90K if were to include all the values between the cpt codes i=10027 to 99607

[orange]

I still don't know what you want this code for. What is the purpose of building lots of this kind of code quickly.

You must have started with this.

If you are looking for Form Controls and event procedures, then you may wish to research Event Procedures Forms and WithEvents.

From M$oft

If you consider event procedures in a form module, you might realize that those event procedures exist only for a particular instance of the form. For example, UserForm1 and UserForm2 are separate instances of an object of type UserForm. Both have an Initialize event, which occurs only when that form is loaded. The Initialize event procedure for UserForm1 runs only when UserForm1 is loaded, not when UserForm2 is loaded, and vice versa.
The same holds true for events on objects that do not have associated modules — an event occurs for a particular instance of an object. More specifically, it occurs for an instance of an object that you have indicated should respond to events, not for any other instance.
To indicate that an instance of an object should respond to events, you declare a module-level object variable of that type by using the WithEvents keyword in a class module. This keyword notifies Microsoft® Visual Basic® for Applications (VBA) that you want to respond to events for the instance that is assigned to that object variable. You can use the WithEvents keyword only with objects that support events, and only in a class module.

Also note there is a limit (I've forgotten the number) to the number of controls on a Form.
Found this http://www.access-programmers.co.uk/...d.php?t=211166