Search results

Hi, I am running a form with a subform, the main form has several search boxes but I want the query to work primarily from two. i.e. OD and ID What i'm after is typing a number in the OD textbox, same for the ID textbox and the query return any record where the OD is between = OD-1 and OD+1...

Hi, thanks for replying again. Please find the screenshots for my set up and the results from the query on the subform i receive. As you can see, the query does work, it just doesn't work on an individual basis. I am positive that this is because the query demands that both boxes be filled...

Hi both and thank you for taking the time to respond. Just so you are aware, the code I wrote above works perfectly fine in the query as I have already tested it. The problem I have is that I want the list to filter down with the rules I have in there rather than making everything dissappear...

Good morning all I have a query which populates a subform based on values as below: main form txtOD, txtID textboxes with an afterupdate event on each set to requery the subform I want the query to be able to show all records if nothing is entered in any of the 2 search boxes. I would have...

Hi Minty, can you explain what you mean by this please? I assume you mean opening the view from the SQL Studio Management screen and seeing if that alone causes the hang up?

Hi, I have done this, but it seems it still locks the table up that people need to be accessing on a regular read write basis. It's strange though as the pass through query is aimed at a view, and not the tables themselves, would this even matter? I apologise, I know I don't know exactly what...

Thank you again Minty, it seems to be working. Now i've got to figure out why having this access database open causes the Sage200c program to lock up and stop users from being able to perform certain tasks. Kindest regards

Hi, thanks again Minty, always appreciated. Now for my next question... Access can use the NZ function to get null and blank fields as well as an option to specify "*" for wildcard search, is there a way to use this in the SQL String? i.e. DocumentNo is fine and works, but if I add another...

Oddly I remember running into a similar issue previously where the ' was required around the field, I tried it here in the code you supplied and now it's perfect! Now I can refer to the items from our orders, I can do a heck of a lot more using our existing databases and upgrade them all, many...

Thank you Minty, it does look as though the format of the Pass Through Query is being changed by the after event of the textbox (which is what I was after) now the code you suggested has been placed there. However, I now have another issue, I am now seeing the following issue if I run the query...

How would this reflect in the subform which will have the items listed dependent on the jobref?

Thank you for your help again Minty, nice to bump into you again. I'm curious, where exactly do I put that code? I'm sorry to ask, but this is completely new to me...

Good afternoon Gents and Gentesses I am looking at Access 2010, and am in the process of creating a small database that will look at Sage 200c data directly on the server and hopefully not lock any tables and records while I run queries on it. I have a form (frmMainForm) On frmMainForm there...

Hi Wayne, no, the views I am currently creating only look at the original tables, not views of views. The tables themselves are brand new and only have a few thousand rows. However our 'stock' table is now about 150k rows and growing. This shouldn't be causing me an issue though :/

Hi Minty, it's the newest version of Sage 200c, running on Windows Server 2016, on SQL Server 2016 :-) Is it possible that the database is seeing this ODBC connection as an extra connection and thereby locking others for that reason?

Hi Minty, thanks for the response, I will see if the data can be edited from with the Access Linked Table. Oddly, the SQL ODBC Driver is the one found in the , having looked at the version I used to create the System DSN, it is: version: 6.01.7601.17514, SQLSRV32.DLL, 21/11/2010 EDIT: Just...

Hi Wayne and others, thank you all for the kind replies. I have had a look and Access is only using 'linked' tables, which are from sql table views. These SQL Views can be made from multiple tables to make it easier for Access to have everything in one place. I've opened up the database today...

Well, that all looks interesting but I have no idea if this helps me or not ;-)

Thank you for your assistance so far, however I have run into another issue. I have a number of access databases that all look at various aspects of the SQL database behind Sage 200. It has been noted that when one particular database is open and is reading from SQL Views, it is stopping Sage...

Well, damn. That was fast... applied the revised code and the Dlookup worked splendid. OK, is there a better way of doing a Dlookup if there are several fields populated from the SQL View on the after update of a single box? Kindest regards