I need to append a table from a multiselect listbox on a form (1 Viewer)

[Ingeneeus]

I've been been combing this forum for a solution since Friday. I was hoping to scavenge some useful bits of code that I could twist to my own evil ends, but I think I've hit a dead end. There are several posts that sort of touch on what I'm trying to do, but most of the scenarios that have been addressed are a bit more complicated than my little conundrum, and I don't seem to be able to whittle them down.

I have a table that I need to append. The table, which is called Academy Awards x, has the following columns: ID (which is an Autonumber), Ceremony, Award Year, Award Category, and Title. Because of the nature of the table, it is possible (likely, actually) that one Title will appear on multiple rows, as the title will have multiple Award Categories. I am attaching a screencap named TableAcademyX for clarity.

To Append this table, I've created a form called xAppendAcademy. The form has three unbound TextBoxes: txtTitle, txtCeremony, and txtYear, which correspond to the columns on my table. The person using the form will enter the appropriate information into those fields. I managed to successfully create an Append Query which adds those fields to my table. (QueryAppend.jpg attached)

So far, so good, except that this is where I get stuck.
I need some way to Append multiple Award Categories to the table. It seems like the best way to do this is to use a multiselect ListBox, so I placed one called ListAwardCategory on the form. It's an unbound box with the various Award Categories in it. Right now, I have the multiselect property set to "Simple," so my user can select multiple award categories.

I thought at first I could point the Award Category field in the Append query at this ListBox, but that was a no-go. I then had the idea that I could create a hidden TextBox, make the Control Source property "=ListAwardCategory.column(0)", and point the Append query at that. This works, but only if there's is not more than one item selected in the ListBox (which kind of defeats the purpose).

I think I need some kind of a code solution, which is supported by most of the postings I've found here that relate to this issue.

I could use a Command Button, with an On Click event that runs some kind of a loop. I need something that says take each item in ListAwardCategory (me.ListAwardCategory.ItemsSelected?), insert it into table Academy Awards x, and fill the Ceremony, Award Year, and Title fields from the appropriate TextBox on the xAppendAcademy form. And then go back and do it again until all the items in ListAwardCategory have been filled.

This is where I'm at. I think I'm close, but lack the nuts-and-bolts knowledge to make this thing work.

I'm not really close, am I?

 

[pbaldy]

Is this what you're after?

http://www.baldyweb.com/MultiselectAppend.htm

 

[Ingeneeus]

Many thanks, pbaldy.

I downloaded the sample database and took a look at the code for the On Click [event procedure].

I used it as a template for my own, substituting the names of my fields and tables, and it worked!

Mostly.

I find I have a problem because one of the columns (Award Category) in my table has a two-word name (well, more than one, actually).

Here's the adjusted code:
Set db = CurrentDb()
Set rs = db.OpenRecordset("Academy Awards x", dbOpenDynaset, dbAppendOnly)

'add selected values to table
Set ctl = Me.ListAwardCategory
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs!Award Category = ctl.ItemData(varItem)
rs!Title = Me.TxtTitle
rs.Update
Next varItem

I get an error message which says "Compile Error: Expected:="
If I go into the table and change the column heading to AwardCategory and use rs!AwardCategory = ctl.ItemData(varItem), it works perfectly.

I really don't want to do that, though, as I have numerous other forms and reports that expect that column to be "Award Category" and not "AwardCategory," and the prospect of making changes in all of them is not one I approach with eagerness.

I'm hoping there is some way to make the code you supplied work with a space in the fieldname. I tried using rs!"Award Category" = ctl.ItemData(varItem), but that gave me "Compile Error: Invalid use of property, with "rs!" highlighted. rs!(Award Category) = ctl.ItemData(varItem) yielded "Compile Error: Expected: list separator or)" with the word "Category" highlighted. It didn't like rs!("Award Category") =ctl.ItemData(varItem) much either.

I know this is my fault for badly-named table headings, but I was young and foolish back then Is there a work-around that doesn't entail me changing the column names?

 

[Ingeneeus]

Um . . . nevermind.

'add selected values to table
Set ctl = Me.ListAwardCategory
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs![Award Category] = ctl.ItemData(varItem)
rs!Title = Me.TxtTitle
rs.Update
Next varItem

Seems to work just fine.
Thank you again for your solution, pbaldy!

 

[pbaldy]

Sorry, been on the road. Glad you got it sorted out.

 

[Ingeneeus]

Paul, perhaps I could prevail upon for one further bit of advice;

I have another table that I need to append with information from this same form. That table is named Films - Joined.

What I need to do is -- at the same time I'm appending the Academy Awards x table from my xAppendAcademy form using the code above -- Add a row to the Films - Joined table which takes information from the txtTitle textbox on the form. I figured out how to do it with a DoCmd.RunQuery statement to run an Append Query, but I'd like to do it from the same Sub() without going to an outside Query.

From what I can gather, what I need is a DoCmd.RunSQL statement which inserts a row into the Films - Joined table and fill in the value of the txtTitle box from the form.

I tried the following, which I cobbled together from various sources: DoCmd.RunSQL "INSERT INTO Films - Joined ([Title]) VALUES forms!xAppendAcademy!txtTitle," but I keep getting error messages, so I must not be doing something right.
I thought maybe I'd need brackets around the source [Forms]![xAppendAcademy]![txtTitle], but Access doesn't seem to like that much, either.

I've seen some more, uh, involved examples which seem to want me to do it in multiple parts:

Dim SQL as String
SQL_X = "INSERT INTO Films - Joined ([Title]) VALUES forms!xAppendAcademy!txtTitle,"
DoCmd.RunSQL "SQL_x"

I get errors there, too -- I think possibly because we've used the Dim SQL as String statement in another part of the Sub() ???

Any light you can shed on this . . .

 

[pbaldy]

Well, you certainly wouldn't want to declare the variable if it already has been. You'll also need brackets around the table name, due to the inadvisable spaces and symbol. I'm fairly sure the VALUES clause requires parentheses around the value(s). Finally, you wouldn't want quotes around the variable name in the RunSQL line.

Personally I'd concatenate the form value into the string, but RunSQL might not require it.

 

[Ingeneeus]

Well, you certainly wouldn't want to declare the variable if it already has been. You'll also need brackets around the table name, due to the inadvisable spaces and symbol. I'm fairly sure the VALUES clause requires parentheses around the value(s). Finally, you wouldn't want quotes around the variable name in the RunSQL line.

Personally I'd concatenate the form value into the string, but RunSQL might not require it.

Wow . . . I so don't know how to do that. Sorry -- the scope of my lack of knowledge is expansive

 

[pbaldy]

This type of thing, presuming Title is a text data type:

SQL_X = "INSERT INTO [Films - Joined] ([Title]) VALUES('" & forms!xAppendAcademy!txtTitle & "')"

 

[Ingeneeus]

This type of thing, presuming Title is a text data type:

SQL_X = "INSERT INTO [Films - Joined] ([Title]) VALUES('" & forms!xAppendAcademy!txtTitle & "')"

Thanks, Paul -- I appreciate the help and the patience! I will give this a try when I get into the office

 

[pbaldy]

No problem. The delimiters can get tricky. Jason wrote a FAQ on the topic here:

http://www.baldyweb.com/BuildSQL.htm

 

[Ingeneeus]

Success!
Between this: SQL_X = "INSERT INTO [Films - Joined] ([Title]) VALUES('" & forms!xAppendAcademy!txtTitle & "')" and this: you wouldn't want quotes around the variable name in the RunSQL line I am in business!

You have been a tremendous help.

1. I'm happy
2. My boss is happy
3. Loop back to #1

It's going to be a good day!

 

[pbaldy]

Excellent! Glad we got it sorted out.