find record if then

[supind]

I am having a difficult time getting started with this report I am trying to create. I am fairly new at programming and any help anyone can give me would be greatly appreciated.

I want to match records from TBL-CastProduction to TBL-ProdScrap by Casting PN.I think I need to use an if...then but i cannot get it to work right. If there is a matching CastingPN then I want to edit TBL-ProdScrap to update certain fields (ex. CastGood) with certain fields from TBL-CastProductin (ex. Shots). If there is not a matching record then I want to add the record to TBL-ProdScrap.


Dim rstCastProd As Recordset
Dim rstProdScrap As Recordset
Dim SQL As Variant
Dim db As Database
Dim strTable1 As String
Dim strTable2 As String

strTable1 = "TBL-ProdScrap"
strTable2 = "TBL-CastProduction"
Set db = CurrentDb
Set rstProdScrap = CurrentDb.OpenRecordset(strTable1, dbOpenDynaset)
Set rstCastProd = CurrentDb.OpenRecordset(strTable2, dbOpenDynaset)

This is as fasr as I have gotten. Everything I try does not work.

 

[Remou]

Hi
If you had something like:

Dim rstCastScrap as Recordset
Set rstCastScrap = CurrentDb.Openrecordset("SELECT [TBL-CastProduction].CastingPN, SomeOtherFields FROM [TBL-CastProduction] INNER JOIN [TBL-ProdScrap] ON [TBL-CastProduction].CastingPN = [TBL-ProdScrap].CastingPN")

You would have all records that matched in both tables. It is best use the query builder to build this, then cut the SQL into your program. Similarly, a selection using a RIGHT JOIN, with 'is null' under [TBL-ProdScrap].CastingPN would select records with no match in [TBL-CastProduction]. You can then update these recordsets as you wish. I hope I have got your point. (PS dashes are not good in table names, underscores are much better.)

 

[supind]

Thank you. I will play with this and see if I can make it work. I have been having a hard time getting the fields from the different tables to blend togther without multiplying. As for the names of the tables, they are mostly what the original programmer named them. It is way too complicated to go through and try to change them now.