The best way to search for a specific record ID?

[BobC]

I want to search an existing (access 2007) table (SiteTBL) for a record
that contains specific text (such as "SD-125") in one of the text
fields. When I locate the specific record, I want to retrieve the
record ID (an autonumbered number ID) for use elsewhere in the program.
I have done some VB coding in the past, but am a beginner. I am looking
for a starting point (example code) to help me get started.
Any help would be appreciated?
Thanks!
Bob

 

[John W. Vinson]

I want to search an existing (access 2007) table (SiteTBL) for a record
that contains specific text (such as "SD-125") in one of the text
fields. When I locate the specific record, I want to retrieve the
record ID (an autonumbered number ID) for use elsewhere in the program.
I have done some VB coding in the past, but am a beginner. I am looking
for a starting point (example code) to help me get started.
Any help would be appreciated?
Thanks!
Bob

A very simple Query would seem to be the appropriate tool. Create a query
based on the table; include the record ID field and the text field in
question; put

"SD-125"

in the Criteria line under the text field. Open the query and you'll see the
record (or all the records) where the field is equal to that string.

To find an ID in VBA code use the DLookUp function:

TheID = DLookUp("[IDfield]", "SiteTbl", "[textfield] = 'SD-125'")

If you mean that SD-125 is *somewhere embedded in* additional text, or might
be in any one of several text fields, post back with a more detailed
explanation.

 

[BobC]

This seems to be what I am looking for!?
THANKS!
Bob

I want to search an existing (access 2007) table (SiteTBL) for a record
that contains specific text (such as "SD-125") in one of the text
fields. When I locate the specific record, I want to retrieve the
record ID (an autonumbered number ID) for use elsewhere in the program.
I have done some VB coding in the past, but am a beginner. I am looking
for a starting point (example code) to help me get started.
Any help would be appreciated?
Thanks!
Bob

A very simple Query would seem to be the appropriate tool. Create a query
based on the table; include the record ID field and the text field in
question; put

"SD-125"

in the Criteria line under the text field. Open the query and you'll see the
record (or all the records) where the field is equal to that string.

To find an ID in VBA code use the DLookUp function:

TheID = DLookUp("[IDfield]", "SiteTbl", "[textfield] = 'SD-125'")

If you mean that SD-125 is *somewhere embedded in* additional text, or might
be in any one of several text fields, post back with a more detailed
explanation.

 

[BobC]

I am testing the code using a command button building a piece at a time.
I am getting a Runtime Error '94' - Invalid use of Null
Site Number is a Text field and Service Volume ID is a Number field

My code is:
Private Sub Command8_Click()
Dim TheID As Long
TheID = DLookup("[ID]", "SiteTBL", "(([Site Number] = '6') And
([Service Volume ID] = 308))")

End Sub

I want to search an existing (access 2007) table (SiteTBL) for a record
that contains specific text (such as "SD-125") in one of the text
fields. When I locate the specific record, I want to retrieve the
record ID (an autonumbered number ID) for use elsewhere in the program.
I have done some VB coding in the past, but am a beginner. I am looking
for a starting point (example code) to help me get started.
Any help would be appreciated?
Thanks!
Bob

A very simple Query would seem to be the appropriate tool. Create a query
based on the table; include the record ID field and the text field in
question; put

"SD-125"

in the Criteria line under the text field. Open the query and you'll see the
record (or all the records) where the field is equal to that string.

To find an ID in VBA code use the DLookUp function:

TheID = DLookUp("[IDfield]", "SiteTbl", "[textfield] = 'SD-125'")

If you mean that SD-125 is *somewhere embedded in* additional text, or might
be in any one of several text fields, post back with a more detailed
explanation.

 

[John W. Vinson]

I am testing the code using a command button building a piece at a time.
I am getting a Runtime Error '94' - Invalid use of Null
Site Number is a Text field and Service Volume ID is a Number field

My code is:
Private Sub Command8_Click()
Dim TheID As Long
TheID = DLookup("[ID]", "SiteTBL", "(([Site Number] = '6') And
([Service Volume ID] = 308))")

End Sub

That suggests that there is no record in the table with a Site Number of 6 and
a Service Volume ID of 308. DLookUp will return NULL, and you cannot assign a
NULL value to a variable of datatype Long (only to a Variant).