after update in ms access form check if data exist in other subform if so update or else copy

Hi guys I have a form and I want to update another subform in ms access form check if data exist in other subform if so update or else copy

form 1 and form 2 the names of the tables are the same  Who can help me out? Is this done by VB expression? I have now the following code for three tables:

Private Sub Quantity_AfterUpdate()
Forms![Workorders]![Transaction facturatie].Form![WorkorderPartID] = Me![WorkorderPartID]
Forms![Workorders]![Transaction facturatie].Form![UnitsSold] = Me![Quantity]
Forms![Workorders]![Transaction facturatie].Form![ProductID] = Me![ProductID]
End Sub

I thought want to add the ID of the both forms to I need a new row in form 2 if there are more products in form 1

Thanks a lot for your help!
Regards Michiel
mvdwalAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
My apologies, I didn't notice that your Table names are not standardized and include a Space:

"INSERT INTO [Inventory Transactions] (WorkorderPartID, ProductID, UnitsSold) SELECT WorkorderPartID, ProductID, Quantity FROM [Workorder Parts] WHERE [Workorder Parts].appended = No"

This is a VERY good reason to use standardized nameing conventions when naming your objects. Your "Inventory Transactions" table should be named InventoryTransactions, your "Workorder Parts" table should be named WorkorderParts, etc etc
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use the Dlookup function to check whether a record exists:

If Nz(Dlookup("SomeField", "SomeTable", "YourCriteria=" & YourValue), "") <> "" Then
  '/the record exists
Else
  '/the record does not exist
End If

So: If the record does NOT Exist, what do you need to do?
0
 
mvdwalAuthor Commented:
hahaha I'll get a headache now... sorry i'm a newbie on this... Thanks for helping me out. But basically I need a solution for this:

I'll have a form and I want to copy or update three fields in to another form or directly to a table...

Who can help me out with a complete code?
Thanks a lot!
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
mvdwalAuthor Commented:
I have found the following code but get an syntax error...

Private Sub Quantity_AfterUpdate()
With DoCmd
.SetWarnings False
.RunSQL "INSERT INTO Inventory Transactions (WorkorderPartID, ProductID, UnitsSold) SELECT WorkorderPartID, ProductID, Quantity, FROM Workorder Parts WHERE Workorder Parts.appended = No"
.RunSQL "UPDATE Workorder Parts SET Workorder Parts.appended = Yes"
.SetWarnings True
End With
End Sub
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
We're not really here to provide you with complete code for your issue, but rather to help you find your own solution.

You should NOT have a , after the word Quantity in your INSERT statement:

.RunSQL "INSERT INTO Inventory Transactions (WorkorderPartID, ProductID, UnitsSold) SELECT WorkorderPartID, ProductID, Quantity FROM Workorder Parts WHERE Workorder Parts.appended = No"
0
 
mvdwalAuthor Commented:
Thank you, I allready noticed it. It's not working. Thanks for your quick reply!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What do you mean by "not working"? Do you get an error? If so, post the full error text and number.

Do you NOT get an error, but the data isn't updated/inserted as you expect? If so, then your SELECT subquery would appear to be incorrect.
0
 
mvdwalAuthor Commented:
Sorry,

If I have updated the field in the form with some kind of value and press enter I'll get an syntax error
0
 
mvdwalAuthor Commented:
Super! It copies! Thanks a lot! but it copies all rows! (also the rows wich are not linked to this form.

I used this code now:

Private Sub Quantity_AfterUpdate()
With DoCmd
.SetWarnings False
.RunSQL "INSERT INTO [Inventory Transactions] (WorkorderPartID, ProductID, UnitsSold) SELECT WorkorderPartID, ProductID, Quantity FROM [Workorder Parts] WHERE [Workorder Parts].appended = No"
.RunSQL "UPDATE [Workorder Parts] SET [Workorder Parts].appended = Yes"
.SetWarnings True
End With
End Sub

But it copies all rows! 1000! I'll better make a test table or change the code. Because it has to copy one row with that particular update instead of updating all! Do you know what I have to change? there was one row viewable in the subform because of the filter.  This code is inserted in after update from Quantity in a form. maybe  there is a better solution? Can I also copy this three fields to a subform? with the same workorderID? Maybe you can help me out with this. I'll appreciate it very much!

Best regards
Michiel
data.jpg
data2.jpg
0
 
mvdwalAuthor Commented:
Ist it possible it copied all rows because of the appended field? because all or on yes now and it copies one row now! only it doesn't change the input when I change the value of quantity...
thanks for your help!
0
 
mvdwalAuthor Commented:
No please do not close I still have no workable solution for this problem...
0
 
Vadim RappConnect With a Mentor Commented:
All you need to do is requery the 2nd subform.

Let's say, you have table1, and two subforms subform1 and subform2 both bound to table1.

The user opens subform1 and updates some record.

As soon as the user navigates to another record, the changed data is committed to table1. If then you have afterupdate event on subform1, which issues subform2.requery, then subform2 will re-read table1 it's bound to, and will reflect the change.

See this sample:
https://filedb.experts-exchange.com/incoming/ee-stuff/7956-db.mdb
0
 
mvdwalAuthor Commented:
Thanks a lot guys!
0
 
Vadim RappCommented:
I'm curious: you accepted actually two contradicting answers. The comment 29996573 instructs to use INSERT, while mine indicates that when the subform is bound, INSERT is not needed because the form is doing all data manipulations by itself. Are you really using INSERT? is your subform really unbound?
0
All Courses

From novice to tech pro — start learning today.