Find Specific Record in database Table via VB code

G

Guest

I need to be able to lookup a specific record in an Indexed database using VB
code.

Table name DPS_FR_ATTORNEY has the following structure:
Atty_Num (numeric value, primary key)
First_Nme (text value, first name)
Middle_Nme (text value, middle name)
Last_Nme (text value, last name)
Firm_Nme (text value, firm name)
Addr1_Txt (text value, address)
City_Nme (text value, city name)
State_Cde (text value, state abbreviated code)
Zip_Cde (numerical value, zip code)

I need to use VB code to locate specific record in a table by supplying a
value for the Atty_Num (primary key) field. If record found, use for
printing address labels. I've attempted to use a macro calling a query to
build recordset, but called query loses search value (temp field in program)
extracted from other record and query fails.
"Select * from DPS_FR_ATTORNEY where Atty_Num =
Form![Fr_Letter_Menu]![intFindKey]". intFindKey was defined with Dim
intFindKey As Integer in program.

I'm not sure if I need to use a query to get the record, then extract needed
data, or read the DPS_FR_ATTORNEY table directly. There would be less
overhead if I could read DPS_FR_ATTORNEY table directly, lookup the record or
find the corresponding record, extract data for new results table and close..
but I'm short on vba code experience to locate that record in
DPS_FR_ATTORNEY Table. Again this table is keyed to Atty_Num field.

Any help would be appreciated, I'd like to not have to loop through the
entire file to find this specific record, when I already know if the record
exists and what its number/key is. Is there vb code or capability to
manually code this lookup/find.


thanks


Robert Nusz
Sr. Programmer Analyst II
 
J

Justin Hoffman

RNUSZ@OKDPS said:
I need to be able to lookup a specific record in an Indexed database using
VB
code.

Table name DPS_FR_ATTORNEY has the following structure:
Atty_Num (numeric value, primary key)
First_Nme (text value, first name)
Middle_Nme (text value, middle name)
Last_Nme (text value, last name)
Firm_Nme (text value, firm name)
Addr1_Txt (text value, address)
City_Nme (text value, city name)
State_Cde (text value, state abbreviated code)
Zip_Cde (numerical value, zip code)

I need to use VB code to locate specific record in a table by supplying a
value for the Atty_Num (primary key) field. If record found, use for
printing address labels. I've attempted to use a macro calling a query to
build recordset, but called query loses search value (temp field in
program)
extracted from other record and query fails.
"Select * from DPS_FR_ATTORNEY where Atty_Num =
Form![Fr_Letter_Menu]![intFindKey]". intFindKey was defined with Dim
intFindKey As Integer in program.

I'm not sure if I need to use a query to get the record, then extract
needed
data, or read the DPS_FR_ATTORNEY table directly. There would be less
overhead if I could read DPS_FR_ATTORNEY table directly, lookup the record
or
find the corresponding record, extract data for new results table and
close..
but I'm short on vba code experience to locate that record in
DPS_FR_ATTORNEY Table. Again this table is keyed to Atty_Num field.

Any help would be appreciated, I'd like to not have to loop through the
entire file to find this specific record, when I already know if the
record
exists and what its number/key is. Is there vb code or capability to
manually code this lookup/find.


thanks


Robert Nusz
Sr. Programmer Analyst II
Click to expand...



If you really need a recordset, then you could create one but I'm not sure
you should bother. If you are simply printing a label for the selected
record then you could simply set the datasource of the report to be:
Select * from DPS_FR_ATTORNEY where Atty_Num=
Forms!MyForm.txtFindKey
Assuming you know how to set the value of the textbox "txtFindKey" to the
correct number. If there are things that you need to do with a recordset
(like updating the data) then let us know if you are using DAO,ADO or are
unsure.
 
G

Guest

Justin,

Thanks for the quick response. To answer your question, no the new Attorney
recordset is not needed. I attempted once to use the select *
DPS_FR_ATTORNEY select but failed to find the record. The key for
DPS_FR_ATTORNEY is ATTY_NUM, there in the primary table is another
corresponding ATTY_NUM field. I select this value, plug it into a field
called intSearchAtty or similar name and used something like:
Select * From DPS_FR_ATTORNEY Where ATTY_NUM = intSearchAtty. When it hit
this strSQL statement it passed syntactical checking but failed to find the
record even though rsA![ATTY_NUM] may have had a value of 1, and
intSearchAtty had been defined as DIM intSearchAtty As Integer and then using
intSearchAtty = rsA![ATTY_NUM], then the strSQL statement above, still lacked
finding the record.


Any other clues what I did incorrectly.

Thanks,

--
Robert Nusz
Sr. Programmer Analyst II


Justin Hoffman said:
RNUSZ@OKDPS said:
I need to be able to lookup a specific record in an Indexed database using
VB
code.

Table name DPS_FR_ATTORNEY has the following structure:
Atty_Num (numeric value, primary key)
First_Nme (text value, first name)
Middle_Nme (text value, middle name)
Last_Nme (text value, last name)
Firm_Nme (text value, firm name)
Addr1_Txt (text value, address)
City_Nme (text value, city name)
State_Cde (text value, state abbreviated code)
Zip_Cde (numerical value, zip code)

I need to use VB code to locate specific record in a table by supplying a
value for the Atty_Num (primary key) field. If record found, use for
printing address labels. I've attempted to use a macro calling a query to
build recordset, but called query loses search value (temp field in
program)
extracted from other record and query fails.
"Select * from DPS_FR_ATTORNEY where Atty_Num =
Form![Fr_Letter_Menu]![intFindKey]". intFindKey was defined with Dim
intFindKey As Integer in program.

I'm not sure if I need to use a query to get the record, then extract
needed
data, or read the DPS_FR_ATTORNEY table directly. There would be less
overhead if I could read DPS_FR_ATTORNEY table directly, lookup the record
or
find the corresponding record, extract data for new results table and
close..
but I'm short on vba code experience to locate that record in
DPS_FR_ATTORNEY Table. Again this table is keyed to Atty_Num field.

Any help would be appreciated, I'd like to not have to loop through the
entire file to find this specific record, when I already know if the
record
exists and what its number/key is. Is there vb code or capability to
manually code this lookup/find.


thanks


Robert Nusz
Sr. Programmer Analyst II
Click to expand...



If you really need a recordset, then you could create one but I'm not sure
you should bother. If you are simply printing a label for the selected
record then you could simply set the datasource of the report to be:
Select * from DPS_FR_ATTORNEY where Atty_Num=
Forms!MyForm.txtFindKey
Assuming you know how to set the value of the textbox "txtFindKey" to the
correct number. If there are things that you need to do with a recordset
(like updating the data) then let us know if you are using DAO,ADO or are
unsure.
Click to expand...
 
J

Justin Hoffman

RNUSZ@OKDPS said:
Justin,

Thanks for the quick response. To answer your question, no the new
Attorney
recordset is not needed. I attempted once to use the select *
DPS_FR_ATTORNEY select but failed to find the record. The key for
DPS_FR_ATTORNEY is ATTY_NUM, there in the primary table is another
corresponding ATTY_NUM field. I select this value, plug it into a field
called intSearchAtty or similar name and used something like:
Select * From DPS_FR_ATTORNEY Where ATTY_NUM = intSearchAtty. When it hit
this strSQL statement it passed syntactical checking but failed to find
the
record even though rsA![ATTY_NUM] may have had a value of 1, and
intSearchAtty had been defined as DIM intSearchAtty As Integer and then
using
intSearchAtty = rsA![ATTY_NUM], then the strSQL statement above, still
lacked
finding the record.


Any other clues what I did incorrectly.

Thanks,
Click to expand...


After you set the sql string, why not write
Debug.Print strSQL
MsgBox strSQL
Then you can see what the sql string is really set to.
If you are still stuck, I could send you something by e-mail if you let me
know where to send it.
 
G

Guest

This is the latest code snippet that is failing on the strSQL string, stating
Select can not be executed.

**************************************************************************
If rsA![ATTY_NUM] <> 0 Then
'select attorney record via sql statement
intSelectAtty = rsA![ATTY_NUM]
strSQL = "SELECT * FROM DPS_FR_ATTORNEY WHERE ATTY_NUM = Me!intSelectAtty"
CurrentDb.Execute strSQL, dbFailOnError
MsgBox " ATTORNEY = " & DSP_FR_ATTORNEY![LAST_NME] & " "
& _
DPS_FR_ATTORNEY![FIRST_NME]
MsgBox " Select Attorney # = " & intSelectAtty & " " &
DPS_FR_ATTORNEY![LAST_NME]
'fncSeekAttyRecord
rsD.AddNew
rsD![CASE_NUM_YR] = intHoldCaseNumYr
rsD![CASE_NUM] = intHoldCaseNum
rsD![PRTNO_NUM] = intHoldPrtNoNum
rsD![NAME_TXT] = DPS_FR_ATTORNEY![FIRST_NME] & " " & _
DPS_FR_ATTORNEY![MIDDLE_NME] & " " & _
DPS_FR_ATTORNEY![LAST_NME] & " " & _
DPS_FR_ATTORNEY![SUBTITLE_TXT]
rsD![FIRM_NME] = DPS_FR_ATTORNEY![FIRM_NME]
rsD![ADDR1_TXT] = DPS_FR_ATTORNEY![ADDR1_TXT]
rsD![ADDR2_TXT] = DPS_FR_ATTORNEY![ADDR2_TXT]
rsD![CITY_TXT] = DPS_FR_ATTORNEY![CITY_NME]
rsD![STATE_CDE] = DPS_FR_ATTORNEY![STATE_CDE]
rsD![ZIPCDE_TXT] = DPS_FR_ATTORNEY![ZIP_CDE] & " " & _
DPS_FR_ATTORNEY![ZIP4_CDE]
MsgBox " NAME_TXT 3 = " & rsD![NAME_TXT]
rsD.Update
End If

*********************************

Appliation is failing with error 3065, error description "CANNOT EXECUTE A
SELECT QUERY!"

I attempted to put it into a macro and call the macro via the code, but when
I do it that way, its states it can not find the value for
Forms![FRR-Letter-Menu]![intSelectAtty] which is where the search value had
been placed prior to the call to execute the macro. Any other clues..

Thanks
--
Robert Nusz
Sr. Programmer Analyst II


Justin Hoffman said:
RNUSZ@OKDPS said:
Justin,

Thanks for the quick response. To answer your question, no the new
Attorney
recordset is not needed. I attempted once to use the select *
DPS_FR_ATTORNEY select but failed to find the record. The key for
DPS_FR_ATTORNEY is ATTY_NUM, there in the primary table is another
corresponding ATTY_NUM field. I select this value, plug it into a field
called intSearchAtty or similar name and used something like:
Select * From DPS_FR_ATTORNEY Where ATTY_NUM = intSearchAtty. When it hit
this strSQL statement it passed syntactical checking but failed to find
the
record even though rsA![ATTY_NUM] may have had a value of 1, and
intSearchAtty had been defined as DIM intSearchAtty As Integer and then
using
intSearchAtty = rsA![ATTY_NUM], then the strSQL statement above, still
lacked
finding the record.


Any other clues what I did incorrectly.

Thanks,
Click to expand...


After you set the sql string, why not write
Debug.Print strSQL
MsgBox strSQL
Then you can see what the sql string is really set to.
If you are still stuck, I could send you something by e-mail if you let me
know where to send it.
Click to expand...
 
J

Justin Hoffman

RNUSZ@OKDPS said:
This is the latest code snippet that is failing on the strSQL string,
stating
Select can not be executed.

**************************************************************************
If rsA![ATTY_NUM] <> 0 Then
'select attorney record via sql statement
intSelectAtty = rsA![ATTY_NUM]
strSQL = "SELECT * FROM DPS_FR_ATTORNEY WHERE ATTY_NUM =
Me!intSelectAtty"
CurrentDb.Execute strSQL, dbFailOnError
MsgBox " ATTORNEY = " & DSP_FR_ATTORNEY![LAST_NME] & "
"
& _
DPS_FR_ATTORNEY![FIRST_NME]
MsgBox " Select Attorney # = " & intSelectAtty & " " &
DPS_FR_ATTORNEY![LAST_NME]
'fncSeekAttyRecord
rsD.AddNew
rsD![CASE_NUM_YR] = intHoldCaseNumYr
rsD![CASE_NUM] = intHoldCaseNum
rsD![PRTNO_NUM] = intHoldPrtNoNum
rsD![NAME_TXT] = DPS_FR_ATTORNEY![FIRST_NME] & " " & _
DPS_FR_ATTORNEY![MIDDLE_NME] & " " & _
DPS_FR_ATTORNEY![LAST_NME] & " " & _
DPS_FR_ATTORNEY![SUBTITLE_TXT]
rsD![FIRM_NME] = DPS_FR_ATTORNEY![FIRM_NME]
rsD![ADDR1_TXT] = DPS_FR_ATTORNEY![ADDR1_TXT]
rsD![ADDR2_TXT] = DPS_FR_ATTORNEY![ADDR2_TXT]
rsD![CITY_TXT] = DPS_FR_ATTORNEY![CITY_NME]
rsD![STATE_CDE] = DPS_FR_ATTORNEY![STATE_CDE]
rsD![ZIPCDE_TXT] = DPS_FR_ATTORNEY![ZIP_CDE] & " " & _
DPS_FR_ATTORNEY![ZIP4_CDE]
MsgBox " NAME_TXT 3 = " & rsD![NAME_TXT]
rsD.Update
End If

*********************************

Appliation is failing with error 3065, error description "CANNOT EXECUTE A
SELECT QUERY!"

I attempted to put it into a macro and call the macro via the code, but
when
I do it that way, its states it can not find the value for
Forms![FRR-Letter-Menu]![intSelectAtty] which is where the search value
had
been placed prior to the call to execute the macro. Any other clues..

Thanks
Click to expand...


Hi Robert
The error is not surprising - where did you get that code from? The syntax
..Execute strSQL, dbFailOnError is for performing action queries so I guess
you haven't done a whole lot of Access programming before.
If you don't mind a quick back-to-basics exercise, create a new form with a
textbox and a button on it. Name the textbox "txtATTY_NUM" and the button
"cmdTest". Paste the code into the module and make sure that when you look
at the button's properties and see the OnClick event shows the code.
Enter a number in the textbox and press the button - and should see the
selected record. I know it isn't exactly what you are doing but it is hard
to see what you are doing with .Execute .AddNew and .Update. The offer of
an example by e-mail still stands.


Public Function DefineQuery(strQueryName As String, strSQL As String) As
Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs(strQueryName)

qdf.SQL = strSQL

DefineQuery = True

Exit_Handler:

On Error Resume Next

If Not qdf Is Nothing Then
Set qdf = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:

Select Case Err.Number

Case 3265
' The querydef doesn't exist so create it
Set qdf = dbs.CreateQueryDef(strQueryName)
Resume Next

Case Else
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Select

End Function

Private Sub cmdTest_Click()

On Error GoTo Err_Handler

Dim strSQL As String

strSQL = "SELECT * FROM DPS_FR_ATTORNEY WHERE ATTY_NUM" & _
Nz(Me.txtATTY_NUM, "")

If DefineQuery("qryTest", strSQL) Then
DoCmd.OpenQuery "qryTest"
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
 
G

Guest

Justin,

I'm still in a quandry on this locate of specific records, I can send you a
copy of the code I'm currently using and would appreciate some guidance on VB
code to assist in record match control. My work email is
(e-mail address removed).

thanks


--
Robert Nusz
Sr. Programmer Analyst II


Justin Hoffman said:
RNUSZ@OKDPS said:
This is the latest code snippet that is failing on the strSQL string,
stating
Select can not be executed.

**************************************************************************
If rsA![ATTY_NUM] <> 0 Then
'select attorney record via sql statement
intSelectAtty = rsA![ATTY_NUM]
strSQL = "SELECT * FROM DPS_FR_ATTORNEY WHERE ATTY_NUM =
Me!intSelectAtty"
CurrentDb.Execute strSQL, dbFailOnError
MsgBox " ATTORNEY = " & DSP_FR_ATTORNEY![LAST_NME] & "
"
& _
DPS_FR_ATTORNEY![FIRST_NME]
MsgBox " Select Attorney # = " & intSelectAtty & " " &
DPS_FR_ATTORNEY![LAST_NME]
'fncSeekAttyRecord
rsD.AddNew
rsD![CASE_NUM_YR] = intHoldCaseNumYr
rsD![CASE_NUM] = intHoldCaseNum
rsD![PRTNO_NUM] = intHoldPrtNoNum
rsD![NAME_TXT] = DPS_FR_ATTORNEY![FIRST_NME] & " " & _
DPS_FR_ATTORNEY![MIDDLE_NME] & " " & _
DPS_FR_ATTORNEY![LAST_NME] & " " & _
DPS_FR_ATTORNEY![SUBTITLE_TXT]
rsD![FIRM_NME] = DPS_FR_ATTORNEY![FIRM_NME]
rsD![ADDR1_TXT] = DPS_FR_ATTORNEY![ADDR1_TXT]
rsD![ADDR2_TXT] = DPS_FR_ATTORNEY![ADDR2_TXT]
rsD![CITY_TXT] = DPS_FR_ATTORNEY![CITY_NME]
rsD![STATE_CDE] = DPS_FR_ATTORNEY![STATE_CDE]
rsD![ZIPCDE_TXT] = DPS_FR_ATTORNEY![ZIP_CDE] & " " & _
DPS_FR_ATTORNEY![ZIP4_CDE]
MsgBox " NAME_TXT 3 = " & rsD![NAME_TXT]
rsD.Update
End If

*********************************

Appliation is failing with error 3065, error description "CANNOT EXECUTE A
SELECT QUERY!"

I attempted to put it into a macro and call the macro via the code, but
when
I do it that way, its states it can not find the value for
Forms![FRR-Letter-Menu]![intSelectAtty] which is where the search value
had
been placed prior to the call to execute the macro. Any other clues..

Thanks
Click to expand...


Hi Robert
The error is not surprising - where did you get that code from? The syntax
..Execute strSQL, dbFailOnError is for performing action queries so I guess
you haven't done a whole lot of Access programming before.
If you don't mind a quick back-to-basics exercise, create a new form with a
textbox and a button on it. Name the textbox "txtATTY_NUM" and the button
"cmdTest". Paste the code into the module and make sure that when you look
at the button's properties and see the OnClick event shows the code.
Enter a number in the textbox and press the button - and should see the
selected record. I know it isn't exactly what you are doing but it is hard
to see what you are doing with .Execute .AddNew and .Update. The offer of
an example by e-mail still stands.


Public Function DefineQuery(strQueryName As String, strSQL As String) As
Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs(strQueryName)

qdf.SQL = strSQL

DefineQuery = True

Exit_Handler:

On Error Resume Next

If Not qdf Is Nothing Then
Set qdf = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:

Select Case Err.Number

Case 3265
' The querydef doesn't exist so create it
Set qdf = dbs.CreateQueryDef(strQueryName)
Resume Next

Case Else
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Select

End Function

Private Sub cmdTest_Click()

On Error GoTo Err_Handler

Dim strSQL As String

strSQL = "SELECT * FROM DPS_FR_ATTORNEY WHERE ATTY_NUM" & _
Nz(Me.txtATTY_NUM, "")

If DefineQuery("qryTest", strSQL) Then
DoCmd.OpenQuery "qryTest"
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
Click to expand...
 
J

Justin Hoffman

RNUSZ@OKDPS said:
Justin,

I'm still in a quandry on this locate of specific records, I can send you
a
copy of the code I'm currently using and would appreciate some guidance on
VB
code to assist in record match control. My work email is
(e-mail address removed).

thanks
Click to expand...



Done - I've sent you an e-mail with my real address.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Write New table record failing with error 424 1
Error 3265 re-post 1
Error # 3265 - Desc. Item Not Found in this collection ERROR 1
glitch: opening form to specific record using its ID 2
Add Record To Existing Table via VB code 1
How to change existing table record value by VBA and How to add newrecord in existing table by VBA 1
Create new record in diff table 1
Duplicate record in Form and Subform 0

Top