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 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 am running in to a brick wall with this. I have an unbound text box with the control source set to =IIF([text42]=0,0,[text42]/[text44])*100 and in continues to return a #name? error.
I am not sure how to get this expression to work. I have even tried to put =[text42]/[text44] and I still get the #name? error.
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.
Hi all, not been here for a year or two, hence i have forgotton a lot of Access stuff.
I have an unbound Data Entry form which when populated, adds the record to the table. Simple enough.
I have some code on the form which basically stops the user entering a new record without having a First Name and a Last Name. This code works well.
I also have some code which confirms that the user wishes to add the record to the table. If Yes, the record is added to the table but when entering data for the next record, the first code doesn't kick in about having a First Name and last name, however the Confirm Add record code kicks in.
How can I solve this, I have tried the me.refresh command in various places but as yet to no avail. Hopefully someone will point me in the right direction.
I have added the DB in both A2k and Access 2003 Versions
I have a form with a subform in it. The main form has a combobox, called vendor. The subform shows all software.
When i select a vendor, i want only the software for that vendor to show in the subform. What I am trying to do is set up the link child fields and link master fields so that this works. However, i am getting an error saying, 'Can't build a link between unbound forms'.
I have checked to make sure that relationships exist between the vendors and Software tables but the problems persist.
My current database (built with a lot of help and sample code form this site..thankyou to all for their advice) tracks the escalations we receive into the business but as we have used it more and more I have started experiencing a massive performance issue.
I basically have a main table that captures the basic details, reference, product, escalation source, escalation reason etc, and these are captured from combo box selections (so only capturing the id numebr of the row in the combo box)
My main problem seems to be a table I have that we have linked, via the unique escalationID, that we cna add notes for each escalation.
So for instance a particular escalations can 1 or 50 notes (or more) depending on the work involved to resolve.
This table now has thousands of entries and is about 10Mb on it;s own. It seems that when I open my bound forms the query is having to scan not only the main table, but also all of the notes in the journal table.
My question is,
Would I be better to - 1 - Use unbound forms and pull the data via a recordset call or 2 - could I use a query that captures everything for only the open escalations and then have my main form query that query or 3 - have the form load but do not link the journal table and have the form pull the data in seperately ?
Hope that makes sense and appreciate any help on this one.
I have a form that's currently bound that I would love to make unbound. However, this form has about 400-500 data controls (ckboxes, cboboxes, txtboxes, etc). Is there an easier way to fill the data than
with rcd data1 = !data1 data2 = !data2 .... data500 = !data500 end with
?????????
this seems extremely tedious, but I'll do it if there's really no other way. Too bad people are too stupid for bound forms
From another forum, I found a way to kind of sort of use an unbound continuous form.
Create the form with no recordsource. But create the textboxes with a recordsource of the field name where the data is going to come from.
in Form_Load you open a recordset, then set me.form.recordset = rs.
The form is not actually bound to the table or query, but to a copy of the records in the recordset. It is read-only.
To get around it being read-only I want the user to click on the field they want to change. This opens up a editable text box where they can change the text. I then do an update sql to update the source table.
But the recordset still shows the old value. So I have tried to do a me.form.requery. The first time it appears to work. The form has the updated value, but more often than not, all the records after the first seem to be dead, the click event does nothing.
If I had edited the first record, more often than not, all the fields in all the records will say "#Name?"
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 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 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.
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?
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.
I have written a call log system in Access 97 for a busy customer help desk. Now I never bind any of my forms as I like to use VBA code to verify the entry first before writing it to the table. However, every now and then that million to one shot occasion occurs where 2 help desk operators click "close call" on the form simultaneously and therefore one of them gets a debug error (ie the system is busy).
So to prevent this error I have included a "on error" statement within my code which utilises the "RESUME" command to go back to the top of my procedure (in effect trying again). Now I have coded this on error trap to present the user with a message box along the lines of "system busy click OK to retry" as I was unsure about just leaving the code to continually loop until succesful? Is my uncaertainty justified or can I just leave the code to keep looping until it carries out a successful write to the table? This would be preferable as really I dont want the user to even be aware as if they are presented with this message too many times they might start getting worried!!!
My final question is should I send the code back to a point preceding the "On Error" statement at the top or does the system still remember this and so I can send it to a point after this? This is only a issue as I can envisage a time when by the time the system has re-tried another user is also writing a record and so the "on error" (or system busy) may be triggered again.
