Retreive data from a table

[Guest]

I have two tables in my Access database. One table is named Cases and the
other table is named Subpoena. My objective is to look up a CasFileID in the
Cases table and make sure it matches the field File_ID which is a field
located in the Subpoeana table. When the user types in their data in the
File_ID field which is located on my form, it will search the Cases table to
seek the value from the CasFileID. If the File_ID doesn't exist, it will
prompt the user for another one. I have figured out that part by using the
DLookUp function(I have displayed a copy of my code below).

Now, I have another field in the Cases table called CasCaption. What I would
like to accomplish is : when the user types in a File_ID on the form, I would
like the CasCaption to automatically populate in a field on my form called
Case. How can I accomplish this? Thanks you.

Private Sub File_ID_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[CasFileID]", "[Cases]", "[CasFileID] = """ &
Me.File_ID.Text & """")) Then
Cancel = False
Else
DoCmd.CancelEvent
MsgBox "File ID does not exist. Please add a different File ID."
End If
End Sub

 

[Guest]

Instead of a text box tou can use a ComboBox that display both fields
CasFileID and CasCaption

Combo RowSouce:
Select CasFileID , CasCaption From Cases

Make sure that the combo properties:
Column count = 2

The Case text box in the form will have
=[ComboName].Column(1)

***** The column count start with 0 ******

That way the user can select CasFileID that is already in the table, and he
can't make mistakes., and the description will be populated automatically
with no need for any code

 

[Guest]

Thanks for your reply. I know how to do it the way you have explained to me.
The Cases table has over 300,000 + records. So, when the user selects from
the combo box, it takes forever to retreive the records. So, my question is:
If I wanted to code it the way I explained earlier, how would I do it?

Instead of a text box tou can use a ComboBox that display both fields
CasFileID and CasCaption

Combo RowSouce:
Select CasFileID , CasCaption From Cases

Make sure that the combo properties:
Column count = 2

The Case text box in the form will have
=[ComboName].Column(1)

***** The column count start with 0 ******

That way the user can select CasFileID that is already in the table, and he
can't make mistakes., and the description will be populated automatically
with no need for any code

--
Good Luck
BS"D

I have two tables in my Access database. One table is named Cases and the
other table is named Subpoena. My objective is to look up a CasFileID in the
Cases table and make sure it matches the field File_ID which is a field
located in the Subpoeana table. When the user types in their data in the
File_ID field which is located on my form, it will search the Cases table to
seek the value from the CasFileID. If the File_ID doesn't exist, it will
prompt the user for another one. I have figured out that part by using the
DLookUp function(I have displayed a copy of my code below).

Now, I have another field in the Cases table called CasCaption. What I would
like to accomplish is : when the user types in a File_ID on the form, I would
like the CasCaption to automatically populate in a field on my form called
Case. How can I accomplish this? Thanks you.

Private Sub File_ID_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[CasFileID]", "[Cases]", "[CasFileID] = """ &
Me.File_ID.Text & """")) Then
Cancel = False
Else
DoCmd.CancelEvent
MsgBox "File ID does not exist. Please add a different File ID."
End If
End Sub

 

[Guest]

So you won't have to run the dlookup twice, try using record set

Private Sub File_ID_BeforeUpdate(Cancel As Integer)
Dim MyDb As DAO.Database, MyRec As DAO.Recordset

Set MyDB = CurrentDb()
Set MyRec = MyDb.OpenRecordSet("Select * From Cases Where [CasFileID] = """
& Me.File_ID.Text & """")
If Not MyRec.Eof Then
Me.Case = MyRec!CasCaption
Else
Cancel = True
MsgBox "File ID does not exist. Please add a different File ID."
End If
End Sub

--
Good Luck
BS"D

Thanks for your reply. I know how to do it the way you have explained to me.
The Cases table has over 300,000 + records. So, when the user selects from
the combo box, it takes forever to retreive the records. So, my question is:
If I wanted to code it the way I explained earlier, how would I do it?

Instead of a text box tou can use a ComboBox that display both fields
CasFileID and CasCaption

Combo RowSouce:
Select CasFileID , CasCaption From Cases

Make sure that the combo properties:
Column count = 2

The Case text box in the form will have
=[ComboName].Column(1)

***** The column count start with 0 ******

That way the user can select CasFileID that is already in the table, and he
can't make mistakes., and the description will be populated automatically
with no need for any code

--
Good Luck
BS"D

I have two tables in my Access database. One table is named Cases and the
other table is named Subpoena. My objective is to look up a CasFileID in the
Cases table and make sure it matches the field File_ID which is a field
located in the Subpoeana table. When the user types in their data in the
File_ID field which is located on my form, it will search the Cases table to
seek the value from the CasFileID. If the File_ID doesn't exist, it will
prompt the user for another one. I have figured out that part by using the
DLookUp function(I have displayed a copy of my code below).

Now, I have another field in the Cases table called CasCaption. What I would
like to accomplish is : when the user types in a File_ID on the form, I would
like the CasCaption to automatically populate in a field on my form called
Case. How can I accomplish this? Thanks you.

Private Sub File_ID_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[CasFileID]", "[Cases]", "[CasFileID] = """ &
Me.File_ID.Text & """")) Then
Cancel = False
Else
DoCmd.CancelEvent
MsgBox "File ID does not exist. Please add a different File ID."
End If
End Sub