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 afterupdateevent?

[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?

[twinnyfo]

In the form's design view, in the Property Sheet, look for "Default View". Set as Single Form. Then, just to make sure, set Allow Form View should be set to Yes. The other four views (Datasheet, PivotTable, PivotChart and Layout may be set to No, but do not need to be.

[Slaxer13]

Good day to all.
I changed it twinnyfo, it was in continuous form view. Thanks.
Still the problem remains and its because of the cboafterupdate event...

[twinnyfo]

Important question here..... Does it still update ALL PREVIOUS records to null, like previously? If it does, there must be something beyond the continuous forms issue.

If it just translates to null, that is now our focus.....

[Slaxer13]

Yes it does. It updates all previous records to null except thos with the same value.

Example: If in the cbotipo of the new record the value is "car" then it updates all previous records to null that don't have the cbotipo value as "car". As for the ones that have "car" as a value they remain untouched.

PS: Sorry for late reply. Was in reunion with teacher and tutor

[twinnyfo]

And the AfterUpdate Event of the Combo Box you listed in Post #3, this is the only code that fires?

I am suspecting that your Form is showing something different than what is in your table (or at least what ought to be in your table.

What are the values that "ought" to be in your table. If there are indexes used for lookup fields, please use the indexes. When you select something in cboArea, what are the values that should be populated in cboTipo?

In your Table, can the field for Area be any value? I.e., what is the row source (from the Table, not the Form)? Likewise, in the Table, what is the row source for your Field for Type?

My thought is that when you limit the rowsource in cboTipo, when you move to a record that does not contain one of those values in cboTipo, it reverts to Null (or at least it appears as Null on the Form). have you checked to see if the values in the Table have actually changed when you just change one record? Or, are you flipping through the records on your Form and getting these results?

Believe it or not, I think we are actually making progress, but it has taken us a while to ask the right questions to move in the right direction.....

[Slaxer13]

In the table the records are there but when i look through the records in the form they are null except the ones with the same value as the last one created. As for the field type is text.

[Slaxer13]

For the rest of today (my last internship day) all that i will do won't count for anything but i'm curious to solve this before letting go of this database.

[twinnyfo]

Your Post #21 just gave me great insight! The reason those values are Null on the form, but not in the Table is because the Combo Box does not have a corresponding value for the field.

One way to fix this is to add some code to the OnCurrent Event of the Form. Requery the row source of cboTipo just like you show in Post #3 (be sure to requery the combo Box).

Then all records should show their appropriate value (I hope!).

Let me know how it goes--hope you stick around the forum even after the internship!

[Slaxer13]

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

Like this?

[twinnyfo]

Yep, although, as discussed, the Abreviatura value is superfluous.

You may also have to add:

Me.Refresh

right after

Me.cboTipo.Requery

Let me know if it works...

[Slaxer13]

works but raises another problem... now,because this is trigered in the oncurrent event my cbotipo doesn't show any values when i am creating or editing. And not that it matters but when i choose cboarea it doesn't fill the txtabreviatura.

[twinnyfo]

It should still be in the cboArea AfterUpdate Event. It is required in both places.

The "Best" practice would be to have those operations in a separate function that is called from both locations, but for now, for these purposes it should work fine in both locations.

[Slaxer13]

I can do the function that's easy. I'll try and say how it goes

[Slaxer13]

Now it stopped working again. When this code is in the afterupdate event it stops working :/

[Slaxer13]

I did this:

Public Function cboUpdate()
 
    Me.cboTipo.RowSource = "SELECT Cod_Tipo_Atendimento, TipoAtendimento " & _
                           "FROM tblTipoAtendimento " & _
                           "WHERE Cod_Area_Atendimento = " & Nz(Me.cboArea)
    Me.cboTipo.Requery
    Me.Refresh
    txtAbreviatura.Value = cboArea.Column(2)
 
End Function

And called it in cboArea_AfterUpdate and in Form_OnCurrent

[twinnyfo]

What were the results of your changes?

[Slaxer13]

None. It's the same as before. When i removed the code (put as comment) it worked fine but didn't limit the values of cbotipo. Is there another way to limit the cbotipo values according to the cboarea?

[twinnyfo]

So, the code that was previously working in the AfterUpdate event is now not working?

In line 8 above, just to make it clear for the code, change to Me.txtAbreviatura = Me.cboArea.

BTW, no need for the .Value, as this is the default property....

[Slaxer13]

Ok. No no. The program works if i don't have those code lines:

 Me.cboTipo.RowSource = "SELECT Cod_Tipo_Atendimento, TipoAtendimento " & _
                            "FROM tblTipoAtendimento " & _
                            "WHERE Cod_Area_Atendimento = " & Nz(Me.cboArea)
     Me.cboTipo.Requery
     Me.Refresh
 

when i put them as commentary the program works but doesn't filter cbotipo values. if i remove them from commentary the program doesn't work but cbotipo has its values filtered

[twinnyfo]

Is this on every record that it does not work or only on new records?

[Slaxer13]

When i call the function in a new record it works but after i save the records in cbotipo of previous records that aren't of equal value become null. if i don't cal the function, cbotipo hasn't got the values restricted but the previous record don't become null when i save

[Rabbit]

If you don't specify a return value, Nz will return a blank string if the value is null. So your SELECT statement becomes

SELECT fields
FROM table
WHERE field =

Which is incorrect because you have nothing after the =.

[Slaxer13]

Sorry for the late reply. No internet at home and internship is over. So because i don't have anything after the (=) they are returning null. I'll try it at home and get back as soon as i can.
Thanks. Cheers, Slaxer13

[twinnyfo]

Yes, instead of Nz(Me.cboArea), make it Nz(Me.cboArea, 0). This will at least cause the query to run, even if cboArea is Null (a new record).

[Slaxer13]

Thanks. I haven't had time to try it but i hadn't forgot about it. When i have time i'll try and post the result here ;)

Cheers, Slaxer13