Loop INSERT INTO statement (1 Viewer)

[krissy21]

Hello All!

I have a form where a person can create/enter a new training CodeID and select the PositionID's, from a listbox, that would need that particular training. All of my vba code works except the following part which is the loop:

Private Sub cmdSaveandAdd_Click()
DoCmd.SetWarnings False
Dim i As Variant, lstChosen As String, StrInsert As String
   
For Each i In Me.List87.ItemsSelected
        lstChosen = Me.List87.ItemData(i) 
        StrInsert = "INSERT INTO [Training per Position]([CodeID],[Position ID]) VALUES ('" & Me!txtCodeID & "','" & lstChosen & "')" 
        DoCmd.RunSQL StrInsert
 Next i
 
DoCmd.SetWarnings True

What I am trying to do is transfer the information to a table. The problem I am having, is that when more than one PositionID is selected, it doesn't enter any of the information. I'd like the information to transfer as such:

CodeID / PositionID
1.12 / AP
1.12 / D
1.12 / SS
etc.

What am I doing wrong? Thanks for all the help!!

 

[The_Doc_Man]

For Each i In Me.List87.ItemsSelected

Might work better as Me.List87.ItemsSelected.Count, maybe?

OR, you might want to iterate I from 0 TO Me.List87.ListCount -1 and then test the .Selected property to be True.

I am not familiar with the implied BEFORE VALUES clause that is near the end of your SQL statement for the record insertion. What is that supposed to do? I don't see "BEFORE" as a keyword for MS Access SQL, so are you talking to a non-Access back-end file, perhaps, for which there IS such a keyword?

 

[krissy21]

I'll go ahead and try the (Count) idea though I'm not sure what you mean by (0 TO ....ListCount-1). Beginning coder. Would you mind writing out the whole line so i understand what it is you mean?

The ('before values...etc) was more of a mental note that I forgot to delete prior to posting. Trial and error. So sorry for the confusion!

 

[The_Doc_Man]

OK, let's try this explanation: Access (and other Office components that also use VBA) are based on the Component Object Model (COM) which sees things - or perhaps just SHOWS you things - as collections of similar objects; i.e. all the objects in the collection are of the same type. It's like a classical array of things, but on steroids. These collections can be addressed individually in several different ways but you need to know what kind of collection you are seeing in order to address the individual parts.

One way is to use the collection's name like this (using the combo box content as an example): Me.comboname.Selected where n is the row number in the collection of rows that COULD be displayed by the combo box when you activate it. This number is zero-based meaning the first item is item 0, the second is item 1, etc., and the last item is Me.comboname.Selected(Me.comboname.ListCount - 1).

If you were going to do it this way, your For syntax would be like this:

Dim i as Long

...

For i = 0 to Me.comboname.ListCount - 1
    If Me.comboname.Selected(i) = TRUE Then
        ... (do something here relating to the selected item)
    End If
....
Next i

As it turns out, this is the syntax you would HAVE to use to step through the rows of the combo box's list of displayed items. And you would have to test each one for having been selected (if multi-select is TRUE for the combo box.) Read on to find out why.

If you were going through the form's controls one at a time for some purpose, you could have a declared generic controls (e.g. Dim ctl As Access.Control) and in THAT case you can use a variant of the FOR syntax:

For  Each ctl in Me.Controls
...
Next ctl

If you think about it, what Access is doing there is it generates your For loop for you by knowing that collections start with index 0 and end with collectionname.Count - 1 so it just builds the implied For i = 0 to collection.count - 1 - and assigns your object variable to the object that is in the collection.

The reason you cannot do a For Each in the case of the combo box is there is no clear-cut collection name for the individual rows so when you got to the "IN" for the syntax For Each object IN collection-name, you don't have a name for the collection that you can use. I.e. there is no way to refer to the collection with this syntax. Not to worry; Access & COM allow you to access the individual elements using the row number (remember, 0-based) for all the properties that are row oriented.

Here is a reference for you about combo box properties. Might be handy for you to have around, not to mention that the MSDN site has a gazillion other useful articles on just about anything you could possibly want to know.

https://msdn.microsoft.com/en-us/library/office/ff845773.aspx

 

[static]

ID's are usually numeric. You don't wrap numeric values in quotes.

For Each i In List87.ItemsSelected
    currentdb.execute "INSERT INTO [Training per Position](CodeID,[Position ID]) VALUES (" & Me!txtCodeID & "," & List87.ItemData(i)  & ")" 
Next i

 

[The_Doc_Man]

static, that would depend on the format of krissy's ID, so we don't know (from the info available whether she needs the quotes or not.)

 

[krissy21]

The [CodeID] is numeric and I tried without the brackets. It didn't work. Thanks for the idea though!!

The_Doc_Man, I've tried several more ways including what you suggested and still not giving me anything. Sometimes it won't enter any information into the table I want. Is there something else I may be doing wrong?

 

[JHB]

Post your database with some sample data, zip it, + a description how to reproduce the problem.

 

[krissy21]

https://drive.google.com/open?id=0B6jZXGf3QPC1OHd6UUc4MFROdzQ

Start the form "Add New Training", enter 1 into the CodeSecNum combo box, write "Test" in the description, and select 1 or more positions from the listbox to apply the training to. Click "Save and Add Training" and close the form. Open the table "Training per Position" and search for the newest CodeSecNum you just added (should be 1.2 since the previous was 1.19) with the positions you selected. It won't be there but in the table "TypeofTraining", you will see the new training code and description.

 

[JHB]

Sorry, I can't get access to your database, please post it here at the forum.

 

[krissy21]

JHB, I don't know any other way to post it. Did you click the down-arrow in the top right corner?

 

[krissy21]

Try this attachment......

 

[JHB]

I got it, I'll at it tomorrow.

 

[JHB]

You have some serious errors in your database.

1. You try to insert child data (table "Training per Position") before the parent data is added to the related table (table "TypeofTraining") .
2. You try to insert text in fields which have number type.
3. You're not taking care of controls which could have Null values, like "Me!txtExpDate".
4. Your table "TypeofTraining" has only one keyfield, (field CodeID), but it required two, (fields CodeID and CodeSecNum).
5. The you've a lookup field type in your table, don't use that at table level.
6. And some other bad (placed) code,

Then you're turning the warnings off, you shouldn't do that before you're 100% sure everything runs as expected, and therefore you missed some of the error message.
I've attached the corrected database, try it and report back if there are any problems.

 

[krissy21]

Wow! You are right JHB!!

1. I suppose that makes sense and I should've realized that.
2. Thought I'd set the fields to be the same.
3-5. Did some research and now I understand the reasons why that's useful.
6. I know now why those settings are there and how to use them.

Thank you so much for taking your time to look over all that!! I really mean it! I've learned a whole lot from it and will try to put those things to practice for future use as well!!

~Krissy21

 

[JHB]

..
Thank you so much for taking your time to look over all that!! I really mean it!

You're welcome, good luck with your project.

Only a last advice:
Often it helps to remove all the data from tables you're manipulate to get focus on the exact problem.
Also adding data manually can give you a point in the right direction.