Trying to edit CBO value in a continuous form with VBA

[Thomasso]

Hello,



I have a database where I store products and every unit with its serial number.

I am trying to create a system for repairs evidence.

The idea is that I enter the serial number into one combo box and the product name appears in another one. I have the exact same functionality working well and I copied it. That's why I got confused because it's working well in one application and it doesn't work properly in another one.

This is the data entry form for adding new orders where it works like I want to:


When serial number is updated, this VBA code runs:

    If DCount("[SerialNumber]", "qryTransakceALL", "[SerialNumber] = [cboSerialNumber]") > 0 Then
        ProduktID = DLookup("[PKProduktID]", "qryTransakceALL", "[SerialNumber] = [cboSerialNumber]")
        cboFKProduktID.Value = ProduktID
        txtCena.SetFocus
    End If

It's working well.

The repair system is different because the product name isn't stored in the corresponding table. I just want to display it as an additional information. I tried this:

    If DCount("[PKDetailObjednavkyID]", "qryTransakceALL", "[PKDetailObjednavkyID] = [cboSerialNumber]") > 0 Then
        ProductName = DLookup("[PKProduktID]", "qryTransakceALL", "[PKDetailObjednavkyID] = [cboSerialNumber]")
        Me.cboProductName.Value = ProductName
        txtNakladyNaOpravu.SetFocus
    Else
        MsgBox ("Zadané výrobní číslo neexistuje.")
        Me.Undo
    End If

The problem is that when I change one serial number, all the product names fields in the continuous form get changed, now just the record that I want.

Like in these pictures... I only changed the first serial number but it changed all the product types in all the records.



Any ideas why?

Thank you,
Tomas

[aytee111]

It is because it is an unbound field, that is what happens.

[Thomasso]

It is because it is an unbound field, that is what happens.

Thanks. Yeah that makes sense. Any ideas how to work around this?

The table where I store individual products repairs actually is a junction table between Order Details (I get serial number from here) and Repair Orders (which can constist of multiple products being repaired).

The junction table itself has a connection to Order Details and Repair Orders primary keys and I store malfunction description and cost of the repair. How should I proceed when I want to display product name based on serial number? To what should I bound the text box?

Thanks.
Tomas

[aytee111]

You could try joining the table in to the record source for the form and then make it a bound field. This has to be a straight join otherwise you won't be able to update.

[June7]

Include the product name as a column in the combobox RowSource - column can be hidden. Then expression in textbox can reference the column by index. Index starts at 0 so if product name is in column 2 its index is 1: =cboProductName.Column(1)

[Thomasso]

Include the product name as a column in the combobox RowSource - column can be hidden. Then expression in textbox can reference the column by index. Index starts at 0 so if product name is in column 2 its index is 1: =cboProductName.Column(1)

That worked, thanks! :-)