List with Multi Select populating table

[cbramsey]

Is this possible? I have a list that users need to be able to select multiple rows on my form, I would like to have these multiple selections populate the table but when I do this I get an error about the Index or Primary Key being null (which it isn't), but I am guessing this has to do with the multiple selection.

[ranman256]

make a field , [MARK], boolean.
user can click it TRUE, then run a query that uses all records MARKED = true.

[cbramsey]

I am thinking that I wasn't clear on what was going on. I have a form with a multiselect list box. I need to get those items selected to populate the table.

[pbaldy]

How about:

http://www.baldyweb.com/MultiselectAppend.htm

[cbramsey]

Thank you for that, I am copying my code here. I am getting the following error - Run-time err '64224': Method 'Collect' of object 'Recordset2' failed

Option Compare Database


Private Sub Command23_Click()
Dim strsql As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varitem As Variant


On erro GoTo errorhandler


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


Set ctl = Me.WELLNAME
For Each varitem In ctl.ItemsSelected
rs.AddNew
rs!BA_NUMBER = Me.BANUMBER
rs!BA_NAME = Me.BANAME
rs!WELLS = ctl.ItemData(varitem)
rs!eff_date = Me.EFFECTIVEDATE
rs!analyst = Me.ANALYSTNAME
rs.Update
Next varitem


exithandler:
Set rs = Nothing
Set db = Nothing
Exit Sub


errorhandler:
Select Case Err
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume exithandler
End Select


End Sub

[pbaldy]

Is this a typo?

On erro GoTo errorhandler

should be "Error". Can you attach the db here?

[cbramsey]

Yeah, I caught the error handler right after I posted, I can't post the DB here, it is using linked tables from one of our accounting system databases, if I comment out the rs!wells = ctl.itemdata(varitem) line, then it will load all of the other data just fine.

My Wellname field is a list box that has multiselect on it that cascades once the BANUMBER field is populated, basically it is pulling active wells for an owner. Any ideas what I could troubleshoot as to what would be causing that area. I tried changing it to DAO.recordset2 and that gave me the same error. DAO.recordsets doesn't allow to AddNew.

[pbaldy]

That's an odd error, particularly since it works without the listbox. That's the correct field name? What is the data type? Not a multivalue field is it? Add this and see what the value is:

Debug.Print ctl.ItemData(varitem)

It will be here if you're not familiar:

http://www.baldyweb.com/ImmediateWindow.htm

[cbramsey]

yes, it is a multivalue field, when I look at the immediate window, it is pulling up the correct data. The items are formatted at 12345.1. would the .1 on the end cause the problem?

[pbaldy]

All bets are off for a multivalue field. I haven't used one (won't; don't like them conceptually). See if this helps:

http://www.fmsinc.com/microsoftacces...alueFields.htm

[cbramsey]

Well, that stinks. How would you handle this.

Situation
We are an oil and gas company that is currently buying properties from other small oil and gas companies

We want the form to work as follows

put in the owner information and get a list of wells they are in and select the wells which we are purchasing.

[Homegrownandy]

what happens after they are selected? would this be done one at a time or in bulk?

[ssanfu]

I agree with Paul. I have never used a MVF and won't ever.

If you research what is going on behind the scenes, a MVF has a 1 to many relationship that you never see.

So I would create a proper table relationship - one property/lease to many wells(?)

We are an oil and gas company that is currently buying properties from other small oil and gas companies

We want the form to work as follows

put in the owner information and get a list of wells they are in and select the wells which we are purchasing.

Then use a main form/sub form design to select the lease to see the wells.