Adding Records Using VBA (1 Viewer)

[DDJKSA]

Morning everyone

I have a basic (pun intended?) question. I have a form ("Form1") which is not bound to any underlying table. On the form are 5 Option buttons ("Option1"..."Option 5"). I've been trying to figure out the code to write a new record to an existing table ("Table1") depending on which of the 5 option buttons are clicked but am getting nowhere fast (eg. clicking Option1 button would add a new record "Blue" to Table1 whereas clicking Option2 button would add a new record "Green" to Table1 etc.). To complicate matters, if I subsequently deselect Option1 button I would like the corresponding record from Table1 (in this example, "Blue") deleted. Maybe I'm asking for something that's programmatically difficult or it's actually quite simple and I'm overthinking it. I'll admit my VBA knowledge is mainly based on copying code from other sources and adapting it for my particular needs. My brain's fried so any help would be much appreciated.

Just thinking...instead of writing a new record each time one of the Option buttons is selected, it might be better (easier?) to have a procedure that executes once the form is completed, loops through the 5 Option buttons and only writes a record for each one that is selected (or "True").

TIA

DDJ

 

[arnelgp]

on the Click event of each Option button. put:

=AddDelRecord()

add this on the form's Code:

Public Function AddDelRecord()
Dim sControlName As String
Dim sql As String
sControlName = Screen.Activecontrol.Name
SELECT CASE sControlName
CASE "Option1"
    If [Option1] Then
        sql = "Insert Into Table1 (YourField) Values 'Blue';"
    Else
        sql = "Delete * From Table1 Where YourField = 'Blue';"
    End If
Case "Option2"
    If [Option2] Then
        sql = "Insert Into Table1 (YourField) Values 'Green';"
    Else
        sql = "Delete * From Table1 Where YourField = 'Green';"
    End If
END SELECT
Currentdb.Execute sql
End Function

 

[Uncle Gizmo]

I have a form ("Form1") which is not bound to any underlying table. On the form are 5 Option buttons ("Option1"..."Option 5"). I've been trying to figure out the code to write a new record to an existing table ("Table1") depending on which of the 5 option buttons are clicked but am getting nowhere fast (eg. clicking Option1 button would add a new record "Blue" to Table1 whereas clicking Option2 button would add a new record "Green" to Table1 etc.). To complicate matters, if I subsequently deselect Option1 button I would like the corresponding record from Table1 (in this example, "Blue") deleted. Maybe I'm asking for something that's programmatically difficult or it's actually quite simple and I'm overthinking it. I'll admit my VBA knowledge is mainly based on copying code from other sources and adapting it for my particular needs

I think you should start out by explaining your problem. Often when you have to look at the problem objectively to explain it to other people, you see the correct solution yourself ... Or more likely, you identify the problems with it ....

 

[DDJKSA]

on the Click event of each Option button. put:

=AddDelRecord()

add this on the form's Code:

Public Function AddDelRecord()
Dim sControlName As String
Dim sql As String
sControlName = Screen.Activecontrol.Name
SELECT CASE sControlName
CASE "Option1"
    If [Option1] Then
        sql = "Insert Into Table1 (YourField) Values 'Blue';"
    Else
        sql = "Delete * From Table1 Where YourField = 'Blue';"
    End If
Case "Option2"
    If [Option2] Then
        sql = "Insert Into Table1 (YourField) Values 'Green';"
    Else
        sql = "Delete * From Table1 Where YourField = 'Green';"
    End If
END SELECT
Currentdb.Execute sql
End Function

Thanks ArnelGP, that looks great. Is there any way of creating a loop for the 5 options?

TIA

DDJ

 

[arnelgp]

Thanks ArnelGP, that looks great. Is there any way of creating a loop for the 5 options?

no need to Loop, whenever you clicked an Option it will get added/deleted from the table.

 

[Pat Hartman]

When you dumb down your question to the point where it is meaningless, you get meaningless suggestions. Whatever you are trying to do, find a different method.

 

[DDJKSA]

Thanks ArnelGP

no need to Loop, whenever you clicked an Option it will get added/deleted from the table.

What I mean is instead of using

CASE "Option1"
If [Option1] Then...

is there a way of passing a loop variable (eg. i=1 to 40) to the statements above instead of writing out 40 different statements? Maybe I'm asking too much?

Thanks for all your help so far.

DDJ

 

[June7]

A For Next loop can dynamically build reference to controls. I have done this. Example:

    For j = 1 To 6
        If Me("Text" & j) > 0 Then intCount = intCount + 1
    Next

However, in your case the code needs more info to know what to do - the Case structure instructs to use "Green" or "Blue" or etc. Eliminating it will require some creative approaches. A value such as "Green", etc, could be set in caption of option button associated label and code would pull from that to build appropriate SQL. Or the Tag property could be used to hold this.

For i = 1 to 40
    Dim strVal As String
    strVal = Me("Option" & i).Tag
    If Me("Option" & i) Then
        sql = "Insert Into Table1 (YourField) Values('" & strVal & "')"
    Else
        sql = "Delete From Table1 Where YourField = '" & strVal & "'"
    End If
Next

Instead of Caption or Tag properties, could have a table that associates option button name with action value and code pulls from there using DLookup().

strVal = DLookup("ValueField", "OptionsTable", "ButtonField='" & Me("Option" & i) & "'")

Then the real trick is figuring out what event is used to trigger this looping code.

Variation on the above, could have function that is called by each Option button so the code executes immediately.

Function Options(intBtn As Integer, strVal As String)
    If Me("Option" & intBtn) Then
        sql = "Insert Into Table1 (YourField) Values('" & strVal & "')"
    Else
        sql = "Delete From Table1 Where YourField = '" & strVal & "'"
    End If
End Function

Call that function in Option button Change event property: =Options(1, "Green") - the number and text would be changed as appropriate for each control

Code or property settings - no matter what there is a time factor to building.

You indicate 40 statements - does that mean 40 option buttons and growing?

 

[DDJKSA]

A For Next loop can dynamically build reference to controls. I have done this. Example:

    For j = 1 To 6
        If Me("Text" & j) > 0 Then intCount = intCount + 1
    Next

However, in your case the code needs more info to know what to do - the Case structure instructs to use "Green" or "Blue" or etc. Eliminating it will require some creative approaches. A value such as "Green", etc, could be set in caption of option button associated label and code would pull from that to build appropriate SQL. Or the Tag property could be used to hold this.

For i = 1 to 40
    Dim strVal As String
    strVal = Me("Option" & i).Tag
    If Me("Option" & i) Then
        sql = "Insert Into Table1 (YourField) Values('" & strVal & "')"
    Else
        sql = "Delete From Table1 Where YourField = '" & strVal & "'"
    End If
Next

Instead of Caption or Tag properties, could have a table that associates option button name with action value and code pulls from there using DLookup().

strVal = DLookup("ValueField", "OptionsTable", "ButtonField='" & Me("Option" & i) & "'")

Then the real trick is figuring out what event is used to trigger this looping code.

Variation on the above, could have function that is called by each Option button so the code executes immediately.

Function Options(intBtn As Integer, strVal As String)
    If Me("Option" & intBtn) Then
        sql = "Insert Into Table1 (YourField) Values('" & strVal & "')"
    Else
        sql = "Delete From Table1 Where YourField = '" & strVal & "'"
    End If
End Function

Call that function in Option button Change event property: =Options(1, "Green") - the number and text would be changed as appropriate for each control

Code or property settings - no matter what there is a time factor to building.

You indicate 40 statements - does that mean 40 option buttons and growing?

Thanks ArnelGP....very much appreciate all your help and suggestions so far. I'm gonna try them out over the next few days and will feedback. There's actually 42 Options so far - each student has 42 modules to complete and I'm trying to record each time a student completes a module with a simple checkbox. When the checkbox for that module is ticked a record is written into the underlying table with student name and module number completed. I then run a simple query on each student to determine the total number of modules completed etc.

Cheers

DDJ

Thanks again

DDJ

 

[arnelgp]

you can just setup a Form/Subform setup where you have the student on main form and the Modules on the subform.
see form frmStudentModules.

 

[DDJKSA]

you can just setup a Form/Subform setup where you have the student on main form and the Modules on the subform.
see form frmStudentModules.

Oh that's much easier for me and looks great. Thanks very much ArnelGP....you're a star

DDJ

 

[Pat Hartman]

There's actually 42 Options so far - each student has 42 modules to complete and I'm trying to record each time a student completes a module with a simple checkbox.

I knew when we got to the actual situation your assumed solution would not be the correct design. Arnelgp's subform suggestion is a far superior design. It is using a properly normalized table and allows the flexibility to add more modules as the program changes.

There are additional techniques that will make your job easier. For example, if there are different programs and each have different modules rather than the one size fits all you are currently designing, you can create a table that links specific modules to a program. Then when you add a new student and you pick his program, you can run an append query that selects all the modules for programA and appends it to David's schedule. Otherwise, you will need to add each module one at a time. That's the next hurdle you will discover on your current path. THIS is the REAL answer to your original question. How can I link David to a bunch of modules without entering each one, one at a time?

Also, don't use checkboxes when you could use dates. Rather than just a check when a module is complete, use a date instead. That will help should there be a requirement in the future to re-take certain modules every couple of years. The date will tell you when David last completed module23.

PS. You will always get better help if you tell us what your real world problem is.

 

[Pat Hartman]

I have a sample that might help with some concepts. It is a survey database. A survey has many questions. A respondant answers questions for a survey. Equate this to a program has many modules. A student studies the modules for a program and completes them.

In the form opened by Patient/Response is a subform that lists the surveys a patient has taken. When you add a new survey to this subform, the survey questions are linked to the Respondant by running an append query in Form_AfterInsert. Pretty simple code.

The sample is a little rough in places. It was cobbled together from two other more complex applications I built for actual clients.

 

[DDJKSA]

I have a sample that might help with some concepts. It is a survey database. A survey has many questions. A respondant answers questions for a survey. Equate this to a program has many modules. A student studies the modules for a program and completes them.

In the form opened by Patient/Response is a subform that lists the surveys a patient has taken. When you add a new survey to this subform, the survey questions are linked to the Respondant by running an append query in Form_AfterInsert. Pretty simple code.

The sample is a little rough in places. It was cobbled together from two other more complex applications I built for actual clients.

Thanks Pat, appreciate the help and your time.

Regards

DDJ

 

[DDJKSA]

I knew when we got to the actual situation your assumed solution would not be the correct design. Arnelgp's subform suggestion is a far superior design. It is using a properly normalized table and allows the flexibility to add more modules as the program changes.

There are additional techniques that will make your job easier. For example, if there are different programs and each have different modules rather than the one size fits all you are currently designing, you can create a table that links specific modules to a program. Then when you add a new student and you pick his program, you can run an append query that selects all the modules for programA and appends it to David's schedule. Otherwise, you will need to add each module one at a time. That's the next hurdle you will discover on your current path. THIS is the REAL answer to your original question. How can I link David to a bunch of modules without entering each one, one at a time?

Also, don't use checkboxes when you could use dates. Rather than just a check when a module is complete, use a date instead. That will help should there be a requirement in the future to re-take certain modules every couple of years. The date will tell you when David last completed module23.

PS. You will always get better help if you tell us what your real world problem is.

Hi Pat

I wasn't really trying to hide the real world situation - just trying (unsuccessfully!) to simplify the example. ArnelGP's solution is very elegant, very flexible, very adaptable and so easy to understand. He (apologies, or she) helped me out before with a similarly elegant timetable design. I agree with your comment re. checkboxes or dates but for now the dates of completion are not important, more so whether or not the module has been completed. I really appreciate all the help and comments and wish I had an ounce of the knowledge and expertise in this forum!

Have a great weekend everyone

DDJ

 

[Pat Hartman]

just trying (unsuccessfully!) to simplify the example.

I'm going to suggest that in the future you don't try to help us out. Most of us are quite capable of understanding complex problems. Tell us what your business requirement is. Stay away from technical stuff as much as possible. If you don't know what the solution is, you probably don't want to send people down rabbit holes. What you told us was YOUR solution which you needed help to implement. As you can see, some experts thought you understood your problem and what you had proposed was a valid solution and so offered help with implementing your "solution". So, they loaded bullets into your gun instead of trying to talk you off the cliff. I shook the coconut tree with my comment which arnelgp didn't like. But at least it made him take a second look.

So, arnelgp took a second look and rather than blindly answering the wrong question, saw that you had settled on a poor solution and so he offered a better one but it still never answered your underlying question - how can I add a bunch of rows to a table. THAT is the question I addressed with words and provided a sample to answer along with other suggestions. If that isn't your question then please tell us what your question is. If you are OK with adding the rows one at a time, then go with what arnelgp posted and add the rows one at a time. I prefer my method.