I have a real simple one

[dustinb via AccessMonster.com]

I want to reference an entire table and check for a certain entry. Table
Name is tblClientInfo.

I have code that saves some files as the client number they are associated
with (Works fine). Next it will look at each file in the directory and then
store only the client number portion of the file name ( 12345.xls = 12345)
under strClientNum (works fine).

I would like to scan tblClientInfo for the unique entry that matches
strClientNum in ClientNumber field of tblClientInfo. When the unique match is
found I would like it to store two other records from tblClientInfo that are
labeled ContactName and ContactEmail under their own strings. I cannot
figure out how to search ClientNumber in tblClientInfo for the entry and then
store the other two bits.

Any help would be appreciated. Any questions please ask. Thanks.

 

[Jeff Boyce]

In your post you mention wanting to "store two other records". I can't tell
if you actually mean to add new records, or if you want to add data in
fields in the current record, or want to update existing fields in the
current record (the last two are approximately the same).

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[dustinbrearton via AccessMonster.com]

Sorry, by "storing" them all I am trying to do is put them into variables in
VBA. So store the value from tblClientInfo.ClientName as strClientName and
store tblClientInfo.ContactEmail as strContactEmail.

In your post you mention wanting to "store two other records". I can't tell
if you actually mean to add new records, or if you want to add data in
fields in the current record, or want to update existing fields in the
current record (the last two are approximately the same).

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I want to reference an entire table and check for a certain entry. Table
Name is tblClientInfo.

[quoted text clipped - 16 lines]

Any help would be appreciated. Any questions please ask. Thanks.

 

[Jeff Boyce]

Without some of your existing code to work from, I can only offer general
suggestions.

If you can create a recordset in VBA that contains (only) the record that
matches your criteria, you could "get" the values of those two fields to put
in the variables. Your SQL might look something like:

SELECT Field1, Field2 FROM YourTable WHERE {your selection criteria}

Create a recordset based on that SQL, then refer to the recordset's Field1
and Field2.

If this is too general, provide a bit more specific information...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, by "storing" them all I am trying to do is put them into variables
in
VBA. So store the value from tblClientInfo.ClientName as strClientName
and
store tblClientInfo.ContactEmail as strContactEmail.

In your post you mention wanting to "store two other records". I can't
tell
if you actually mean to add new records, or if you want to add data in
fields in the current record, or want to update existing fields in the
current record (the last two are approximately the same).

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I want to reference an entire table and check for a certain entry. Table
Name is tblClientInfo.

[quoted text clipped - 16 lines]

Any help would be appreciated. Any questions please ask. Thanks.

 

[dustinbrearton via AccessMonster.com]

Thanks. I will research using SQL statements in VBA more and post any
questions that come up trying to use the below concept.

Without some of your existing code to work from, I can only offer general
suggestions.

If you can create a recordset in VBA that contains (only) the record that
matches your criteria, you could "get" the values of those two fields to put
in the variables. Your SQL might look something like:

SELECT Field1, Field2 FROM YourTable WHERE {your selection criteria}

Create a recordset based on that SQL, then refer to the recordset's Field1
and Field2.

If this is too general, provide a bit more specific information...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, by "storing" them all I am trying to do is put them into variables
in

[quoted text clipped - 20 lines]

 

[dustinbrearton via AccessMonster.com]

Thanks. I will research using SQL statements in VBA more and post any
questions that come up trying to use the below concept.

Without some of your existing code to work from, I can only offer general
suggestions.

[quoted text clipped - 20 lines]

Looks like it is doing what I was looking for now. Thanks for the direction.
In case anyone should want an example of the above here is the code. THe
problem was that I did not know to use SQL statements in my VBA code.

Private Sub SendEmails()

Dim strClientNum As String
Dim strobjFileName As String

Dim FSO As Object
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim sFolder As String

Dim RS As Recordset
Dim Db As Database

Set Db = CurrentDb()

strDocName = "tblClientInfo"

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "F:\Scripting\Import\ACT_IMPORT_COLLECTIONS_001\CSVFiles"


If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)
Set Files = Folder.Files
For Each file In Files
strFileName = file.Name
strClientNum = Left(strFileName, 7)
Set Db = CurrentDb() ' Open pointer to current database
SQL = "SELECT * FROM tblClientInfo WHERE tblClientInfo.
ClientNumber = '" & strClientNum & "'"
Set RS = Db.OpenRecordset(SQL)

strContactName = RS![ContactName]
strContactEmail = RS![ContactEmail]

MsgBox strContactName
MsgBox strContactEmail
MsgBox strClientNum
Next file
End If