search and listbox

[val_87]

Hey all,
Ran in to a bit of a problem, again.

DB is attached.
Now there is a List Box called listbox1 and a searchbox calld txtSearch.
The idea is that when searched in txtSearch box the stuff in list box is displayed and filetered.
I used a youtube tutorial to do it, did all like the guy on the screen, just used my values and names.
His worked, mine does not.

My code:

strRowsource = " SELECT [Baby UPI], Consent " & "FROM Personal Info"
listbox1.RowSource = strRowsource

He wrote code in to txtSearch, can anyone look at the code and see what i am doing wrong? It seems correct, it does not throw any errors or bugs, but it does not display.... I can't move forward to options box there until i have this working.

Thank you!

 

[Minty]

Your code doesn't refer to the search box, and is based on the On_Click event which means you won't get a chance to enter anything. Assuming I have grasped what you are trying to do, I don't see the point of having a list box that would only display the one record you are trying to search for. List boxes by definition are normally used to display a list, not a single record.

Normally you would have all your data displayed, then search or filter for the record based on your search string.

If I get time today I'll add a simple search type of function to the form I did in the last database I uploaded.

 

[JHB]

Surround table and field names which have spaces in them by [...], or even better don't have spaces in them.

 

[val_87]

Your code doesn't refer to the search box, and is based on the On_Click event which means you won't get a chance to enter anything. Assuming I have grasped what you are trying to do, I don't see the point of having a list box that would only display the one record you are trying to search for. List boxes by definition are normally used to display a list, not a single record.

Normally you would have all your data displayed, then search or filter for the record based on your search string.

If I get time today I'll add a simple search type of function to the form I did in the last database I uploaded.

This is what i am trying to accomplish, i want data displayed in a list box. In a list box data originates from Personal Info containing UPI, DOB and Consent. When searched against UPI i can then click it and it will open a form full_personal_details... of that record. I don't even think i need the filter function i have in there, i might just delete it.

Did i explain it ok? Tell me if you did not understand what i mean.

This is the tutorial i used.

 

[val_87]

Ok it displays now! So JHB solution works, it now displays the only 2 "records i have" no i just have to make it search

 

[Minty]

I've attached a version with a simple search box.
This also has the tables and field names adjusted to remove spaces and other characters that shouldn't be included.

There are many other ways of achieving search functions this is just a simple one. You can program it more elegantly removing the repeating code, however I wanted to show you a simple way of getting to where you want to head.

 

[dlhappy]

I've attached a version with a simple search box.
This also has the tables and field names adjusted to remove spaces and other characters that shouldn't be included.

There are many other ways of achieving search functions this is just a simple one. You can program it more elegantly removing the repeating code, however I wanted to show you a simple way of getting to where you want to head.

Hi Minty:

I downloaded the database with the search box that you added and this is exactly what I want, but with a slight modification.

I am retired and keep a membership database as an unpaid volunteer for a non-profit organization (we are all unpaid volunteers).

The only other thing I would like added is a Find Next function on the form.
I sorted A to Z on LastName and if I need to search on FirstName, the Find Next function would be very beneficial as the first name will not be in order on the two lists.

I do not know VBA coding and I tried to copy what I've seen to set up a Find Next function but it does not work. I added a second button for FindNext and I probably need a new section on the VBA coding but have no idea how to do it or how to key it in with the "bookmark" created by the first found selection from the other button, the Find button.

I would be most grateful if you could edit the code which I have pasted here to get the Find Next function to work with the form. I've have made a screen capture of the form in design view (your form, but modified to include the button), and inserted it below.

Thanks for any help.

EDIT: I have deleted the old code and copied new code here -- note, I copied a sample Find (search) next, but it does not work

------------------------------------------------------------------------------------------------------------
| cmdFinder | | Click |
------------------------------------------------------------------------------------------------------------

Option Compare Database
Option Explicit
-----------------------------------------------------------------------------------------------------------------

Private Sub cmdFinder_Click()

Dim sFind As String
Dim daoRS As DAO.Recordset


If IsNull(Me.txtFinder) Then Exit Sub

sFind = Me.txtFinder

Select Case Me.optFind_Wnat

Case 1 ' Last Name

Set daoRS = Me.RecordsetClone
daoRS.FindFirst ("[LastName] Like '*" & sFind & "*'")
'If we could find the newly added record, jump to it
If Not daoRS.NoMatch Then
Me.Bookmark = daoRS.Bookmark
End If

Case 2 ' First Name


Set daoRS = Me.RecordsetClone
daoRS.FindFirst ("[FirstName] Like '*" & sFind & "*'")
'If we could find the newly added record, jump to it
If Not daoRS.NoMatch Then
Me.Bookmark = daoRS.Bookmark
End If
End Select


End Sub
----------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------
| (General) | | cmdSearch_Click |
------------------------------------------------------------------------------------------------------------


Private Sub cmdSearch_Click()
Dim RS As Object
Dim strSearch As String

Set RS = Me.RecordsetClone
strSearch = "LastName=" & Nz(Me.txtID, 0)

With RS
.Bookmark = Me.Bookmark
If Me.cmdSearch.Caption = "Find" Then
.FindFirst strSearch
Else
.FindNext "LastName=" & Me.txtID
End If
If .NoMatch Then
MsgBox "No match"
cmdSearch.Caption = "Find"
Else
Me.Bookmark = .Bookmark
Me.cmdSearch.Caption = "Find Next"
End If

End With

RS.Close
Set RS = Nothing

End Sub
------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------
| cmdOpenInfo | | Click |
------------------------------------------------------------------------------------------------------------

Private Sub cmdOpenInfo_Click()

Dim iMembershipID As Integer
Dim sWhereCondition As String
iMembershipID = Me.MembershipID

If IsNull(iMembershipID) Then Exit Sub

sWhereCondition = "MembershipID = " & iMembershipID

DoCmd.OpenForm "frmMembershipMembersTransPOSTINGListBoxCONTINUOUS", acNormal, , sWhereCondition, , acWindowNormal


End Sub
------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------
| Membership_ID | | DblClick |
------------------------------------------------------------------------------------------------------------
Private Sub Membership_ID_DblClick(Cancel As Integer)
Dim iMembershipID As Integer
Dim sWhereCondition As String
iMembershipID = Me.MembershipID

If IsNull(iMembershipID) Then Exit Sub

sWhereCondition = "MembershipID = " & iMembershipID

DoCmd.OpenForm "frmMembershipMembersTransPOSTINGListBoxCONTINUOUS", acNormal, , sWhereCondition, , acWindowNormal

End Sub

 

[dlhappy]

Please disregard my prior post, as the problem has been solved!

 

[Alvarogue]

have you tried to use Combo box as a search making combo box looking for that .tbl and putting them together in a form???

 

[dlhappy]

Hi Alvarogue:

I'm not sure if the below is what you mean, but it all works fine now.

Here is the revised form, with one button for Find Next rather than two buttons and here is the working, corrected code (given to me by a member of another database forum):

This is the working code:


cmdFindNext | Click
---------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
-------------------------------------------------------------------------------------

Private Sub cmdFindNext_Click()

Dim sFind As String
Dim sFld As String
Dim daoRS As DAO.Recordset
If IsNull(Me.txtFinder) Then Exit Sub
sFind = Me.txtFinder
Select Case Me.optFind_Wnat
Case 1 ' Last Name
sFld = "[LastName]"
Case 2 ' First Name
sFld = "[FirstName]"
End Select
Set daoRS = Me.RecordsetClone
'daoRS.FindFirst (sFld & " Like '*" & sFind & "*'")
daoRS.FindNext (sFld & " Like '*" & sFind & "*'")
'If we could find the newly added record, jump to it
If Not daoRS.NoMatch Then
Me.Bookmark = daoRS.Bookmark
Else
MsgBox "No 'Next' Record", vbInformation
End If

End Sub
----------------------------------------------------------------------------------
So, everything is set up and works fine now; nevertheless, thanks for your reply!

 

[Alvarogue]

Hi Alvarogue:

I'm not sure if the below is what you mean, but it all works fine now.

Here is the revised form, with one button for Find Next rather than two buttons and here is the working, corrected code (given to me by a member of another database forum):

This is the working code:


cmdFindNext | Click
---------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
-------------------------------------------------------------------------------------

Private Sub cmdFindNext_Click()

Dim sFind As String
Dim sFld As String
Dim daoRS As DAO.Recordset
If IsNull(Me.txtFinder) Then Exit Sub
sFind = Me.txtFinder
Select Case Me.optFind_Wnat
Case 1 ' Last Name
sFld = "[LastName]"
Case 2 ' First Name
sFld = "[FirstName]"
End Select
Set daoRS = Me.RecordsetClone
'daoRS.FindFirst (sFld & " Like '*" & sFind & "*'")
daoRS.FindNext (sFld & " Like '*" & sFind & "*'")
'If we could find the newly added record, jump to it
If Not daoRS.NoMatch Then
Me.Bookmark = daoRS.Bookmark
Else
MsgBox "No 'Next' Record", vbInformation
End If

End Sub
----------------------------------------------------------------------------------
So, everything is set up and works fine now; nevertheless, thanks for your reply!

I'm having major problems with relationships and subforms... are you acknowledge in those areas???

 

[dlhappy]

I'm having major problems with relationships and subforms... are you acknowledge in those areas???

Not very knowledgeable, I generally use the wizards for forms and queries. However, as far as relationship to get a subform show, I think you need the relationship between two tables to have a common, non-duplicative, field. I use the Access generated Primary Key, AutoNumber (as the data type). I have a subform (transactions) on my main form (which lists the members, name, address, etc. and even has a another section on the form which includes a headshot photo of the member, if I have one, from a tblMembersPix.

Anyway, for the main part of the form, from tblMembership, I linked the MembershipID to the MemberID field from tblMembersTrans. This can be done by selecting Database Tools form the Access Ribbon/Menu and then selecting Relationships. In the window that opens, you can right click on a blank space to add one of your tables, and for example, in the case of two tables, use the mouse to indicate which fields are to be related (the two primary key fields, as mentioned above.

Then use Create/More Forms/Forms Wizard to create your form with a subform. I actually did do this is a very long time, so I trying to remember the steps.

Hope that this works for you.

This is a view of my main posting form, with the transaction sub-form on the bottom: (It was done a long time ago, and with assistance from others, and as far as the coding behind the form, entirely by others. If I had to do this from scratch, it would be a bit of a challenge):