Solved Combined results in multi user search queries

[TML15]

I've created a split db with the be on a shared server and fe on each users desktop. I have tables for the users to search using criteria they input into the form.
When they press a 'search' button, it runs the query against the tables and finds the data based on their criteria and only provides results based on their criteria.
The issue is when 2 or more users press the 'search' button at the same time, the results output on the form from the query are combined. How can I keep the search (query) results separate? Thanks!

 

[theDBguy]

Move the search tables to the FE.

 

[TML15]

Move the search tables to the FE.

I should also add that the tables being searched are being updated daily with new / updated data and are linked tables.

 

[Isaac]

I take it that your users are all sharing the same, single, front end application?

 

[TML15]

I take it that your users are all sharing the same, single, front end application?

The users are using a FE that was shared from my OneDrive and downloaded to their desktop.

 

[Isaac]

So they each have their own separate copy of it on their own desktop?

Where is the back end?

 

[TML15]

The BE is on a shared drive (for work).

 

[Isaac]

It sounds like you are utilizing an intermediary table for your search results. You should probably switch to writing code that filters the form or sets the form's recordsource in code rather than in a table where multiple people are accessing them. What you are doing is kind of an alternative method. Or as dbGuy said you can move the table to their FE.

Personally, I would keep tables in the back end. So either modify your process to pick up the username (to segregate what people are doing for their own purposes), or modify your Search button code to stop using a table as an intermediary

 

[TML15]

As I am learning Access on the go, I admit my coding skills are not great and that Google & YouTube have been helpful. So, I am grateful for your advice and will look into how to implement it. Is there a website you might recommend that may help with coding this?

 

[Isaac]

Search results for query: search button to filter form | Access World Forums (access-programmers.co.uk)

 

[TML15]

This search I'm creating is to find an address or a range of addresses based on a table of addresses. So, as you suggested, I tried writing code and got this far:

Dim strsearch As String
Dim strText As String

strText = Me.txtStreet.Value
strText1 = Me.txtMun.Value
strText2 = Me.txtStType.Value
strText3 = Me.txtDir.Value

strsearch = "SELECT * from Ontario where ((street_name like ""*" & strText & "*"")and(municipality like ""*" & strText1 & "*"")and(Street_Type like ""*" & strText2 & "*"")and(Direction like ""*" & strText3 & "*""))"

Me.RecordSource = strsearch

But, I have to extend this search to include searching 4 number fields. There are ranges to search. Odd numbers & Even numbers within a range.
So for example, the street would have Odd numbers from 1 to 9 and Even numbers from 2 to 10. The search needs to find any number input between 1 to 10 or show all results if left blank (null). So, if the criteria entered has the street name (txtStreet) as 'main', and municipality (txtMun) as 'Ottawa' and the street number (from 4 number fields - Odd Start, Odd End, Even Start, Even End) as '5'. Then the search result would output

5 MAIN ST OTTAWA

(assuming the street type in the table is 'ST')

I hope this make sense.

Would you be able to help me figure out how to add to this search to include the street number ranges? My guess is that the code would include
the greater than, less than and equals (<>=) symbols but I'm not sure how to go about it. Also, I would assume I'd have to do something like
Dim strNumber As Integer and assign a value to it like strValue = Me.IntOdd.Value ? Just guessing....I'm not sure about this.

Any help is appreciated!!

 

[TML15]

Okay, so this is what I have...

strsearch = "SELECT * from Ontario Where((street_name like ""*" & strText & "*"")and(municipality like ""*" & strText1 & "*"")and(Street_Type like ""*" & strText2 & "*"")and(Direction like ""*" & strText3 & "*"")and Odd_Start>=Me.txtStNum and Odd_End<=Me.txtStNum and Even_Start>=Me.txtStNum and Even_End<=Me.txtStNum)"

It looks like it might work but I'm getting the "Enter Parameter Value" popup for Me.txtStNum. Can you guys help?

 

[theDBguy]

Try:

strsearch = "SELECT * from Ontario Where((street_name like ""*" & strText & "*"")and(municipality like ""*" & strText1 & "*"")and(Street_Type like ""*" & strText2 & "*"")and(Direction like ""*" & strText3 & "*"")and Odd_Start>=" & Me.txtStNum & " and Odd_End<=" & Me.txtStNum & " and Even_Start>=" & Me.txtStNum & " and Even_End<=" & Me.txtStNum & ")"

That assumes Me.tstStNum is a numeric value.

 

[TML15]

Sorry...good assumption! As it is for an address number, it is a numeric value. I am now getting this error:

with Me.RecordSource = strsearch being highlighted yellow when I debug. What do you think @theDBguy ?

 

[theDBguy]

Let's try it again...

        strsearch = "SELECT * from Ontario Where((street_name like ""*" & strText & "*"") and (municipality like ""*" & strText1 & "*"") and (Street_Type like ""*" & strText2 & "*"") and (Direction like ""*" & strText3 & "*"") and Odd_Start>=" & Me.txtStNum & " and Odd_End<=" & Me.txtStNum & " and Even_Start>=" & Me.txtStNum & " and Even_End<=" & Me.txtStNum & ")"

Hope that helps...

 

[TML15]

@theDBguy I really appreciate the effort!! Unfortunately, we have the same result as before with error 3075 & with Me.RecordSource = strsearch being highlighted yellow

 

[theDBguy]

@theDBguy I really appreciate the effort!! Unfortunately, we have the same result as before with error 3075 & with Me.RecordSource = strsearch being highlighted yellow

Oh, looking back at the error message you posted, there was no value in Me.txtStNum. Not sure what you want to happen if that Textbox is empty, but you could try using the Nz() function. For example: Nz(Me.txtStNum, 0)

strsearch = "SELECT * from Ontario Where((street_name like ""*" & strText & "*"") and (municipality like ""*" & strText1 & "*"") and (Street_Type like ""*" & strText2 & "*"") and (Direction like ""*" & strText3 & "*"") and Odd_Start>=" & Nz(Me.txtStNum,0) & " and Odd_End<=" & Nz(Me.txtStNum,0) & " and Even_Start>=" & Nz(Me.txtStNum,0) & " and Even_End<=" & Nz(Me.txtStNum,0) & ")"

 

[TML15]

Sorry @theDBguy , I know it's hard to figure out online. Here is all the code:

Dim strsearch As String
Dim strText As String
Dim strText1 As String
Dim strText2 As String
Dim strText3 As String
Dim strNumber As Integer

strText = Nz(Me.txtStreet.Value)
strText1 = Nz(Me.txtMun.Value)
strText2 = Nz(Me.txtStType.Value)
strText3 = Nz(Me.txtDir.Value)
strNumber = Nz(Me.txtStNum, 0)

strsearch = "SELECT * from Ontario Where((street_name like ""*" & strText & "*"") and (municipality like ""*" & strText1 & "*"") and (Street_Type like ""*" & strText2 & "*"") and (Direction like ""*" & strText3 & "*"") and Odd_Start>=" & Me.txtStNum & " and Odd_End<=" & Me.txtStNum & " and Even_Start>=" & Me.txtStNum & " and Even_End<=" & Me.txtStNum & ")"
Me.RecordSource = strsearch

 

[theDBguy]

Sorry @theDBguy , I know it's hard to figure out online. Here is all the code:

Dim strsearch As String
Dim strText As String
Dim strText1 As String
Dim strText2 As String
Dim strText3 As String
Dim strNumber As Integer

strText = Nz(Me.txtStreet.Value)
strText1 = Nz(Me.txtMun.Value)
strText2 = Nz(Me.txtStType.Value)
strText3 = Nz(Me.txtDir.Value)
strNumber = Nz(Me.txtStNum, 0)

strsearch = "SELECT * from Ontario Where((street_name like ""*" & strText & "*"") and (municipality like ""*" & strText1 & "*"") and (Street_Type like ""*" & strText2 & "*"") and (Direction like ""*" & strText3 & "*"") and Odd_Start>=" & Me.txtStNum & " and Odd_End<=" & Me.txtStNum & " and Even_Start>=" & Me.txtStNum & " and Even_End<=" & Me.txtStNum & ")"
Me.RecordSource = strsearch

Did you try my last attempt?

 

[TML15]

Hi @theDBguy ! Yes, I did and had the same error message.