Solved Adding Records Using VBA

[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.

 

[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

 

[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

 

[DDJKSA]

Thanks Pat

I understand your point and will try to be much more direct, much clearer in future when framing my problem. That said, I found the tone of your answers quite abrupt to the point of being rude. Perhaps with new members you could try and be a little more empathetic, less confrontational.? As I say, I really do appreciate all the help you guys have given.

Cheers

DDJ

 

[MajP]

Please ignore my working sample since you don't want my help and add the items one by one.

Before you "get your panties in a bunch", because the OP did not use your example. You might want to double check what you posted. I seriously doubt it is what you think. No forms, no code, no action queries, and no hidden objects. A few empty tables and a couple select queries. At first I thought you mistakenly posted the back end, but then it would not have queries. Not sure what this is.

Maybe I am missing something, but I have no idea how you interpreted this.

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.

From what the OP asked

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.

@arnelgp subform idea is obviously the easiest most flexible to do what the OP intended.

A real example of what the OP asked to do is demoed here. Pretty much same approach as @arnelgp
This is flexible, and allows to select and unselect. But requires some code and the subform is probably more suitable for novices.

Making a custom checklist that saves to a table

Good evening all! I have a need to build a checklist form. We have 19 steps that our leads have to do every engagement. Items 1-4 are pre-engagement, 5- 8 are mid-engagement, and 9- 19 are post-engagememt. I need a checklist form that saves checked items for the next time the DB is opened and I...

www.access-programmers.co.uk

 

[MajP]

@DDJKSA,

Yes, the code could be modified to run only after everything is selected. See attached. It does that and uses a multi select listbox

Most likely you want to use a table to hold your choices and then do something like I and @arnelgp demoed with a subform or listbox. You do not want to build lots of options, because it is time consuming and it then requires you to modify the form design whenever you add or delete a choice. With these designs you simply add the choice to the table.

Here is a third example using a subform that fakes a multivalue combobox

Faking a Multi Value Field

I have seen a few posts lately on people wanting to use multi valued fields. Most Access developers steer away from them and build their own related tables. Although they are properly normalized and work well, they can get very confusing even if you know the ins and outs. I wanted to see if I...

www.access-programmers.co.uk

Here is a fourth interface, but likely the most complicated. Still uses the general approach.

Solved - Majp Treeview

I use the treeview control extensively. Thank you very much, Majp! However, I'm stumped at how to select the nodes I want. I use the PK to select individual records, but using it for a print selection, I'd like to be able to select the top levels and all under them would be selected. Or select a...

www.access-programmers.co.uk

In general all these approaches work the same way.
You move to a record
1. Need to show all previously selected records by looping the records and loading the control
2. If checked run an insert query
3. if Unchecked run a delete query.
4. If you do it after all selections get made the code is different than doing it when it is checked and unchecked.

 

[MajP]

Maybe you could assume that i am dyslexic rather than an idiot

I assumed neither. I only assumed you posted the wrong file, I do it all the time. But you were coming down pretty hard on the OP, and I am guessing they are like "WTF is going on," especially after @DDJKSA was so polite

Thanks Pat, appreciate the help and your time.

Instead of saying "what is this crap supposed to show?"

 

[DDJKSA]

Thanks again everyone, all great suggestions. Really appreciate all the time you guys are giving me.

DDJ

 

[DDJKSA]

Maybe you could assume that i am dyslexic rather than an idiot given that I specified the name of the form and how to interpret the code. I did indeed post the wrong file.

If the OP wants it, I will post the actual file since it includes the example of how to use a query to append a set of records at one time.

Thanks Pat. I did have a quick look at your original DL but couldn't really figure it out so makes sense it was the wrong file. I would appreciate the correct one if could could UL it. Even if I don't use it now it'll be handy for future projects.

Cheers

DDJ

 

[DDJKSA]

All of the experts who answered the original post assumed incorrectly that the OP knew what he was talking about and helpfully jumped right in to solve the red herring. So, the "wake up" comment was directed at all of you rather than the OP. Arnel got the point at least and looked again even though he didn't care for my comment. So, if you notice, in the next comment, I complemented Arnel and very politely and completely explained the actual solution. Then I attempted to post a sample that showed how to actually solve the problem. So, yes, I was annoyed with the OP for completely ignoring my helpful comments. He never looked at the sample or he would have asked WHAT are you talking about? None of that is in the file that was attached.

So, if the OP wants the solution I offered, he can ask and I will post it. Otherwise I'm done.

Hi Pat

I DID look at the sample you posted but couldn't see how it applied to my post and, being a raw noob, assumed it would involve too much work to interpret and adapt, especially when @arnelgp solution works so well. Your very first comment was perhaps intended by you as helpful but was certainly not received as such. You're all taking your valuable spare time to help me out and I thank each and every one of you. No hard feelings.

Cheers

DDJ

 

[DDJKSA]

Hi Pat

You didn't even notice that it bore no resemblance to what my comment said???? All you needed to do was to ask. If you think that arnel's solution solves your problem then you're right. I cannot help you. Good luck.

Not sure why you're so angry, is it because you posted the wrong file, I only looked at it briefly deciding it was too difficult to see how it pertained to my situation, I adopted a different solution or you feel you answered my original post to the letter and others didn't? Do you remember when you first started using Access? Whose help would you have responded more positively to? I did ask for the correct file a few days ago but you never posted it so you've clearly taken the hump with me for some reason. Have a great weekend anyway.

DDJ