Record selection from combo

[caferacer]

Hello all,

Re the code below, I am using Combo40 to find an EquipmentID record, which works fine. However, an Equipment can have two suppliers. Both suppliers are listed in Combo40, but when choosing the second supplier from Combo40, the record found always defaults to the first supplier.

I would appreciate some help in modifying the standard wizard code above to force the find to look at the EquipmentID and also the VendorID.

FYI, I have a vendor, equipment and equipment/vendor junction table. The comboo is via a query from the three tables.

Thanks



Private Sub Combo40_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[EquipmentID] = " & str(Nz(Me![Combo40], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

 

[jzwp22]

Since you are using the find first method, the code will always take you to the first occurence of the equipmentID that it runs into.

Since you have multiple suppliers for an equipmentID, the equipmentID is not unique. Could you use the primary key of the junction table instead of equipmentID? I'm not sure where you are doing this and whether the primary key is in the form's recordsource.

 

[caferacer]

Yes of course - basic errors. Thanks for pointing this out.

Regards

 

[caferacer]

Hi,

leading on from this, after updating the form with the new record from the selection from the combo, I would like to be able to set the value of a tick box on the same form if there are duplicate records in the EquipmentID field.


Something like -

On find record (after update I guess) run duplicate record query on EquipmentID field
If query returns >1 records
then set tick box value to 1 else 0

Any help much appreciated.

Thanks

 

[jzwp22]

a tick box on the same form if there are duplicate records in the EquipmentID field.

Do you mean that if you have duplicate records with the same EquipmentID you want to flag one of them? But, based on what you said earlier, you can have multiple records with the same EquipmentID (but different suppliers). Do you really mean that you want to flag records that have the same EquipmentID AND the same supplierID? If that is the case, which one do you want to flag? For example, lets say that you have 3 records that have the same EquipmentID & the same SupplierID, which of the 3 would you want to flag? One particular one or all 3? If one particular one, how are you going to identify to Access which one?

 

[caferacer]

Sorry, yeah it would be all three.

Regards

 

[jzwp22]

The code would go something like this (air code, not tested):

Dim mySQL as string

IF DCount("*", "junctiontablename", "equipmentID=" & comboboxname.column(x) & " AND supplierID=" & comboboxname.column )>1 THEN

mySQL="Update tablename SET flagfield=-1 WHERE equipmentID=" & comboboxname.column(x) & " AND supplierID=" & comboboxname.column

currentdb.execute mySQL, debfailonerror

END IF