Using an unbound texbox to find a record in form

S

s.f.

Hi, I am using Access 2002.

I tried to set up an unbound texbox in form so that users can type in a
value to find a specific record to update other fields.

For the unbound textbox (named ttrackid) I put in the following VBA codes
for event procedure on EXIT (please see below). However, the line " Dim
currdb As Database" was highlighted and I received an error msg saying --

Compile error:
user-defined type not defined.

Please help. The codes were working fine in another database.

Thank you,

Private Sub ttrackid_Exit(Cancel As Integer)
On Error GoTo Err_ttrackid_exit
Dim currdb As Database
Set currdb = CurrentDb()
' If ttrackid is BLANK
If IsNull(Me![ttrackid]) Then
' Do nothing but leave subroutine. Any msg gets in way.
'MsgBox "The ENTER TRACKID box is empty."
Exit Sub
End If

' If ttrackid NOT BLANK,
' check to see if ttrackid exists already
If Not IsNull(Me![ttrackid]) Then
Dim rsTRACKID As Recordset
Set rsTRACKID = currdb.OpenRecordset _
("select * from QFUIX where " & _
"QFUIX![TRACKID] =" & Me![ttrackid])

Dim intCountTRACKID As Integer
intCountTRCKID = rsTRACKID.RecordCount
End If

' If TRACKID DOES NOT EXIST,
If intCountTRACKID = 0 Then
MsgBox "This TRACKID does not exist. " & _
"Enter another TRACKID or leave blank."
Forms!FUIXForm!TRACKID.SetFocus
Forms!FUIXForm!ttrackid.SetFocus

' If TRACKID EXISTS, show record
Else
[Forms]![FUIXForm]![TRACKID].SetFocus
DoCmd.FindRecord Me![ttrackid], , , , 0
Me![ttrackid] = Null
Exit Sub
End If

Exit_ttrackid_exit:
Exit Sub

Err_ttrackid_exit:
MsgBox Err.Description
Resume Exit_ttrackid_exit

End Sub
 
J

Jeff Boyce

That's quite a bit of work ... and there may be an easier way!

You could put an unbound combobox in the form's header and let the user look
up the record they want to find. Then, in the combobox's AfterUpdate event,
you could either requery the form's source or apply a filter to limit the
record displayed.

If you have to use the code approach, there's a good chance that Access
can't tell whether you mean an ADO.Database or a DAO.Database. You might
need to set a reference to the DAO model to use that...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
B

Bob

I am using access 2003 and to search for a record assuming each one has an id
number i place unbound text box named "search key"on the switchbord from
plus a command button .
with the following code on click
Private Sub Command146_Click()


If IsNothing(Me.SearchKey) Then
MsgBox " pse enter a client id in searchKey Box"
Exit Sub
End If

Dim db As Database
Set db = CurrentDb
Dim rec As DAO.Recordset
Set rec = db.OpenRecordset("name of table", dbOpenDynaset)
With rec
..FindFirst "[ClientID] = " & Me!SearchKey

If Not .NoMatch Then ' we found it
DoCmd.OpenForm "Name of Form", WhereCondition:="[Client ID] = " &
Me!SearchKey
End If

If Not .NoMatch = False Then
MsgBox "No Match"
End If

End With

As i said this work fine for me, hope i understood you question






End Sub
--
RFortune


Jeff Boyce said:
That's quite a bit of work ... and there may be an easier way!

You could put an unbound combobox in the form's header and let the user look
up the record they want to find. Then, in the combobox's AfterUpdate event,
you could either requery the form's source or apply a filter to limit the
record displayed.

If you have to use the code approach, there's a good chance that Access
can't tell whether you mean an ADO.Database or a DAO.Database. You might
need to set a reference to the DAO model to use that...

Regards

Jeff Boyce
Microsoft Office/Access MVP

s.f. said:
Hi, I am using Access 2002.

I tried to set up an unbound texbox in form so that users can type in a
value to find a specific record to update other fields.

For the unbound textbox (named ttrackid) I put in the following VBA codes
for event procedure on EXIT (please see below). However, the line " Dim
currdb As Database" was highlighted and I received an error msg saying --

Compile error:
user-defined type not defined.

Please help. The codes were working fine in another database.

Thank you,

Private Sub ttrackid_Exit(Cancel As Integer)
On Error GoTo Err_ttrackid_exit
Dim currdb As Database
Set currdb = CurrentDb()
' If ttrackid is BLANK
If IsNull(Me![ttrackid]) Then
' Do nothing but leave subroutine. Any msg gets in way.
'MsgBox "The ENTER TRACKID box is empty."
Exit Sub
End If

' If ttrackid NOT BLANK,
' check to see if ttrackid exists already
If Not IsNull(Me![ttrackid]) Then
Dim rsTRACKID As Recordset
Set rsTRACKID = currdb.OpenRecordset _
("select * from QFUIX where " & _
"QFUIX![TRACKID] =" & Me![ttrackid])

Dim intCountTRACKID As Integer
intCountTRCKID = rsTRACKID.RecordCount
End If

' If TRACKID DOES NOT EXIST,
If intCountTRACKID = 0 Then
MsgBox "This TRACKID does not exist. " & _
"Enter another TRACKID or leave blank."
Forms!FUIXForm!TRACKID.SetFocus
Forms!FUIXForm!ttrackid.SetFocus

' If TRACKID EXISTS, show record
Else
[Forms]![FUIXForm]![TRACKID].SetFocus
DoCmd.FindRecord Me![ttrackid], , , , 0
Me![ttrackid] = Null
Exit Sub
End If

Exit_ttrackid_exit:
Exit Sub

Err_ttrackid_exit:
MsgBox Err.Description
Resume Exit_ttrackid_exit

End Sub
Click to expand...
Click to expand...
 
J

Jeff Boyce

Do you have a reference set to the DAO object model?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Bob said:
I am using access 2003 and to search for a record assuming each one has an
id
number i place unbound text box named "search key"on the switchbord from
plus a command button .
with the following code on click
Private Sub Command146_Click()


If IsNothing(Me.SearchKey) Then
MsgBox " pse enter a client id in searchKey Box"
Exit Sub
End If

Dim db As Database
Set db = CurrentDb
Dim rec As DAO.Recordset
Set rec = db.OpenRecordset("name of table", dbOpenDynaset)
With rec
.FindFirst "[ClientID] = " & Me!SearchKey

If Not .NoMatch Then ' we found it
DoCmd.OpenForm "Name of Form", WhereCondition:="[Client ID] = " &
Me!SearchKey
End If

If Not .NoMatch = False Then
MsgBox "No Match"
End If

End With

As i said this work fine for me, hope i understood you question






End Sub
--
RFortune


Jeff Boyce said:
That's quite a bit of work ... and there may be an easier way!

You could put an unbound combobox in the form's header and let the user
look
up the record they want to find. Then, in the combobox's AfterUpdate
event,
you could either requery the form's source or apply a filter to limit the
record displayed.

If you have to use the code approach, there's a good chance that Access
can't tell whether you mean an ADO.Database or a DAO.Database. You might
need to set a reference to the DAO model to use that...

Regards

Jeff Boyce
Microsoft Office/Access MVP

s.f. said:
Hi, I am using Access 2002.

I tried to set up an unbound texbox in form so that users can type in a
value to find a specific record to update other fields.

For the unbound textbox (named ttrackid) I put in the following VBA
codes
for event procedure on EXIT (please see below). However, the line " Dim
currdb As Database" was highlighted and I received an error msg
saying --

Compile error:
user-defined type not defined.

Please help. The codes were working fine in another database.

Thank you,

Private Sub ttrackid_Exit(Cancel As Integer)
On Error GoTo Err_ttrackid_exit
Dim currdb As Database
Set currdb = CurrentDb()
' If ttrackid is BLANK
If IsNull(Me![ttrackid]) Then
' Do nothing but leave subroutine. Any msg gets in way.
'MsgBox "The ENTER TRACKID box is empty."
Exit Sub
End If

' If ttrackid NOT BLANK,
' check to see if ttrackid exists already
If Not IsNull(Me![ttrackid]) Then
Dim rsTRACKID As Recordset
Set rsTRACKID = currdb.OpenRecordset _
("select * from QFUIX where " & _
"QFUIX![TRACKID] =" & Me![ttrackid])

Dim intCountTRACKID As Integer
intCountTRCKID = rsTRACKID.RecordCount
End If

' If TRACKID DOES NOT EXIST,
If intCountTRACKID = 0 Then
MsgBox "This TRACKID does not exist. " & _
"Enter another TRACKID or leave blank."
Forms!FUIXForm!TRACKID.SetFocus
Forms!FUIXForm!ttrackid.SetFocus

' If TRACKID EXISTS, show record
Else
[Forms]![FUIXForm]![TRACKID].SetFocus
DoCmd.FindRecord Me![ttrackid], , , , 0
Me![ttrackid] = Null
Exit Sub
End If

Exit_ttrackid_exit:
Exit Sub

Err_ttrackid_exit:
MsgBox Err.Description
Resume Exit_ttrackid_exit

End Sub
Click to expand...
Click to expand...
Click to expand...
 
B

Bob

i trust you mean the references my progamme uses located in OFFICE 11
Microsoft DAO 3.6 Object library
Mircosoft access 11.object 11 object library
microsof active Data object 2 .1 library

As i stated earlier my code works fine for 2003, but for version 2002 it
might be slightly different?
--
Bob
RFortune


Jeff Boyce said:
Do you have a reference set to the DAO object model?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Bob said:
I am using access 2003 and to search for a record assuming each one has an
id
number i place unbound text box named "search key"on the switchbord from
plus a command button .
with the following code on click
Private Sub Command146_Click()


If IsNothing(Me.SearchKey) Then
MsgBox " pse enter a client id in searchKey Box"
Exit Sub
End If

Dim db As Database
Set db = CurrentDb
Dim rec As DAO.Recordset
Set rec = db.OpenRecordset("name of table", dbOpenDynaset)
With rec
.FindFirst "[ClientID] = " & Me!SearchKey

If Not .NoMatch Then ' we found it
DoCmd.OpenForm "Name of Form", WhereCondition:="[Client ID] = " &
Me!SearchKey
End If

If Not .NoMatch = False Then
MsgBox "No Match"
End If

End With

As i said this work fine for me, hope i understood you question






End Sub
--
RFortune


Jeff Boyce said:
That's quite a bit of work ... and there may be an easier way!

You could put an unbound combobox in the form's header and let the user
look
up the record they want to find. Then, in the combobox's AfterUpdate
event,
you could either requery the form's source or apply a filter to limit the
record displayed.

If you have to use the code approach, there's a good chance that Access
can't tell whether you mean an ADO.Database or a DAO.Database. You might
need to set a reference to the DAO model to use that...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, I am using Access 2002.

I tried to set up an unbound texbox in form so that users can type in a
value to find a specific record to update other fields.

For the unbound textbox (named ttrackid) I put in the following VBA
codes
for event procedure on EXIT (please see below). However, the line " Dim
currdb As Database" was highlighted and I received an error msg
saying --

Compile error:
user-defined type not defined.

Please help. The codes were working fine in another database.

Thank you,

Private Sub ttrackid_Exit(Cancel As Integer)
On Error GoTo Err_ttrackid_exit
Dim currdb As Database
Set currdb = CurrentDb()
' If ttrackid is BLANK
If IsNull(Me![ttrackid]) Then
' Do nothing but leave subroutine. Any msg gets in way.
'MsgBox "The ENTER TRACKID box is empty."
Exit Sub
End If

' If ttrackid NOT BLANK,
' check to see if ttrackid exists already
If Not IsNull(Me![ttrackid]) Then
Dim rsTRACKID As Recordset
Set rsTRACKID = currdb.OpenRecordset _
("select * from QFUIX where " & _
"QFUIX![TRACKID] =" & Me![ttrackid])

Dim intCountTRACKID As Integer
intCountTRCKID = rsTRACKID.RecordCount
End If

' If TRACKID DOES NOT EXIST,
If intCountTRACKID = 0 Then
MsgBox "This TRACKID does not exist. " & _
"Enter another TRACKID or leave blank."
Forms!FUIXForm!TRACKID.SetFocus
Forms!FUIXForm!ttrackid.SetFocus

' If TRACKID EXISTS, show record
Else
[Forms]![FUIXForm]![TRACKID].SetFocus
DoCmd.FindRecord Me![ttrackid], , , , 0
Me![ttrackid] = Null
Exit Sub
End If

Exit_ttrackid_exit:
Exit Sub

Err_ttrackid_exit:
MsgBox Err.Description
Resume Exit_ttrackid_exit

End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

Yes.


Regards

Jeff Boyce
Microsoft Office/Access MVP

Bob said:
i trust you mean the references my progamme uses located in OFFICE 11
Microsoft DAO 3.6 Object library
Mircosoft access 11.object 11 object library
microsof active Data object 2 .1 library

As i stated earlier my code works fine for 2003, but for version 2002 it
might be slightly different?
--
Bob
RFortune


Jeff Boyce said:
Do you have a reference set to the DAO object model?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Bob said:
I am using access 2003 and to search for a record assuming each one has
an
id
number i place unbound text box named "search key"on the switchbord
from
plus a command button .
with the following code on click
Private Sub Command146_Click()


If IsNothing(Me.SearchKey) Then
MsgBox " pse enter a client id in searchKey Box"
Exit Sub
End If

Dim db As Database
Set db = CurrentDb
Dim rec As DAO.Recordset
Set rec = db.OpenRecordset("name of table", dbOpenDynaset)
With rec
.FindFirst "[ClientID] = " & Me!SearchKey

If Not .NoMatch Then ' we found it
DoCmd.OpenForm "Name of Form", WhereCondition:="[Client ID] = " &
Me!SearchKey
End If

If Not .NoMatch = False Then
MsgBox "No Match"
End If

End With

As i said this work fine for me, hope i understood you question






End Sub
--
RFortune


:

That's quite a bit of work ... and there may be an easier way!

You could put an unbound combobox in the form's header and let the
user
look
up the record they want to find. Then, in the combobox's AfterUpdate
event,
you could either requery the form's source or apply a filter to limit
the
record displayed.

If you have to use the code approach, there's a good chance that
Access
can't tell whether you mean an ADO.Database or a DAO.Database. You
might
need to set a reference to the DAO model to use that...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, I am using Access 2002.

I tried to set up an unbound texbox in form so that users can type
in a
value to find a specific record to update other fields.

For the unbound textbox (named ttrackid) I put in the following VBA
codes
for event procedure on EXIT (please see below). However, the line "
Dim
currdb As Database" was highlighted and I received an error msg
saying --

Compile error:
user-defined type not defined.

Please help. The codes were working fine in another database.

Thank you,

Private Sub ttrackid_Exit(Cancel As Integer)
On Error GoTo Err_ttrackid_exit
Dim currdb As Database
Set currdb = CurrentDb()
' If ttrackid is BLANK
If IsNull(Me![ttrackid]) Then
' Do nothing but leave subroutine. Any msg gets in way.
'MsgBox "The ENTER TRACKID box is empty."
Exit Sub
End If

' If ttrackid NOT BLANK,
' check to see if ttrackid exists already
If Not IsNull(Me![ttrackid]) Then
Dim rsTRACKID As Recordset
Set rsTRACKID = currdb.OpenRecordset _
("select * from QFUIX where " & _
"QFUIX![TRACKID] =" & Me![ttrackid])

Dim intCountTRACKID As Integer
intCountTRCKID = rsTRACKID.RecordCount
End If

' If TRACKID DOES NOT EXIST,
If intCountTRACKID = 0 Then
MsgBox "This TRACKID does not exist. " & _
"Enter another TRACKID or leave blank."
Forms!FUIXForm!TRACKID.SetFocus
Forms!FUIXForm!ttrackid.SetFocus

' If TRACKID EXISTS, show record
Else
[Forms]![FUIXForm]![TRACKID].SetFocus
DoCmd.FindRecord Me![ttrackid], , , , 0
Me![ttrackid] = Null
Exit Sub
End If

Exit_ttrackid_exit:
Exit Sub

Err_ttrackid_exit:
MsgBox Err.Description
Resume Exit_ttrackid_exit

End Sub
Click to expand...
Click to expand...
Click to expand...
 

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

Using variables as table names in an append sql statemnt 1
Query to Filter by Dates, Using Form 7
Unbound Form needing to add new record 7
Going Nuts with Selecting the Value of List Box using an unbound TextBox 11
Help with VBA with an unbound form 4
Find #N/A in column D 2
Trying to save selected record from a form to a table 3
Using Find as lookup method goes to semi endless loop 10

Top