Computer Support Forum

Solved: MS Access - How to get a field auto-update based on date entered in a form

Question: Solved: MS Access - How to get a field auto-update based on date entered in a form

Firstly, let me apologise, I'm a complete Access noob & am trying to teach myself as I've got no one to help me in person.

I'm trying to build a Time Sheet database that should record daily duties via an entry form:

This form updates an underlying table:

The date field of the "Work_Hours" table is:

However, workers get paid fortnightly and all their entitlements, allowances & overtime are calculated on these fortnightly periods. To be more specific, the workers are paid for a certain amount of overtime for each fortnight upfront & when they exceed that, then they are entitled to all excess time back in TIME OFF. So it's pretty important that each day's duties are assigned to the correct fortnight period so that their overtime & so-forth are calculated correctly.

Each fortnightly period is numbered according to year. For example, each year there are 26 fortnightly periods, so as an example today (3rd October 2013) falls in period 2013-19 (23rd September 2013 to 6th October 2013).

I've created a table called:

Which has the following Fields:
"ID" (Auto-number)
"Period_Start" (date - the first day of each new fortnight period)
"Period_Number" (number - eg: 2013-19)

Back in the "Work_Hours" table, I inserted a new (Number) FIELD called:

and set it's LOOKUP to:
Display Control: List Box
Row Source Type: Table/Query
Row Source: SELECT[tbl_PayPeriod].ID, [tbl_PayPeriod].Period_Start, [tbl_PayPeriod].Period_Number FROM tbl_PayPeriod;
Bound Column: 1
Column Count: 3
Column Heads: No
Column Widths: 0cm;0cm;5cm

This works as expected (displays the "Period_Number" field) but I have to manually select which "Period_Number" from the list.

I'd like to automate the process by having the date entered in "Date_Worked" force a behind-the-scenes auto-select of the correct "Period_Number" based on the appropriate "Period_Start".

I hope that all makes sense; sorry for the lengthy questions, but I thought it might be best to fully explain myself.

Thanks heaps in advance,

Relevance 100%
Preferred Solution: Solved: MS Access - How to get a field auto-update based on date entered in a form

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Solved: MS Access - How to get a field auto-update based on date entered in a form

9 more replies
Relevance 80.04%

I have a table with a field called "current fund balance" which is intended to be a data entry field. I have a query with multiple calculated fields. Both the table and query share a common field. I'd like to have a datasheet form to where data from the query and table are combined and have that field "current fund balance" be a data entry field. I'm having the hardest time figuring this out.

I've read a form/subform would work but I need to be able to see all the records at once. And so, datasheet with all fields would be best. Anyone have any thoughts?

Answer:Solved: Having a form field in Access to enter data based on a query with calculated

16 more replies
Relevance 78.59%

I attached my database what i would like to do is create a form when an end user will enter a corresponding user ID from that I wish to populate textbox fields with Agent Name, Supervisor, Location, Dept *all seperate text boxes of course* I have attached the database if someone could help me do this it would be great. Let me know if you need any further details to help get me through this.

Answer:update field A in form based on entry in field b

Welcome to the forum, before answering you question can I suggest some improvements to your database.
Currently you do not have any "Key", "Indexed" fields in your tables and there are no relationships set up between the tables.
Also do you really need the Archive Table?
The data looks like it has been exported in from a spreadsheet, relational databases work quite differently to spreadsheets, so to make the most of their advantages you need to correctly relate your Tables.
Your UserID Roster also has some disconnect between the Field Names and the actual data in them, particularly the Name and Type feilds.
In the Supervisor Table the Birthdate does not seem to have Translated correctly.

2 more replies
Relevance 76.85%

I have a subform with information drawn from a query. The main form will have multiple entries on each day. The query is based on the date entered in the form so that as new entries are made on the main form, all of the entries with the same date show up in the subform. Right now, the subform updates after the record is completed, but I would like for it to update as soon as I enter the date. How do I do that?

Answer:Solved: Access - Subform update as soon as criteria entered in form

In the Date Field's After Update event procedure try putting
where subfromname is the actual name of your sub form.

2 more replies
Relevance 104.55%

This seems like it would be a common task in Access but I can't find any reference to it.

I have a combo box with three choices, Kamazaoo, Grand Rapids, and Traverse City. The field name is location. The location is entered only once but there is no limit to the amount of records that can be added. When one record is finished Access defaults to the previous location and starts a new record.

I know how to do a make table query but can I do a make table query based on the fields value? For instance if the entry person is finished entering data for Kalamazoo a button could be pushed that would make a Kalamazoo table.

Building the Ultimate site list for PC support.

Answer:Make table based on form field in Access

16 more replies
Relevance 101.68%

I have a Date field in a Form. I want the Date to be appear in red when it is more than 6 months older than today's date. Where and how do I make that happen?

Answer:Access 2010 Date Field in Form Macro or ()Iff ??

10 more replies
Relevance 99.22%

Hi I wonder if anyone can help. I want the back colour in an organisation name field to change to green if they are a member company based on a "member yes no" check box this possible. I am pretty good with access generally but conditional formatting and code are not something I know much about. Any help gratefully received. PS both fields are held in the same form and table.

Thank you

More replies
Relevance 97.58%

Good afternoon all!

I am working on writing a Security Incident Report program, and I am running into a problem. I have a Date/Time field, which I am attempting to auto-update from another Date/Time field, based on the result of a Yes/No field input. Is this even possible? To be specific, the user enters the date of the report. On the next form page, it asks the user if the report date is the same as the incident date. If the user answers Yes, I would like it to enter the data from the report date field and grey out the option to enter the new date. If the report date is different from the incident date, the user would then be required to enter the date of the incident. This takes place three different times on the same form, based on the incident date, date responders cleared from the incident, and the date the incident was placed under control. I am running Access 2007. Thank you!

Answer:Creating Access 2007 Auto-populate field based on different table Yes/No data

GrahamTechnology, welcome to the Forum.
Yes it is using VBA.

3 more replies
Relevance 95.53%

How do I stop update of date field in Word? I set the date to original date and try to save as PDF but it updates on save.

Answer:can't stop date field in Word from auto update in old docs

What did you do to get the Date, Insert --> Date and Time?Or did you use a DATE FIELD:If you are using: { DATE } Then it will always display TODAY'S date.If you are using: { CREATEDATE } Then it will display the date the document was created or saved using Save As.MIKE

2 more replies
Relevance 95.12%

I have a table titled time_slips used to track the amount of time an attorney spends with a member. In the time_slips table there is a field titled reference_id which is a combo box pulling it's displayed information from another table titled references. Also in the time_slips table is a field named details which is native to this table.

My question is, can I have the details field auto-populate "no charge" when a certain reference id is selected (i.e. 300)?

Thanks for any help you can provide.

Answer:Solved: Access Auto-Populate a Field using a field from another table

15 more replies
Relevance 94.71%

Hello! I'm hoping someone out there can help me. I'm creating a basic form, it would be nice if when a record is created the day you created the record could autofill into place. Does anyone have any suggestions out there how I could do this?

Thank you,

Answer:MS Access Date Auto-Fill Form

You could set the default for the date field to Now().

2 more replies
Relevance 93.07%

Hi fellow PCReviewers

I'm having difficulties with my Word 2007 form. I have set it up to allow fields to be populated and have the sum total appear in another field. Unfortunately I am having no success in regards to the total automatically updating as data (numbers) are being populated.The F9 fails to update the total as well.

I have checked and double checked the properties and it all seems fine to me, ie, data fields all set to "calculate on exit", the total sum field is not set to "calculate on exit" and the calculation formula reads =Text1+Text2+Text3+Text4 i've even used =sum(Text1+Text2+Text3+Text4) without success.

As a footnote, I am able to open a former 2003 Word form and it does behave correctly. The form outlined above was created in W2007.

Can anyone help me please

Answer:Word 2007 form calculate field fails to udate when data entered

3 more replies
Relevance 92.66%

I know this is an advanced question. So I will be specific.A61 =TODAY()Now, based on date, I want to add 2.5 to the numerical value in cell B22. So, when the first of the month hits, I want the value in B22 to have 2.5 more added to it.I know it would be something like, WHEN A61 VALUE=1stOFTHEMONTH;THEN B22VALUE+2.5()I know what I want it to do, however not how to apply it. If anyone can help me out, please, and thanks for even entertaining me. Just to give more information, this is a military tracker for leaves and passes. I want it automated.

Answer:Dating formula auto cell update based on the date

Possibly a macro would even be helpful. Say, for instance, the value in B22 is 30. We all now that the next value would be 32.5. I just wan to know how to do it so I'm not number plugging for 45-60 people every month.

4 more replies
Relevance 92.25%


Trying to write some code that checks the DateDiff between 2 fields once the 2nd date is entered. This is the code as it stands...

Dim Days As Integer
Dim msg As String
Days = DateDiff("d", [Agent Notified], [Collected])
msg = "Date Difference is " & Days
MsgBox (msg)

I tried placing the code behind the "On Change" event but it tries to run the code after each digit of the date is entered so I placed it behind the "Before Update" event. This seems to work better apart from if a date is entered in error which is then deleted. I then get a "Invalid Use Of Null" message.

Question 1: Is the "Before Update" event the place to have this code?

Question 2: What is the "Ignore if null" code?


Answer:Solved: Access 2003 - DateDiff after date is entered.

6 more replies
Relevance 91.84%

I have created a database in Access 2007 with 4 tables, Customers, Products, Orders, and Order Details (the specific items for each order). I know you don't want to dupliate data and "calculated fields belong in reports and queries" but we change the prices frequently and I need the UnitPrice from the Product Database to "update" to the Order Details Table on the day the order is input.

I have a Order Input Form with 2 sub-forms and my relationships are set one-to-many on primary keys. All other queries and forms work great. I have tried looking at the Northwinds database to determine how they do an "AfterUpdate" event procedure but I haven't used VBA since Access 97 and am not able to convert it for use with my form.

Help is appreciated!


Answer:Access 2007 Update Table Field from Form

Cosla, have a look at the database that I posted here
at post #5.

3 more replies
Relevance 91.84%

I have a control in a form that calculates the Gross Monthly Income based on amount entered in Annual Income (=IIf([AnnIncome]>0,[AnnIncome]/12,0)). Can the results of this expression be used to update the Gross Monthly Income field in the underlying table?


Answer:Access-Update field in table from expression in form?

10 more replies
Relevance 89.38%

Hi all,

I need some more help with a form in Access. I used the wizard and created a form with all fields I thought I wanted. Now I want to add another field (item from the field list) from a table in my database. I cannot find a way to update or change the field list of my form once I have created it.

It is the same form I had help with last time (an overtime tracker). I select an employee from the list who has his salary info saved in the employee table. When I select the employee in my form, I want to display his salary in another text box so I can use it for calculations.

On a similar note. I updated the table that my form is based off of and renamed a column. Whenever I open the field list in my form, the same column heading remains even though it exists nowhere else in the database. How do I update the field list?

Any help would be appreciated. If you need more info please let me know, thanks.

Answer:Solved: Adding field to Access Form

10 more replies
Relevance 89.38%

Hello All,

I am fairly new to Access and can really use some help here. I have a form with a field "PO Number" that needs to be a required field before my user can use the print button.

All the other ideas I have seen seem to be very complicated to me as I do not do VBA and I am using a button with a macro that saves the form and prints the report. The form is a Sales Ticket.

All I want is to require the field "PO Number" to be required before it will save or print the report from the form.

Thanks in advance for any and all assistance.


Answer:Solved: Required Field in Access Form

16 more replies
Relevance 88.97%

Access 2007 Update Query For Updating A Date Field

Hi I have been asked to write a query for an Access 2007 db

I need to update a date field in a table based on another date field in the same table

1st date field is the person's start date (StartDate)
2nd date field is the person's project date (ProjectDate)
Without going into too much detail, the Project Start date always begins on the 1st of the month

If the person's StartDate is between the 1st - 14th the project start date is counted as on the first day of the StartDate month/year

If the person's StartDate is between the 15th - end of month the project start date is counted on the 1st day of the NEXT month

StartDate = 7/1/2011
ProjectDate = 7/1/2011

StartDate = 7/14/2011
ProjectDate = 7/1/2011

StartDate = 7/15/2011
ProjectDate = 8/1/2011

StartDate = 7/30/2011
ProjectDate = 8/1/2011

I would like to update the ProjectDate in an update query

Any suggestions? Thanks

Answer:Access 2007 Update Query For Updating A Date Field

Create a Column heading like this
numdays: format([StartDate], "dd")
numdays: datepart("dd", [StartDate])
this will give you the day of the month on it's own
add a second Column
NewProjdate: IIf([numdays]>14,Format([StartDate],"mm")+1 & "/01/" & Format([StartDate],"yyyy"),Format([StartDate],"mm") & "/01/" & Format([StartDate],"yyyy"))

This will give you the date to use in the update query.

3 more replies
Relevance 88.15%

I have a table that has service information which is categorised as ServiceType, ServiceDescription, ServiceCode, etc. The are up to 200 unique ServiceDescription/ServiceCode enrties per ServiceType.

I am trying to create table for orders that will only allow ServiceType from a listbox. I then need service description to be listed as a listbox based on the selection made from service type. I then need the ServiceCode to show based on previous 2 selections.


ServiceType ServiceDescription ServiceCode
Cleaning Clean Dwelling 001
Skips Empty Skip 002
Skips Replace Skip 003
Skips Remove Skip 004
Scaffold Erect New 005

So if I select Scaffold from drop down, it only shows Erect New in drop down for description and once that is selected, 005 is autofilled for Code

Answer:Solved: Access - Lookup Based On Previous Field Result?

This is fairly easy to achieve. The easiest is with the queries supplying your 2nd & 3rd Combos.
Asuming that your ServiceDescription Table contains the ServiceType ID
and the ServiceCode Table contains the ServiceDescription ID.
Create a query for your ServiceDescription including the ServiceType ID.
Create a query for your ServiceCode including the ServiceDescription ID.
In the first combo Afterupdate event procedure requery the 2nd Combo.
In the 2nd combo Afterupdate event procedure requery the 3rd Combo.
The key to make it work is to enter in to the 2nd combo's query's Criteria row for the
ServiceType ID - forms![fromname]![combo().column(1)]
where the second column of the 1st combo is the ServiceType ID.
Repeat this for the 3rd combo and the ServiceDescription ID.

2 more replies
Relevance 88.15%

I have the following code behind the After_Update event of the textbox...

On Error Resume Next

Dim strName As String

strName = StrConv(Me.Name, vbProperCase)

Me.Name = strName

The code is used to make sure that the last name is in proper case. The code works fine as long as the last name is a typical name: smith, williams, doe, etc.

Is there a way to get the proper case to work properly with names like McMillian, MacSmith, McCord, etc.?

Answer:Solved: Access Form - Name Field (Proper Case)

15 more replies
Relevance 88.15%

I've been working on a database to save subscriber & ticket information for my community non-profit theater group. After lots of trial and a ton or error, I like what I have, but determined that I need to change the way I store some of my data. This required the addition of a text field which has a list of 3 lookup values. I added this to the table in design view--no problem.

The problem is in the forms I've already generated. I went to design view for my form and put in a combo box for this new field. The wizard asks me what I want to do with the values: remember them for later or enter them into a field. I would like them entered into my new field. That's what this combo box is for. However, to enter them into a field, the wizard lets me select only from a drop down list of the fields available in that table, and it doesn't include my newly added field. I imagine that this is because the form itself was created before I added this field. I could recreate the whole form, but I spent a long time designing it, and all I want is to add the one new field. If push comes to shove, I'll start from scratch and do the whole form over again, but I can't believe there is not a way to do this.

If it's of any consequence, this form is actually a subform, but I'm attempting all the modifications to the subform on it's own, so I don't imagine that's got anything to do with this prpoblem.

If someone could please educate me I'... Read more

Answer:Solved: Access--new field in table & problem with form

8 more replies
Relevance 86.1%

from email address removed by Dreamboat

I have a database with a date field; 'Birthday'.

I want to take the month info from the date field and have access put it into another (adjacent) field. Ie;

Month of B'day (New Field)

Also I want to be able to query the birthday field from previous data base tables which do not have the month field and make a table with the month information extrapolated to a new 'Month of B'day' field.

(Month([Birthday])) does not work.

I have printed a report with the 'Month of B'day" info extrapolated to the report but it is not useful because the report does not sort by the new information.

I know these must be possible but . . . . . AAAaaagh I'm going nuts.

Answer:Solved: Extrapolate Month from an Access date field

16 more replies
Relevance 86.1%

I have a field that is date/time, formatted to display the long date (which starts with the day of the week). This has been extremely useful for some append, update and delete queries.

However, I would like to be able to do an update query based on the day of the week for each record. I know that the date/time field is stored as a number so I can't search for any string of letters. But, is there a way to take the long date format and copy what's there as a string so I can then extract the day of the week out of it? Otherwise, I guess I'm stuck with adding a separate field for day of the week, and manually inputting it. Seems like such a waste.

Answer:Solved: Access, Date/Time Field to String -- is it possible

7 more replies
Relevance 86.1%

The company I work for has asked me to work with the DB (as you may well guess in this economy I now have to learn how to work with Access DB. This DB was created by someone who had a book “Access 2000 for dummies” in 2002, I have since then converted the 2000 DB to 2007 accdb format. That being said, in a form that is used to track records-TS# is the primary key, they want a YES/NO or actual name- Completed *check box for yes and blank for no*. I created the check box as you may know that was easy enough. The problem comes in when I go to the next record the box remains in whatever state it was on the previous record and does not show up anywhere else. I am sure this is likely because there are steps that I am omitting or not aware of. Any help offered would be amazing as I continue to work on this train wreck.


Answer:Solved: Access 2007 YES/NO field with time/date

Mike, the Check Box field has to be in the Table that the Form uses as it's Record Source. The Check box has to be "Bound" to that table's field, i.e. it's "Contorl Source" of the check box in design view must be the table field.

2 more replies
Relevance 85.69%

I have 2 buttons, New Record and Duplicate. When these buttons are clicked on, I want the cursor to move to the Employee ID field after executing the command. Can anyone help please? If it is just a bit of code can you tell me exactly where to put it? Any help would be greatly appreciated!!

Answer:Solved: Move cursor to required field when form is complete, access 2007

Solved this myself. I created a macro at the end of the Buttons Embedded Macro 'On Clilck' event. Macro:

GoToControl cbotextboxname

Works like a charm.

3 more replies
Relevance 85.28%

Hi all,

I am looking for help on VB script to send automated emails based on the date column in excel sheet..

Uploading the excel sheet as well. The email should trigger on dates in column F to corresponding email addressed in column E ..

The email body should say :

Your employee "Column C" is approaching his probation period on "Col D". Kindly confirm if you want to confirm on the date of "Column D" or modify it.

Answer:Need help to send automated email based on the date field

Have a look at the attached I have written the code necessary however if you have any changes you would like made let me know.

1 more replies
Relevance 85.28%

Good day guys

My puzzler today (and has been for ages) is this:

In my Access report, I pull in "StartDate" and "EndDate" fields from the calling form and wish to display them as part of a report subtitle.

I therefore have a field called "DateDetails" in my report header whose ControlSource is:
="Records Selected between " & Forms!Reports!StartDate & " and " & Forms!Reports!EndDate
The dates actually derive from a table and the date format for both fields in that table is "General Date".

The result is that the report shows the following as an example:
Records Selected between 17/05/07 12:00:07 and 10/08/07 12:00:07

BUT what I want is the following:

Records Selected between 17/05/07 and 10/08/07

or even nicer

Records Selected between 17-May-2007 and 10-August-2007

Can this be done in such a control?


Answer:Solved: Access 2003 - Date Format in a Calculated Field

7 more replies
Relevance 85.28%

I want to add a validation rule to a date/time field in Access 2010. This worked in 2003 and I'd like to know why it won't work now.

The rule is

Is Null Or <= Date()

I have entered this through the expression builder as well as typing it to avoid typos.

The error message is:

Unknown function 'Date' in validation expression or default value on 'Artists.Date edited'.

I am doing this at work: we are running Windows 8, Server 2012, Access 2010 via virtual desktop.

Any help would be gratefully received!

Thank you

Answer:Solved: Access 2010 date field validation problem

Alison, welcome to the Forum.
I think you will find that this is an Access problem of Access 2010.
You may be able to overcome it by using Now() instead of Date().
If not I can provide you with some simple VBA code that will do the same thing that may work.
I have also seen advice to open a new 2010 blank database and import everything in to it to solve this problem.

2 more replies
Relevance 85.28%

I've searched the forum and can't find the help I need...perhaps I'm not using the right keywords.

Anyway, I want to automatically have a time stamp entered in a field named "CancelDate" if a yes/no field named "Cancel?" is checked. On the form I use an action button for that field.

I'm new with VBA, but after some research I entered this code in the AfterUpdate line in Properties:

Private Sub CancelDate_AfterUpdate()
If Me.txtCancel?.value = 1 Then Me.txtCancelDate = Now()
End If
End Sub

...and it doesn't work. It seems to hang up because of the question mark. I get a Compile Error: Expected:Then or GoTo.

Can anyone help me?

Answer:Solved: Access 2007 Record Date When Field Updated

10 more replies
Relevance 84.46%

Here is what I'm trying to do.

For lists Equipment in drop down box.
Whatever equipment that is select, the equipment type field needs to be updated from a table.

Is there a way to get a value from SQL statement?

SQL = "SELECT [Equipment Type] FROM OrderDetails Where " _
& " Equipment = '" & Me.Equipment & "'"
[Forms]![OrderDetails]![Equipment Type] = SQL

Answer:Help with access form (insert table value into form field)

Mhouser, if you are trying to "display" a value related to the Combo selection you can have thta value as an extra column in the combo and refer to it with simple VBA.
You should not populate a Field's actual value with that from the combo as that is duplication.
Can you tell me which one you are trying to do?

3 more replies
Relevance 84.46%

When items are loaned out we want them returned in 30 working days (approximately 6 weeks I'm not going to bother trying to calculate holidays) but to only come back on a week day Monday through Friday.

Field Name ReturnDate_Fld this needs to be calculated as 30 working days from the current date

or should I not worry about the extra effort and use the approximate by adding 42 to the BorrowDate_Fld (plus 42 days = 6 weeks)

Should I worry about this in a form or only set it as field in the report/query?

Answer:Solved: Access 2010 Set a date field to today plus 30 working days

16 more replies
Relevance 84.46%

As a part of my data base I want to issue receipts for a charitable income tax donation.

I want the field to increment by 1.

I have it set to show the format year in the field ie. 2011000 but what I want it to do is assign the number consecutively by the year.

So that when the year changes... 2011 to 2012 it changes the number to 2012xxxx amd increases the number by 1.

the final 4 digits should start over from 0 each January 1.

Attached in 2007 format and 2003 conversion for convenience.

Answer:Solved: Access 2007 Auto incremente a field

15 more replies
Relevance 84.46%

I think this is a simple question, but not sure.

I want a field in a form to auto populate as the user types into it. For example, as they type "J", the first name that begins with "J" comes up. As they type "Jo" the first name with "Jo" comes up, etc. That part is basic. I am using a combo box and it works fine.

The next part is the tricky part. If the name does not appear in the list, is there a way to add it as a record in the related table?

Actually I created a simple table (only one field) just for this purpose, so if there is a trick on the field control level, to add the new entry to the combo box list that would be fine.

I don't think I can use a sub form because it won't give me the "auto populate as you type" part, right?

I also found some code on the MS help menus that may do it, but I am not a VBA programmer so I am not sure but here it is:

Private Sub DoctorName_NotInList(NewData As String, Response As Integer)

Dim dbsIPutMyDatabaseNameHere As DAO.Database
Dim rstDoctors As DAO.Recordset
Dim txtDoctorName As Text

On Error GoTo ErrorHandler

txtDocotorName = MsgBox("Add " & NewData & " to the list of doctors?", _
vbQuestion + vbYesNo)

If txtDocotorName = vbYes Then

' Add doctor stored in NewData argument to the Doctors table.
Set dbsIPutMyDatabaseNameHere = CurrentDb
Set rstDoctors = dbsOrders.OpenRecordset("Doctors")
rstDoctors!D... Read more

Answer:auto populate form field

10 more replies
Relevance 83.64%

I have a Form called "EngineerSelection" that has a combo box "EngineerSelect" where a person selects an Engineer's name. The person then clicks a button next to the name. OnClick is suppose to open a form called "ProjectFileCoverSheet" and only display the forms where the combo box value at the time of the button being clicked matches the "ProjectEngineer" field on the new form.

I used the following code on the button:

Private Sub Command38_Click()
DoCmd.OpenForm ProjectFileCoverSheet, , , "[ProjectEngineer]='" & Me![EngineerSelect] & "'"
End Sub

However, when I click the button I get a Run-time error '2494': The action or method requires a Form Name argument. The form name is ProjectFileCoverSheet so I am not sure what I am doing wrong? Any help is greatly appreciated.


Answer:Solved: Access 2007- Open Form based on Combo Box Selection

guess I forgot the quotation marks around the file name:
DoCmd.OpenForm "ProjectFileCoverSheet", , , "[ProjectEngineer]='" & Me![EngineerSelect] & "'"

However, it opens the form but goes to a blank form instead of opening with the information with the matching ProjectEngineer name.

1 more replies
Relevance 83.23%


I have a number of problems with a database that I am creating, one of which is how to automatically add a date to a field in a form.

This is a database that will be used by multiple users, and one of the fields is called "Date last updated". I have imported the data and locked the fields that shouldn't be changed (thanks to a reply to a previous post on here). I thought it would be straightforward just to add =NOW() to the field on the form, but that doesn't make any difference. I have also tried setting the default value of the field in the original table to =NOW() and "binding" the field in the table to that. Doesn't work either! Does anyone know how I can do this?

I also have another question for you....see my other post!!!



Answer:Solved: Access 97 automatically add date to a form

Ed, in the Form's On Current Event Procedure enter the following code

Me.[Date last updated] = Now()

3 more replies
Relevance 82.82%

Morning Guys.

I am having a problem with Access 2007. I am not good with code, so would like to resolve this without using code if possible?

I have a form "A" that I have created. I want to be able to select a row on form "A" press a button and it will open a form "B" based on the selected record in form "A".

I have had a look at the button wizard, and it lets you have the option, but when you go through the wizard, it gives you an empty box on the left and a box on the right showing all the fields in the form "A". Nothing to relate to?

Any ideas?


More replies
Relevance 81.18%

I am trying to make a text box update to Now() whenever someone changes the data on a form using:

Private Sub Form_DataChange(ByVal Reason As Long)
Date Changed = Now()
End Sub


Private Sub Form_DataChange(ByVal Reason As Long)
[Date Changed] = Now()
End Sub

Nothing seems to work. What am I doing wrong?

Answer:Solved: Update date when form data is changed

Been struggling with this one for hours! My boss wants to know if any of the form data was changed, I don't need to know what was changed, just that something was changed by populating a date everytime a change was made. (Probably should also include who changed it by using Environ("username"))

I have been looking for code all over the web and keep seeing that I should use the "Before Update" event but when I use this it populates the text box with a date just by simply going to the form and looking at it (Not what I need) I thought that using the "Data Changed" would work but I can't get it to do anything.

My text box for the date is "Data Changed" and the text box for who changed it is "Changed By"

Does anyone know how to do this with code, macro,???
Thanks, Joe

2 more replies
Relevance 80.77%

Sub datepaste1()
' datepaste Macro
' unprotect sheet
Sheets("SCARD").Unprotect Password:="sillybilly"
nxtRow = Sheets("SCARD").Range("R" & Rows.Count).End(xlUp).Row + 1
With Sheets("SCARD").Range("R1" & ":R" & nxtRow)
Set d = .Find(Range("K6"))
If d Is Nothing Then
If MsgBox("Is the Competition Date Correct?", vbYesNo Or vbInformation, "Date") = vbYes Then
' Date is Copied and pasted into this range of merged cells
Range("K6").Copy _
' date is pasted into the next blank cell in column R
Sheets("SCARD").Range("K6").Copy _
Destination:=Sheets("SCARD").Range("R" & nxtRow)
' protect sheet
Sheets("SCARD").Protect Password:="sillybilly", DrawingObjects:=True, Contents:=True, Scenarios:=True

MsgBox "Please re-enter the Correct Date", vbCritical Or vbOKOnly, "Date"
End If
MsgBox "Date Already Exists. Please Try Again", vbCritical Or vbOKOnly, "Date"
End If
MsgBox "Date is BEFORE last Date entered. Please Try Again", vbCritical Or vbOKOnly, "Date"
End If
End With
End SubThe above Vba works very well. I have added the last message box as I need for the date NOT to be entered if it IS OLDER than the last entered date. I am missing a line of instruction and cannot seem to work it out.Any ideas, ?????

Answer:stop date being entered if older than last entered date

I believe that this version will do what I think you want. (How's that for confidence?)It's a bit shorter than your version because it basically does 2 fairly simple things:It checks the Date that was entered in K6, then either presents a MsgBox that the date is invalid or it copies the date to the 2 cells, D5 and Column R.You will also notice that the code does not Unprotect the sheet until it has been determined that the date will be copied. Besides being ineffcient to Unprotect the sheet before we know if the sheet will be changed, it is also more dangerous. Let's assume that the Unprotect instruction is placed before the instruction that checks the date. Now let's assume that the user enters an invalid date. The MsgBox will appear and the code will pause while it waits for the user to click OK. During the pause the sheet is Unprotected because the Unprotect instruction has already been executed. If the user hits Ctrl-Break while the MsgBox is on the screen, he can stop the code. The sheet will then be left Unprotected. However, if the code doesn't Unprotect the sheet until just before the Date is copied, it would be next to impossible for the user to hit Ctrl-Break during the very brief time that the sheet is Unprotected.
Sub datepaste3()
' datepaste Macro

'Determine last row with data in Column R
lastDateRow = Sheets("SCARD").Range("R" & Rows.Count).End(xlUp).Row

'Date is compared to last date in Column R
If Sheets("SCARD").Range("K6") <= Sheets("SCARD").... Read more

13 more replies
Relevance 79.54%

I've got a form in Access 2010 that I'm using to enter new orders, users, etc. Everything works fine until I try to duplicate and/or simply create a new record with the form.

The new record is created, but if it's duplicated from another record, it stores that previous record's primary key instead of adding the next available one in the series. If a new record is simply created within the form, the primary key field is blank.

How can I get the form to add the next value in the primary key series for the table?

Thanks in advance - let me know if you guys need any further information.

Answer:Solved: Access 2010: Auto-Add Primary Key for New Reocrds in Form

You need to set the Key field to a key field Indexed with no duplicates and as an Autonumber field as well.

3 more replies
Relevance 78.72%

I have a nice little database that I set up to track donations of items to our organization. The primary table tracks the donor information: name, address, etc. (main form) and I have created a related table that tracks the date of donation and items donated utilizing a sub-form. Thanks to the fine help I received here I was able to relate these tables using a “Link_ID” field and it has worked nicely. (One donor could have multiple donations.)

Now, I’d like to enhance things a little bit. When I enter donor information into the main form and then go to the sub-form for the donation information, I’d like the business_name field to autofill as I start to type. Now, I know this can be done because most commercial databases have this function. Quicken is one that comes to mind. I know it has to pull this information from somewhere, I just need to know how to get it to work!

I have been unsuccessful finding any pertinent information on an “autofill” field in some sources. (I really don’t want a drop-down list box because this could potentially get too large.)


Answer:Auto complete or Auto Fill field in Access?

Yes, well, unfortunately, in Access, the drop-down list is the only type of control that uses auto-fill, unless you want to use a text box and code (there's an example of that in the developer's handbook, but it's complicated).

No matter how big your list is, if you sort the underlying query alphabetically on company name, it will still autofill, and you can even build a not-in-list response to add new customers...

Make sure the auto-expand property of the drop-down (or combo) control is set to Yes, and you'll see that you never even have to drop down the list...

2 more replies
Relevance 78.31%

Am looking for a way to automatically delete files based on date (files that are 2 days or older) on a NT4.0 server. I'd guess there's some way to do this with a batch file, just not having very good luck figuring it out.

Answer:Auto Delete Files based on date

Well i think you are posting it in the wrong section it should be under development. I am also curious on this to, So if you find out how send me a PM.

I am also wondering if you could do this with a script? Maybe a VBS script or somthing that will just do what you stated above removed any NT4.0 File thats Older than two days.

2 more replies
Relevance 78.31%

First the background: We are gradually deploying Office 2007, but not everyone in the agency has it. I have both 2003 and 2007 installed on my system, but I do my work in 2003, and none of our databases have actually been converted to 2007 format (at least, none that I work with). There are occasionally people who use 2007 when viewing databases, but I don't know if any of the people who are working in this particular database are using 2007. The problem is reproducible in 2003 and so I've been trying to fix it here. The query behind the problem report used to work. It has been a problem for several months (it is run monthly, and every month for the past five they've had problems with it) and I've finally gotten to the root of the actual issue.

The purpose of the report is to generate invoice for outstanding loans. The DateNextPayment is a calculated field based on the payment plan for the loan. It is calculated using a function in the query named qryGetSitesDDInvoices1. This is the function that is called (I did not write this function, I'm supporting this db long after the original writer has left):
Function DateNextPayment(PaymentSchedule As String, DateFirstPayment As Date, Balance As Currency)

On Error GoTo Handle_err

Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date
Dim NextQtrDate As Date

'The code adds one month to datenextpayment at start of each month. Or yearly or weekly

... Read more

Answer:Solved: Date Parameter Failing in Query from calculated date field using DateValue Ac

16 more replies
Relevance 77.9%

Hi there

I know how to do add a criteria to a Query. For example in the QBE pane in design view in the criteria row I enter this statement: Between [Start Date] And [End Date]. When I open up the form that is bound to the query, a message box pops up and I enter the required dates and I get the required data that is between the dates that I specify. However, I'm now finding out that this method is now becoming a nuisance.

Is there a way that I can have 2 unbound controls - one for the start date and the other for the end date in a criteria form. This way I input the dates I want for a particular customer and the form with the required data shows up. For example, I have already developed a criteria form for customers. I have an unbound combo box control that has customer names and when I click on a particular customer name, all the data for that customer is displayed in a form. In the row source of the unbound combo box control there is a SELECT .... FROM ... statement. I also have a button next to the unbound combo box control that opens up the form for that particular customer. Now I want to add two more Unbound controls in the criteria form, one for Start Date and another for End Date.

This way I can select a customer from the customer combo Box, select the start start date from the start date combo box and the end date from the second combo box control and get the desired data for that particular customer between the specified dates. I have attempted to do this bu... Read more

Answer:Access - Start Date and End Date in a Criteria form

Mario: What you want is called a query by form (QBF). I don't know how to do them myself, offhand, but here's the MS Technet article:

2 more replies
Relevance 77.9%


I have used various answers on this board to set up a form in access 2007 that includes a combo box that when changed, auto populates 2 other text boxes in the form. I used the code builder to this. It works fine.

HOWEVER...the information is not being saved in the individual records. When you reopen the form, those 3 fields (the combo box and subsequent text boxes) in the past records are blank again. They all change to whatever is chosen in the NEW record.

Any idea on what I may have done incorrectly? I have been using Access for one week, so I am sure it could be one of a million things I did wrong!


Answer:Solved: Auto populated form fields in access 2007 not retaining the information

JacklynKy, welcome to the Forum.
It sounds like you have no "Control Source" for your Combo box, this means it has no field associated with it in the table to store the selection.

3 more replies
Relevance 77.9%


I hope this is an "easy" answer.

We use an Access database to track class registrations for recreation programs.

Each program has a limit on class size.
How can I set a limit in Access so that when that number is reached, it displays a message....

I'd like to be able to continue to add records if it's decided that the class size can be adjusted, I really just want the message to appear.

I have a course table which has the course name, course code, start date, end date, and cost

I have a registration table in which all the registrants info is entered and the course is selected from the course table.

Thanks for any help with this.



Answer:Access 2K-Limit records based on a field

I would do a record count and see if it is equal to or less than the class limit. It is was, then I would do a messagebox or pop up window to give a message. you could to the message box with a yes or no. yes would add it, no would do nothing. the easiest is the message box. just look up msgbox in access help or give some more information on what you are doing.

2 more replies
Relevance 77.9%

I am trying to create a database, to keep track for stores orders for the ship i am on.
I want to auto generate the stores order number (Order Number) in the format
The 0730 is fixed as is the TAK. The yy is 2 digit year and the $$$ is the sequential number.
The yy field i want to take the year from the "Order Date" field in the same form.

IS this possible?
I have Access 2007 but the final database may have to run on 2003.
The database is still in early stages so is still small can be forwarded if it helps

Many Thanks

Answer:Sequential numbering in access based on another field

11 more replies
Relevance 77.9%


I have a table1 with a lot of fields a,b,c,d,e,f and so forth.
I also have a table2 with just a and b. (because you are not allowed to fill in value a if it is not in table 2.
I have a form that i use to fill in a,b,c,d,e,f
Is it possible to make the form fill in b based on the value of a ?

Answer:Inheriting from another field in access form

The answer to your question is Yes.
However as the data has to be in table 2 then the User should not actually enter the data in the Form for table 1.
Ideally the User would "Select" the data from table 2 and the only thing stored in table 1 is the ID Key value from table 2.
The selection process would be made using a Combo drop down box.
Normally the data from Table 2 would be on display in a Main form and the data to be entered in table 1 would be entered and shown on a Sub Form, usually in continuous forms mode or Datasheet view.

1 more replies
Relevance 77.49%

I've read the previous post with the same issue, but I'm unable to understand how to use the other codes posted within my product. I would like to send an email based on a date. I will attach my document so it is easier for me to explain the requirement. Columns L37-L45 have due dates - I would like the email to be sent 60 days prior. I have posted some mock emails in R37-R45 and the email message in the EMAIL workbook tab. Any assistance would be greatly appreciated.

Thank you so much!

Answer:Auto send an email based on date in Excel

Welcome to the board.
I've had to save it as 2003 version but the code works under 2007

See attached my copy of your sheet with the code in ThisWorksheet module.

This just a simple way of doing it and you will have to edit it for your needs but maybe it can put you on the right track.

2 more replies
Relevance 77.49%

Dear Friends,

I'm in a serious need of updating the respective teams with reminders on due dates of statutory compliance requirements. I've developed an excel workbook having three sheets namely Permissions, Returns and Records. All the three sheets are having due dates. (Workbook is attached for your solution please)

I need your expert solution in sending an e- mail message as a reminder to the person concerned before 15 days of due date with an optional text of my choice.

Its not possible for me to open neither the excel sheet nor the ms outlook application on a time basis coz my profile mostly deals with Industrial Relations aspects. Hence I've to be in the field 12/7 during the shift period and on call 24/7.

Awaiting early reverts.



More replies
Relevance 77.49%

Update a time field
I have a Table with 2 fields. The 1st field is Labeled "TimeStopped". The 2nd field is Labeled "TimeDiff". I need to subtract the next record of TimeStopped field from the preceeding record TimeStopped field and put the difference in field 2 TimeDiff so I can know how long each stop was.
Any help in using Vba to do this.

Answer:Solved: Update a time field with access vba

16 more replies
Relevance 77.08%

Hi everyone,

I am trying to created a form from a query. these query is created with two tables A and B
table A have two fields that I match with table B, I need a couple of fields from table B. but then when I open the form I need to enter some of the fields on table A but I get a msg saying this recordset is not updateable..!
I could moved those fields from table B to A and then open the table but I really would like to have then if those fields change on table B. table A would be wrong.
What am I doing wrong?


Answer:How come I can update a field on a form?

describe the purpose for the A & B tables, give examples of the data you are puttin in.

2 more replies
Relevance 77.08%

Dear Guys,

I am trying to get MS Access to print labels with names on them times the number in another field (i.e., Joe Smith is the name in the "name" field and a field called "Tags" has the number of car tags he is alotted, let's say 4. I need the database to print his name on 4 labels.)
I don't write code! Is there any way to do this, even in code.
Please e-mail a response to [email protected]

Thank you.



Answer:Access: Print Label Quantity Based on Value in a Field

Make a query in which you join the table with the person's name to the table with the tags. I assume there's a common field between the two, or how could you count? Join the tables by drawing a line between the fields with your mouse in the query design window.

Add the fields you want to show up on your labels. Base the report on that query. Access will print one label for each instance of the name in the tags table, i.e. 4 labels for 4 tags.

Hope this helps.

1 more replies
Relevance 77.08%

Here's a sample

Record 1 - John Smith #23 - want 1
Record 2 -John Smith #29 - want 2
Record 3 -John Smith #27 - want 3
Record 4 -Fred Jones #24 - want 1
Record 5 -Fred Jones #25 - want 2

So what I want is for each row of data to place a count #. So for record 1 it would have 1, record 2 would be 2, records 3 would be 3, record 4 would be 1 and record 5 would be 2.

I need a count of records for each name. I do not want a subtotal. I need it to actually list out 1,2,3, 1,2.

I can do this in Crystal reports - but I really need this is Access.

More replies
Relevance 77.08%

I have an Access form and want to search on a field that contains duplicate records. At that point the user could manually scroll through the records to find the one they need.

Does anyone know how this would work?


Answer:Searching in Access form on field with duplicates

16 more replies
Relevance 77.08%

I have been put in charge of creating a database to record all of our quality audits on parts. (a simple part inspection)

Most info will be entered into the Audit form named frmAudit. However, if the parts fail inspection another form is opened from frmAudit called frmNCM.
All the info entered into the NCM form needs to be linked back to the audit form. So, my NCM table has a field for AuditID.

The problem is I can not get the NCM form to open and automatically fill in the AuditID in the NCM form from the Audit form.

My current code:

Private Sub btnNCM_Click()
On Error GoTo Err_btnNCM_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmNCM"

stLinkCriteria = "[AuditID]=" & Me.[AuditID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit Sub
MsgBox Err.Description
Resume Exit_btnNCM_Click

End Sub

Thanks for the help

Answer:MS Access Open form new record ID field

6 more replies
Relevance 77.08%


On the form that I have on my Access Database, I would like to add a field in the form so that I can do a search by ID # and hence pull up the information for the record matching this ID #.

I am somewhat familiar with Access. I would really appreciate any help that you could provide.




Answer:Creating a search field in MS Access Form

Doesn't the form wizard automatically give you the id field to type into? Are you saying you want a dropdown box instead of the regular field-type box?

3 more replies
Relevance 77.08%

I have a data entry form that logs materials purchased on a single invoice. Master Form contains the invoice number, date, vendor, and amount. The sub-form has the details. Item description, quantity, and unit price.

After all information is printed I want to print a report which is based off a query.

I want a single command button on the form that will print the report using the information entered. Either the IDNbr_FLD (autonumber index) or the InvoiceNbr_FLD

While I can get it to print the report it BUT it prints all of the invoices not restricted to the invoice I'm data entering.

Answer:Solved: Print Report From Form Using Data Entered

Do you have a query for the report?
If so add in the Criteria first row of the IDNbr_FLD column
forms![name of your form]![IDNbr_FLD]
where name of your form is the actual name of your Mainform.

2 more replies
Relevance 76.67%

Dear All,

I am creating a tracker file for Purchase Orders in which end date in column 'J' is very important.
Column 'J' has following conditional formatting
1. If cell date is in between todays date + 14 days --- YELLOW
2. cell date <= today's date ------------------------- RED
3. cell valus > today + 14 days ---------------------- GREEN

So now when the cell turn Yellow I want the excel should send the mail to the concerned person whose mail id will be mentioned in same row of column 'G'

One more requirement there should be two mails. Reminder 1 & Reminder 2.

I use officer 2010 & mail is outlook 2010 & OS is windows 7.
This file will be on share point. This file may not be opened everyday.

Please reply with the procedure as I am not a programming/ IT person... I may not understand all terms.. please try to simplify the response.
Thanks in advance for all the help.

Answer:Auto mail from Excel to Outlook based on date in cell

Hi there, welcome to the board!

You'd want a location to mark when an email was sent. I'm assuming you want a worksheet change event for this, which will basically always run when a cell on this specific worksheet is changed. There are other events you could use to fire it off, like the calculate event. You could, if you wanted to, assign this functionality to a button, but then it wouldn't be automatic.

The below code does what you ask. It goes in the worksheet module of the worksheet your data is on. To get to it, right click the sheet tab name and select 'View Code', then paste this there.

EDIT: The locations to mark when an email was sent (btw) were columns K and L, as you'll see them set as constants at the top of the code. You can change those letters to any column you want to house it in. It just puts the current system date in those cells, and that will be checked when the values in column J are checked. If nothing is there it assumes an email hasn't been generated yet and will do so. But if it has a value - any value, it will ignore that row.

Also, I assumed a 'yellow' highlighted value was your first reminder, and a 'red' highlighted value was your second reminder. It uses this as text in the subject and body of the email.
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Const ReminderOne As String = "K" 'column letter
Const ReminderTwo As S... Read more

1 more replies
Relevance 76.26%

I'm building a database for a social service organization to track their rental/utilities assistance program, in which they track pledges clients receive from local churches and other organizations. Part of the year, they also have a program called Gift of Warmth to help with utilities. If clients are eligible for GOW, then GOW matches the amount of pledges they receive from the churches. So basically, I have a form with the controls:
[GOW] which is a yes/no checkbox
[Total Amount Needed]
[Total Client Payments] (calculated field from an underlying query)
[Total Other Funding](calculated field from an underlying query)
[Total Pledges Received](calculated field from an underlying query)
[GOW Amount](the field I need to work with)
[Amount Still Needed]{=[Total Amount Needed]-([Total Client Payments]+[Total Other Funding]+[Total Pledges Received]+[GOW Amount])}

So basically, if [GOW]=no then the field [GOW amount] needs to be set to zero, but if [GOW]=yes, then [GOW Amount]=[Total Pledges Received]

I really hope someone can help me with this. Other than this problem, the database is coming along nicely, and I want to be able to start testing it in real use soon, as they are using a shared excel workbook to track this now and are have tons of problems with it. They can't really afford to hire a professional database designer to do it, so i volunteered (or was volunteered, anyway).

Thanks so much,


Answer:MS Access - Changing content of a calculated field based on value of checkbox-Help!

aaldridge said:

I'm building a database for a social service organization to track their rental/utilities assistance program, in which they track pledges clients receive from local churches and other organizations. Part of the year, they also have a program called Gift of Warmth to help with utilities. If clients are eligible for GOW, then GOW matches the amount of pledges they receive from the churches. So basically, I have a form with the controls:
[GOW] which is a yes/no checkbox
[Total Amount Needed]
[Total Client Payments] (calculated field from an underlying query)
[Total Other Funding](calculated field from an underlying query)
[Total Pledges Received](calculated field from an underlying query)
[GOW Amount](the field I need to work with)
[Amount Still Needed]{=[Total Amount Needed]-([Total Client Payments]+[Total Other Funding]+[Total Pledges Received]+[GOW Amount])}

So basically, if [GOW]=no then the field [GOW amount] needs to be set to zero, but if [GOW]=yes, then [GOW Amount]=[Total Pledges Received]

I really hope someone can help me with this. Other than this problem, the database is coming along nicely, and I want to be able to start testing it in real use soon, as they are using a shared excel workbook to track this now and are have tons of problems with it. They can't really afford to hire a professional database designer to do it, so i volunteered (or was volunteered, anyway).

Thanks so much,

AmandaClick to expan... Read more

6 more replies
Relevance 76.26%

The OLE field on a form in Access 2002 SP-2 will only show the icon (can double click to open a photo) instead of the content, even though the "Display as Icon" box is not checked in the "Insert > object" instruction, and the field property is set to "content." This is on a new computer. The old computer had Access 2002 SP-1 and there was no problem with viewing the content of a photo in the form. I can copy and paste a picture from a Word document ok, but I have to explain that to others who use the database. Any help appreciated.

More replies
Relevance 76.26%

Sorry - I forgot to tell you that I am running MS Access 2003 SP2 on an XP machine


Answer:Entering Multiple values into an Access form field

Delete this thread by using the "edit" button & add this info into your other post.
It will only confuse people if your keep 2 running.

3 more replies
Relevance 76.26%

Hi There

To make a long story short say I have a table called Table1 with only 3 currency fields called Deposit1, Deposit2, Deposit3 and TotalDeposit. I then create an input form based on Table1. On the form, In design view, I use all 4 fields. On the TotalDeposit field on the form, I do a calculated field as follows:


I then populate the 3 fields with currency - $100, $200 and $300

On the form the calculated field shows a total of $600. However, when I go back to the table, the TotalDeposit field has a zero? How come? How do I get the calculated field on the form to appear on the table??


Answer:Access problem in calculated field on form to appear in Table

Hi Mario

How exactly have you set your TotalDeposit field to be populated with the sum of the other three fields? That is - where have you typed [Deposit1]+[Deposit2]+[Deposit3]?


2 more replies
Relevance 76.26%

I'm trying to make a simple form but one of the fields needs more complex validation than it allows me to do. Basically, the user chooses a material, enters the number of units and their combined weights. Another field calculates their average weight (total weight/units). I have a table specifying the range that each material's average unit should weight. I'm trying to make access validate the calculated average weight field (or something equivalent) according to the material chosen by the range specified in the other table.

I'd rather not forgo the table with the ranges by hard coding the ranges in VBA so that users could change the ranges.

I've put a relationship for materials from each table and display the range in the form but now I can't enter in a new entry.

I need help.

Answer:Access 2007 Complex form field validation

16 more replies
Relevance 76.26%

Hi I am working on an existing database and trying to change the input method on a form to a combo dropdown box. I am trying to use the existing data in tblProjects and the data is in a column called "PM" I sort of have it working but instead of showing my "PM" data it is showing the data in the first column of the table???

Any ideas how to get this to work? It's been a few years since I messed with Access so I am a little rusty. I have included a screenshot.

Answer:Change form field entry to a combo box in Access

14 more replies
Relevance 76.26%


I am looking for some help on my code to lookup the largest incident # then add 1 to it when a new form is selected. I created a new record button on my form but would also like to have the new incident number populate when the new record comes up.

What I have is
Private Sub cmdnew_Click()
If Me.New Record Then
Me.Incident# (not really sure if I should put the field name here or not?) =Nz(DMax(Incident#, External_Theft),0)+1
End If
End Sub

Any help is greatly appreciated.

The field name is Incident# and the Table name is External_Theft

Answer:Access 2007 Increase Incident# by 1 in field on form

You may want to consider changing the name of your field since the # is used for dates in certain instances.

You could create a query to do Max(Incident#)+1 and then call that query from your forms onLoad event handler to populate the field you want with that number.

2 more replies
Relevance 75.85%


I need to update one field in the most recent record in my table; the new value for the field needs to come from prompting the user. I've been able to update the field to a hard-coded value using the following SQL in an update query. I just can't figure out how to prompt the user for the value instead of using the hard-coded value of 2. The Urgency field is of type Integer, and the Id field is of type AutoNumber. Thanx in advance for your help.

SET Urgency = 2
(SELECT MAX(Id) FROM tableA));

Answer:Solved: MS Access 2003, Update One Field in Most Recent Record

You can use a form for the user to enter the Urgency value, preferably using a Combo Box to select from a list of possible values.
You can also use an InputBox function but it is not as versatile as a Form.

2 more replies
Relevance 75.85%

Hello everyone,

I'm working on an order management database and I'm stuck on one item.

I have a form that users can fill in with purchase order data. One section of the form relates to contract review completion.

I would like to have a checkbox that the user can check when contract review is complete, but I'd also like for a completion date field to be auto-filled when the checkbox is checked.

I've tried a couple of things, both unsuccessful.

Any ideas?


Answer:MS Access - autofill date field

6 more replies
Relevance 75.44%

Hey there, long time reader first time poster!

(If you know of a great advanced tutorial on Access, please let me know. I know how to set up the basics, now I'd like to know how the program really runs)

I'm currently working on a form that allows users to search for equipment. The category of the equipment is slectable through a combo box, and the searched text is entered in a text field. The results are displayed in a list box.

What've I've tried is using a 'Find' button to enter this sql query into the RowSource of the list box:

Private Sub Equipment_Find_Click()

Me.Equipment_List.RowSource = "SELECT Equipment.Type, Equipment.Manufacturer, Equipment.Model FROM Equipment WHERE (((Equipment." & strQuote & Me.Equipment_Search_Category.Column(0) & strQuote & ")=" & strQuote & Me.Equipment_Find_Text & strQuote & "))ORDER BY Equipment.Type DESC;"

End Sub

The result i get is a pop up window with a text field. If i re-enter the text in that feild the info pops up nicely. What am I doing wrong, and how do i get rid of that popup?

Answer:Access 2003: Search criteria based on combo box and text field

12 more replies
Relevance 75.44%


Could anybody help out with MS Access 2007?
Is there any way that the prompt parameter value which is entered to generate a report is captured somehow and its value is passed to be displayed in a form's field. The report is generated from this form.

Specifically, the form has a subform (datasheet). One of the columns is Date. This subform is populated throughout several days. User needs to generate a report that includes the data from this subform every day (or every other day). User enters Date as a Parameter Value to filter data to a report. User does not remember what is the last Date for which report was created.

If a form's field will display the last Date for which report was created this would be solution.

More replies
Relevance 75.44%

Hi All!

I have a form that calculates an employees current age in a Text box (based on today's date and their date of birth). I created the form to act as an 'entry' form for new employees in an HR database.

I have a field called 'age' in a table that I would like to populate with the result of this formula. How can I do so when the 'Control Source' property of the text box on the form is holding the formula!? That's where I would normally link it back to the table!

Thanks in advance for any help!



Answer:Can an access table field populate from a calculated cell in a form?

You use simple VBA code in the Date of Birth field's After Update Event Procedure and set the Age field's Control Source to the table field to store the data.

1 more replies
Relevance 75.44%

MS Access 2003 crashes after I add a new record and try to edit any form field. I can add a new record in the underlying table and then go to the form and edit any field. Can't catch the error with a breakpoint either - it seems the new record is not being initialized properly from the form that creates it.

Other forms in the same database do not have this problem. No server or network or sql involved with this database.

(Microsoft Office Access has encountered a problem and needs to close)
AppName: msaccess.exe AppVer: 11.0.8166.0 AppStamp:46437912
ModName: msaccess.exe ModVer: 11.0.8166.0 ModStamp:46437912
fDebug: 0 Offset: 0020ba36
Tech Support Guy System Info Utility version
OS Version: Microsoft Windows XP Home Edition, Service Pack 3, 32 bit
Processor: Intel(R) Pentium(R) D CPU 2.80GHz, x86 Family 15 Model 4 Stepping 4
Processor Count: 2
RAM: 499 Mb
Graphics Card: Intel(R) 82945G Express Chipset Family, 128 Mb
Hard Drives: C: Total - 147644 MB, Free - 64485 MB; D: Total - 4963 MB, Free - 2775 MB;
Motherboard: Intel Corporation, D945GBI, AAC99325-202, AZBI52800690
Antivirus: avast! Antivirus, Updated: Yes, On-Demand Scanner: Enabled

Answer:MS Access 2003 crashes after I add a new record and try to edit any form field

9 more replies
Relevance 75.03%
Question: Update date field

Thanks in advance for your help!

I used to know this and I did a search and couldn't find the answer.

How do you update a field to a date either 6 months away or 12 months away for the current date in that field?

Thanks again!

Answer:Update date field

6 more replies
Relevance 75.03%

I want to combine a text field and a date field into one field to use as a reference number. IE 'Mil - 05/11'.

I have got a field with the standard short date format 29/05/2011 (that I have to keep) and I have created a second field with the same date but formatted to a "mm/yy". When I concatenate the Operater Ref field "Mil" to the Date Ref field "05/11" I still get the full date format IE Mil - 29/05/2011.

Answer:MS Access Concatenate a date and text field


If you are creating the second field in your table, go to design view and change the Data Type to Text then try your concatenation. I built a mini-table and queried it and this worked for me... hope this works for you!

19 more replies
Relevance 74.62%

I am trying to hide rows of my worksheet that show a date in the completed column.
The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.
Can someone assist me with creating a macro that will do this?
I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,

Answer:Solved: Macro for excel 2007 to hide rows based on any data entered in a column

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it

3 more replies
Relevance 74.62%

Hi, I would like to display a message box when a total on a subform does not match a field in the main form.

I have a form (FormA) with a subform (SubformA)

I need to compare the total on subformA (located on SubForm A footer) with a field on FormA

I am entering a number on the subform and in the On Exit Event I entered me.requery to requery the data and update the total in the footer - then I entered the code to compare the two numbers

This is the code I am using (edited):

If [FormA field] <> [SubformA total] Then
Msgbox ("Amounts Do Not Equal")
End IF

The problem I am having is the refresh time of subformA footer - the field takes longer to refresh or requery (I tried both) and the message box displays even when the numbers are equal

Thanks in advance

Answer:Access 2007 Compare subform total with field on main form

6 more replies
Relevance 74.62%

I have a form that I'm using to enter orders for inventory. I just ordered 2 phones I'd like to have listed as 2 separate records (they will be physically tagged individually, so they need to be separated in the table).

I'd like to have a Quantity field that when I type a number and save the record from the form, the number in the Quantity field adds that number of identical records (aside from the Autonumber field for the primary key).

Is this possible? If not, how should I go about it?

We have larger orders, sometimes 10-20 items, all with the same everything in the record except for the physical tag number that will be applied to each item when it comes in.

Thanks in advance.

Answer:Access 2010: Form quantity field that adds records to table

If the form is for entering the actual order details then you shouldn't have a record in the order table for each item in the quantity field.
What you could do is is to add a record for each item in the quantity field in the Inventory table using the Item's ID to identify it.

1 more replies
Relevance 74.21%

I have a linked Excel spreadsheet to show invoices received and I insert a date when they are paid. I cannot figure out the expression to use to show all records where no date has been inserted. Can anyone help please.Thankyou

More replies
Relevance 74.21%


Access 2003
Windows XP

I am creating a new table and would like to format a field to short date. Here is my code. Is there a way to format to short date from here?


Set fld = tbl.CreateField("Date", dbDate)
fld.Required = True
tbl.Fields.Append fld

Answer:Access 2003 - Format field to Short Date

try this:
fld.NumberFormat = "m/d/yyyy"

3 more replies
Relevance 74.21%

Hi guys, I'm very new to access and I'm having some issues. I have made a field for an expiry date, and I have attempted to create a calculated yes/no field that gets ticked when the expiry date is up, so basically I have a box that I want ticked for when the expiry date is before the current date (I hope that makes sense). Thanks guys,


More replies
Relevance 73.8%

I have 2 tables that are linked by a zipcode field. One one table there is a kinda count field. If the zipcode field on the first table is updated, then I want the count on the second table to increase by one.How can I go about doing this? Thanks for all the help.

Additional info left out: The zipcode field on the count table has a list of zipcodes. When a zipcode is entered on the other table, I want it to match the zipcode in the count table and update the count for that zipcode. Thanks.

Answer:Update one field in a table based on another field in another table.

Not quite sure what you are trying to do, but is linking the zip code fields a good idea? Are you tring to find the number of times that a particular zip code is entered?

2 more replies
Relevance 73.8%

Maybe it is just late in the day and the brain has quit. But I need help. I have a form that I have to base on two tables. So I wrote a query to pull the fields needed from each table, since visually trying to use sub-forms could get very messy. Now the problem is how to I save any of the data the user enters/changes in the form. In other words, I want to be able to update the appropriate table, but can't remember how to do that.

Thanks in advance for any help.

Answer:Access 2000 Form based on 2 Tables

For Access to update changes using information in both tables, there needs to be a relationship between them. Although you have created a query using fields from both tables, unless they are linked Access won't know how to treat the information

2 more replies
Relevance 73.8%

Is there an easy way to input a SS# and have the cursor move to the next field without me hitting the tab or enter key? I have looked everywhere.

More replies
Relevance 72.98%

I am experienced in Access, but am writing my first Access web application. I have a Company table (primary key is [ID]) and a related Contacts table (foreign key into Company is [CompanyID]. I'm trying to list the contacts for a company on the company form, either as a subform or a list box, but I can't make it synchronize. It always shows all contacts, not just those for the current company.

I've tried using a where clause (not allowed on the web) and TempVars, but nothing seems to work. I'm going to face this problem extensively in this app, so any help would be greatly appreciated.



Answer:Synchronizing a subform in an Access 2010 web-based form

Irish2879, welcome to the forum.
I know nothing about Web based applications.
But I may be able to offer some Problem Solving Guidance.
I assume from what you say that Master/Child links are not allowed on the web based versions of main/sub forms?
Can Access read the current record selected on the Main form?
If so you could have a query using a criteria filter to filter the subform records.
Can you manipulate the Subform using VBA code from Access?

3 more replies
Relevance 72.98%

i am in the process of creating database and i have an issue i am trying to resolve.

On my main form i have a list box where the user choses a binder number. The binder number, alond with the binder name and binder location is stored in table, binders. On my main form i also have a filed called Location. What i need is for the location field to be filled with the location of the binder that was chosen from the list box mentioned above. I know this can be done i am just having trouble figuring out how to do it. I know it involes a query but i am not sure how to set it up. I am an advanced begginner with VBA so if you reference code, i will probably be able to figure it out...

Thanks in advance!

Answer:Access Quesiton Auto Populate a field

16 more replies
Relevance 72.98%

Please help

I have 2 tables:-

1 Master Input Table.
1 Job Lookup table - (contains Job Number and Name)

When in-putting job details into the Master Input table i would like to enter the job number only and have the job name appear automatically without running a query, much the same as a 'vLookup' command in excel.

Is this possible?

Can anyone help?

Answer:Auto Fill In MS Access Table Field

Yes it is, the easiest way is to have the Job Lookup Table as a Sub form on the master form (Master Input Table) linked via the Job Number.
Or you could select it from a combo box, (no typing).

1 more replies
Relevance 72.57%


I need to create two applications based on the same table. On the First application users will add (change) records and the Second application will display all the records in tabular layout. Every time a new record is added in First application, the display on the Second application will reflect the changes. I need the Second application (form) to get updated automatically, without user intervention.
Is this possible to do?



Answer:Solved: access 2003 form (table) update

13 more replies
Relevance 72.57%

I am currently building a database to track uniform service orders. I have created tables for Customers / Employees / uniform# / uniform options (individual tables for each option)/ Service Item (IE type of repair) etc. I have linked everything to the unique uniform #.

I have a Order ID Table which has the fields "OrderID" "Customer Name" "DateIn" "DateOut" "Uniform#". I have a Order Items Table which has the fields "OrderID" "Service Item" "Service Item Quantity".

I have created a form "Work Order". The OrderID is an Autonumber from the Order ID Table. When I type in the Uniform # the workorder autofills all the information pertaining to that uniform. Ie: Customer Name, Employee Name, Colour, Size.....etc. I input DateIn and DateOut. The items I input myself ie: uniform#, datein, dateout all update my order tracking table just fine.

The problem I am having is that the autofilled items ie: Customer Name, employee name do not update the order tracking tables.

I have subforms that have dropdown boxes to select the Service item, which in turn autofills the price.
I input the quantity of the selected item. All the inputs are linked to the Order ID Table and the Order Items table. These update the Servicetable just fine.

The whole idea of the database is so the seamstresses can fill out the order form for repairs to each uniform as well as wash rate and rental rate. Then at the end of th... Read more

Answer:Solved: Access 2007 Form does not update table

16 more replies
Relevance 72.57%

Please assist, I have a Form with a Rand/Ha field (currency) and a Ha field (number), the Value field should be the (Rand/ha) / (Ha)? How do I get this value to reflect in the Value field and have it stored in the table for reporting purposes?

Answer:Solved: Add calculation field to Form

Are you talking about Access?
If so you use simple VBA to add the calculation to the field. The Field's Control Source must be the Table Field.

3 more replies
Relevance 72.57%

I would like for my Access 2007 database to automatically record the date/time when the data in a SPECIFIC field is modified (not when any data in the record changes).

Here is a simplified explanation of my scenario. This database keeps track of cases in my office. A data entry form named "Cases" feeds a table named "Cases." The form includes text boxes that correspond to the fields in the table. Three of these are: "Case_Notes," "Case_Status," and "Date_Status_Change."

I would like for the "Date_Status_Change" field to automatically reflect the current date/time whenever the data in the "Case_Status" field changes. I do not want the date/time to change when information in the "Case_Notes" field is modified.

What code would accomplish this?

The following code will enter the date/time for a change anywhere in the record:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me![Date_Status_Change].Value = Now()
End Sub

But I want to limit it so that it dates changes ONLY in the ?Case_Status? field.

More replies
Relevance 72.57%


I have been struggling with this for a couple of days.

Here's the scenario: I have 2 sets of data, one shows repeat calls for a list of customers, the other shows trouble reports for the same set of customers. I want to count how many times each of these customers called within 30 days of the original trouble report.

I have tried using a nested count Iif expression but I don't have a lot of experience with expressions and apparently am using it incorrectly. What I have tried is something like this:

=Count(IIf([Contact Date/Time],+30,1))

Any suggestions or ideas are welcome at this point. Thank you in advance!



Answer:Access 2013 - Count only if date falls within 30 days of field from different table

I would create a 'groupby' query using the DateAdd function for criteria. If this is a report you should be able to link this query to the report query by acct number or something.
SQL looks like this example
SELECT Table1.Name2, Count(Table1.ID) AS NameCount
FROM Table1
WHERE (((Table1.Date1)>DateAdd('d',-30,Date())))
GROUP BY Table1.Name2;

1 more replies