appending listbox selections to table

[lilsincere]

evening folks... this is my first post here but i have ghosted this site for years learning.

to get to it... I have a form with a listbox of different voting demographics. I will be taking notes and i want to select various demographics that are correlated to said notes.

so far I have gather a bit of code mostly from here:

Private Sub Command371_Click()

On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM tblDemographic"

'Build the IN string by looping through the listbox
For i = 0 To lstDemographic.ListCount - 1
If lstDemographic.Selected(i) Then
If lstDemographic.Column(1, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & lstDemographic.Column(1, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [demographic] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "qryDemographic"
Set qdef = MyDB.CreateQueryDef("qryDemographic", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryDemographic", acViewNormal


'Clear listbox selection after running query
For Each varItem In Me.lstDemographic.ItemsSelected
Me.lstDemographic.Selected(varItem) = False
Next varItem

Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

End Sub

to append my results to a table I used:

MyDB.execute INSERT INTO tblNotesDemographic
SELECT *
FROM qryDemographic
MyDB.close

Is this the correct code? Where would I properly insert this, as I have tried various places throughout my code and i get all kinds of crazy syntax?

Thank you.

 

[sneuberg]

The code you post is used to allow the user to make multiple selections from a listbox and generate a query or report base on those selections. I'm don't see how that relates to:

I will be taking notes and i want to select various demographics that are correlated to said notes.

I suggest you zip your database and upload it and tell us precisely what you want the listbox to do for you.

 

[lilsincere]

The code you post is used to allow the user to make multiple selections from a listbox and generate a query or report base on those selections. I'm don't see how that relates to:



I suggest you zip your database and upload it and tell us precisely what you want the listbox to do for you.

correct it generates a query. I wanted to append that query to a table. I cannot upload the db at the moment.

 

[sneuberg]

Ok you could put the insert statement in place of the line

DoCmd.OpenQuery "qryDemographic", acViewNormal

But use the Query Designer to get it working first and use the SQL from that. Let's assume you've done that and what you got was

INSERT INTO tblNotesDemographic
SELECT * 
FROM qryDemographic

You take the new lines out, put it in a string and then include that string as the argument to the Execute statement. It would look like:

CurrentDb.Execute "INSERT INTO tblNotesDemographic SELECT * FROM qryDemographic"

or since you have a database object open

MyDB.Execute "INSERT INTO tblNotesDemographic SELECT * FROM qryDemographic"

should work too.

 

[GinaWhipp]

Hmm, thinking that INSERT statement needs some work..

                strSQL = "INSERT INTO tblNotesDemographic( Field, Field, etc...) " & _
                            "SELECT Field, Field" & _
                                "FROM qryDemographic"
                          CurrentDb.Execute strSQL, dbFailOnError

Now, if qryDemographic has a parameter you will need to make an adjustment. Post eh SQL of the query for assistance.

 

[sneuberg]

I forgot. It's best to put a dbFailOnError on these so that it will error for key violations, etc rather than just fail silently.

CurrentDb.Execute "INSERT INTO tblNotesDemographic SELECT * FROM qryDemographic", dbFailOnError

would be better

 

[lilsincere]

Hmm, thinking that INSERT statement needs some work..

                strSQL = "INSERT INTO tblNotesDemographic( Field, Field, etc...) " & _
                            "SELECT Field, Field" & _
                                "FROM qryDemographic"
                          CurrentDb.Execute strSQL, dbFailOnError

Now, if qryDemographic has a parameter you will need to make an adjustment. Post eh SQL of the query for assistance.

HA this works! with some adjustment of course but awesome!

thank you.

 

[lilsincere]

Hmm, thinking that INSERT statement needs some work..

                strSQL = "INSERT INTO tblNotesDemographic( Field, Field, etc...) " & _
                            "SELECT Field, Field" & _
                                "FROM qryDemographic"
                          CurrentDb.Execute strSQL, dbFailOnError

Now, if qryDemographic has a parameter you will need to make an adjustment. Post eh SQL of the query for assistance.

so i was playing around with this particular script, lets say i wanted to append a temporary value into field2 with the list of demographics.

would I write:

Dim valuename As TempVar

tempvar!valuename = 8

would i add the tempvar in the select line?

 

[sneuberg]

You would concatenate it in. Assuming Field2 is a number then something like:

strSQL = "INSERT INTO tblNotesDemographic( Field, Field2, etc...) " & _
              "SELECT Field, " & tempvar!valuename  & _
               " FROM qryDemographic"
CurrentDb.Execute strSQL, dbFailOnError

 

[Cronk]

Folks, what's the purpose of generating qryDemographic?

Why not just
'--If All is selected, set strWhere=""

strSQL = "INSERT INTO tblNotesDemographic( Field, Field, etc...) " & _ "SELECT Field, Field" & _ "FROM tblDemographic " & strWhere CurrentDb.Execute strSQL, dbFailOnError

 

[lilsincere]

Folks, what's the purpose of generating qryDemographic?

Why not just
'--If All is selected, set strWhere=""

strSQL = "INSERT INTO tblNotesDemographic( Field, Field, etc...) " & _ "SELECT Field, Field" & _ "FROM tblDemographic " & strWhere CurrentDb.Execute strSQL, dbFailOnError

i was under the impression that it was to test or see the results of what was selected from the list box?