how to double click a list box item to add to subform

[Kathy R.]

I would like my user to be able to add those attending an event to a
subform by doubleclicking a member in a listbox on the main form.

Main form (frmAttendance) shows event information
EventName, EventDate, from tblEvent
and MemberList - an unbound listbox which includes IndID and LastName
from tblIndividual

Subform (sfrAttendee) is a continuous form showing which individuals
(AtIndID, LastName) are attending that particular event. Master/Child
link is EventID/AtEventID. Record source is qryAttendance which
includes tblIndividual and tblAttendance.

The form/subform is working. What I need help with is the code to put
in the doubleclick event to enter the IndID (first column in the unbound
listbox) into the AtIndID field in the subform, and then have the
subform update that record and move to the next blank row in the
continuous form.

The listbox is actually based on a SQL statement with a custom sort
order, but I figured I’d start with baby steps using just the basic
fields so that I can actually see how it works, before I muck it up with
something more complicated. Thank you in advance for your help. I
really appreciate all the help that you folks “in the know” devote to
these forums!

Kathy R.

Tables used:

tblIndividual
IndID (primary key, autonumber)
LastName

tblAttendance
AttendID (primary key, autonumber)
AtIndID (foreign key)
AtEventID (foreign key)

tblEvent
EventID (primary key, autonumber)
EventName
EventDate

one Individual can attend many Events
one Event can have many Individuals attending

 

[Mike Painter]

I would like my user to be able to add those attending an event to a
subform by doubleclicking a member in a listbox on the main form.

DoCmd.RunSQL "INSERT INTO tblAttendance ([MemberID],... VALUES
(Me.FieldFromForm....);”
Then requery the subform.
Doing it in the subform with a combo box would be faster that scrolling
through a long list of names.

 

[Kathy R.]

Mike, thanks for pointing me in the right direction. I do have a combo
box in the subform that is working, but I think my users will prefer the
list box. Being the visual/hands on learner that I am, I have to see it
working before I say "duh, that wasn't such a good idea after all." So,
having finally found some time to get back to this, I'm afraid I'm still
not doing something quite right.

I started simply by trying it with just one field and used

DoCmd.RunSQL "INSERT INTO tblAttendance ([AtEventID]) VALUES (Me.EventID);"

where "EventID" is an autonumber and "AtEventID" is a long integer

When I doubleclick on a entry in the listbox, an "Enter Parameter Value"
window opens asking for Me.EventID. Not quite what I want to happen
Is this because the field contains a number and not text? Is there
some number syntax that I'm missing? By the way, if I enter a number in
the parameter window it will append to the table.

Once I get that, I can move on to the second number I need to append to
the table, which is the IndID in the first column of the list box. How
do I reference the first column in the MemberList list box?

Thank you again for your help, it is very much appreciated!

I would like my user to be able to add those attending an event to a
subform by doubleclicking a member in a listbox on the main form.

DoCmd.RunSQL "INSERT INTO tblAttendance ([MemberID],... VALUES
(Me.FieldFromForm....);”
Then requery the subform.
Doing it in the subform with a combo box would be faster that scrolling
through a long list of names.

Main form (frmAttendance) shows event information
EventName, EventDate, from tblEvent
and MemberList - an unbound listbox which includes IndID and LastName
from tblIndividual

Subform (sfrAttendee) is a continuous form showing which individuals
(AtIndID, LastName) are attending that particular event. Master/Child
link is EventID/AtEventID. Record source is qryAttendance which
includes tblIndividual and tblAttendance.

The form/subform is working. What I need help with is the code to put
in the doubleclick event to enter the IndID (first column in the
unbound listbox) into the AtIndID field in the subform, and then have
the subform update that record and move to the next blank row in the
continuous form.

The listbox is actually based on a SQL statement with a custom sort
order, but I figured I’d start with baby steps using just the basic
fields so that I can actually see how it works, before I muck it up
with something more complicated. Thank you in advance for your help.
I really appreciate all the help that you folks “in the know” devote
to these forums!

Kathy R.

Tables used:

tblIndividual
IndID (primary key, autonumber)
LastName

tblAttendance
AttendID (primary key, autonumber)
AtIndID (foreign key)
AtEventID (foreign key)

tblEvent
EventID (primary key, autonumber)
EventName
EventDate

one Individual can attend many Events
one Event can have many Individuals attending

 

[Mike Painter]

Mike, thanks for pointing me in the right direction. I do have a
combo box in the subform that is working, but I think my users will
prefer the list box. Being the visual/hands on learner that I am, I
have to see it working before I say "duh, that wasn't such a good
idea after all." So, having finally found some time to get back to
this, I'm afraid I'm still not doing something quite right.

I started simply by trying it with just one field and used

DoCmd.RunSQL "INSERT INTO tblAttendance ([AtEventID]) VALUES
(Me.EventID);"
where "EventID" is an autonumber and "AtEventID" is a long integer

If [AtEventID] is an autonumber then you don't need to insert it, it is done
automatically.

When I doubleclick on a entry in the listbox, an "Enter Parameter
Value" window opens asking for Me.EventID. Not quite what I want to
happen Is this because the field contains a number and not text? Is
there some number syntax that I'm missing? By the way, if I enter a
number
in the parameter window it will append to the table.

"INSERT INTO tblAttendance ([AtEventID]) VALUES " & (Me.EventID)&";"
if it is a number.
If not after VALUES use '" (single quote and doublequote) and Doublequote,
singlequote, semicolan double quote at the end.
I often use strQry = "INSERT INTO tblAttendance ([AtEventID]) VALUES " &
(Me.EventID)&";"

Then instead of DoCmd runSql strSQL I place
msgbox strSQL to make sure it looks right.

Once I get that, I can move on to the second number I need to append
to the table, which is the IndID in the first column of the list box.

Are you *appending* two records?
If not all can be done with the single insert.

How do I reference the first column in the MemberList list box?

When you setup the list box you determine what column is bound, usually that
is column zero and this is returned so Me.YourListbox would return that
value.
If not then it is zero based so column 2 is col(1), etc (This makes sense if
you are old enough to remember when 4000 bytes of memory was a huge amount.)

http://office.microsoft.com/en-us/access/HP051877861033.aspx has info.

 

[Kathy R.]

If [AtEventID] is an autonumber then you don't need to insert it, it is done
automatically.

Both AtEventID and EventID are long integers.

"INSERT INTO tblAttendance ([AtEventID]) VALUES " & (Me.EventID)&";"
if it is a number.
If not after VALUES use '" (single quote and doublequote) and Doublequote,
singlequote, semicolan double quote at the end.

I tried using both of these statements and got "Syntax error in INSERT
INTO statement." Here's some others that I tried and their results:

DoCmd.RunSQL "INSERT INTO tblAttendance ([AtEventID]) VALUES " &
(Me.EventID) & ";" 'from Mike Painter - syntax error

DoCmd.RunSQL "INSERT INTO tblAttendance ([AtEventID]) VALUES '" &
(Me.EventID) & "';" 'syntax error

DoCmd.RunSQL "INSERT INTO tblAttendance ([AtEventID]) VALUES " &
(Me.[EventID]) & ";" 'syntax error

DoCmd.RunSQL "INSERT INTO tblAttendance ([AtEventID]) VALUES (9);"
'works

DoCmd.RunSQL "INSERT INTO tblAttendance ([AtEventID]) VALUES
(Me.EventID);" 'enter parameter value

DoCmd.RunSQL "INSERT INTO tblAttendance ([AtEventID]) VALUES &
(Me.EventID)& ;" 'syntax error

DoCmd.RunSQL "INSERT INTO tblAttendance ([AtEventID]) VALUES " & "
(Me.EventID) " & " ;" 'reserved error

DoCmd.RunSQL "INSERT INTO tblAttendance ([AtEventID]) VALUES
"(Me.EventID)" ;" 'expected end of statement

DoCmd.RunSQL "INSERT INTO tblAttendance ([AtEventID]) VALUES " &
(Me!EventID) & ";" 'syntax error

I can use the Me.EventID in an INSERT INTO statement, can't I? Can you
please doublecheck the syntax? I would really appreciate it.

Are you *appending* two records?
If not all can be done with the single insert.

Thank you for the info on the columns. Both fields will be appended to
one record. I am aware that I can do it in one INSERT INTO statement.
But, as you can see, I am having trouble with just the first part of it
and knew that the columns were slightly more complicated. So I figured
I'd iron out the simple stuff first before I tried adding the second field.

If not then it is zero based so column 2 is col(1), etc (This makes

sense if you are old enough to remember when 4000 bytes of memory was a
huge amount.)

I started on a PC Junior with the OS on a 5 1/4" floppy. I didn't
realize that lack of memory was the reason we start at zero though.
((goes off to wiki the capacity of a 5 1/4" floppy)) Now you've
distracted me ;-)

Kathy R.

 

[John W. Vinson]

DoCmd.RunSQL "INSERT INTO tblAttendance ([AtEventID]) VALUES " &
(Me.EventID) & ";" 'from Mike Painter - syntax error

You need to build up the SQL string from pieces: text literals in quotes, and
a value from the form control. The parentheses need to be part of the text
literals. Try

DoCmd.RunSQL "INSERT INTO tblAttendance ([AtEventID]) VALUES (" &
Me.EventID & ");"

 

[Kathy R.]

BINGO! Thank you John, that did the trick. I really appreciate yours
and Mike's help.

Kathy

DoCmd.RunSQL "INSERT INTO tblAttendance ([AtEventID]) VALUES " &
(Me.EventID) & ";" 'from Mike Painter - syntax error

You need to build up the SQL string from pieces: text literals in quotes, and
a value from the form control. The parentheses need to be part of the text
literals. Try

DoCmd.RunSQL "INSERT INTO tblAttendance ([AtEventID]) VALUES (" &
Me.EventID & ");"