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
WorcseOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
<I have attached the code I have so far.>
???
0
WorcseOwnerAuthor Commented:
boag2000 - sorry about that
try this
LoopCode.txt
0
Gustav BrockCIOCommented:
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

WorcseOwnerAuthor Commented:
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
0
Gustav BrockCIOCommented:
But it might be way faster for many records.
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

Open in new window

/gustav
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
WorcseOwnerAuthor Commented:
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 #]
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
WorcseOwnerAuthor Commented:
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.
0
Jeffrey CoachmanMIS LiasonCommented:
A sample will always be better than an explanation IMHO
    "A Picture is worth a thousand words"...

<"but I would rather accomplish this with a loop because 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)">
SQL will almost always be faster and use less resources than a recordset.
All things being equal, the same amount of work will be needed with either system, if the fields will be changing.

At a minimum, I would do the Append via SQL.
There is absolutely no benefit to doing this with a recordset.

With your insistence on using a recordset, ...you still seem to be avoiding the fact that you need to "Move to" the matching record in order to edit it....
(Perhaps there is an easier way to do this in a recordest loop, but i am still waiting to hear it...)

This too, IMHO is a lot simpler to do in SQL.

JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
If you don't get any records edited/updated, then either none are found or somehow the edit fails.

Your test could be simplified and rearranged for debugging:

Dim varExist As Variant

varExist = DLookup("[MLS #]", "[DLR Properties1]", "[MLS #] = '" & rsNL![MLS #] & "'")
Debug.Print rsNL![MLS #], varExist

If IsNull(varExist) Then
  rsDL.AddNew
' ... etc.
Else
  rsDL.Edit
' ... etc.
End If
' ... etc.
rsDL.Update

/gustav
0
Jeffrey CoachmanMIS LiasonCommented:
Gustav,

I'll let you handle this from here...

;-)

Jeff
0
Gustav BrockCIOCommented:
But I have no more ideas ...

/gustav
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
WorcseOwnerAuthor Commented:
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
0
WorcseOwnerAuthor Commented:
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
0
Gustav BrockCIOCommented:
No problem. Glad you found out.

/gustav
0
WorcseOwnerAuthor Commented:
Thank you both for your assistance...
~Worcse
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.