Actually, I made a error: right now, both fields are going to get set to
the
same value. I'd meant to say
"AND Department = '" & Me!lstEmployees.Column(1, varEntry) & "'"
(since, as I said initially, I assumed Name was in column 1 of the list
box,
and department was in column 2. When I say "column 1" and "column 2"
here,
I'm talking in terms of how a human would count them. I was not assuming
that there was a "column 0".)
That error makes no sense, since the semi-colon is always optional in
Access
SQL statements.
Try:
strSQL = "INSERT INTO data (kursus) " & _
"VALUES('" & Me![Text2] & "') " & _
"WHERE [Navn] = '" & Me!List5.Column(0, varEntry) & "' " & _
"AND Afd = '" & Me!List5.Column(1, varEntry) & "';"
or, if you do have an ID as the first column of your list box,
strSQL = "INSERT INTO data (kursus) " & _
"VALUES('" & Me![Text2] & "') " & _
"WHERE [Navn] = '" & Me!List5.Column(1, varEntry) & "' " & _
"AND Afd = '" & Me!List5.Column(2, varEntry) & "';"
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi Douglas,
Thanks alot for your quick answer.
I have renamed the columns to fit my own language (should also avoid
conflicting names), and have this now:
Private Sub Command4_Click()
Dim strSQL As String
Dim varEntry As Variant
For Each varEntry In Me!List5.ItemsSelected
strSQL = "INSERT INTO data (kursus) " & _
"VALUES('" & Me![Text2] & "') " & _
"WHERE [Navn] = '" & Me!List5.Column(0, varEntry) & "' " & _
"AND Afd = '" & Me!List5.Column(0, varEntry) & "'"
DoCmd.RunSQL strSQL
Next varEntry
End Sub
--------
I also see that you wrote that column numbering starts at 0, so did you
assume I have an ID column as my first column?
I cant really see how you choose columns up in that code (other than
the
named ones), but im sure you know what you're doing
-- When i run this, it gives me a Runtime Error: 3137, "Missing
semicolon
(
at end of SQL statement".
Now i tried to insert a semicolon pretty much everywhere, but i cant
get
rid
of that error. Kinda hate being so stupid, but I just cant figure it
out.
Thanks again.
"Douglas J. Steele" skrev:
Assuming your listbox has Name and Department as columns 1 and 2
respectively (and that Name and Department are text fields in table)
and
that your Listbox is named lstEmployees, try something like:
Private Sub Command4_Click()
Dim strSQL As String
Dim varEntry As Variant
For Each varEntry in Me!lstEmployees.ItemsSelected
strSQL = "INSERT INTO data (Course) " & _
"VALUES('" & Me![CourseTxtbox]) & "') " & _
"WHERE [Name] = '" & Me!lstEmployees.Column(0, varEntry) & "' "
& _
"AND Department = '" & Me!lstEmployees.Column(0, varEntry) & "'"
DoCmd.RunSQL strSQL
Next varEntry
End Sub
Note that I've put the field name Name in square brackets: Name is a
reserved word, and hence isn't a good choice for your own purposes.
(for
a
good discussion on what names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html) Note, too, that the
Column
collection starts numbering columns at 0.
Incidentally, you might find it better to use
CurrentDb.Execute strSQL, dbFailOnError
This has the benefits that a trappable error will be raised if
something
goes wrong with running the SQL and you won't get the "About to insert
1
record..." nonsense. Tests have also shown it may be a little bit
quicker.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi,
I have a pretty simple setup with a table, having 3 columns:
Name
Department
Course
Now, the first two columns is filled, with the employees (name &
department).
On my form I have a text box, a list box and a command button.
The list box shows all the records (names & departments), and has
multiple
selections enabled. What i want is to be able to select 5 names, and
then
type something in the text box, and then have this word (in the text
box)
written in the third column (Course) on all 5 records on the press
of a
command button.
Is this possible?
So far i got this for the command button(in code):
-----------------
Private Sub Command4_Click()
Dim test123 As String
test123 = "INSERT INTO data (Course) VALUES(
Forms![Mainform]![CourseTxtbox]
)"
DoCmd.RunSQL test123
End Sub
---------------------
What i need now is to insert it to the correct records...
I hope some of you amazingly skilled people can help me, I couldnt
find
anything about this when searching.
Thanks in advance.
