Search and Listbox

[Guest]

I am developing a search facility for my db of patients. I have a form with
an unbound txtbox and unbound listbox. You enter the PatientID you're looking
for in the txtbox and as you type, the listbox displays the PatientID numbers
in the db (using a query as the RowSource). You can either enter all 6 digits
of the number and are left with one option in the listbox or after say 3
digits you see your required PatientID in the listbox. Double Clicking the
required entry in the listbox opens a different form with the rest of that
patients details for further editing. If the patient is not in the db, the
listbox will not display any data. This is where I get stuck with my next
step. If the list box is empty, ie: the patient is not in the db, I'd like
the patient detail form to open ready to enter the patient as a new record.

So, first I can trap the fact that the listbox is empty, does the listbox
have a property that I can use? Second can you code opening a form at a new
record page?

This code updates the listbox as the user types the PatientID in the textbox:

Screen.ActiveForm![lstSearch].RowSource = "qry_TEST_Filter"
DoCmd.Requery "lstSearch"

This is the code I'm trying to use to test if the listbox has actually got
any entries in it:

Private Sub lstSearch_AfterUpdate()
If Screen.ActiveForm![lstSearch].RowSource = "" Then
DoCmd.OpenForm "frmTEST", acNormal
End If
End Sub

Any suggestions would be most welcome,

TIA,
Ian.

 

[Graham Mandeno]

Hi Ian

The number of items in a listbox may be ascertained from the ListCount
property.
If lstSearch.ListCount = 0 then ...

A couple of other points...

First, when referring to controls and properties of the form in whose module
you are currently running, you should just use the "Me" keyword (or
sometimes nothing at all) instead of "Screen.ActiveForm". This is akin to
sending a note to your wife through the post instead of leaving it on the
kitchen table

Second, instead of calling the DoCmd.Requery method (which uses the same
code path as the Requery macro action), simply call the Requery method of
the listbox:
lstSearch.Requery
(actually, the Requery is redundant, as changing the RowSource always
performs a Requery automatically)

I'm not sure what you are doing with "qry_TEST_Filter", but it looks as
though it uses the contents of your textbox in its WHERE clause to filter
the results. This means that you won't get your list updated until you exit
the textbox.

I suggest you remove the WHERE condition from the query so it returns all
records. Then use the Change event of the textbox to alter the filter as
you type:

Public Sub txtSearch_Change()
If Len(txtSearch.text) = 0 then
lstSearch.RowSource = "qryListPatients"
Else
lstSearch.RowSource = _
"Select * from qryListpatients where PatientID like '" _
& txtSearch.Text & "*'"
End If
DoEvents ' this may of may not be necessary
If lstSearch.ListCount = 0 then
' ask if you want to add a new record
End If
End Sub
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am developing a search facility for my db of patients. I have a form with
an unbound txtbox and unbound listbox. You enter the PatientID you're
looking
for in the txtbox and as you type, the listbox displays the PatientID
numbers
in the db (using a query as the RowSource). You can either enter all 6
digits
of the number and are left with one option in the listbox or after say 3
digits you see your required PatientID in the listbox. Double Clicking the
required entry in the listbox opens a different form with the rest of that
patients details for further editing. If the patient is not in the db, the
listbox will not display any data. This is where I get stuck with my next
step. If the list box is empty, ie: the patient is not in the db, I'd like
the patient detail form to open ready to enter the patient as a new
record.

So, first I can trap the fact that the listbox is empty, does the listbox
have a property that I can use? Second can you code opening a form at a
new
record page?

This code updates the listbox as the user types the PatientID in the
textbox:

Screen.ActiveForm![lstSearch].RowSource = "qry_TEST_Filter"
DoCmd.Requery "lstSearch"

This is the code I'm trying to use to test if the listbox has actually got
any entries in it:

Private Sub lstSearch_AfterUpdate()
If Screen.ActiveForm![lstSearch].RowSource = "" Then
DoCmd.OpenForm "frmTEST", acNormal
End If
End Sub

Any suggestions would be most welcome,

TIA,
Ian.

 

[Guest]

Hi Graham,

Thanks for the suggestions but I'm still struggling somewhat to get the
lstSearch.ListCount to work.

The qry_TEST_Filter has this SQL code,

SELECT [1a_PreBypass_Copy].HospitalNo, [1a_PreBypass_Copy].[First Name],
[1a_PreBypass_Copy].Surname, [1a_PreBypass_Copy].DoB
FROM 1a_PreBypass_Copy
WHERE ((([1a_PreBypass_Copy].HospitalNo) Like
[Forms]![frmSearch]![txtSearch] & "*"))
ORDER BY [1a_PreBypass_Copy].HospitalNo;

it looks at the Patient table and populates the ListBox with possible
options. When I find my selection a double click on the correct ListBox line
opens that patients record. The textbox (txtSearch) that I use to enter the
PatientID runs a function called "Search", held in a module, on its "On
Change" event.

The Serach function is:

Function Search()
DoCmd.Hourglass True
DoCmd.RunCommand acCmdSaveRecord
Screen.ActiveForm![lstSearch].RowSource = "qry_TEST_Filter"
[lstSearch].Requery
Forms!frmSearch!txtSearch.SetFocus
Screen.ActiveControl.SelStart = 255
DoCmd.Hourglass False
End Function

What I'm trying to do is trap when the number entered in the textbox
(txtSearch) is not in the database and so no records populate the ListBox.
I'd like to either put up a message box saying no record in Db or open a form
to enter a new patient record.

I guess I'm not sure whether I should be looking to trap the ListBox that is
empty or the qry_TEST_Filter that produces no output to act as the ListBox
RowSource?

When I tried the .ListCount option (in the lstSearch_AfterUpdate()) it
didn't put up my test msgbox when I entered numbers known not to exist.

Private Sub lstSearch_AfterUpdate()
If lstSearch.ListCount = 0 Then
MsgBox "There are no records in the database", vbOKCancel
End If
End Sub

TIA for any further help you can offer,

Ian.

Hi Ian

The number of items in a listbox may be ascertained from the ListCount
property.
If lstSearch.ListCount = 0 then ...

A couple of other points...

First, when referring to controls and properties of the form in whose module
you are currently running, you should just use the "Me" keyword (or
sometimes nothing at all) instead of "Screen.ActiveForm". This is akin to
sending a note to your wife through the post instead of leaving it on the
kitchen table

Second, instead of calling the DoCmd.Requery method (which uses the same
code path as the Requery macro action), simply call the Requery method of
the listbox:
lstSearch.Requery
(actually, the Requery is redundant, as changing the RowSource always
performs a Requery automatically)

I'm not sure what you are doing with "qry_TEST_Filter", but it looks as
though it uses the contents of your textbox in its WHERE clause to filter
the results. This means that you won't get your list updated until you exit
the textbox.

I suggest you remove the WHERE condition from the query so it returns all
records. Then use the Change event of the textbox to alter the filter as
you type:

Public Sub txtSearch_Change()
If Len(txtSearch.text) = 0 then
lstSearch.RowSource = "qryListPatients"
Else
lstSearch.RowSource = _
"Select * from qryListpatients where PatientID like '" _
& txtSearch.Text & "*'"
End If
DoEvents ' this may of may not be necessary
If lstSearch.ListCount = 0 then
' ask if you want to add a new record
End If
End Sub
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am developing a search facility for my db of patients. I have a form with
an unbound txtbox and unbound listbox. You enter the PatientID you're
looking
for in the txtbox and as you type, the listbox displays the PatientID
numbers
in the db (using a query as the RowSource). You can either enter all 6
digits
of the number and are left with one option in the listbox or after say 3
digits you see your required PatientID in the listbox. Double Clicking the
required entry in the listbox opens a different form with the rest of that
patients details for further editing. If the patient is not in the db, the
listbox will not display any data. This is where I get stuck with my next
step. If the list box is empty, ie: the patient is not in the db, I'd like
the patient detail form to open ready to enter the patient as a new
record.

So, first I can trap the fact that the listbox is empty, does the listbox
have a property that I can use? Second can you code opening a form at a
new
record page?

This code updates the listbox as the user types the PatientID in the
textbox:

Screen.ActiveForm![lstSearch].RowSource = "qry_TEST_Filter"
DoCmd.Requery "lstSearch"

This is the code I'm trying to use to test if the listbox has actually got
any entries in it:

Private Sub lstSearch_AfterUpdate()
If Screen.ActiveForm![lstSearch].RowSource = "" Then
DoCmd.OpenForm "frmTEST", acNormal
End If
End Sub

Any suggestions would be most welcome,

TIA,
Ian.

 

[Graham Mandeno]

Hi Ian

The main problem is that your query uses in its WHERE clause a reference to
your textbox txtSearch. However, the new value in the textbox will not be
available until the textbox has been updated (usually when it has lost the
focus).

Typing in the textbox changes the *Text* property of the textbox and
triggers the Change event, but it does not change the *Value* property. You
are performing the Requery on your listbox in the Change EP (event
procedure) of the textbox, at which point the Value property will not yet
have changed.

I suggest you make the following changes:

1. Remove the WHERE clause from your query, so the SQL reads:
SELECT [1a_PreBypass_Copy].HospitalNo, [1a_PreBypass_Copy].[First Name],
[1a_PreBypass_Copy].Surname, [1a_PreBypass_Copy].DoB
FROM 1a_PreBypass_Copy
ORDER BY [1a_PreBypass_Copy].HospitalNo;

2. Rename the query to something less "testy" - e.g. qryPatientSearch

3. Get rid of your Search function and the txtSearch_AfterUpdate EP.

4. Add the following Change EP for your textbox:
Public Sub txtSearch_Change()
If Len(txtSearch.text) = 0 then
lstSearch.RowSource = "qryPatientSearch"
Else
lstSearch.RowSource = _
"Select * from qryPatientSearch where HospitalNo like '" _
& txtSearch.Text & "*'"
End If
' uncomment the following line if you have any timing issues
' with getting the correct ListCount
' DoEvents
If lstSearch.ListCount = 0 then
' ask if you want to add a new record
If MsgBox( "Do you wish to add a new patient record?", _
vbYesNo Or vbQuestion, "Patient not found") = vbYes Then
' open your form to add a new patient
End If
End If
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks for the suggestions but I'm still struggling somewhat to get the
lstSearch.ListCount to work.

The qry_TEST_Filter has this SQL code,

SELECT [1a_PreBypass_Copy].HospitalNo, [1a_PreBypass_Copy].[First Name],
[1a_PreBypass_Copy].Surname, [1a_PreBypass_Copy].DoB
FROM 1a_PreBypass_Copy
WHERE ((([1a_PreBypass_Copy].HospitalNo) Like
[Forms]![frmSearch]![txtSearch] & "*"))
ORDER BY [1a_PreBypass_Copy].HospitalNo;

it looks at the Patient table and populates the ListBox with possible
options. When I find my selection a double click on the correct ListBox
line
opens that patients record. The textbox (txtSearch) that I use to enter
the
PatientID runs a function called "Search", held in a module, on its "On
Change" event.

The Serach function is:

Function Search()
DoCmd.Hourglass True
DoCmd.RunCommand acCmdSaveRecord
Screen.ActiveForm![lstSearch].RowSource = "qry_TEST_Filter"
[lstSearch].Requery
Forms!frmSearch!txtSearch.SetFocus
Screen.ActiveControl.SelStart = 255
DoCmd.Hourglass False
End Function

What I'm trying to do is trap when the number entered in the textbox
(txtSearch) is not in the database and so no records populate the ListBox.
I'd like to either put up a message box saying no record in Db or open a
form
to enter a new patient record.

I guess I'm not sure whether I should be looking to trap the ListBox that
is
empty or the qry_TEST_Filter that produces no output to act as the ListBox
RowSource?

When I tried the .ListCount option (in the lstSearch_AfterUpdate()) it
didn't put up my test msgbox when I entered numbers known not to exist.

Private Sub lstSearch_AfterUpdate()
If lstSearch.ListCount = 0 Then
MsgBox "There are no records in the database", vbOKCancel
End If
End Sub

TIA for any further help you can offer,

Ian.

Hi Ian

The number of items in a listbox may be ascertained from the ListCount
property.
If lstSearch.ListCount = 0 then ...

A couple of other points...

First, when referring to controls and properties of the form in whose
module
you are currently running, you should just use the "Me" keyword (or
sometimes nothing at all) instead of "Screen.ActiveForm". This is akin
to
sending a note to your wife through the post instead of leaving it on the
kitchen table

Second, instead of calling the DoCmd.Requery method (which uses the same
code path as the Requery macro action), simply call the Requery method of
the listbox:
lstSearch.Requery
(actually, the Requery is redundant, as changing the RowSource always
performs a Requery automatically)

I'm not sure what you are doing with "qry_TEST_Filter", but it looks as
though it uses the contents of your textbox in its WHERE clause to filter
the results. This means that you won't get your list updated until you
exit
the textbox.

I suggest you remove the WHERE condition from the query so it returns all
records. Then use the Change event of the textbox to alter the filter as
you type:

Public Sub txtSearch_Change()
If Len(txtSearch.text) = 0 then
lstSearch.RowSource = "qryListPatients"
Else
lstSearch.RowSource = _
"Select * from qryListpatients where PatientID like '" _
& txtSearch.Text & "*'"
End If
DoEvents ' this may of may not be necessary
If lstSearch.ListCount = 0 then
' ask if you want to add a new record
End If
End Sub
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am developing a search facility for my db of patients. I have a form
with
an unbound txtbox and unbound listbox. You enter the PatientID you're
looking
for in the txtbox and as you type, the listbox displays the PatientID
numbers
in the db (using a query as the RowSource). You can either enter all 6
digits
of the number and are left with one option in the listbox or after say
3
digits you see your required PatientID in the listbox. Double Clicking
the
required entry in the listbox opens a different form with the rest of
that
patients details for further editing. If the patient is not in the db,
the
listbox will not display any data. This is where I get stuck with my
next
step. If the list box is empty, ie: the patient is not in the db, I'd
like
the patient detail form to open ready to enter the patient as a new
record.

So, first I can trap the fact that the listbox is empty, does the
listbox
have a property that I can use? Second can you code opening a form at a
new
record page?

This code updates the listbox as the user types the PatientID in the
textbox:

Screen.ActiveForm![lstSearch].RowSource = "qry_TEST_Filter"
DoCmd.Requery "lstSearch"

This is the code I'm trying to use to test if the listbox has actually
got
any entries in it:

Private Sub lstSearch_AfterUpdate()
If Screen.ActiveForm![lstSearch].RowSource = "" Then
DoCmd.OpenForm "frmTEST", acNormal
End If
End Sub

Any suggestions would be most welcome,

TIA,
Ian.

 

[Guest]

Hi Graham,

Fantastic, works like a dream, thank you very much.

Can I just ask you what the lines:

If Len(txtSearch.text) = 0 then
lstSearch.RowSource = "qryPatientSearch"

do? I can't quite figure it out I'm afraid. Also is it possible to open the
form to enter a new record in edit mode?

Thanks again,
Ian.

Hi Ian

The main problem is that your query uses in its WHERE clause a reference to
your textbox txtSearch. However, the new value in the textbox will not be
available until the textbox has been updated (usually when it has lost the
focus).

Typing in the textbox changes the *Text* property of the textbox and
triggers the Change event, but it does not change the *Value* property. You
are performing the Requery on your listbox in the Change EP (event
procedure) of the textbox, at which point the Value property will not yet
have changed.

I suggest you make the following changes:

1. Remove the WHERE clause from your query, so the SQL reads:
SELECT [1a_PreBypass_Copy].HospitalNo, [1a_PreBypass_Copy].[First Name],
[1a_PreBypass_Copy].Surname, [1a_PreBypass_Copy].DoB
FROM 1a_PreBypass_Copy
ORDER BY [1a_PreBypass_Copy].HospitalNo;

2. Rename the query to something less "testy" - e.g. qryPatientSearch

3. Get rid of your Search function and the txtSearch_AfterUpdate EP.

4. Add the following Change EP for your textbox:
Public Sub txtSearch_Change()
If Len(txtSearch.text) = 0 then
lstSearch.RowSource = "qryPatientSearch"
Else
lstSearch.RowSource = _
"Select * from qryPatientSearch where HospitalNo like '" _
& txtSearch.Text & "*'"
End If
' uncomment the following line if you have any timing issues
' with getting the correct ListCount
' DoEvents
If lstSearch.ListCount = 0 then
' ask if you want to add a new record
If MsgBox( "Do you wish to add a new patient record?", _
vbYesNo Or vbQuestion, "Patient not found") = vbYes Then
' open your form to add a new patient
End If
End If
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks for the suggestions but I'm still struggling somewhat to get the
lstSearch.ListCount to work.

The qry_TEST_Filter has this SQL code,

SELECT [1a_PreBypass_Copy].HospitalNo, [1a_PreBypass_Copy].[First Name],
[1a_PreBypass_Copy].Surname, [1a_PreBypass_Copy].DoB
FROM 1a_PreBypass_Copy
WHERE ((([1a_PreBypass_Copy].HospitalNo) Like
[Forms]![frmSearch]![txtSearch] & "*"))
ORDER BY [1a_PreBypass_Copy].HospitalNo;

it looks at the Patient table and populates the ListBox with possible
options. When I find my selection a double click on the correct ListBox
line
opens that patients record. The textbox (txtSearch) that I use to enter
the
PatientID runs a function called "Search", held in a module, on its "On
Change" event.

The Serach function is:

Function Search()
DoCmd.Hourglass True
DoCmd.RunCommand acCmdSaveRecord
Screen.ActiveForm![lstSearch].RowSource = "qry_TEST_Filter"
[lstSearch].Requery
Forms!frmSearch!txtSearch.SetFocus
Screen.ActiveControl.SelStart = 255
DoCmd.Hourglass False
End Function

What I'm trying to do is trap when the number entered in the textbox
(txtSearch) is not in the database and so no records populate the ListBox.
I'd like to either put up a message box saying no record in Db or open a
form
to enter a new patient record.

I guess I'm not sure whether I should be looking to trap the ListBox that
is
empty or the qry_TEST_Filter that produces no output to act as the ListBox
RowSource?

When I tried the .ListCount option (in the lstSearch_AfterUpdate()) it
didn't put up my test msgbox when I entered numbers known not to exist.

Private Sub lstSearch_AfterUpdate()
If lstSearch.ListCount = 0 Then
MsgBox "There are no records in the database", vbOKCancel
End If
End Sub

TIA for any further help you can offer,

Ian.

Hi Ian

The number of items in a listbox may be ascertained from the ListCount
property.
If lstSearch.ListCount = 0 then ...

A couple of other points...

First, when referring to controls and properties of the form in whose
module
you are currently running, you should just use the "Me" keyword (or
sometimes nothing at all) instead of "Screen.ActiveForm". This is akin
to
sending a note to your wife through the post instead of leaving it on the
kitchen table

Second, instead of calling the DoCmd.Requery method (which uses the same
code path as the Requery macro action), simply call the Requery method of
the listbox:
lstSearch.Requery
(actually, the Requery is redundant, as changing the RowSource always
performs a Requery automatically)

I'm not sure what you are doing with "qry_TEST_Filter", but it looks as
though it uses the contents of your textbox in its WHERE clause to filter
the results. This means that you won't get your list updated until you
exit
the textbox.

I suggest you remove the WHERE condition from the query so it returns all
records. Then use the Change event of the textbox to alter the filter as
you type:

Public Sub txtSearch_Change()
If Len(txtSearch.text) = 0 then
lstSearch.RowSource = "qryListPatients"
Else
lstSearch.RowSource = _
"Select * from qryListpatients where PatientID like '" _
& txtSearch.Text & "*'"
End If
DoEvents ' this may of may not be necessary
If lstSearch.ListCount = 0 then
' ask if you want to add a new record
End If
End Sub
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand



I am developing a search facility for my db of patients. I have a form
with
an unbound txtbox and unbound listbox. You enter the PatientID you're
looking
for in the txtbox and as you type, the listbox displays the PatientID
numbers
in the db (using a query as the RowSource). You can either enter all 6
digits
of the number and are left with one option in the listbox or after say
3
digits you see your required PatientID in the listbox. Double Clicking
the
required entry in the listbox opens a different form with the rest of
that
patients details for further editing. If the patient is not in the db,
the
listbox will not display any data. This is where I get stuck with my
next
step. If the list box is empty, ie: the patient is not in the db, I'd
like
the patient detail form to open ready to enter the patient as a new
record.

So, first I can trap the fact that the listbox is empty, does the
listbox
have a property that I can use? Second can you code opening a form at a
new
record page?

This code updates the listbox as the user types the PatientID in the
textbox:

Screen.ActiveForm![lstSearch].RowSource = "qry_TEST_Filter"
DoCmd.Requery "lstSearch"

This is the code I'm trying to use to test if the listbox has actually
got
any entries in it:

Private Sub lstSearch_AfterUpdate()
If Screen.ActiveForm![lstSearch].RowSource = "" Then
DoCmd.OpenForm "frmTEST", acNormal
End If
End Sub

Any suggestions would be most welcome,

TIA,
Ian.

 

[Guest]

Hi Graham,

No worries, I've figured out the opening the form as a new record.

Thanks again for your help.... I hope your All Blacks don't give our Lions
too much of a hiding again at the weekend!!

Cheers,
Ian.

Hi Graham,

Fantastic, works like a dream, thank you very much.

Can I just ask you what the lines:

If Len(txtSearch.text) = 0 then
lstSearch.RowSource = "qryPatientSearch"

do? I can't quite figure it out I'm afraid. Also is it possible to open the
form to enter a new record in edit mode?

Thanks again,
Ian.

Hi Ian

The main problem is that your query uses in its WHERE clause a reference to
your textbox txtSearch. However, the new value in the textbox will not be
available until the textbox has been updated (usually when it has lost the
focus).

Typing in the textbox changes the *Text* property of the textbox and
triggers the Change event, but it does not change the *Value* property. You
are performing the Requery on your listbox in the Change EP (event
procedure) of the textbox, at which point the Value property will not yet
have changed.

I suggest you make the following changes:

1. Remove the WHERE clause from your query, so the SQL reads:
SELECT [1a_PreBypass_Copy].HospitalNo, [1a_PreBypass_Copy].[First Name],
[1a_PreBypass_Copy].Surname, [1a_PreBypass_Copy].DoB
FROM 1a_PreBypass_Copy
ORDER BY [1a_PreBypass_Copy].HospitalNo;

2. Rename the query to something less "testy" - e.g. qryPatientSearch

3. Get rid of your Search function and the txtSearch_AfterUpdate EP.

4. Add the following Change EP for your textbox:
Public Sub txtSearch_Change()
If Len(txtSearch.text) = 0 then
lstSearch.RowSource = "qryPatientSearch"
Else
lstSearch.RowSource = _
"Select * from qryPatientSearch where HospitalNo like '" _
& txtSearch.Text & "*'"
End If
' uncomment the following line if you have any timing issues
' with getting the correct ListCount
' DoEvents
If lstSearch.ListCount = 0 then
' ask if you want to add a new record
If MsgBox( "Do you wish to add a new patient record?", _
vbYesNo Or vbQuestion, "Patient not found") = vbYes Then
' open your form to add a new patient
End If
End If
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks for the suggestions but I'm still struggling somewhat to get the
lstSearch.ListCount to work.

The qry_TEST_Filter has this SQL code,

SELECT [1a_PreBypass_Copy].HospitalNo, [1a_PreBypass_Copy].[First Name],
[1a_PreBypass_Copy].Surname, [1a_PreBypass_Copy].DoB
FROM 1a_PreBypass_Copy
WHERE ((([1a_PreBypass_Copy].HospitalNo) Like
[Forms]![frmSearch]![txtSearch] & "*"))
ORDER BY [1a_PreBypass_Copy].HospitalNo;

it looks at the Patient table and populates the ListBox with possible
options. When I find my selection a double click on the correct ListBox
line
opens that patients record. The textbox (txtSearch) that I use to enter
the
PatientID runs a function called "Search", held in a module, on its "On
Change" event.

The Serach function is:

Function Search()
DoCmd.Hourglass True
DoCmd.RunCommand acCmdSaveRecord
Screen.ActiveForm![lstSearch].RowSource = "qry_TEST_Filter"
[lstSearch].Requery
Forms!frmSearch!txtSearch.SetFocus
Screen.ActiveControl.SelStart = 255
DoCmd.Hourglass False
End Function

What I'm trying to do is trap when the number entered in the textbox
(txtSearch) is not in the database and so no records populate the ListBox.
I'd like to either put up a message box saying no record in Db or open a
form
to enter a new patient record.

I guess I'm not sure whether I should be looking to trap the ListBox that
is
empty or the qry_TEST_Filter that produces no output to act as the ListBox
RowSource?

When I tried the .ListCount option (in the lstSearch_AfterUpdate()) it
didn't put up my test msgbox when I entered numbers known not to exist.

Private Sub lstSearch_AfterUpdate()
If lstSearch.ListCount = 0 Then
MsgBox "There are no records in the database", vbOKCancel
End If
End Sub

TIA for any further help you can offer,

Ian.

:

Hi Ian

The number of items in a listbox may be ascertained from the ListCount
property.
If lstSearch.ListCount = 0 then ...

A couple of other points...

First, when referring to controls and properties of the form in whose
module
you are currently running, you should just use the "Me" keyword (or
sometimes nothing at all) instead of "Screen.ActiveForm". This is akin
to
sending a note to your wife through the post instead of leaving it on the
kitchen table

Second, instead of calling the DoCmd.Requery method (which uses the same
code path as the Requery macro action), simply call the Requery method of
the listbox:
lstSearch.Requery
(actually, the Requery is redundant, as changing the RowSource always
performs a Requery automatically)

I'm not sure what you are doing with "qry_TEST_Filter", but it looks as
though it uses the contents of your textbox in its WHERE clause to filter
the results. This means that you won't get your list updated until you
exit
the textbox.

I suggest you remove the WHERE condition from the query so it returns all
records. Then use the Change event of the textbox to alter the filter as
you type:

Public Sub txtSearch_Change()
If Len(txtSearch.text) = 0 then
lstSearch.RowSource = "qryListPatients"
Else
lstSearch.RowSource = _
"Select * from qryListpatients where PatientID like '" _
& txtSearch.Text & "*'"
End If
DoEvents ' this may of may not be necessary
If lstSearch.ListCount = 0 then
' ask if you want to add a new record
End If
End Sub
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand



I am developing a search facility for my db of patients. I have a form
with
an unbound txtbox and unbound listbox. You enter the PatientID you're
looking
for in the txtbox and as you type, the listbox displays the PatientID
numbers
in the db (using a query as the RowSource). You can either enter all 6
digits
of the number and are left with one option in the listbox or after say
3
digits you see your required PatientID in the listbox. Double Clicking
the
required entry in the listbox opens a different form with the rest of
that
patients details for further editing. If the patient is not in the db,
the
listbox will not display any data. This is where I get stuck with my
next
step. If the list box is empty, ie: the patient is not in the db, I'd
like
the patient detail form to open ready to enter the patient as a new
record.

So, first I can trap the fact that the listbox is empty, does the
listbox
have a property that I can use? Second can you code opening a form at a
new
record page?

This code updates the listbox as the user types the PatientID in the
textbox:

Screen.ActiveForm![lstSearch].RowSource = "qry_TEST_Filter"
DoCmd.Requery "lstSearch"

This is the code I'm trying to use to test if the listbox has actually
got
any entries in it:

Private Sub lstSearch_AfterUpdate()
If Screen.ActiveForm![lstSearch].RowSource = "" Then
DoCmd.OpenForm "frmTEST", acNormal
End If
End Sub

Any suggestions would be most welcome,

TIA,
Ian.

 

[Graham Mandeno]

Hi Ian

Hi Graham,

Fantastic, works like a dream, thank you very much.

Great! Glad you got it working

Can I just ask you what the lines:
If Len(txtSearch.text) = 0 then
lstSearch.RowSource = "qryPatientSearch"
do? I can't quite figure it out I'm afraid.

It says "If the length of the text in the textbox is zero (i.e. the textbox
is empty) then show the entire unfiltered query in the listbox".
It's not strictly necessary, but it avoids the unnecessary overhead of
applying this filter:
WHERE [HospitalNo] Like '*'

Also is it possible to open the
form to enter a new record in edit mode?

Certainly! At the point in my example code where I said:
' open your form to add a new patient
you should insert something appropriate, such as:
DoCmd.OpenForm "frmPatient", DataMode:=acAdd, _
WindowMode:=acDialog, OpenArgs:=txtSearch.Text
lstSearch.Requery

This will open your patient details form (frmPatient) at a new record
(DataMode:=acAdd), and suspend execution of your code until that form closes
(WindowMode:=acDialog). It also passes the text that has been typed into
the textbox (txtSearch.Text) as the form's OpenArgs property. When the form
closes, the listbox is requeried again (lstSearch.Requery).

In the Form_Load EP of frmPatient, you use OpenArgs as follows:

If Me.NewRecord and not IsNull(Me.OpenArgs) then
HospitalNo.DefaultValue = """" & Me.OpenArgs & """"
End If

This fills in the text from txtSearch as the default value of HospitalNo in
the new record.

 

[Graham Mandeno]

Well, it's not looking too good is it? I feel very sorry for all the people
who have paid thousands of pounds to travel to the other side of the world
for some good, close, exciting rugby!

Cheers,
Graham

 

[Guest]

Hi Graham,

I fumbled around using the code the Button wizard used for adding a new
record to a form, which ended up giving me two DoCmd lines to your one, more
elegant soultion. The VBA Help files are not too helpful with this.

Thanks again for all your help, much appreciated,
Ian.

Hi Ian

Hi Graham,

Fantastic, works like a dream, thank you very much.

Great! Glad you got it working

Can I just ask you what the lines:
If Len(txtSearch.text) = 0 then
lstSearch.RowSource = "qryPatientSearch"
do? I can't quite figure it out I'm afraid.

It says "If the length of the text in the textbox is zero (i.e. the textbox
is empty) then show the entire unfiltered query in the listbox".
It's not strictly necessary, but it avoids the unnecessary overhead of
applying this filter:
WHERE [HospitalNo] Like '*'

Also is it possible to open the
form to enter a new record in edit mode?

Certainly! At the point in my example code where I said:
' open your form to add a new patient
you should insert something appropriate, such as:
DoCmd.OpenForm "frmPatient", DataMode:=acAdd, _
WindowMode:=acDialog, OpenArgs:=txtSearch.Text
lstSearch.Requery

This will open your patient details form (frmPatient) at a new record
(DataMode:=acAdd), and suspend execution of your code until that form closes
(WindowMode:=acDialog). It also passes the text that has been typed into
the textbox (txtSearch.Text) as the form's OpenArgs property. When the form
closes, the listbox is requeried again (lstSearch.Requery).

In the Form_Load EP of frmPatient, you use OpenArgs as follows:

If Me.NewRecord and not IsNull(Me.OpenArgs) then
HospitalNo.DefaultValue = """" & Me.OpenArgs & """"
End If

This fills in the text from txtSearch as the default value of HospitalNo in
the new record.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand