The query in #1 is not well thought out. It can be shortened to the following formulation:
SQL:
INSERT INTO Individual_Spiritual_Gift_Totals ( Survey_Taker_First_Name )
SELECT "Evans" AS Survey_Taker_First_Name
FROM Individual_Spiritual_Gift_Totals
It means that the term "Evans" is added multiple times to the table. The number of appends corresponds to the record number of Individual_Spiritual_Gift_Totals. If necessary, these records are also reproduced, namely if there are multiple "Evans" in Spiritual_Gift_Totals.
Second note: To add exactly one record (VALUES variant), you can also use a recordset with AddNew. The syntax is much simpler and less error-prone because no data type-dependent formatting is required. For a multiple append you then use a loop (which everyone immediately recognizes and understands).
if you look at the arguments in the code you'll see one argument for eDelimiterType ( just the name I gave it). When you type that argument it gives you the choices listed in the Enum.
If you try it you'll see. Put both the enum and the dlmt procedure in a standard module. Then in the immediate window
type ? dlmt("xyz" , and you will see the list when you enter the comma.
Code:
Public Function Dlmt(objIN As Variant, Optional Delimiter As eDelimiterType = 1) As Variant
this is the full code module with 2 enums
Code:
Public Enum eDelimiterType
NoDelimiter = 0
DoubleQuotes = 1
Octothorpes = 2
SingleQuotes = 3
End Enum
Public Enum eSeperatorType
Comma = 0
Pipe = 1
SemiColon = 2
Tilde = 3
NewLine = 4
End Enum
' ----------------------------------------------------------------
' Procedure Name: fGetLbx
' Purpose: Get array of item in a multiselect listbox
' Procedure Kind: Function
' Procedure Access: Public
' Parameter lbx (ListBox): Your listbox object (ie. Me.MyList)
' Parameter intColumn (Integer): The listbox column to return
' Parameter Seperator (eSeperatorType): character seperating the array values
' Parameter Delimiter (eDelimiterType): Delimiters for array values (ie.Double Quotes or Octothorpes)
' Return Type: Variant
' Author: Moke123
'
' **** NOTE **** Returns Null if no items selected. Use NZ() in calling code to handle nulls
'
' ----------------------------------------------------------------
Public Function fGetLbx(Lbx As ListBox, Optional intColumn As Integer = 0, Optional Seperator As eSeperatorType = 0, _
Optional Delimiter As eDelimiterType = 0) As Variant
On Error GoTo fGetLbx_Error
Dim strlist As String, varSelected As Variant, DeLimit As Variant, SepChar As String
Select Case Delimiter
Case 0
DeLimit = Null
Case 1
DeLimit = Chr(34) 'Quotes
Case 2
DeLimit = Chr(35) 'Octothorpes
Case 3
DeLimit = Chr(39) 'SingleQuotes
End Select
Select Case Seperator
Case 0
SepChar = Chr(44) 'comma
Case 1
SepChar = Chr(124) 'pipe
Case 2
SepChar = Chr(59) 'semicolon
Case 3
SepChar = Chr(126) 'tilde
Case 4
SepChar = vbNewLine 'newline
End Select
If Lbx.ItemsSelected.Count > 0 Then
For Each varSelected In Lbx.ItemsSelected
If Lbx.Column(intColumn, (varSelected)) <> "" Then
If strlist <> "" Then
strlist = strlist & SepChar & DeLimit & Lbx.Column(intColumn, (varSelected)) & DeLimit
Else
strlist = DeLimit & Lbx.Column(intColumn, (varSelected)) & DeLimit
End If
End If
Next varSelected
fGetLbx = strlist
Else
fGetLbx = Null
End If
On Error GoTo 0
Exit Function
fGetLbx_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fGetLbx, line " & Erl & "."
End Function
Public Function Dlmt(objIN As Variant, Optional Delimiter As eDelimiterType = 1) As Variant
'returns the passed in value wrapped with the selected delimiter
On Error GoTo Dlmt_Error
Dim DeLimit As String
Select Case Delimiter
Case 0
DeLimit = Null
Case 1
DeLimit = Chr(34) 'Quotes
Case 2
DeLimit = Chr(35) 'Octothorpes
Case 3
DeLimit = Chr(39) 'SingleQuotes
End Select
Dlmt = DeLimit & objIN & DeLimit
On Error GoTo 0
Exit Function
Dlmt_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Dlmt, line " & Erl & "."
End Function
Are you trying to add a new duplicate record, copying the data from the existing record where Survey_Taker_First_Name = 'Evans' ?
Or are you trying to update the existing record where Survey_Taker_First_Name = 'Evans' with new values for FirstName, MiddleName, LastName and TelephoneNumber?
Or are you trying to do something else?
The above query doesn't make much sense, unless you are specifically trying to to insert new records in to your table.
If you have more than one existing record where Survey_Taker_First_Name = 'Evans' then you will insert the same number of new records. ie, if you had five existing records in table Spiritual_Gift_Totals then the statement will add five new records using this query.
I doubt that's what you intend! (though let us know if it is what you intended!)
Also, there is no real utility to using the INSERT ... SELECT ... syntax since the new record's Survey_Taker_First_Name will be 'Evans' as well. You may as well specify it directly using the INSERT ... VALUES ... syntax.
But all this leads me to suspect you are actually trying to update an existing record.
Please explain in words what you are trying to achieve.
Thank you for your patience ! ! ! . . . You're absolutely correct. I'm am confused, but I'm determined to find the best and easiest solution to my problem. As see, I've been searching the internet for a solution. Currently, I'm trying to retrieve one record associated with the current user's survey answers. This one record with be displayed on a form. My latest attempt is to extract this one record from the summary table. This summary table is update by the same process (via a form button). I recently discovered that the "DoCmd.OpenForm" command. I'm currently working the below "DoCmd.OpenForm" command. I described the form names and table for this process below.
"Form_Spiritual_Gift_Totals" is called by "Questions"
Form Name is "Form_Spiritual_Gift_Totals"
Data Source for "Form_Spiritual_Gift_Totals" is the table name "Spiritual_Gift_Totals"
Field Name is "Survey_Taker_First_Name" in the "Spiritual_Gift_Totals" table
"[Forms]![Questions]![First_Name]" has the value for the DoCmd.OpenForm command
I hope this makes a little more sense in what I'm trying to accomplish.
What happens when there are 2 Toms? Do you have a table of Survey Takers? A SurveyTakerID as a primary key ?
Normally we would expect a table of survey takers. This table would have a primary key, FirstName, LastName, and whatever other info you may need for that person. Your other tables would have a foreign key to identify that survey taker.
your form opening code would look like (no quotes needed for numerical datatype)
Are "Spiritual_Gift_Totals" and "Individual_Spiritual_Gift_Totals" similiar or exact copies of each other?
Are you splitting this out just for the report? You probably don't need to if your data is set up correctly.
An overview of what this database is for would also be helpful. Its a little confusing as it appears you are tracking gift totals (ie. donations) but refer to questions and survey takers.
If this code is within a forms module you can use the ME keyword along with the name of the control. Intellisense will kick in an make writing it easier and without spelling errors.
Are "Spiritual_Gift_Totals" and "Individual_Spiritual_Gift_Totals" similiar or exact copies of each other?
Are you splitting this out just for the report? You probably don't need to if your data is set up correctly.
Getting delimiters right can sometimes be difficult in complicated code. It often helps to have "Helper" functions like the one Josef P. has in post 5.
I sometimes use the following function. I have a public enum because it it used in a couple procedures.
Code:
Public Enum eDelimiterType
NoDelimiter = 0
DoubleQuotes = 1
Octothorpes = 2
SingleQuotes = 3
End Enum
Code:
Public Function Dlmt(objIN As Variant, Optional Delimiter As eDelimiterType = 1) As Variant
'returns the passed in value wrapped with the selected delimiter
On Error GoTo Dlmt_Error
Dim DeLimit As String
Select Case Delimiter
Case 0
DeLimit = Null
Case 1
DeLimit = Chr(34) 'Quotes
Case 2
DeLimit = Chr(35) 'Octothorpes
Case 3
DeLimit = Chr(39) 'SingleQuotes
End Select
Dlmt = DeLimit & objIN & DeLimit
On Error GoTo 0
Exit Function
Dlmt_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Dlmt, line " & Erl & "."
End Function
Do you have a table of Survey Takers? A SurveyTakerID as a primary key ?
No, Each Survey Takers will be will a new Survey Takers. Their name (first, middle, and last),
address, location . . . etc. will be stored in a record for the Survey Takers. Also, the Survey
Takers will only be allowed to take the survey once.
SurveyTakerID as a primary key ? No, there no primary keys thus far.
The DoCmd.OpenForm statement below needs to extract the current Survey Takers data just entered
and provide data for "Form_Spiritual_Gift_Totals" and append to the "Spiritual_Gift_Totals"
table in the database.
Are "Spiritual_Gift_Totals" and "Individual_Spiritual_Gift_Totals" similiar or exact copies
of each other?
Yes, the structure is the same. As well, the field names are identical. The differences is that
"Spiritual_Gift_Totals" has all Survey Takers' data and the "Individual_Spiritual_Gift_Totals"
has only the current Survey Takers data for the form ("Form_Spiritual_Gift_Totals") to be
presented and will be allowed to print the Survey Takers' information.
Are you splitting this out just for the report?
For both the the form "Form_Spiritual_Gift_Totals" and a report.
This database is not tracking donations, but calculating spirtual gifts totals dtermined by
the acuumalation of numbers (in response to a question) entered by the Survey Takers. I believe
these two tables (i.e. "Spiritual_Gift_Totals" and "Individual_Spiritual_Gift_Totals") is the
information needed to produce the needed results.
I hope this answers all of your questions. Thanks ! ! !
Are "Spiritual_Gift_Totals" and "Individual_Spiritual_Gift_Totals" similiar or exact copies
of each other?
Yes, the structure is the same. As well, the field names are identical. The differences is that
"Spiritual_Gift_Totals" has all Survey Takers' data and the "Individual_Spiritual_Gift_Totals"
has only the current Survey Takers data for the form ("Form_Spiritual_Gift_Totals") to be
presented and will be allowed to print the Survey Takers' information.
