How to records to tables from a listbox?

[asp3232]

Hi all,

This is related to a different thread, but this topic better represents what's left to do...

I've got a many-to-many relationship defined for issues and causes, and am building a nice interface to map which issues are associated with which causes.

I've got a form set up with a combo box which selects the issue, and a multi-select listbox with all of the issues in it. Multiple causes can be selected for each issue. So far, so good...

What I need help with is getting this data back into the linking table (2 fields, IssueRef and CauseRef, both numeric). If records already exist in the linking table, how do I update them, and if I need a new record, how do I create it and get the right data into it?

Thanks for your help!

asp

 

[FoxProProgrammer]

hi asp,

If I understood your issue correctly, you will need to enumerate through the items selected in the list box, and store the data for each row. The following code assumes that IssueRef is in column 1 of the list box, and CauseRef is in column 2.

Dim i as Integer
Dim db as Database
Dim rs as Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", dbOpenDynaset)

For i = 0 To nameoflistbox.Listcount - 1
If nameoflistbox.Selected(i) = True Then
rs.FindFirst <criteria> ' criteria identifies the record to look for
If rs.NoMatch Then ' No matching record; add new one
rs.AddNew
Else ' Update existing record
rs.Edit
End If

rs!IssueRef = nameoflistbox.Column(1)
rs!CauseRef = nameoflistbox.Column(2)
rs!otherfield = ....
rs.Update

rs.Close
db.Close

I don't understand where the data in the list box comes from. How can it be in the list box if it isn't in the table? What are you using to build the list box? Maybe I am confused? It happens a lot! lol At any rate, the code above does give you an idea how to add and edit existing records. If you post back more details I'll take another stab at it.

dz

 

[FoxProProgrammer]

Sorry, I left off the end of the code.

For i = 0 To nameoflistbox.Listcount - 1
If nameoflistbox.Selected(i) = True Then
rs.FindFirst <criteria> ' criteria identifies the record to look for
If rs.NoMatch Then ' No matching record; add new one
rs.AddNew
Else ' Update existing record
rs.Edit
End If

rs!IssueRef = nameoflistbox.Column(1)
rs!CauseRef = nameoflistbox.Column(2)
rs!otherfield = ....
rs.Update

rs.Close
db.Close
End If
Next

 

[FoxProProgrammer]

Yikes, there's a typo! I'm sorry. lol

Dim i as Integer
Dim db as Database
Dim rs as Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(&quot;tablename&quot;, dbOpenDynaset)

For i = 0 To nameoflistbox.Listcount - 1
If nameoflistbox.Selected(i) = True Then
rs.FindFirst <criteria> ' criteria identifies the record to look for
If rs.NoMatch Then ' No matching record; add new one
rs.AddNew
Else ' Update existing record
rs.Edit
End If

rs!IssueRef = nameoflistbox.Column(1)
rs!CauseRef = nameoflistbox.Column(2)
rs!otherfield = ....
rs.Update
End If
Next

rs.Close
db.Close

 

[asp3232]

Thanks for the reply. The data comes from a table of Issues (with description and IssueID fields), and a table of Causes (with decription and CauseID fields).

I originally created the linking table entries from scratch, but it's harder to modify them since all it has it 2 numeric fields (IssueRef and CauseRef).

Thanks again!

asp