Solved

Loop and AddNew or Edit

Posted on 2012-04-09
18
401 Views
Last Modified: 2012-04-18
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
0
Comment
Question by:Worcse
  • 7
  • 6
  • 5
18 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37825024
<I have attached the code I have so far.>
???
0
 

Author Comment

by:Worcse
ID: 37825036
boag2000 - sorry about that
try this
LoopCode.txt
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37825039
0
 

Author Comment

by:Worcse
ID: 37825089
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37825135
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37825213
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
 

Author Comment

by:Worcse
ID: 37825286
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37825324
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
 

Author Comment

by:Worcse
ID: 37825628
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 200 total points
ID: 37826211
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
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 200 total points
ID: 37826476
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37827758
Gustav,

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

;-)

Jeff
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37827842
But I have no more ideas ...

/gustav
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37828027
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
 

Author Comment

by:Worcse
ID: 37828375
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
 

Author Comment

by:Worcse
ID: 37864272
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37864377
No problem. Glad you found out.

/gustav
0
 

Author Closing Comment

by:Worcse
ID: 37864398
Thank you both for your assistance...
~Worcse
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now