Finding a particular value in a recordset

[DNS809904]

Hello, (my first post woo hoo!)
I am trying to find a specific value in a recordset which will determine whether I add a new record or not when transferring data from one table to another. I am wondering if there is some sort of "search" command available rather than looping through the entire recordset. Eventually my tables are going to contain thousands of records and I am trying to work with efficiency in mind. Oh yeah, I am working with DAO also. If anybody has any words of wisdom to share with me I would greatly appreciate it!
Thanks in advance,
Dana S.

 

[WayneRyan]

Dana,

For starters, you can try the DLookUp function.

If Not IsNull(DLook("[SomeField]", "SomeTable", "[SomeField] = '" & Me.SomeFormControl & "'") Then
   ' It is there
Else
   ' It is not there
End If

Wayne

 

[DNS809904]

Thanks a bunch for the quick reply Wayne,
I hadn't thought about the DLookup, in fact I have never even used it before. =P I've heard a bit about it, some good and some bad, but it looks like that should help me still. While I was contemplating after I wrote my first message, I was thinking I could also pass in a select statement to another recordset with a where clause checking for the individual (possibly) matching field and see if I come up with any records with that. Any ideas on which would possibly be more efficient?
Thanks again,
Dana

 

[WayneRyan]

DNS,

You have basically three options:

Stored query with parameter.
Open dynamic recordset.
DLookUp.

Those are in order of performance with the DLookUp being the slowest.

However, DLookUp (DSum, DMin, DMax, etc) is also the easiest.
In general, it you don't have too many records, DLookUp is fine.
What is too many records? I think DLookUp is acceptable for numbers
in the low thousands (even more if you're looking at an indexed field).

I'd quickly implement the DLookUp and migrate up to a recordset, then
to a stored query, if performance became an issue.

Wayne

 

[DNS809904]

Further clarification on my conquest.

Wayne,
Thanks for the clarification on the efficiency of each method. Unfortunately my skill level is currently a little too low to correctly understand the detailed differences in each method and how to successfully perform each.
Basically what I am trying to do is import a bunch (approximately between 10,000 and 15,000 and eventually more) records that were once stored terribly in a bunch of Excel spreadsheets into a new relational database that I designed (and am continuously improving). The process of manually importing the records into one table would be quite simple to do manually. However, as this is a relational database, I need to break the data up into several tables. This would be QUITE a tedious task to accomplish manually, especially with the linking of the tables, which is why i would like to automate the task as much as possible, and using VBA is the only and best way that I currently know how to do it.
So where I am at now is importing the data from the spreadsheet into a table, "NewTable". I create a recordset with that, and copy the data from there to the appropriate tables and fields. (Currently, primarily to an Individual table and an Organization table, I should also probably break those up further into address and phone tables, but I was having problems previously that I think I should be able to handle now).
So basically the only way I can think of doing this is with multiple recordsets working together which I hope I will be able to get in order. I guess I will just get the data copying to work correctly and then deal with duplicates when the copying is working smoothly.

As always, if you see anything that could be improved, any comments or suggestions are greatly appreciated!
Thanks again,
DanaS

 

[WayneRyan]

Dana,

Rough idea, not real code, just an idea ...

Let me know if you need help.

Open Tab-Delimited-export For input
Open rst recordset on your table (multiple rsts if multi-tables)
Read A Line
Use Split to force into an array
While Not EOF
   Use DLookUp to check is ArrayElement #1 exists
   If Exists
      rst.Edit
      rst!SomeField = Array(n) ... repeat for other fields
      rst.Update
   Else
      rst.AddNew
      rst!SomeField = Array(n) ... repeat for other fields
      rst.Update
   End If
   '
   ' Work on some other Table
   '
   Read A Line
   Use Split to force into an array
   Wend

Alternative could be to import into a Temp Access table and
use that for processing.

Wayne

 

[DNS809904]

Remembering the recently inserted record?

Wayne,
Thanks for that little template. I am currently working on something pretty similar to that right now. I've got one question though. After I insert the values for one table record, I need to link it with the other table that I am inserting a record for. The tables are linked via a foreign key which is an autonumber on the other table. So when I add the required values and then do the .update, the autonumber primary key is automatically added. Then, how do I remember the primary key value to set as the foreign key in the next table? Would I need to set a bookmark or something? I haven't worked with bookmarks yet so I'm not familiar with them as of yet. Please let me know if I'm on the right track.
Thanks again,
Dana

 

[WayneRyan]

DNS,

rst.AddNew
rst!SomeField = "Something"
'
' Get the AutoNumber here (rst!YourAutoNumberField)
'
rst.Update ' (Can't get it now, it's not viable

Wayne

 

[DNS809904]

Wow you're fast.
Ok, so just save that value into a temporary integer or something?
Thanks,
Dana

 

[WayneRyan]

Dana,

Yes, save it:

Dim SaveKey As Long

Also, while you're developing this, put a new line in your code that
says STOP. When Access runs it will stop (obviously). Then you
can debug by:

CTRL-F9: Move exection to some line
F8: Single-Step
F5: Run to end of code

Hover over a variable and its value will be displayed.

Menu: View --> Immediate Window

Will bring up a lower window.

Then you can do stuff like:

?SaveKey

Which means print the value of SaveKey.

Have fun,
Wayne

 

[DNS809904]

Hm... thanks for the idea about the STOP command, I've actually never heard of that. I have, however, become quite proficient at setting breakpoints, and tracing through the code as neccessary. I assume yours is a similar method. Still, always good to learn new methods of doing the same things.
Thanks again,
Dana

 

[WayneRyan]

Dana,

Breakpoints can disappear, it's just easier for me to put Stop in and
remove it later.

See you later,
Wayne

 

[DNS809904]

Dlookup issue

Ok, I'm trying to check for a duplicate value with the Dlookup and I'm getting an error: Compile Error: Sub or Function not defined.
Here's my syntax:
If(Not IsNull(DLook("[OrganizationName]", "Organization", "[OrganizationName] = '" & newdataRS!Organization & "'")))

Ok, the error is highlighting on the red part of the line above. It should be ok to use a value from the recordset to check from, correct? Otherwise I guess I could assign it to another variable and use that to check, but I don't see why it wouldn't work by using the direct recordset value.... also, is there a difference between "DLook" and "DLookup"???
Thanks,
Dana

UPDATE:
OK, I realized that the command is DLookup, and DLook is probably nothing, just shorthand for DLookup? It appears to be functioning correctly now.
Sorry for the ignorance.