=> Bookmark

  • Thread starter Thread starter Rhonda Fischer
  • Start date Start date
R

Rhonda Fischer

Hello,

I am using the below stated bit of code to display an
integer value in a textbox (fuelsurcharge) on selecting
a Supplier from a combo drop down list.

I am not sure of the relevance of the last line of code
Me.Bookmark = rs.Bookmark
that I have commented out. When I reinstate this line
of code I receive the following error message:

Single-row update/delete affected more than one
row of a linked table. Unique index contains
duplicate values

however I also receive this error message when I make
a new selection of Supplier, where the fuelsurcharge is
display and then change the form into Design Mode. So
I think there is something wrong?

How can I change my code to avoid this error message?
Why does it occur when I am not trying to change the
data in the table just view the form in design mode?

Thank you very much for any suggestions

Kind Regards
Rhonda


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

Set rs = Me.Recordset.Clone
rs.FindFirst "[supplierID] = " & Str(Me!
[cboSupplierName])
txtFuelSurcharge = rs!fuelSurcharge
'Me.Bookmark = rs.Bookmark
End Sub
'*************************************************
 
Rhonda Fischer said:
I am using the below stated bit of code to display an
integer value in a textbox (fuelsurcharge) on selecting
a Supplier from a combo drop down list.

I am not sure of the relevance of the last line of code
Me.Bookmark = rs.Bookmark
that I have commented out. When I reinstate this line
of code I receive the following error message:

Single-row update/delete affected more than one
row of a linked table. Unique index contains
duplicate values

however I also receive this error message when I make
a new selection of Supplier, where the fuelsurcharge is
display and then change the form into Design Mode. So
I think there is something wrong?
Click to expand...

Rhonda,

there are two situations where you can use a combo which got a bit
mingled together in your code.

1) Unbound combo in form header/footer to search a record in the
form's recordset and go to it (i.e. display it in the form). This is
where you use the code you have, the very jumping is done by the
statement Me.Bookmark = rs.Bookmark.

2) A _bound_ combo where you select an entry and the code in
AfterUpdate displays some other related data from the lookup table in
a text field. The displayed record does not change. In this case the
selected value in the combo will be stored in the current record, the
text field is usually unbound and deactivated, it only displays some
additional info.

From the line
txtFuelSurcharge = rs!fuelSurcharge
Click to expand...

I suppose you would want to achieve the situation (2). OTOH the rest
of the code is trying to find a record and go to it. The two actions
collide, hence the error message.

Try this:
Add the fuelSurcharge field to the the RowSource query of the combo as
the last column.
Open the form in design view, open the properties window and select
the combo.
Set the Column Number property to the appropriate number of fields
(probably 2).

Write this in the combo's AfterUpdate event procedure:

If IsNull(Me![cboSupplierName]) Then
Me![txtFuelSurcharge] = Null
Else
'You can use the Column property to read the
'value of the other fields from the combo's RowSource.
'The Column property index starts with 0, so if
'fuelSurcharge is the second field, use Column(1).
Me![txtFuelSurcharge] = Me![cboSupplierName].Column(1)
End If

If the text field is unbound, you'll need the same code in the form's
OnCurrent event procedure too, so the correct fuel surcharge will be
displaying when navigating in the form.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
Back
Top