I have designed a data entry form in MS Access 2003. One function I find frustrating is when I want to find a particular record while using this entry form. If I use the Edit>Find facility, for instance, looking for someone with the last name "Jones", eg, I must page through all the Joneses in the database by using either the "Next" button or by pressing the "Enter" key. This works but if I pass by the Jones I am looking for, I have to go all the way back to the first record and then slowly repeat the above process to find the Jones record I was looking for in the first place.
I am wondering if there is some VBA code somewhere that would pop up a table, for example, of all the Jones records in my database. Then I could simply click on the Jones' record of interest in this pop up table and go directly to this record in my data entry form.
I have a report that runs a number of queries via a macro in the on open property. These queries populate a table used in the report. The on close property runs a query to clear the table contents.
When a user ran this, he had a flurry of warning notices which I tracked down to the Confirm settings in the tools/options/edit find tab.
Is is possible to set these using vba?
I guess that this could be achieved using send keys, but this would probably be a bit clumsy - to say the least.
Can it be done so that the settings are changed only for the current session so that users have the original settings?
All of our book numbers have the # symbol in front of them. I want to run a query that shows the same book number but replaces the # symbol with an *. I don't want the actual fields to be changed in the database, i just want them to show up in the query that way. I know in excel you can use the substitute function, i.e. substitute(A1,"#","*") is there a similar function in access? I do not want to change the data in my tables, just be able to export the query showing the * instead of the #. Any ideas?
I can't figure out how to replace a period that is in the middle of a string and end up with 10 digits. For example 55.5555 would be 5500005555. I can use replace() but the tricky part is I have to end up with 10 digits.
Ultimately what I'm trying to do is - when a user enters 55.5555, 555.5, 5.5 or any variation they will be able to find the corresponding record. So a wildcard for the search or the replacement of the "." with enough zeros for 10 digits.
Here is what I'm using now - i making them enter the full 10 digit number but would like to give them the ability to use the period in place of the zeros.
Function Search() Dim lssql As String Dim lsSn As Recordset Dim db As Database Dim lsMessage As String Dim sMsg As String Dim vRetVal As Variant Set db = CurrentDb()
Is there a function similar to the find function in excel which will return the starting postion of a charcter in a string and allow you to set the position for seaching IE I want to know at what postion the second ":" starts at in the following string
I am trying to add a find button to the form. But I keep getting an error saying "Error accessing file. Network connection may have been lost" I have a feeling it has something to do with the Event Procedure. Any thoughts?
I have a database functioning pretty well. All I did was move the files from one computer to another via USB stick and now I get this alarm when I try to run this particular macro.
It is a macro to set a value to specific field. The Macro uses a condition [Forms]![tblCustomerCall]![Alarm Number]="000". If the condition is met then it is supposed to set a value to a field in the same form. [Forms]![tblCustomerCall]![Alarm Descrption] is the item to set. Left("TV Parity Alarm",50) is the expression. So if 000 is entered in then the text TV Parity Alarm is filled into the Alarm description field.
It was working perfect until I moved the files to another computer. I have Office SP2 installed and have updated office with everything available.
I have a field (SNumber) which is a text field and contains records that have a number which sometimes also have 2 letters on the right. ( examples: 1, 2, 3CD, 5, 6 HD). I am using the Right function in a code I am writing.
If Right (SNumber, 2) = "CD" then .....rest of code here
I would like instead of stating actual letters to code "any 2 digits". Can this be done and what is the correct code for any digits in this instance?
I'm relavtively new to databases but after a steep learning curve (being left with many databases created by my old manager with no instructions on use) I'm getting there.What I am trying to do is pull out the date from records in a field called "Model_ID". The Model_ID field contains this kind of information:
Endscopy20120726JSmith GISurgery20120521JDoe
I want to bring back "20120726" or "20120521". In excel I can do this with the mid and find functions but find doesn't work in access. I've tried combining the mid function with instr function but it comes back as too complex!
I have a form with couple of textboxes bound to a table. When the user opens the form to enter records, i want to write a function that would go through the textboxes to check whether the textboxes are left NULL. Now i can write code on button click for each form, but i was wondering if its possible to write a function that could be called for each form that i have to check for null values.
I am using VBA for MS Access 2003. I left this project since 4 years and I know back to it as mass needs forced me to use it again.
I use trim function. it raises the compile error: can't find project or library. I know that the solution is by adding the library in the References item from the Tools menu. Thus What are the minimum libraries that I should add so that these basic problems solved. By the way: I already added the Microsoft Visual Basic for Applications Extensibility 5.3.
The company I work for is consolidating regional server space onto a single server. I have relinked all of my tables, but my ~180 queries are still pointing to the old server.
My question: Is there a script or other process that can be executed that will search through all my queries within the database to find the string "dbo_tbl" and replace with "dbo_vwtbl"? I would very much like to avoid taking each individual query to a notepad...
I'm looking for some type of custom function that will search a specified column for any keywords listed inside another table.
I can run a query on each keyword individually, however there are 50 and it takes a long time each time I do it. I was hoping to write in a function for that column and it would just select all records that match.
These would all need to be a "like" with an " * " on each end of the word.
With SQL it would look something like:
Code: select a.address1 from main_tbl as a where a.address1 like '* north *' or a.address1 like '* park *' or a.address1 like '* south *';
I just want it to read each of the table values instead of hard coding them and the column name would be the function name so it can be used in any column I specify. I'm just not sure how to incorporate this into a custom function.
Is it possible on the Find Record button added to a form to adjust the search function so it defaults to a specific box on the form? I have a form for tracking employee's and on my Find Record button I would like it to default to the Last name instead of the Record number.
I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total ----------------------- 2 | 1234 | 80.00 ------------------------ 1 | 43526 | 20.00 ------------------------ > | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer) Dim SPSelect As String SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE" SPSelect = SPSelect & " ItemID = '" & ItemID SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time End Date/Time Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee 12/06/2014 01:00--12/06/2014 03:00------John Smith 12/06/2014 04:00--12/06/2014 06:00------Jane Doe 13/06/2014 02:00--13/06/2014 05:00------John Smith 13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
We have a date function that converts a text date format. Can someone help me with time function to do the same thing? We want military time. The field is like this now: txt fields. 160037 213137 224356 235716 235800 12341 21708 22732 Here is the date function we use: Function f2Date(strDateOld As String) Dim strDate As String, strMonth As String, strYear As String strMonth = Mid(strDateOld, 5, 2) strDate = Right(strDateOld, 2) strYear = Left(strDateOld, 4) f2Date = strMonth & "-" & strDate & "-" & strYear f2Date = CDate(f2Date) f2Date = Format(f2Date, "mmmm d yyyy") End Function
I need a little help. In my DB, I have a command button set up (I was tired of typing in dates) for date, but I used the Now function, which also gives me the time.
Now I have over 3000 subrecords of the main ones. I now need to queries transaction for that specific date, but it also retrieves the time.
I tried to go back and change the NOW to DATE in VB, but the code does not run.
How do I change all records that have date and time (using NOW function) and only click that command button to show only the date (mm/dd/yyyy)?
A customer's name is SZEE. Seek him through the SName textbox with Find, and Access can't find him. (Same in the table.) Seek him with a wildcard Sz* and there he is.
I've tried it on another machine - also with Access2k - and it's the same.
Is it an Access quirk? Is there an answer? (The client asks why. Be good to be able to say.) Cheers.
