Loop and AddNew or Edit
I want to open two tables (rsNL, rsDL) and loop through one.
If a record in rsNL is not in rsDL I want to add that record to rsDL
If a record in rsNL is in rsDL I want to edit that record in rsDL to equal the same record in rsNL
The field [MLS #] is common in both tables
I have attached the code I have so far.
The loop does add a new record in rsDL if it is not in rsNL
But it is not updating those records in rsDL that are found in rsNL
Suggestions....?
~Worcse
If a record in rsNL is not in rsDL I want to add that record to rsDL
If a record in rsNL is in rsDL I want to edit that record in rsDL to equal the same record in rsNL
The field [MLS #] is common in both tables
I have attached the code I have so far.
The loop does add a new record in rsDL if it is not in rsNL
But it is not updating those records in rsDL that are found in rsNL
Suggestions....?
~Worcse
ASKER
Here is how using a single query:
https://www.experts-exchange.com/questions/23594743/Combined-update-or-Append-query.html?anchorAnswerId=22089157#a22089157
/gustav
https://www.experts-exchange.com/questions/23594743/Combined-update-or-Append-query.html?anchorAnswerId=22089157#a22089157
/gustav
ASKER
cactus_data
thank you for your answer... but not what I am looking for
I wish to imporve upon the loop code I have already created
thank you for your answer... but not what I am looking for
I wish to imporve upon the loop code I have already created
But it might be way faster for many records.
Anyway, something like this should be doable:
Anyway, something like this should be doable:
If Nz(DLookup("[MLS #]", "[DLR Properties1]", "[MLS #] = '" & rsNL![MLS #] & "'"), "") = "" Then
rsDL.AddNew
Else
rsDL.Edit
End If
rsDL!AddDate = Date
rsDL!AddByWho = Forms!UserName!UserName
rsDL![Property Type] = "RES"
rsDL![Prop_Style] = rsNL![Property Style]
rsDL!Listing_Office = 20230
rsDL!LOfficeName = "TEST"
rsDL!Listing_Agent = rsNL![Agent ID]
rsDL!LAgentName = DLookup("[Full_Name]", "DLRAgents_MLS#", "val([Agent #]) = " & rsNL![Agent ID])
rsDL!Bedroom = rsNL!Beds
rsDL!FullBath = rsNL![Full Baths]
rsDL!HalfBath = Nz(rsNL![Half Baths], 0)
rsDL!SqFt_Heated = rsNL![Sq Ft Heated]
rsDL!Total_SqFt = rsNL![Total Bldg SF]
rsDL![House Number] = rsNL![House Number]
rsDL!StreetPfx = StrConv(rsNL![Str Dir Pre], vbProperCase)
rsDL![Street Name] = StrConv(rsNL![Street Name], vbProperCase)
rsDL!StreetSfx = StrConv(rsNL![Str Dir Post], vbProperCase)
rsDL!StreetType = StrConv(rsNL![Street Type], vbProperCase)
rsDL!Unit_No = StrConv(rsNL![Unit #], 3)
rsDL!City = StrConv(rsNL![City], vbProperCase)
rsDL![Zip Code] = rsNL![Zip Code]
rsDL!County = StrConv(rsNL![County], vbUpperCase)
rsDL.Update
rsNL.MoveNext
Wend
End If
In your ELSE, I would think you need code to actually *Move to the record*, ...then edit it...
You can use rst.MoveFirst and rst.FindFirst to do this...
But this gets complex because:
Dlookup will only tell you if *a* match is found, it wont tell you if more that one match is found.
(So you may have duplicates to contend with)
Perhaps DCount() would be a better alternative...?
And if you did use rst.MoveFirst and rst.FindFirst to do this...
...you would still have to have code to "store" the last Looped value, so you could return back to the loop where you left off.
I may be off here, so let's see what other Experts post...
JeffCoachman
You can use rst.MoveFirst and rst.FindFirst to do this...
But this gets complex because:
Dlookup will only tell you if *a* match is found, it wont tell you if more that one match is found.
(So you may have duplicates to contend with)
Perhaps DCount() would be a better alternative...?
And if you did use rst.MoveFirst and rst.FindFirst to do this...
...you would still have to have code to "store" the last Looped value, so you could return back to the loop where you left off.
I may be off here, so let's see what other Experts post...
JeffCoachman
ASKER
cactus_data - your code is still giving me the same results
boag2000 - I believe you are correct b/c I know that the rsNL tbl holds one record that is not in the rsDL tbl... therefore it adds that record to the rsDL tbl...
then the code starts with the first record in the rsDL tbl and tries to edit it... rather than trying to edit the specific record that was found in the DLookup
Is it possibel to use a were cause in the ELSE protion of the code to force the code to edit the specific [MLS #]
boag2000 - I believe you are correct b/c I know that the rsNL tbl holds one record that is not in the rsDL tbl... therefore it adds that record to the rsDL tbl...
then the code starts with the first record in the rsDL tbl and tries to edit it... rather than trying to edit the specific record that was found in the DLookup
Is it possibel to use a were cause in the ELSE protion of the code to force the code to edit the specific [MLS #]
Not sure if a WHERE would really help.
Again, as I am seeing this, you actually have to *Move* the recordset to the matching Record, ...then edit it...
But lets avoid the guesswork of what your data actually is...
Can you post a sample Database?
Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
(Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.
In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.
JeffCoachman
Again, as I am seeing this, you actually have to *Move* the recordset to the matching Record, ...then edit it...
But lets avoid the guesswork of what your data actually is...
Can you post a sample Database?
Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
(Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.
In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.
JeffCoachman
ASKER
boag2000 - here is a better explanation of what I am trying to accomplish
In my datatbase I have a master table (rsDL) which over time has been populated with records that I download from a internet site.
The internet database uses the field [MLS #] as their primary key.
On a daily basis I will download data from the internet site into a temp table (rsNL) which will include only active data for that day (typically no more than 200 records)
While my master table (rsDL) might hold over 10,000 records... I am only looping through the 200 or so records in my tmp table (rsNL) to either update the existing records in my rsDL table or to add those new records into my rsDL table that have not yet been imported from the rsNL table.
There are no duplicate records in my master table (rsDL)
I could do an inner join on the two tables using [MLS #] as the primay key and then do an update.... but I would rather accomplish this with a loop becuse it easier for me to change the coding in the loop as the field names that I download from the internet database change (which they do on a regular basis)
Also, for me, it would be easier to have either an rsDL.Edit or a rsDL.AddNew run within the same loop.
Hope this makes my task a little more unstandable.
In my datatbase I have a master table (rsDL) which over time has been populated with records that I download from a internet site.
The internet database uses the field [MLS #] as their primary key.
On a daily basis I will download data from the internet site into a temp table (rsNL) which will include only active data for that day (typically no more than 200 records)
While my master table (rsDL) might hold over 10,000 records... I am only looping through the 200 or so records in my tmp table (rsNL) to either update the existing records in my rsDL table or to add those new records into my rsDL table that have not yet been imported from the rsNL table.
There are no duplicate records in my master table (rsDL)
I could do an inner join on the two tables using [MLS #] as the primay key and then do an update.... but I would rather accomplish this with a loop becuse it easier for me to change the coding in the loop as the field names that I download from the internet database change (which they do on a regular basis)
Also, for me, it would be easier to have either an rsDL.Edit or a rsDL.AddNew run within the same loop.
Hope this makes my task a little more unstandable.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Gustav,
I'll let you handle this from here...
;-)
Jeff
I'll let you handle this from here...
;-)
Jeff
But I have no more ideas ...
/gustav
/gustav
LOL
Me too...
I can't see a reason why SQL can't be used instead of the record set.
(Especially if the recordset is not working...)
Even though I am not that good at SQL, ...As soon as I have to do things like FindFirst and use multiple recordsets in one sub, I consider using SQL instead.
Both requirements of this system can be done via SQL: (Append records, Update existing records) AFAICT...
I will yield to you on matters concerning either of these disciplines...
Jeff
Me too...
I can't see a reason why SQL can't be used instead of the record set.
(Especially if the recordset is not working...)
Even though I am not that good at SQL, ...As soon as I have to do things like FindFirst and use multiple recordsets in one sub, I consider using SQL instead.
Both requirements of this system can be done via SQL: (Append records, Update existing records) AFAICT...
I will yield to you on matters concerning either of these disciplines...
Jeff
ASKER
Jeff - thank you for your advise... which may be my ultimate course of action
Gustav - I will try this last bit of code you gave, failing which I will resort to accomplishing the task via SQL
Will let you know how I fare...!!
~Worcse
Gustav - I will try this last bit of code you gave, failing which I will resort to accomplishing the task via SQL
Will let you know how I fare...!!
~Worcse
ASKER
Sorry for the delayed response gentleman... just got back from a little out-of-town trip.
As mentioned earlier it appears that I will not be able to accomplish my task solely with the record set so I first import and compile my data to a temp tbl using the record set (looping) and then either add or update this data to my master table via a SQL statement.
Being that both of you basically suggested the same thing... I would like to split the points equally if that bodes well with you.
~Worcse
As mentioned earlier it appears that I will not be able to accomplish my task solely with the record set so I first import and compile my data to a temp tbl using the record set (looping) and then either add or update this data to my master table via a SQL statement.
Being that both of you basically suggested the same thing... I would like to split the points equally if that bodes well with you.
~Worcse
No problem. Glad you found out.
/gustav
/gustav
ASKER
Thank you both for your assistance...
~Worcse
~Worcse
???