Hello and Help please :) (1 Viewer)

Jaydub1991

New member
Local time
Today, 23:43
Joined
Sep 21, 2021
Messages
7
Hello all,

I'm new around here and have a fair knowledge of Excel and Access but VBA is not quite my friend yet!

I can put together something that has lots of nice and simple features I would say I'm verging on intermediate but not quite pro.

Long and short of it, I have an Access Database which I have put together with multiple tables and multiple forms to pull data from each of these tables.

Now I am trying to build a Search form so I can Search by either the Name or Postcode (Zip Code for my American cousins)

So Far I have a Search function with a list box which will populate based on the persons Name only ( I have also added in an 'after event' so it auto populates) but I cannot get this to multifunction and allow either name or postcode search it will only search by name.

I would also like to be able to double click on one of the results and click through to the entry on the Report form.

Whether it would be possible to Search multiple forms from one Search box without joining all the tables/ Forms together.

Any help would be Very Greatly appreciated.
 

Ranman256

Well-known member
Local time
Today, 19:43
Joined
Apr 9, 2015
Messages
4,339
Make text boxes on the form for txtName & txtZip

have a query open (or listbox fill) in the afterupdate event of the box:
Code:
sub txtName_afterupdate()
   docmd.openquery "qsFindName"
 'or
 lstBox.requery     'the listbox bound to a query,qsFindName, that looks at the textbox
   
end sub

qsFindName sql = select * from table where [lastname] = forms!fMyForm!txtName

same with zip/postal codes.
 

Jaydub1991

New member
Local time
Today, 23:43
Joined
Sep 21, 2021
Messages
7
1632236496068.png



So this is what I have so far (the 2 columns have address and zip code)

Search works for Name but not for Postcode. Below is the Search Query I have been used for the name but this doesnt seem to work for postcode.

1632236594083.png
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:43
Joined
Feb 28, 2001
Messages
27,001
Whether it would be possible to Search multiple forms from one Search box without joining all the tables/ Forms together.

To do this kind of searching, there are two possibilities. "Joining all the tables together" makes me wonder if the tables are properly normalized. If you do not understand that word in database context, you really need to read up on Database Normalization. That's a good web search all by itself. If you DO that web search, look at the .EDU sites (usually university or college sites) because they are less likely to pepper their offering with ads than the .COM sites.

The other possibility is that your multiple tables really SHOULD be separate but they share some element as a prime key/foreign key relationship. In that case, to give good advice we would need to know more about the structure.
 

mike60smart

Registered User.
Local time
Today, 23:43
Joined
Aug 6, 2017
Messages
1,899
View attachment 94643


So this is what I have so far (the 2 columns have address and zip code)

Search works for Name but not for Postcode. Below is the Search Query I have been used for the name but this doesnt seem to work for postcode.

View attachment 94644
Hi

Point to note you need to change the fieldname "Name" to something else because "Name" is a reserved word in Access.

On your Search Form you need an Unbound Textbox in the Header named :"txt_Search"

Then in the AfterUpdate of the TextBox you need to use the following:-

Dim strFilter As String
strFilter = "[Name] Like '*" & Me.txt_Search & "*' OR [PostCode] Like '*" & Me.txt_Search & "*' "
Me.Filter = strFilter
Me.FilterOn = True
Me.txt_Search.SetFocus
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:43
Joined
Oct 29, 2018
Messages
21,358
View attachment 94643


So this is what I have so far (the 2 columns have address and zip code)

Search works for Name but not for Postcode. Below is the Search Query I have been used for the name but this doesnt seem to work for postcode.

View attachment 94644
Hi. Welcome to AWF!

What does "doesn't seem to work" mean? What is happening instead? To add a search criteria for multiple columns, you will have to place the criteria in separate rows. Have you tried that?
 

Jaydub1991

New member
Local time
Today, 23:43
Joined
Sep 21, 2021
Messages
7
To do this kind of searching, there are two possibilities. "Joining all the tables together" makes me wonder if the tables are properly normalized. If you do not understand that word in database context, you really need to read up on Database Normalization. That's a good web search all by itself. If you DO that web search, look at the .EDU sites (usually university or college sites) because they are less likely to pepper their offering with ads than the .COM sites.

The other possibility is that your multiple tables really SHOULD be separate but they share some element as a prime key/foreign key relationship. In that case, to give good advice we would need to know more about the structure.


Ok So Each Table Represents a different client of mine, and each table differs slightly in the columns, I could do some works to normalise some of the columns so they all have the same column headings.

But The reference numbers from each all differ, but Ideally I would only like to search for the Name or Zip (postal) Code. But the results in the List box show results from all of the tables but with their respective client name in a column which I could add.
Hi. Welcome to AWF!

What does "doesn't seem to work" mean? What is happening instead? To add a search criteria for multiple columns, you will have to place the criteria in separate rows. Have you tried that?


I can search for the Name but the Zip/ Postal code returns no results
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:43
Joined
Feb 19, 2002
Messages
42,981
Search works for Name but not for Postcode. Below is the Search Query I have been used for the name but this doesnt seem to work for postcode.
There is no criteria in the postcode column. Put it on the same row if you want to "AND" the conditions. Put it on a different row if you want to "OR" the conditions.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:43
Joined
Oct 29, 2018
Messages
21,358
I can search for the Name but the Zip/ Postal code returns no results
Can you please show us what you've tried? Did you try what I said about placing your criteria in separate rows?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:43
Joined
Jul 9, 2003
Messages
16,245
Now I am trying to build a Search form

I answered a similar question recently. My Search Form returns the results into a subform, not a list box. I incorporated the search form into the user's database. You can download the example here:-

 

Users who are viewing this thread

Top Bottom