Guidance in searching...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I've been having some problems with a database I've been constructing as I'm
quite new to Access, but I've got it to a point I'm relatively happy with
although as it stands the only way users can search through records is to use
Access' default navigation buttons at the bottom of the record. I would
really appreciate any help in expanding this functionality.

I currently have a welcome page which links to form views of each of the
tables in my database through a series of simple open form buttons. I would
ideally like to build a quick search facility into this welcome page which
looks at 2 columns only (First Name and Last Name) from just one of my tables
and brings up a selected matching record in its form view.

I'm anticipating using a either a combo box, or a text box which links into
a list box to bring up a selection of matching records; but either way I'd
like to be able to highlight one of the strings in the list and hit a 'go to
record' button next to the list to open the record.

As I said, I'm quite new to Access so I'd be really grateful if any
responses could be as explicit as possible in terms of where any code needs
to be entered and any properties of fields that will need to be altered.

Any help greatly appreciated,

Thanks in anticipation!
 
With either a list or a combo box, there is a wizard for finding a record
based upon the findings of the search:

Sub MyCombo_AfterUpdate()

Me.RecordsetClone.FindFirst "[ID] = " & Me![MyCombo]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Thanks for that Arvin although ideally I'd like to be able to search in more
than one field by typing a full name in, I only know how to look for one
field in a combo box. I'd also like to be able to open the resulting record
in form view by using a button, bearing in mind that I'm working on frm_entry
and I want to bring up the appropriate record as it appears in frm_candidate.
Do you have any suggestions on these?

Arvin Meyer said:
With either a list or a combo box, there is a wizard for finding a record
based upon the findings of the search:

Sub MyCombo_AfterUpdate()

Me.RecordsetClone.FindFirst "[ID] = " & Me![MyCombo]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Duncan said:
Hello,

I've been having some problems with a database I've been constructing as I'm
quite new to Access, but I've got it to a point I'm relatively happy with
although as it stands the only way users can search through records is to use
Access' default navigation buttons at the bottom of the record. I would
really appreciate any help in expanding this functionality.

I currently have a welcome page which links to form views of each of the
tables in my database through a series of simple open form buttons. I would
ideally like to build a quick search facility into this welcome page which
looks at 2 columns only (First Name and Last Name) from just one of my tables
and brings up a selected matching record in its form view.

I'm anticipating using a either a combo box, or a text box which links into
a list box to bring up a selection of matching records; but either way I'd
like to be able to highlight one of the strings in the list and hit a 'go to
record' button next to the list to open the record.

As I said, I'm quite new to Access so I'd be really grateful if any
responses could be as explicit as possible in terms of where any code needs
to be entered and any properties of fields that will need to be altered.

Any help greatly appreciated,

Thanks in anticipation!
Click to expand...
Click to expand...
 
If I understand you correctly, you want to type into a text box to initiate
a search for a person or persons. On your Welcome form, add an unbound text
box and list box. Name the new controls something like txtSearch and
listResult.

For txtSearch:
leave the control source blank
In the AfterUpdate event, requery listResult.

For listResult:
Columns =2
Col Widths= 0'; 2";
BoundColumn =1
Row Source =
SELECT TablePersons.PersonID, [PFirst] & " " & [PLast] AS Person FROM
TablePersons WHERE ((([PFirst] & " " & [PLast]) Like "*" &
[Forms]![YourWelcomeForm]![txtSearch] & "*"));

Then if you want to open another form to the selected person's record,
either add a command button to open the form or use the DoubleClick event of
listResult.

HTH,
Brian
 
Perhaps a where clause is what you are looking for. Try something like the
following in the combo's afterupdate event:

DoCmd.OpenForm "FormToOpen",,,,"ID =" & Me![MyCombo]

If there is no record, the form will open blank, ready to add one. You can
add a form referenece, like:

=Forms!CallingForm!ID

in the DefaultValue property of your second form.

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Duncan said:
Thanks for that Arvin although ideally I'd like to be able to search in more
than one field by typing a full name in, I only know how to look for one
field in a combo box. I'd also like to be able to open the resulting record
in form view by using a button, bearing in mind that I'm working on frm_entry
and I want to bring up the appropriate record as it appears in frm_candidate.
Do you have any suggestions on these?

Arvin Meyer said:
With either a list or a combo box, there is a wizard for finding a record
based upon the findings of the search:

Sub MyCombo_AfterUpdate()

Me.RecordsetClone.FindFirst "[ID] = " & Me![MyCombo]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Duncan said:
Hello,

I've been having some problems with a database I've been constructing
Click to expand...
as
I'm
quite new to Access, but I've got it to a point I'm relatively happy with
although as it stands the only way users can search through records is
Click to expand...
to
use
Access' default navigation buttons at the bottom of the record. I would
really appreciate any help in expanding this functionality.

I currently have a welcome page which links to form views of each of the
tables in my database through a series of simple open form buttons. I would
ideally like to build a quick search facility into this welcome page which
looks at 2 columns only (First Name and Last Name) from just one of my tables
and brings up a selected matching record in its form view.

I'm anticipating using a either a combo box, or a text box which links into
a list box to bring up a selection of matching records; but either way I'd
like to be able to highlight one of the strings in the list and hit a
Click to expand...
'go
to
record' button next to the list to open the record.

As I said, I'm quite new to Access so I'd be really grateful if any
responses could be as explicit as possible in terms of where any code needs
to be entered and any properties of fields that will need to be altered.

Any help greatly appreciated,

Thanks in anticipation!
Click to expand...
Click to expand...
Click to expand...
 
Thanks very much for that Brian, that's really helpful - exactly what I was
hoping to achieve, just a couple of further questions if you don't mind -

1) Is there any way I can automatically highlight the top record (or the
only record if there's only one returned) in the list box so that I only have
to hit the button without selecting the name?

2) Purely aesthetic, but is there any way I can I get the list box to appear
blank initially before typing my entry into the text box?

Thanks again for all your help - really appreciated!

Duncan



Brian Bastl said:
If I understand you correctly, you want to type into a text box to initiate
a search for a person or persons. On your Welcome form, add an unbound text
box and list box. Name the new controls something like txtSearch and
listResult.

For txtSearch:
leave the control source blank
In the AfterUpdate event, requery listResult.

For listResult:
Columns =2
Col Widths= 0'; 2";
BoundColumn =1
Row Source =
SELECT TablePersons.PersonID, [PFirst] & " " & [PLast] AS Person FROM
TablePersons WHERE ((([PFirst] & " " & [PLast]) Like "*" &
[Forms]![YourWelcomeForm]![txtSearch] & "*"));

Then if you want to open another form to the selected person's record,
either add a command button to open the form or use the DoubleClick event of
listResult.

HTH,
Brian



Duncan said:
Hello,

I've been having some problems with a database I've been constructing as I'm
quite new to Access, but I've got it to a point I'm relatively happy with
although as it stands the only way users can search through records is to use
Access' default navigation buttons at the bottom of the record. I would
really appreciate any help in expanding this functionality.

I currently have a welcome page which links to form views of each of the
tables in my database through a series of simple open form buttons. I would
ideally like to build a quick search facility into this welcome page which
looks at 2 columns only (First Name and Last Name) from just one of my tables
and brings up a selected matching record in its form view.

I'm anticipating using a either a combo box, or a text box which links into
a list box to bring up a selection of matching records; but either way I'd
like to be able to highlight one of the strings in the list and hit a 'go to
record' button next to the list to open the record.

As I said, I'm quite new to Access so I'd be really grateful if any
responses could be as explicit as possible in terms of where any code needs
to be entered and any properties of fields that will need to be altered.

Any help greatly appreciated,

Thanks in anticipation!
Click to expand...
Click to expand...
 
Hi Duncan,

As to your first question, I'm not really sure how to do it. I'd suggest
posting this as a new thread.

As for your second question, in your form's current event, type Me.txtSearch
= "" .
I'd also put that in the last line above End Sub or at least below your line
to requery listResults in the AfterUpdate event of txtSearch.

Brian

Duncan said:
Thanks very much for that Brian, that's really helpful - exactly what I was
hoping to achieve, just a couple of further questions if you don't mind -

1) Is there any way I can automatically highlight the top record (or the
only record if there's only one returned) in the list box so that I only have
to hit the button without selecting the name?

2) Purely aesthetic, but is there any way I can I get the list box to appear
blank initially before typing my entry into the text box?

Thanks again for all your help - really appreciated!

Duncan



Brian Bastl said:
If I understand you correctly, you want to type into a text box to initiate
a search for a person or persons. On your Welcome form, add an unbound text
box and list box. Name the new controls something like txtSearch and
listResult.

For txtSearch:
leave the control source blank
In the AfterUpdate event, requery listResult.

For listResult:
Columns =2
Col Widths= 0'; 2";
BoundColumn =1
Row Source =
SELECT TablePersons.PersonID, [PFirst] & " " & [PLast] AS Person FROM
TablePersons WHERE ((([PFirst] & " " & [PLast]) Like "*" &
[Forms]![YourWelcomeForm]![txtSearch] & "*"));

Then if you want to open another form to the selected person's record,
either add a command button to open the form or use the DoubleClick event of
listResult.

HTH,
Brian



Duncan said:
Hello,

I've been having some problems with a database I've been constructing
Click to expand...
as
I'm
quite new to Access, but I've got it to a point I'm relatively happy with
although as it stands the only way users can search through records is
Click to expand...
to
use
Access' default navigation buttons at the bottom of the record. I would
really appreciate any help in expanding this functionality.

I currently have a welcome page which links to form views of each of the
tables in my database through a series of simple open form buttons. I would
ideally like to build a quick search facility into this welcome page which
looks at 2 columns only (First Name and Last Name) from just one of my tables
and brings up a selected matching record in its form view.

I'm anticipating using a either a combo box, or a text box which links into
a list box to bring up a selection of matching records; but either way I'd
like to be able to highlight one of the strings in the list and hit a
Click to expand...
'go
to
record' button next to the list to open the record.

As I said, I'm quite new to Access so I'd be really grateful if any
responses could be as explicit as possible in terms of where any code needs
to be entered and any properties of fields that will need to be altered.

Any help greatly appreciated,

Thanks in anticipation!
Click to expand...
Click to expand...
Click to expand...
 
Duncan,

After spending a bit of time with the lack-of-Help files, I've barfed out
something that seems to work, although it may not be the most efficient or
simplest. You'll have to change the names to fit your forms and controls.

Private Sub cmdOpenClients_Click()
On Error GoTo Err_cmdOpenClients_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim lngID As Long
Dim lngRow As Long

stDocName = "frmClients" 'form to open
If Me.lbResults.ListCount <> 0 Then 'check if there's anything in list
box
If IsNull(Me.lbResults) Then 'nothing selected so grab the first
available item
lngRow = Me.lbResults.ListIndex + 1
lngID = Me.lbResults.Column(0, lngRow)
Else
lngID = Me.lbResults
End If

stLinkCriteria = "[ClientID]=" & lngID
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "List Box is empty. Start another search."
Me.txtSearch.SetFocus
End If
Me.lbResults = Null

Exit_cmdOpenClients_Click:
Exit Sub

Err_cmdOpenClients_Click:
MsgBox Err.Description
Resume Exit_cmdOpenClients_Click

End Sub

HTH,
Brian
 
Thanks Brian,

You've been a great help - all of that was really useful!

All the best

Duncan
 
Back
Top