best way?

[brightstar]

Hi,
Its been a long while since ive plaed with access - and its showing.

ive got a list box on a form showing contact details.
i want the user to be able to select multipple selections in this box and then click a button that apends these selections to another table in the database.

it's been so long i cant even wrap my head around where to start!

ive got the multiple selcetin thing working and the right info is displayed in the list box - just cant get the button working. do i need some ADO/DAO code or just an update/append query or something?

much appreciate any help.

http://www.agonyonline.co.uk

free, anonymous advice provided by the whole world

 

[Crowley16]

there's 2 ways, you can either open up your table with a recrodset(ado/dao) and then add the selected items into the table with the recordset.add and .update or you can generate a sql insert statement and use docmd.runsql to run that sql statement.

I tend to go the sql route since making connections and opening recordsets are generally longer procedures, although there are many factors which could change that...

--------------------
Procrastinate Now!

 

[hwkranger]

I'll add my 2 cents.

1) Don't use docmd, it's just a wrapper.
2) You need to loop through your items to get which are select.
3) If you're not using SQL or ADO, you can always pass parameters to a saved query, and run it (not recommended)

Recommended example:

dim str_Tmp as string
dim str_MyString as string

str_tmp = ""
str_Mystring = ""
for i = 0 to me.list0.listcount - 1
 if me.list0.select(i) = true then
   str_Mystring = str_tmp & str_Mystring
   str_tmp = ", "
 end if
next i
  
currentproject.connection.execute "insert into TableRecievingData [Your fields] select [Your Fields] from [TableSendingData] where [YourKey] in (" & str_mystring & ")"

This was by the seat of my pants, but you get the idea.





Randall Vollen
National City Bank Corp.

 

[brightstar]

ended up with this - and it worked a treat, thanks!

str_Tmp = ""
str_MyString = ""
For i = 0 To Me.LstContacts.ListCount - 1
If Me.LstContacts.Selected(i) = True Then
str_MyString = Me.LstContacts.Column(0, i) & str_Tmp & str_MyString
str_Tmp = ", "
End If
Next i

CurrentProject.Connection.Execute "INSERT INTO TblMailGroupMembers (Mailgroup, MailGroupMember) SELECT " & Me.OpenArgs & ", TblContacts.contactid FROM [TblContacts] WHERE ([TblContacts.ContactID] in (" & str_MyString & "))"

http://www.agonyonline.com

free, anonymous advice provided by the whole world