How to populate a combobox with an updatable recordset?

[leach613]

First of all, I'm use Access 2003 but most of the people that will be using this database with be using Access 2002. I'm not sure if that will make much of a difference but anyways here is my question. Oh and I'm making an Accounts Receivable Database.

My main question is, can you populate a multi column comboBox by using a recordset?

I would like to choose a CustID in one comboBox and use afterupdate event to create the recordsource for the Invoices comboBox.

I want to be able to remove the invoice records from the recordset when a payment will be applied to it. Therefore when the Invoice comboBox is click on again that invoice is not shown. Of course if they change their mind or made a mistake in the invoice they chose, they should have the option to add it back to the Invoice comboBox.

I've been looking for days for a solution but I'm starting to think that this is not possible the way I would like to do it. I know there are other ways to accomplish the same thing but I was hoping that this would be an easier way.

Thanks for your help.

[leach613]

Update, I just found out what I was missing.

I was using......Me.I nvNo.recordset = rstInv 'By the way, this recordset is declared as Public.

What I was missing was.....Set Me.InvNo.record erset = rstInv

This worked. Now I need to know how to remove item in the recordset or update a field in the recordset and then requery it to filter out the invoice.

I tried...Me.InvN o.RemoveItem (rstInv.Index) but got an error.

Thanks...

[patjones]

I guess it depends on whether you want to remove the item from the actual recordset, or just from the combo box - because I think either of those things could be done. Judging from what you just posted, it looks like you're just trying to remove it from the combo box.

Pat

[leach613]

Yes I would like to remove it from the combo box but since the combo box is populated by the recordset isn't that the same thing? Also don't I have to requery and won't that just bring in the same info I had to begin with.

I tried using removeitem but it said that the recordsourcetyp e had to be a value list to use.

So now what?

Evy

[missinglinq]

I tried using removeitem but it said that the recordsourcetyp e had to be a value list to use.

That's correct in versions 2003 and earlier. You'll either have to remove the record from the underlying table, or add a field, such as a checkbox, to the record that when checked precludes it from being included in the query the combobox is based on. You'll then have to tick the checkbox on any record/item that is selected and then requery the combobox.

Linq ;0)>

[leach613]

Thanks Linq for your reply but the whole purpose of populating the combo box with a recordset was to be able to manipulate the recordset and not the table. The table will be update in the end when the person clicks the post button.

I tried to update the recordset but I keep getting an error. Here is my code..

Dim strInv as string

strInv = me.InvNo

With rstInv
     .Index = "InvNo"
     .MoveLast
     .MoveFirst
     .FindFirst "InvNo = " & strInv    [B] 'This is where the error is[/B]
     .edit
     !Posting = -1
     .update
End With

The error is "Object variable or With Block variable not set".

I'm I missing something?

Evy

[patjones]

Since strInv is a string you definitely have to enclose it in ' ' as such:

.FindFirst "InvNo = '" & strInv & "'"

Pat

[leach613]

That's the way I had it the first time I tried but I still get the same error.

Evy

[patjones]

Can we see the rest of the code, like where you define the rstInv?

Pat

[leach613]

I defined it as a public recordset.

Option Compare Database
Option Explicit
Public dbTrans as DAO.Database, rstInv as DAO.Recordset

After they choose what Customer sent the payment from a combo box I have this in the afterupdate event.

Set dbTrans = CurrentDb
Set rstInv = dbTrans.OpenRecordset("SELECT [InvNo], [DueDate], CCur([BalAmt]) as Payment, [InvAmt], [CustID], [Posting] FROM tblTransactions WHERE [CustID] = '" & Me.CustID & "' And [Posting] = 0", dbOpenDynaset)

Set Me.InvNo.Recordset = rstInv

This part works good. The error happens right after .index = "InvNo".
I don't know what else to put as the index for the recordset. That is the primary key in the table.

Thanks for your help.

[patjones]

Hi,

I wanted to check a couple things out before responding to you, and also construct a simple recordset of my own to see what happened when I tried to reproduce your error.

I think you don't need the .Index = "InvNo" line. The .Index recordset method is used with table-type recordsets, and in conjunction with the .Seek method (which is similar to .FindFirst). It will not work with a dynaset-type recordset, which is what you specified in OpenRecordset. On the other hand, .FindFirst works with dynaset-type recordsets and not table-type.

A side point here...when you use .Index, you don't actually set it to the field name. When you open a table in design view and examine the table's indexes, you'll see that they have an "Index Name" and "Field Name". You want to use the Index Name (for instance, .Index = "PrimaryKey ", which is the default name Access assigns to the primary key field of a table).

Pat

[leach613]

Well it seems like I got the first part of my question resolved but still could not get the second part resolve.

First part was to populate the comboBox with a recordset. Got that working. Second was to be able to update/delete/add the records in the recordset and have it reflect on the comboBox.

I have taken a different approach. After creating the recordset, I looped through them and used the .AddItem to add them to the comboBox. I closed out the recordset since I don't need it anymore. (I'll have to think about that, I may just leave it open.)

Now it's just a matter of manipulating the comboBox to add or remove items. I'm going to stay away from updating since I don't really need that.

Thanks for all your input. Sometimes I just need someone to point me in the right direction or make me think in a different way.

Evy
"If it can be done, I will find a way."

[missinglinq]

"...the whole purpose of populating the combo box with a recordset was to be able to manipulate the recordset and not the table."

I'm afraid you're laboring under a misconception here. When you manipulate a record in a Recordset, you're manipulating that record in the underlying table! If, for example, you delete a record from the Recordset, that record is gone from the table, then and there, without anything else being done.

Linq ;0)>

[leach613]

Thanks for the info, I certainly didn't want to delete any records in the table so thank goodness I couldn't figure it out.

Evy