I am designing a database to allow a business to comply with the new Working Time Directive in March. I want to be able to check if a driver has worked any period of the hours of 00:00 and 04:00. Users will only be keying start of shift and end of shift. How can I get Access to determine if and how many of these hours were worked between 00:00 and 04:00?
View RepliesI 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.
Cheers,
Recall.
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.
Any ideas?
Recall.
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.
Please help,
Recall
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.
Any ideas guys,
Cheers,
Recall.
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.
Any ideas,
Recall
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.
Where am I going wrong?
Recall.
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.
View 1 Replies View RelatedI'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.
Cheers,
Recall.
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.
Cheers,
Recall.
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.
Cheers,
Recall.
How do I hide the built in Access menus on a DB? i.e. the file, edit, view etc.
Recall.
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.
Cheers in advance,
Recall.
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.
Cheers in advance,
Recall.
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?
Cheers,
Recall.
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.
Crossed fingers someone knows,
Recall
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.
Cheers,
Recall.
I've got text box on a form that contains a text value. It is a bound form. I've put code behind the text box that runs on the 'Before Update' event to check if the figure already keyed is a duplicate. If it is then I want the text box to be cleared. I've got it to work if I use the me.undo code, but I only want the text box to be blanked and not the whole forms data. When I put in code txtAccNo = "" or txtAccNo = null then I get an error message telling me the form data cannot be saved using this method. In the table I've set this field up so AccNo is indexed and does allow duplicates, but then I check for duplicates actually on the form in question. It is not the primary key in the table. Any ideas what I can do just to blank this cell when a duplicate occurs?
Cheers,
Recall
I'm trying to convert minutes to time in a query and am using the following formula. It works fine for employees that have got a record of minutes, but for employees who do not have minutes on their record then #error is displayed.
ShHrsFinal: IIf([shhrs]=Null,"",TimeSerial(0,[ShHrs],0))
Any ideas,
Recall.
PS* shhrs is as sum that is as follows:
ShHrs: IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))
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.
Cheers,
Recall.
I have a main form called FrmCalls with a button on, which when pressed brings up a pop up form called FrmSurround, within which is a subform in datasheet format called FrmContacts. This has 3 fields within it. I want the system to tak a value from Frmcalls (numeric value) when the button is pressed and place it as a new record in one of the fields on the datasheet (FrmContacts).
Any ideas anyone?
Please,
Recall.
I have a database with over one hundred employees in. Attached to each employee is a relationship where they can do many activities. How can I automatically create one activity record per employee at the beginning of a day and give the date field in the many part of the relationship the current days date.
I assume you've got to create a recordset and do something along the line of - do until EOF docmd.acrecnew.
But haven't got a clue where to start.
Pease help,
Recall.
I'm trying to do a simple sum in a query to add up 3 costs to give me an overall cost. I've put the sum in as follows:
Total:sum(nz([Cost1],0)+nz([Cost2],0)+nz([Cost3],0)).
But I'm coming up with an error as follows:
"You tried to execute a query that does not include the specified expression 'AccountNum' as part of an aggregate function".
Where am I going wrong?
Cheers,
Recall.
Find attached an example.
This could be one for you Mr Baxter.
I want to automatically create interview slots. On a form I've got an interview set up and there is a one to many relationship with a table containing interview slots. I want the user to key in the start time of interviews and the end time and the duration of the interview. It should then create the records (time slots) and display them.
I assume it will be some kind of append query, but to be honest I just don't know where to start.
Please help. A cyber pint goes to the boffin that susses it.
Cheers,
Recall.
I've developed a multi user DB. When a user is keying in a record I want to be able to give them the ability to click on a button and the system automatically looks at two value from the previous record they keyed. There is a text box that contains their user ID so I can use this as a reference to look up their last record (I don't want them to take other users last records).
The fields are StartTime and EndTime. So I want the startTime and EndTime from their previous record to be dropped into the current record(without physically going backwards on screen).
Cheers,
Recall
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
Thanks,
Recall