Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rating: Thread Rating: 4 votes, 5.00 average. Display Modes
Old 09-19-2013, 03:06 AM   #1
mikesp1234
Newly Registered User
 
Join Date: Aug 2013
Posts: 16
Thanks: 4
Thanked 0 Times in 0 Posts
mikesp1234 is on a distinguished road
Problem with data change on table

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

mikesp1234 is offline   Reply With Quote
Old 09-19-2013, 11:26 AM   #2
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,165
Thanks: 15
Thanked 1,577 Times in 1,499 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Problem with data change on table

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is online now   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
mikesp1234 (09-25-2013)
Old 09-25-2013, 06:29 AM   #3
mikesp1234
Newly Registered User
 
Join Date: Aug 2013
Posts: 16
Thanks: 4
Thanked 0 Times in 0 Posts
mikesp1234 is on a distinguished road
Re: Problem with data change on table

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?

mikesp1234 is offline   Reply With Quote
Old 09-26-2013, 07:54 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,165
Thanks: 15
Thanked 1,577 Times in 1,499 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Problem with data change on table

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is online now   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
mikesp1234 (10-08-2013)
Reply

Tags
button , combobox , form , table

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Change data in table using VBA spudracer Modules & VBA 2 11-08-2011 06:33 AM
Problem calling a sub routine on data change mlai08 Forms 3 07-27-2010 07:45 AM
Move record to new table after data change in combo box value in original table. muirchez Forms 1 11-24-2006 07:41 PM
Change Data In A Second Table From A Form statsman Forms 1 02-02-2006 06:34 PM
[SOLVED] subform problem after table name change Pawser Forms 2 08-12-2005 10:16 AM




All times are GMT -8. The time now is 10:51 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World