Inserting text into multiple records selected in a listbox

M

MortenR

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.
 
D

Douglas J. Steele

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.
 
M

MortenR

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!)


MortenR said:
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.
Click to expand...
Click to expand...
 
D

Douglas J. Steele

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!)


MortenR said:
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!)


MortenR said:
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.
Click to expand...
Click to expand...
Click to expand...
 
M

MortenR

Hi Douglas,

I tried this one:

strSQL = "INSERT INTO data (kursus) " & _
"VALUES('" & Me![Text2] & "') " & _
"WHERE [Navn] = '" & Me!List5.Column(0, varEntry) & "' " & _
"AND Afd = '" & Me!List5.Column(1, varEntry) & "';"

I get the same mistake as before.

-- It didnt work. I tried googling it abit, and people say you cant do a
"Where" clause in an INSERT string, which makes sense to me. However I tried
replacing "INSERT INTO" with "UPDATE", but it wasnt that easy :(

Can you get the "INSERT INTO" to work? Or should it be an update?

//Morten R.

Douglas J. Steele said:
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!)


MortenR said:
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.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Sorry! Brainfart. Of course you need an Update, not an Insert Into.

strSQL = "UPDATE data " & _
"SET kursus = '" & Me![Text2] & "' " & _
"WHERE [Navn] = '" & Me!List5.Column(0, varEntry) & "' " & _
"AND Afd = '" & Me!List5.Column(1, varEntry) & "';"

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


MortenR said:
Hi Douglas,

I tried this one:

strSQL = "INSERT INTO data (kursus) " & _
"VALUES('" & Me![Text2] & "') " & _
"WHERE [Navn] = '" & Me!List5.Column(0, varEntry) & "' " & _
"AND Afd = '" & Me!List5.Column(1, varEntry) & "';"

I get the same mistake as before.

-- It didnt work. I tried googling it abit, and people say you cant do a
"Where" clause in an INSERT string, which makes sense to me. However I
tried
replacing "INSERT INTO" with "UPDATE", but it wasnt that easy :(

Can you get the "INSERT INTO" to work? Or should it be an update?

//Morten R.

Douglas J. Steele said:
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!)


MortenR said:
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.
Click to expand...
Click to expand...
Click to expand...
 
M

MortenR

Great! It works (I had to change column 0/column 1, cause i have them listed
in the listbox with department first, and then name. (and vice versa in the
table)

Thanks alot, i'd never have gotten that working without your help :)

//Morten

Douglas J. Steele said:
Sorry! Brainfart. Of course you need an Update, not an Insert Into.

strSQL = "UPDATE data " & _
"SET kursus = '" & Me![Text2] & "' " & _
"WHERE [Navn] = '" & Me!List5.Column(0, varEntry) & "' " & _
"AND Afd = '" & Me!List5.Column(1, varEntry) & "';"

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


MortenR said:
Hi Douglas,

I tried this one:

strSQL = "INSERT INTO data (kursus) " & _
"VALUES('" & Me![Text2] & "') " & _
"WHERE [Navn] = '" & Me!List5.Column(0, varEntry) & "' " & _
"AND Afd = '" & Me!List5.Column(1, varEntry) & "';"

I get the same mistake as before.

-- It didnt work. I tried googling it abit, and people say you cant do a
"Where" clause in an INSERT string, which makes sense to me. However I
tried
replacing "INSERT INTO" with "UPDATE", but it wasnt that easy :(

Can you get the "INSERT INTO" to work? Or should it be an update?

//Morten R.

Douglas J. Steele said:
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Unable to display values in 2nd list box from 1st list box selection 2
Getting Radio Buttons to work, that are inserted via AutoText 0
Creating Labels Over 3-Up Image 2
Text box visibility remains same after every record 4
Combo box selection to give Specific Information to Query 2
Combo Box that fills Text Box in form 4
Listbox questions 2
unbound listbox... how do i make the current record selected 4

Top