I have a listbox (4 columns) on a master form that gets its data from another listbox. The user can select and de-select items. When I update the form, the selected data (rows) gets added to a table.
2 of the columns are ordernumbers. 1 of these is the autonumber that comes up on the form itself. When I update the record, I want to search the table to see if a combination of the 2 fields exists.
If my form # is: 1234, and I have 4 items in the list: #'s 2220, 2221, 2222, & 2223. Then the 2 fields in the tables would be: [1234] & [2220], [1234] & [2221], [1234] & [2222], [1234] & [2223].
Two problems: First, if I save, then re-open the record #1234, and see its list, I want to be able to edit it. If I re-save it, I don't want to duplicate those records. The fields have to be non-unique (they all exist in more than one record), so I have to search the table to see if, say, the combination: [1234] & [2222] exists. If it does, the update doesn't add that row.
Also, if there are records with the form's number (1234), and they have been removed from the list on the form, I want to delete them from the table.
So, first I need to search all the records for the form (1234), then see if records on my list (2222, 2223, etc) match, and if they don't, add them. Also, if there are records in the table with the form's number (1234), with no matching list #'s (2222, etc) I want to delete them from the table.
Oh, and don't be confused by the autonumber. I'm only using a copy of it, so the copy is not actually unique in this table.
I hope I haven't confused this issue horribly. Thanks a lot.
John
2 of the columns are ordernumbers. 1 of these is the autonumber that comes up on the form itself. When I update the record, I want to search the table to see if a combination of the 2 fields exists.
If my form # is: 1234, and I have 4 items in the list: #'s 2220, 2221, 2222, & 2223. Then the 2 fields in the tables would be: [1234] & [2220], [1234] & [2221], [1234] & [2222], [1234] & [2223].
Two problems: First, if I save, then re-open the record #1234, and see its list, I want to be able to edit it. If I re-save it, I don't want to duplicate those records. The fields have to be non-unique (they all exist in more than one record), so I have to search the table to see if, say, the combination: [1234] & [2222] exists. If it does, the update doesn't add that row.
Also, if there are records with the form's number (1234), and they have been removed from the list on the form, I want to delete them from the table.
So, first I need to search all the records for the form (1234), then see if records on my list (2222, 2223, etc) match, and if they don't, add them. Also, if there are records in the table with the form's number (1234), with no matching list #'s (2222, etc) I want to delete them from the table.
Oh, and don't be confused by the autonumber. I'm only using a copy of it, so the copy is not actually unique in this table.
I hope I haven't confused this issue horribly. Thanks a lot.
John
