Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA lookup to see if a record exists

Posted on 2013-02-03
2
Medium Priority
?
292 Views
Last Modified: 2013-02-04
I have a master item table (Table1) with about 1500 records in it. I am looping through the records in this table and checking them against a much smaller record set I am creating. If the Itemstr field in table1 exists in any of the solditems fields of the record set, I want to perform some calculations, if not I want to exit the loop.

The record set is created with the following code, and this code seems to work fine.


ProstrSQL = "SELECT ActiveGroupItems.TYPE, ActiveGroupItems.ITEM_NUMBER AS SoldItems"
ProstrSQL = ProstrSQL & " FROM ActiveGroupItems"
ProstrSQL = ProstrSQL & " WHERE ActiveGroupItems.TYPE = " & "'" & CGName & "'"

Set SoldItems = CurrentDb.OpenRecordset(ProstrSQL)



I would like to check the ItemStr variable agains the recordset for the SoldItems field.
0
Comment
Question by:rrudolph
2 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38848779
Could you not do all in one single query?
0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 38848791
Follow on with..

Solditems.findfirst "solditems= '" & ItemsStr & "'"
if not solditems.Nomatch then
' at least one item found
else
' no items found
End if
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

783 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