Problem with data change on table (1 Viewer)

mikesp1234

Registered User.
Local time
Today, 13:29
Joined
Aug 28, 2013
Messages
16
I have a form with a drop down to select ink type. Then a textbox with the quantity in it. When I change the quantity, it changes the top combobox item to whatever was selected at the time of the change. IE: If Magenta is selected, and Cyan is the top item it will change the top item to Magenta. Both the combobox and text box are linked to a table called 'canon'. Please Help. Here is my code.

Option Compare Database

Private Sub Canon_Ink_Change() 'Changes the quantity to the same record as the ink
Me!Quantity = Me![Canon Ink].Column(1)
End Sub

Private Sub increase_Click() ' increases the value of quantity by 1
Quantity.Value = Quantity.Value + 1
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.Requery
End Sub

Private Sub decrease_Click() ' decreases the value of quantity by 1

Dim quanval As Integer
Quantity.Value = Quantity.Value - 1
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
quanval = Quantity.Value
If Quantity.Value < 3 Then MsgBox ("Quantities are low. You should send a report advising the current inventory status.")
If quanval < 3 Then DoCmd.OpenForm "Reports"
Me.Requery

End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:29
Joined
Feb 19, 2002
Messages
42,971
Requery runs the recordsource query again so the form goes back to the state it was in when it opened.

1. Don't use Requery to force a save. Use DoCmd.RunCommand acCmdSaveRecord Or Me.Dirty = False
2. Don't use Access 95 menu commands to save records. See #1 above.
3. Reference form/report controls as Me.controlname rather than controlname.value. The first is faster because you have saved the interpreter work by telling it which library to use to resolve the variable reference.

I don't think any of the requeries in the code above are necessary.
 

mikesp1234

Registered User.
Local time
Today, 13:29
Joined
Aug 28, 2013
Messages
16
Thanks that does make it faster. However I am still having a problem with table data being overwritten.

If I have a list in my table that says black/brown/blue/yellow and I make a change to the quantites, it over writes the first entry with whatever combobox item I had selected. So if Blue is selected, the table items are now blue/brown/blue/yellow.

Thoughts?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:29
Joined
Feb 19, 2002
Messages
42,971
Sounds like you are using a combo for searching that you have bound to the field you are searching for. Add a separate, unbound combo to use for searching. What you are doing now is updating the record you are sitting on when you enter a value in the combo.
 

Users who are viewing this thread

Top Bottom