Form improvement Results

I have a form with all the stock items on it. On this form I currently have a command button which brings up the Find Record box.

This is ok for finding records, but is a pain that once the record has been found you then have to close the find box, before being able to see the record on the form completely.

Ideally, I want a box where I can enter the stock items code, and the other fields appear automaticallly

Sure it is possible, but can't get my head around it at the moment!

Hi everyone,
I have an interesting problem at hand.

Assume that I have a normalized table with the following structure:
Amt order to cater to spreadsheet users and also to allow data to be seen at a glance while being entered(as opposed to opening a form), how would I go about designing the form etc?

I have done something similar but it is using a denormalized structure and feel like further improving my design. How the underlying table looks like:

Amount is entered under the month. I have understood and realised some drawbacks by using the aforementioned structure.

But it seems like for the normalised structure, it is rather hard to make a similar looking form. And yes, I have read up on normalisation and searched on the forum. What I'm looking for is perhaps a solution to the form problem?
I decided to embark on the normalised structure because there will be occasions where complex functions are needed to look up for a particular sum of an acct on a particular month. A simple query will solve the problem if I used a normalised table.


have the application and I am just working on some improvements for the front end. I have about 30 mail merge letters (and 2 merge envelopes) set up for use with the records in this database.

I am working on setting up a menu with a button for each of the letters ( 2 menus, each with about 15 buttons - 1 for each letter)

Here are the things that I am looking at:

1) I tried some code, but it is only opening Word, not the file.
Here is the code:

	Private Sub cmdFirstLnIntLtr_Click()
On Error GoTo Err_cmdFirstLnIntLtr_Click
  Dim wd As New Word.Application
  Dim doc As Word.Document
  Set doc = wd.Documents.Open("G:HR Candidate HiringStaff Merge Ltrs/First Line Intvw Ltr.doc" = strDoc,

  wd.Visible = True
  Exit Sub
    Resume Exit_cmdFirstLnIntLtr_Click
End Sub

2) I tried a hyperlink on a button ( or doesn't really need a button) but that brings up the web toolbar floating in the Word document, and I don't want it to do that.

3) When any of the merge letters are opened, I am presented with a message:
"Opening this document will run the following SQL command:
Select * From 'qrymailmerges'
data from your database will be placed in the document. Do you want to continue?

I would like this to be answered as yes, without asking the user.

4) most people I know (including me) do not start Word with the task pane up, but for these merges, I would want the task pane to show on open, and set to the mail merge task pane, as the document is saved at the right point of the merge. This allows the user to edit the recipient list, and complete the merge.

If anyone has any suggestions, I would appreciate any help that I can get. Thanks!

Thanks to Mile-O-Phile, I have this great little form that will run a query that loops thru my table and gets every email address to paste in the BCC field of my email client (Outlook Express).

So far So good!

But I need the query to run based on parameters I chose on a form from unbound combo boxes. ie. send email (yes/no), State (list of states), etc.

I can get the query to open in datasheet view using the form as the basis for any combination of parameters, . .

. . . BUT when I go to run the code to open the email client and paste the email addresses in the BCC field, using the form as the basis for the parameters, the code crashes. As long as i open the query in design veiw and manually type in the criteria it works, but if a set the parameters for the query from the form, NO GO.

I have posted a sample of what works (and does not work) so far. Please anyone, have a go at it and post improvements back.

Can parameters be set thur a form in a loop query?

Thanks everyone the help. This forum has been a "God-Send"


Here is a few methods of improving the time it takes to load a form onto the screen.


This example shows you how to number your rows in a query, and thus be able to do the same in a form based on the query. Queries can be numbered using this same technique when combined with other techniques as well. Such as:

*Calling VBA functions within queries

This is a simple procedure, and the code is basically as follows:


Hi all,

I am getting the following

Technical Information (for support personnel)Error Type:
Provider (0x80004005)
Unspecified error
/add_to_guestbook.asp, line 60
Browser Type:
Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR
3.5.30729; InfoPath.1; .NET4.0C; .NET4.0E)
POST 0 bytes to /add_to_guestbook.asp
POST Data:
29 October 2009, 17:42:13 
More information:
Microsoft Support

and the script never places the info into the database.

the add_to_database.asp is below:

Dim adoCon    'Holds the Database Connection Object
Dim rsAddComments  'Holds the recordset for the new record to be added to the database
Dim strSQL   'Holds the SQL query for the database
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("guestbook.mdb")
'Set an active connection to the Connection object using DSN connection
'adoCon.Open "DSN=guestbook"
'Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT tblComments.Name, tblComments.Speed, tblComments.Patience, tblComments.Clear, tblComments.Helpfulness,
tblComments.Accuracy, tblComments.Persistance, tblComments.Time, tblComments.Met, tblComments.Servicehours,
tblComments.Person, tblComments.Knowledge, tblComments.Improve, tblComments.Comments, FROM tblComments;"
'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3
'Open the tblComments table using the SQL query held in the strSQL varaiable
rsAddComments.Open strSQL, adoCon
'Tell the recordset we are adding a new record to it
'Add a new record to the recordset
rsAddComments.Fields("Name") = Request.Form("name")
rsAddComments.Fields("Speed") = Request.Form("speed")
rsAddComments.Fields("Patience") = Request.Form("patience")
rsAddComments.Fields("Clear") = Request.Form("clear")
rsAddComments.Fields("Helpfulness") = Request.Form("helpfulness")
rsAddComments.Fields("Accuracy") = Request.Form("accuracy")
rsAddComments.Fields("Persistance") = Request.Form("persistance")
rsAddComments.Fields("Time") = Request.Form("time")
rsAddComments.Fields("Met") = Request.Form("met")
rsAddComments.Fields("Servicehours") = Request.Form("servicehours")
rsAddComments.Fields("Person") = Request.Form("person")
rsAddComments.Fields("Knowledge") = Request.Form("knowledge")
rsAddComments.Fields("Improve") = Request.Form("improve")
rsAddComments.Fields("Comments") = Request.Form("comments")
rsAddComments.Fields("how") = Request.Form("how")
'Write the updated recordset to the database
'Reset server objects
Set rsAddComments = Nothing
Set adoCon = Nothing
'Redirect to the guestbook.asp page
Response.Redirect "success.html"

line 60 is:
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("guestbook.mdb")

which I know works as i did have it working.

Any ideas anybody?


I have made further improvements to my Calendar Form, it now works on a tab control, I think it works just about everywhere you would want a Calendar Form to work, however if there's something I've missed please let me know.

call from a command button
click or double click on:
text box
combo box

I know it works in Access 2003, I assume it works in most other versions but I would like confirmation if you happen to try it in those versions.

Copy the class "clsGetActiveFrm" and the form "frmCalendar" Into your project. You will also need to make sure your project has a reference to.....

Microsoft visual basic for applications extensibility 5.3

Videos: (Look at Video number 3 first)


Text: (brief explanation of most recent changes)

To view the above links you will need to join my MS Access Hints and Tips Club .... I understand that some people quite rightly do not like joining this that and the other club, group etc, therefore for whatever reason if you want me to e-mail you a copy then just drop me a note and I will send you an e-mail version.

Cheers Tony ...


As you can see in my database attached I have A LOT of fields in my form F_All_Fields and it looks a little messy.

Would you have some suggestions in term of form layout to make it look better?

Many thanks, Attached Files (222.2 KB, 1 views) Reply With Quote 11-02-2011, 10:18 PM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,127 You might consider a Tab control.

Hi, everyone!

I'm newby here, so sorry if I ask something too simple for you...

Here goes my question:

One of the forms in the system I'm in charge of improving, has a list of records coming from a report (SubQryInfoUtilProximasPartidas). Once you double click in one of the records in the list, it opens up a second form with the detailed information of the record.

While I was correcting the form, somehow it lost the link with the record being double clicked, though it does open the detailed view of the record... but it focuses in a new one.

I checked the method and:
- The invocation is the same.
- The Report is unchanged...
- No visible change was made, except for graphical desplay.

Somebody can help?

Thanks in advance.

I was requested to improve a timesheet form that currently resides in Excel. The main goal is ease of use. I had the brilliant idea of moving it ot Access because then I could combine it with other forms that are used and make it a one-stop shop. I then had another brilliant idea of making a form with a calendar on it.

The attached is the result, so far.

The problem I am having is that in order to make the form appear as a calendar, I had to include 42 days (6 weeks with 7 days per week). Each day displays the day, the Hours Absent and the Reason Code for the absence. This gives me 146 controls in the calendar, 84 of which use DLookup.

It is not unbearably slow, but it is noticeably slow, and some users may get frustrated.

Is there any way I can avoid the Dlookups? Some magic SQL statement or VBA solution?

I tried this sub to clear the controls and assign values, but it is slower than the DLookup method.

Code: Sub UpdateExemptTimesheet() Dim Ctrl As Control Dim db As Database Dim rs As DAO.Recordset 'Clear controls For Each Ctrl In Forms!frmExemptTimesheetRequest.Controls If Ctrl.Name Like "txtDay*" Then Forms!frmExemptTimesheetRequest.Controls("txtHoursDay" & Mid(Ctrl.Name, 7, Len(Ctrl.Name) - 6)).ControlSource = "" Forms!frmExemptTimesheetRequest.Controls("txtReasonDay" & Mid(Ctrl.Name, 7, Len(Ctrl.Name) - 6)).ControlSource = "" End If Next Ctrl Set db = CurrentDb() Set rs = db.OpenRecordset("SELECT tblExemptTimeSheetAbsences.[Time ID], tblExemptTimeSheetAbsences.[Request ID], tblExemptTimeSheetAbsences.[Date of Absence], tblExemptTimeSheetAbsences.[Hours Absent], tblExemptTimeSheetAbsences.[Reason Code] FROM tblExemptTimeSheetAbsences WHERE (((tblExemptTimeSheetAbsences.[Request ID])=" & [Forms]![frmExemptTimesheetRequest]![Request ID] & "));", dbOpenDynaset) For Each Ctrl In Forms!frmExemptTimesheetRequest.Controls If Ctrl.Name Like "txtDay*" Then rs.FindFirst ("[Date of Absence] = #" & Ctrl.Value & "#") If Not rs.NoMatch Then Forms!frmExemptTimesheetRequest.Controls("txtHoursDay" & Mid(Ctrl.Name, 7, Len(Ctrl.Name) - 6)).ControlSource = "=" & rs.Fields("[Hours Absent]").Value Forms!frmExemptTimesheetRequest.Controls("txtReasonDay" & Mid(Ctrl.Name, 7, Len(Ctrl.Name) - 6)).ControlSource = "=""" & rs.Fields("[Reason Code]").Value & """" End If End If Next Ctrl rs.Close Set rs = Nothing End Sub

Cross Post: Attached Files Finance (77.4 KB, 22 views) Last edited by Whizbang; 10-18-2011 at 02:11 PM. Reply With Quote 10-18-2011, 12:44 PM #2 Rawb Expert Windows XP Access 2000 Join Date Dec 2009 Location Somewhere Posts 678 The easiest solution would be to go snag a copy of the ELookup function and use it instead of DLookup. I haven't used it in a while (I try and stay away from DLookup, DCount, etc. on general principles) but from what I remember, my Forms loaded 2-3 times faster after switching from DLookup to ELookup.

The other option is to load Recordsets in VBA and do the lookup manually. This would drastically increase performance because you'd only be opening a Recordset a handful of times instead of every time ELookup/DLookup is called.

Im looking for some guidance on how the way I have structured a forms 'status' update can be streamlined to make it execute much faster.

OK here's the grit: I have a homescreen which represents the top down view of a real estate project consisting of >700 plots. Each plot is represented by a label so that I can change the back colour depending on the status of the sale of the land.

On the forms load I call a 'StatusUpdate' procedure which is looping through all controls on the form. If I find a label that Im using to represent a plot (I know because the is in a _ format i.e A_1) then I pass the control to procedures which parse the name and return get the block and plot numbers back.

These values are then used to setup the onClick Event and onMouseMove for each button. I then also pass these to another function which looks up the statusID value for that plot within the plots table. This function then hits up the StatusLookupTable to get the RGB values that I have assigned against each statusID, and returns a byte array with the RGB value. This RGB lookup is happening for every plot.

My loop then sets the back colour of the label with this colour, and moves onto the next control.

At the moment this works, but it takes about 15 seconds to load up this form. I want to improve the loading time, and thought about creating a global 2d array of the each status and its RGB value which I populate on startup of my application, meaning I dont have to keep hitting up another two tables for each control found on my form every time the map form is refreshed.

I've not yet implemented updating the form when the status of a plot is changed by a user either. Once rolled out the database will have around 5 users on the network, and I will somehow need to reflect the changes from one user to the other users through the 'map' form.

If the map is taking this long to load, then I cant really achieve the status update this using a timer calling the StatusUpdate function as the system is unusable while the refresh is running. Ideally Id want to use conditional formatting on the labels and bind these to their corresponding records in the Plots table, then use sort of subscription to refresh the form when the table is changed. However, my understanding at the moment is as follows:

Labels cannot be boundLabels cannot use conditional formattingThe jet database doesn't give the option to fire any code on table update

Any ideas on how to streamline this would be greatly appreciated. Pseudo code or code samples would also help me very much!!


Hi everybody,

I've been tasked with improving a database, which includes a search form. The code of the search form reads as follows:

Code: Private Sub Command_Go_Click() Dim strSearch As String Dim strSQL As String Dim strMatches As String Dim rst As DAO.Recordset Dim fld As DAO.Field Dim i As Integer If IsNull(Me.Text_Search.Value) Then MsgBox "Bitte geben Sie einen Suchbegriff ein!" Text_Search.SetFocus End If If Len(Nz(Me.Text_Search.Value, "")) Then strSearch = "*" & Trim(Me.Text_Search.Value) & "*" strSQL = "SELECT tblSitzung.sitzung_id " & _ ", tblSitzung.sitzung_datum " & _ ", tblSitzung.sitzung_art " & _ ", tblSitzung.sitzung_teilnehmer " & _ ", tblThemen.themen_id " & _ ", tblThemen.themen_fragestellung " & _ ", tblThemen.themen_input " & _ ", tblThemen.themen_ergebnis " & _ "FROM tblSitzung INNER JOIN tblThemen ON tblSitzung.sitzung_id=tblThemen.sitzung_id_f;" Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot) With rst Do Until .EOF For Each fld In .Fields If Nz(fld, "") Like strSearch Then If Not InStr(strMatches, rst!sitzung_id) Then If Len(strMatches) > 0 Then strMatches = strMatches & ", " strMatches = strMatches & !sitzung_id End If End If Next fld .MoveNext Loop .Close End With Set rst = Nothing If Len(strMatches) Then DoCmd.OpenForm "frmSitzung", , , "sitzung_id IN ( " & strMatches & " )" Forms!frmSitzung.[Unterformular].Form.Filter = "[themen_fragestellung] LIKE '*" & Me.Text_Search.Value & "*' OR [themen_input] LIKE '*" & Me.Text_Search.Value & "*' OR [themen_ergebnis] LIKE '*" & Me.Text_Search.Value & "*'" Forms("frmSitzung").Sought = Me.Text_Search.Value Forms!frmSitzung.AllowEdits = False Forms!frmSitzung!Unterformular.Form.AllowEdits = False Else MsgBox "Keine Ergebnisse für den Suchbegriff: " & Me.Text_Search.Value, vbInformation, "Suche abgeschlossen" Text_Search.SetFocus End If End If End Sub I’d now like the search keywords that were found in the database to be highlighted in the results form. Could anybody help me out with the code for that? Your assistance would be greatly appreciated.


I'm building out a form that will eventually contain 150 or so calculations against a query with 2M+ records. The calculations are primarily Average IIfs and Count IIfs with multiple criteria. My problem is that I'm not even 1/4 finished with building out the expressions and already the sheet takes a really long time to finish calculating (5-10 minutes). My main issue is that it's hard to confirm that the expressions actually work. I've commented out a lot of them so they're not actually calculating but that is a bit of a pain.

I'm wondering if there is a different/better way to get to my desired end state of a summary table that contains all the desired calculations. This is my first time using Access and I had to start because Excel could not accomodate the number of records I have so I figure there could very well be a better way. A couple sample expressions are listed below. The summary table has 13 different metrics as the rows and 12 different categories as the columns.

Any suggestions are greatly appreciated, or even just confirmation that this is the only way to do it would be great too. If you see something I can be doing better with my sytax, improvements would be much appreciated as well.


=Count(IIf([CAT] In ("F","G","H","I","J","K") And [TYP]="AF",1))/Count(IIf([TYP]="AF",1))
=Count(IIf([CAT] In ("F","G","H","I","J","K") And [TYP] In ("AF","DE","SR","KR","OP","RT"),1))/Count(IIf([TYP] In ("AF","DE","SR","KR","OP","RT"),1))
=Count(IIf([SCORE]="R" And [TYP] In ("AF","DE","SR","KR","OP","RT"),1))

I've only tested out the Average IIfs, I have not actually built them out yet


I really need some help in a problem im facing right now which has made me frustrated.

Please take a look at the attached pic which shows my latest form design.

Right now i have unwanted duplicate fields in my form which is the borrower's name. In my case, for each borrower that the user want to add (under the same title of borrower and SerialID), each borrower name will have his own country,address and up to 5 contact parties with each contact party having his own tel and fax no. The reason why there is 5 fields of name in my form is because at the time i actually thought of putting 5 borrowername fields and the relating fields in the form which is a bad idea. Its sloppy work and i read about normalisation so i must avoid duplicate data . Also it would waste alot of space if one case only has one borrower under it at first and many fields in the form would be empty.

Heres how i want the improved form to work:
for the new form, the design will be the same just that there will be one field of borrower name. If there are 3 borrowers under a title( or a case), i want to add the first borrower details in the respective field. Then,by pressing a button(or having a function, the form will clear the contents in the borrowername,contactparty,faxno and tel no and then let the user type the second borrower info while the rest of the fields remain the same and so and so forth. I just want the serial ID to link to the second borrower and beyond so that i can perform queries using this link.

So how do i do it?using subforms? or some other functioN? the problem i see with my idea is that how can I prevent the serialno from incrementing as it is a autonumber and i want to make sure that the borrowers i add fall under the same serialID.

Also, if I only add 3 borrowers at first, but later i need to add 2 borrowers to the same serialID is it possible?

If you have a better method than this, do tell me Attached Files screenshot of form.doc (91.0 KB, 2 views) Reply With Quote 11-10-2011, 06:12 AM #2 Rawb Expert Windows XP Access 2000 Join Date Dec 2009 Location Somewhere Posts 678 It sounds like you'll want to use a Subform for the borrower information.

Your Subform will show a single Record at a time. If there are no associated borrower Records, it will default to a blank (new) Record, allowing you to enter your information.

Then, when you want to add another borrower, just click a button to make another new (blank) Record.

If you use this method, you can add as many new borrowers as you want, whenever you want to.

I'll try to be clear. Sometimes I'm not .
We have Cardiac Rehab patients that come 3 times a week (M-W-F). Once they are on program they stay there for 3 months. There are classes given hourly from 8am thru 6pm. There are 12 available slots per class. Right now this is being handled in Excel, but I thought I might improve things by putting it in Access.
The Excel "form" I built has fields for PatientName, MedicalNotes, DoctorName, and Notes. Because the names rarely move (a patient gets a slot and stays there) the user opens Excel, reviews the form to make sure no one cancelled-is out sick-etc, then prints the schedule. When the day is complete I have a macro that takes this information and saves it in a tablelike manner on another sheet. A dynamic pivot table references this info to give the user quick answers as to daily volume, etc.

The thing I can't get my head around is how to make this as easy in Access. My thought was I could have a PatientT, listing all of the fields mentioned above. I could have DailyF open, bound to PatientT, with additional unbound textboxes to add "today's date", cancellations or no shows, and then Save all of this in another table and print out the daily schedule.

Am I on the wrong track completely? If not can you point me in the right direction please? Your help would be appreciated.


I created a simple DB for registering improvement suggestions. Nothing fancy, just a couple of basic forms and reports. I have split the database and now have the front end forms and the back end tables. I used the wizards to create the runtime package containing both runtime 2007 and the front end forms without any far....

My question is... (after the initial install) when I make a change to the front end forms or reports, how do I deploy those changes to the employees that are using the runtime client? Does runtime have an option to look at the initial front end forms and check for changes or do I have to email all the users that a change has been made?

Thanks in advance for any help!!!


Hi, I'm new to the forum and new to Access. I'm putting together an ordering database for our helpdesk staff to use to improve the way it's done now, which is by excel spreadsheets. I've successfully migrated the excel spreadsheet list of sites and parts into new tables and now I'm building the order form. I'm having 2 main challenges now:

1. Under normal circumstances we will bill the same site that we ship to but under other circumstances we may need to bill another "head office" and ship to a different site. So, we have a "bill to" address and a "ship to" address and they seem to be linked together. When I select a site in the bill to list it mirrors the same information in the ship to list. We need them to be unlinked so we can select different sites, based on the same table. Is that possible or do I need to make a copy of the "Site List" table and call it something like "Site List2" to generate different results?

2. I'm building the order subform and I have the order subform as a seperate table linked to another table that has all our parts that we supply and prices. What we need is when selecting the part description it auto populates the partno and retailprice. I'm pretty clear on calculating the extended price based on the qty entered but when I select a part description based on the price list none of the other information carries over.

I've attached a screenshot to help better describe the challenges I'm facing, I'm sure it's an easy fix but just need some directional guidance on how to fix the issue. I've also attached a zip file of the database and form for a better eyes on look at the issue.

FA_FORM.docI've inherited an Access database in my new role at work and have been looking to make some improvements to it, but know really nothing at this point about Access. Basically, on this form (please see attached), I need to come up with a way to make the selections in some of the fields dependent on a previous selection.

Just some background, this system collects information and investigation results on failed electronic devices. So what I would like to see is: In the confirmed field, if the user selects Y, only certain options will be available in the Fail Code and Root Cause fields, and make it limit the selections available based on previous selections. I see way too many inconsistencies from users in doing this correctly with all of the selections available in each field.

First, is this possible?? If so, does anyone have any suggestions or even a tutorial I can follow to set this up? Thanks for any help!!

Hi Guys,

Would anyone know if it is possible to have the tables only of an Access 2007 table saved online, and the front end offline on a few Windows machines that have Access installed, so that the tables are always up and up-to-date and can share the database content from a few Pc's anywhere in the world that have slightly different frontend forms? Is this possible with Access? Also, will it be very slow if it is indeed doable even though the heavy stuff like reports and queries and forms are local? I was suggested by to convert the database to SQL and build web forms for it, but Im not sure if I wish to do this as I can only use Access (im not a techie) and wish to improve the database and built on it in the future easily.


Not finding an answer? Try a Google search.