I've created a search on a text and date field which has been working fine for few days now. However when I've started to search on the early days in February (i.e. 01/02/2005) it is not finding the records (but they do exist). When I create a record with 13/02/2005 or higher however it does find the record. I desperately need a solution as the DB will fall down without it.
The code I'm using is:
If Not IsNothing(Me.TxtEmpNameSearch) Then
VarWhere = (VarWhere + " AND ") & "[EmpName] ='" & Me.TxtEmpNameSearch & "'"
End If
If Not IsNothing(Me.TxtDateSearch) Then
VarWhere = (VarWhere + " AND ") & "[Dates] =#" & Me.TxtDateSearch & "#"
End If
If IsNothing(DLookup("txtempno", "tblwork", VarWhere)) Then
MsgBox Mes5, OKIn, Ti
End
Exit Sub
End If
I want to be able to find the last value entered into a table from a form that is not based on that table. How do I go about this?
What I am trying to do is produce a 'purchase order number system' that adds 1 onto the previous number, but this purchase order can be generated on many forms and for many reasons across the database. I do not want to use autonumber.
I want to calculate total hours and mins by adding a load of times (in time format) on a report. I've got the total number of minutes and converted this to hours : [txtmins]/60
Now I want to get the remaining minutes by using : [totalmins] - ([totalhours]*60)
However, how do I round DOWN the [totalhours] before it goes into the above sum. I can round it, but it always seems to round up.
I have a bit of an odd request. I won't be using normal dates and times, it's a military way for date and time, called a Date Time Group (DTG). It looks like this: 052337Z APR 06
The first two characters (05) represent the day of the month. The next four characters (2337) represent the time. The sixth character (Z) stands for Zulu, which is what we call the format of the time. The next three characters (APR) is the month abbreviation and finally (06) is the current year. Zulu time is obviously a 24 hour clock and it is always GMT...no daylight savings, etc...
I need some ideas on how I can store this in the database and search it as if it were an actual date, so I could get all records between two certain dates, for example. I'm not quite sure on how I would go about doing this.
A couple things I thought of maybe doing is when the user enters the criteria, converting the DTG they enter into local time and have the values in the database stored as local time. Not sure how to do that. Second I thought maybe storing each part of the DTG individually and then searching that way. Any help appreciated.
I would like to make an Access database for my collection of scientific papers. From that database I should be able to find articles using descriptive keywords. The problem is I haven’t figured out how I could search for the articles needed using multiple keywords at the same time (eg. papers that fill the criteria: <keyword a> AND <keyword b> AND <keyword c>).
What kind of tables and queries I have to create to do that?
I have a query that contains a complete_date including time (this is automatically datestamped when the user presses a button)At the end of the day, I want to show how many records where processed that day but cannot figure out how to display records between 06:00am and 08:00pm on todays date.
Also, the same applies to selecting a range of dates (eg. the full week), How do I display the records that fall between Monday - Friday including the time.
In criteria of a Query I have this SQL: Like [Forms]![FSearch].[qb1] & "*"
This SQL is to write in a textbox one parameter at a time to search in a table.I need to search for more than one parameter at a time and I have been trying to add to the SQL the operator OR or AND but does not work.
Like [Forms]![FSearch].[qb1] & "*" OR Like [Forms]![FSearch].[qb1] & "*" Like [Forms]![FSearch].[qb1] & "*" AND Like [Forms]![FSearch].[qb1] & "*"
How can I search for more than parameters at a time?
I'm getting a Run-time error 2295: Unknown Message Recepients.
I've got a DB of about 2000 clients. In testing I did a test DB with 50 random clients. Using any search criteria, it would grab those clients and open a new email with their email addresses no problem. However when I do this same thing with my complete DB of 2000 clients, I get this run time error. I'm pretty confused here, and anxious. It looks like the program is working, I just need it to run with all my clients. This is the last thing I must conquer to be finished with this project.
I am attempting to create a search form where a user can search by either employee name or company name. I have 5 tables to use. Is there a simple way of creating a search method for this? I would like to be able to have the user type in a name and click a button that says search with any records matching the search come up. However, I could definitely use a method where they type it in a box and it finds it as the person types.
How to search from all five tables at the same time.
I have a "Search" form that I am using to search through a query in a list box of all of my Customers. Once the record that I am wanting to view comes up I highlight it and click a "View" button to bring up another form with the customers full record. When this form is open I am able to update the customer information and add User's to it if necessary.
My problem comes after I click on my save and close button that takes me back to me search form. I want to close the search form and the error I get is "The record cannot be deleted or changed because table "tblUserProfile" includes related records." I click OK and get a SECOND error stating "You can't save this record at this time. MS Access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?"
I have already made my changes and saved them in the form with the customer record so am not sure why I am getting an error when trying to close the Search form.
I want to add a system of getting unique numbers into my database based on the month followed by a unique 3 digit code. For example, if it is February and this is the first record then the reference will read 02/001. Any ideas how I would go about doing this. I've set up a seperate table called Reference numbers as these reference numbers will be used on any number of forms.
I've set up a DB to monitor employee hours. I have an employee table with a relationship to work done (one record = one days of work), so everyday is a new record in the work table. This is fine to monitor daily work patterns. However I need to calculate weekly work and other calculations (such as sixth and seventh shift payments, 15%payments, 25% payments etc). Is there any way that I can create a query that will give me one record for an employee that gives me start and end time for a Sunday, same for a Monday etc.
In other words I want to put start and end time for a Sunday to Saturday ( 7 records) into 1 record at the end of the week? (for each employee in the DB.
How can I specify the list that appears in a combo box by programming it with VBA. I have eight unbound text boxes on a form and I want a combobox to generate its drop down based on the values in these boxes. I think I'd have to create 8 variables and take the values from these text boxes and assign to the variables. The bit I get stuck on is how to make these variables appear in the combo box list.
I have a template fax set up in Microsoft word and want to be able to export fields from my database (in form view) to the word document. For example, I have a 'purchase order No' on the access form which I want to send to the Word document. When exported I then want the user to be prompted to save the document as a new name so they do not overwrite the original template fax.
I do not want the cursor to move to the next control on a form when the user presses the enter key. It is a memo field and I want the cursor to move to the next line as it would in a word document.
This is probably v. simple but is doin me head in.
I've got a form based on a query containing three figures (currency). I want a total box that will add the 3 up. On the query I've created a field called Total with the following:
Total:sum([Text1]+[Text2]+Text3])
When three figures are in it works fine. If one of the figures is not there it displays #Name. I could get the fields in the table to automatically display 0, but if a user deletes off the 0 I'm stuffed.
Could you please post me the VBAunbound.zip file so I can start getting to grips with unbound forms within Access. All the links in the forum seem to take me nowhere.
I've been reading a bit on the site from users who are working with DB over the network and speed issues it brings. I've got the same problem over a Citrix network with a split DB. The speed issue comes when loading up a bound form. I've rewritten some of the code so that when a user leaves a form, instead of closing down, it simple gets hidden and things have speeded up a lot. However, the initial load of the screen on DB start up is v.slow.
I'm interested in finding out how to use an unbound form ad then associate it with the data when it has loaded. Can anyone give me any tips or point me in the way of a sample DB.
I've created a linked table database which is running currently at my offices over a Citrix network. We want to put the database into 2 other sites (initially), however, the drive that all the sites see is slightly different for my site than the other sites (i.e. I see it as W: but they see it as P, but physically they are the same drive. Obviously, the links to the tables would have to be changed so the other FE could see the BE. I can't do this from my site as I cannot see the drive as they see it. I don't want them going into the FE design to update the linked table manager everytime I make changes and send them a new DB. Does anyone know how (or have an example DB) to create a button on the FE that will ask the user to select the location of the BE with a file browser, for example and then update the table links.
Think this is a bit of a tough one, but I'm sure someone must have done it at some point.
I want to bring up an input box for a user to enter a password, but want the characters they key to appear as *. I can do the input box bit, just the * bit I'm unsure of.
I've been looking at Sbaxters example unbound form. I want the form to be unbound, but I also need the form to show the autonumber straight away when they create a new record, but the data they enter into the text boxes only gets written to the DB tables when they hit the Save button. Is this possible?
Also, on my forms I always have a record count (on the On_Current event) of the form. When they use the next button this changes to show 1 of 100 for example. If the form is unbound how do I get this to display how many records are in the DB or how many records have been found during a search.
I've sussed out how to do DLookups now to find duplicates etc. What I want to do however is look for duplicates say in the primary key of a field and then return a message saying their is a duplicate (and return the company name for example) instead of just saying there is a duplicate.
To explain a bit better I have companies set up with codes to recognise them (which a user keys in). If a user keys in a code already used by a company I want a message to display there is a duplicate and tell them the name of the company this is already being used for.
I've created an Access DB on a Citrix server which is multi user so has been split and user linked tables. It runs quite slow however. At the moment I don't have time to convert it to unbound forms, so have read that one solution to speeding it up is to create a table in the back end tables to the main DB. Then use the open recordset event to keep the link between the two open.
I know how to link the two, but can someone explain the open recordset part please. What do have to do?
I'm fine using the dcount function to count records in a table or query. However, I've got a problem which I can't get my head round. I've got a relatively large DB of up to 80,000 records. When someone does a search and haven't narrowed down the search criteria enough it takes a while to search through the records. I want a piece of code to search for records until the number of records found is 50. At that point it will pause seaching and tell the user '50 records found - carry on searching?'. If they select 'yes', it will continue for another 50 records, then prompt with this message again. This should continue until the user either select 'NO' and ends the search or the search finds all the records.
I think I may have to use a 'for loop' with a counter going up 1 every time a record is found. But I don't really know how to increment a search by one record each time there's a match. I also don't know how to pause the search when it gets to 50, 100, 150 etc.
I want to create a combobox from 3 tables. The tables are like this:
Table / Field
TblSource / Source
TblAdverts / AdRef
Tbljobcentre / JobCentre
I now want a combo box listing everything from these fields in the 3 tables. Can this be done through VB or will I have to create a series of queries to append etc (which I think tends to run slower than code)?
I also want everything in the tbladvert table to be prefixed by PR and everything in the TblJobCentre table to be prefixed with JC.
Help on the first one would be great, but even jufirst part would be a big help.
