Combo box adding new fields

[Slaxer13]

Hello ppl,

In one of my forms i have a combo box that whenever i create a new record it adds a new field everytime with the id of the value i put in the record before.

Example: i create a new record and the field on the combo is "water" (in the table the "water" id is "2")then when i start a new record the fields on the combo box this time will be "water" and "2".

Anybody have a clue why?

Any help appreciated.
Cheers, Slaxer13

[burrina]

Is this combo bound and do you have code using the afterupdateeven t?

[Slaxer13]

Yes it is bound to a table. As for the AfterUpdate Code is the following:

 Me.cboTipo.RowSource = "SELECT Cod_Tipo_Atendimento, TipoAtendimento " & _
                           "FROM tblTipoAtendimento " & _
                           "WHERE Cod_Area_Atendimento = " & Nz(Me.cboArea)
    txtAbreviatura.Value = cboArea.Column(1)

Basically the code is supposed to set the values of another combo box according to the value on this one. and when the value on this combo box is choosen i have a textbox "txtAbreviatura " that will receive the abreviation of the value i choose

[Seth Schrock]

Another place to look would be in the form's OnCurrent event to see if you are setting the value using code.

[Slaxer13]

Form's onCurrent Code:

Dim rst As DAO.Recordset
    Dim lngCount As Long
    Set rst = Me.RecordsetClone
    With rst
    .MoveLast
    lngCount = .RecordCount
    End With
    Me.txtCount2 = "Registo " & Me.CurrentRecord & " de " & lngCount

It has nothing that would mess with the cbo

[Slaxer13]

Sorry for troubling you people. The problem is sovled. My form was updating three tables and two of them where updating twice because my combo boxes where supposed to be unbound and they weren't. The moment i unbounded them the problem went away. Anyway thanks for the help ;)
Cheers, Slaxer13

[twinnyfo]

slaxer,

My comment may be off topic, but I think relevant to the posts you have been making, in general.

It seems you have recurring problems with combo boxes and how they interact with your forms and tables. Some recurring themes that I see are:

1. Combo boxes with constantly changing row sources
2. Combo boxes that set the values of other text boxes and/or
3. Text boxes that are utterly dependent upon the ID of a combo box

It appears, to my cursory glance at your implementation of combo boxes/text boxes relying on those combo boxes, that you are highly over-complicating things.

It "appears" that you have a form. The form is bound to a table. There is a combo box, which is not bound. You also have a text box which may or may not be bound. Then when you go to a record, the user updates the unbound combo box. Then the combo box updates the text box with the ID of the combo box (which is the value of the combo box in the first place). Then the text box, which now has the ID of the combo box updates the value int he table (whether by being bound or unbound).

Do you see where I am going? Why can't you simply have a combo box, bound to the field on the table, so that when a user updates the combo box, the value in the table is updated?

I just want you to be aware of the apparent over complication your forms appear to be creating. If I am missing something, please explain.

[Slaxer13]

No, you are right. My knowledge is the basic at access and seems i am really complicating things. Sorry to ask this but can you tell me how to do what you have said? The part where "when a user updates the combo box, the value in the table is updated" is it through vba?

[twinnyfo]

slaxer,

typically, if you have a table with a field that uses a foreign key, with a lookup field, when you add that field to your form, it will be a bound combo box. When you open the form, whenever you update the combo box, you have updated the field in the table. No VBA required. It is the basic setup for lookup fields.

An example:

I have a table tblOrders. That table has several fields OrderID, OrderDate, Vendor, OrderTotal.

For the field Vendor, we could have it as a text value (i.e. "Smith's Foods", "Jones Shipping" etc.). But, whenever we have orders from the same Vendor, we are duplicating the data in the table. Plus, we have the possibility for mis-typing the name (i.e. "Smyth's Foods"). Then, when we run a query to find out our biggest vendors, we would have two vendors for Smith's Foods, one of which is correct, one which is incorrect, but neither would be accurate.

So, we create a table for vendors, in which we have VendorID, VendorName, Vendor Address, etc. Then in our tblOrders, instead of using the Vendor name, we use the index (VendorID)--this is the foreign key--by using a lookup field. This saves a ton of data (especially when you have lots of records).

You may already understand this principle, so forgive me if I am repeating what you already know, but it appears that your forms are not operating on this principle, but you are trying to re-invent the wheel by using unbound controls to update your table. Remember, the only way you can use an unbound control to update a table is through VBA (there are some clumsy ways to do it, but VBA works easiest). However, in your situation, it appears that properly created bound controls are what you need.

Let me know if this helps you out.

[Slaxer13]

At the moment i have no FK (foreign Keys) in any table. But i kinda understand what you mean in your exemple. It seems i may have to change somethings in order for this to work. I will try to do things the way you said in the example.

And you don't need to ask forgiveness because the more times i ear things the most likely is that i am going to understand them better and use them more eficiently ;)

[twinnyfo]

Keep in mind that your problem here may be related to the problems you are having in your other current thread. Perhaps we have been seeking solutions based on principles that are not in place in your database forms and tables.

This could be why the threads are lengthy and apparently come to no conclusions. However, we will continue to crack away at this issue until we make you a pro!!

[Slaxer13]

I think it is related. And i noticed something. In the afterupdate of the cboarea if i don't use that select statement to define the values of the other cbo my values of previous records remain untouched

[Slaxer13]

I tried putting that statement in comment and i works great

[twinnyfo]

Your form is still "behaving" like it is in continuous forms mode.....

[Slaxer13]

How do i change it to single view?