Computer Support Forum

Access XP Form Not displaying Table data

Question: Access XP Form Not displaying Table data

Hello OBP and others,

I have 3 Access XP survey forms that are not displaying the uploaded data in the tables, though they are restricting the number of records to the number of surveys in entered into the tables (as they should be doing). The "Data Entry" settings are set to NO for all forms and subforms. Do you have any suggestions as to how this can be fixed?

Thank you,
TMet

Relevance 100%
Preferred Solution: Access XP Form Not displaying Table data

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 http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Access XP Form Not displaying Table data

16 more replies
Relevance 98.4%

Hello - I am currently building an "Agent Journal" Database. This will be an area where supervisors can enter "Journal Entries" for their direct reports. The way it is currently setup is: The Supervisor form pops up. Supervisor selects their name from a dropdown, and select a command button. This opens another form with another dropdown that lists only the selected supervisors direct reports. Once the "direct report" name is selected, they will hit another command button that opens the "Journal Entry Form" On this form, there are text boxes for Supervisor and Agent. These fields are auto-populated based on the names selected on the previous 2 forms. There is also a combo box where they can select a "reason" from a dropdown menu, and a date picker so they can enter the date. The problem that i am having is: The data entered into the final form will not save to a table. I want to maintain the auto-populating of the Supervisor and Agent names when this form opens, and i want to have them select a command button to save all of the data on this form to a table. Can anyone help me with this????
 

More replies
Relevance 96.35%

Hi,

I am trying to create a data entry form for a large database. There is a parent form with 6 forms linked with a one-to-one relationship (due to a large number of fields) and several tables linked with a one-to-many. All have auto update/delete checked. The date entry form uses tab control (across 11 tabs) and subforms for some of the tables. I have two problems...

Firstly, if I enter part of the data for a new record in the form (eg fill in tabs 1-3) and then close it, the data appears in the tables, but when I re-open the form the record does not appear, so it is impossible to complete the data entry.

Secondly, when using the tab key to move through the fields in the table, when I get to the end of a tab control, instead of moving to the next tab control for the same record, it moves to a new record on the same tab.

I am fairly new to access and not familiar with all the code etc behind it, so I would be grateful for any advice!

Thanks
 

Answer:Access form not displaying all data

9 more replies
Relevance 95.94%

I have a form called GET ORDERS that requires input( i.e. Customer #, product, qty...etc) The form has a field called TOTAL PRICE. In the properties for TOTAL PRICE, on the DATA tab, I have the following calculation in the control source field
=[SalesTax]+([UnitPrice]*[Quantity])
Now that expression works fine and displays the correct total price. However, I'm looking for a way to commit that value to my ORDERS table, in the TOTAL_PRICE field.
I'm assuming that I can do this under PROPERTIES and the Event tab..... I'm looking for someone with a little more Access experience to provide some help. Thanks in advance for your time.....
 

Answer:MS Access: Enter data into a table from a form.....

Hey. Will this help?
http://support.microsoft.com/?kbid=292042

That'd be cool, 'cause I found it pretty quickly.

 

3 more replies
Relevance 95.94%

Hello,

We are currently running access 97, we have a database table created, but when we enter on the form that we have created the date and time does not show up on the table. We have on the form the date and time automatically set. Is there a way to have it transfer to the table??
 

Answer:Access 97: Transfer Data From Form to Table

Sounds like you don't have that form control linked to the table. I'm not sure how to do it off the top of my head, but you can try right-clicking the data field in form design view, and go to the Data tab and check out the control source. It's gotta be something like tablename->field. If you hit ctrl-F2, you should be able to "build" it. It may be helpful to you to look at how another control in the form is set up, then duplicate it, but change it to the date field.

(Maybe that'll give you something to do until the troops arrive.)
 

1 more replies
Relevance 95.94%

Hello

I am using Access to hold data. The table in Access runs across from right to left. I want to copy the data that runs from right to left and paste it into Excel from top to bottom ( up and down). Is there a way to do that without doing one field at a time. When I copy out of Access now and try to paste into EXCEL it of course paste's it across not up and down like I need it to

Thanks
 

Answer:Data from Access table to Excel Form

Once it's in XL, you could try re-copy ; then (in a free area), Edit -- Paste Special -- Transpose -- OK.

HTH,
Andy
 

2 more replies
Relevance 94.71%

Okay, I've got an Access 2003 database. For this particular task I have a form with 3 comboboxes and a textbox, all four unbound. The data on the comboboxes are populated (RowSource) from three separate queries, which run fine. I have a button which, when clicked, I want to take the information and add it to a table (tData). My SQL is very rusty and I can't get it working right. Here is the SQL statement...

Code:
INSERT INTO tData ( CowType, CowLocation, CowMovement, [User], [Date], CowValue )
SELECT Forms!fData!CowType AS Expr1, Forms!fData!CowLocation AS Expr2, Forms!fData!CowMovement AS Expr3, CurrentUser() AS Expr4, Date()+Time() AS Expr5, Forms!fData!CowValue AS Expr6
FROM tData;

As you can see, the form (fData) should take the controls and add the value of each to the respective field in the table (tData). It's not working though, and I'm getting the retarded "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'". Can any Access/SQL guru tell me what I'm doing wrong? Driving me crazy here.
 

Answer:Solved: Access 2003: Add data to table from form

Nevermind, I got it. Went away from the Append query I was trying and did it via VBA...
Code:
Dim rsData As ADODB.Recordset
'Create data set for entry into table
Set rsData = New ADODB.Recordset
rsData.Open "SELECT * FROM [tData];", CurrentProject.Connection, adOpenStatic, adLockOptimistic
If rsData.Supports(adAddNew) Then
With rsData
.AddNew
.Fields("CowType") = Me.CowType.Value
.Fields("CowLocation") = Me.CowLocation.Value
.Fields("CowMovement") = Me.CowMovement.Value
.Fields("User") = CurrentUser()
.Fields("Date") = Date + Time()
.Fields("CowValue") = Me.CowValue.Value
.Update
End With
End If
rsData.Close
Set rsData = Nothing

 

2 more replies
Relevance 93.89%

Hi all,
Not a huge tech head so tread lightly with me
I've recently designed and have been using a database for tracking of research participants. I have my backend and frontend separate (as per my husband's instructions )
I have 2 buttons on my switchboard. One for enter new participant. That form can enter new data and doesn't show existing data. My other button/form is "edit existing participant" - i had to make some changes to it yesterday - add new fields, move stuff around etc etc. was having probs as computer kept shutting down etc. However, all going well but end of day open it up and the 'edit participant' form no longer shows any existing participants/data. It's all still there in the backend but wont' show up on the form.

1. My data entry is set to No - so that's not the problem.
2. The data/record source is correct.... and all tables have been linked correctly between back and front ends.

Help please - this is work i've been doing casually for my old organisation and they desperately need this and I've mucked it up!!! Aaaagh.

Thank you
Carla
 

Answer:Solved: Access Form no longer reading data from table

6 more replies
Relevance 93.89%

Hello -

I have a data table that is recording inspection dates and inspection purposes for specific records. There are a total of six occurences (i.e. Inspetion Purpose (1), Inspection Date (1), Inspection Purpose (2), Inspection Date (2)).I would like to use a form to input data into the fields. Is it possible to build an expression so that the data input into the form inserts the data into the table with the first available null value for that record.

In other words, if there is a value present in Inspection Purpose (1) & Inspection Date (1) then the data from the form is inserted into the fields for Inspection Purpose (2) & Inspection Date (2).

I would greatly appreciate any help!
 

Answer:Solved: Access: Inserting Data from a Form Into Fields in a Table

10 more replies
Relevance 91.43%

Hello!

I have linked my calendar 11.0 to my text boxes, and then to my query criteria. Everything works, except after I click on the Calendar to choose my end date and it shows up on the text box thats linked to the query, the information from the query doesn't automatically show up on my Form in other text box I have linked for results (d*count funtion, etc).

BUT, when I go into design view, and then back to form view, the Data is Updated!

So I am very confused as to how to set it up after I input the end date to automatically update instead of hitting the design view and back to form view for it to update.

Not sure if a "refresh" or "after update" function needs to be in place? I hope I explained it correctly! Thank you!
Jess
 

More replies
Relevance 82.82%

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 79.54%

Ok I have created a database, split the database, and provided multiple users with the Front End copy of the data. Everyone is keying in the form and adding records to the table.

I have a little problem.

One user will key in the bulk of the information but another user will need to finish the record. I have a "Find" command button on the form for them to be able to look up a record number but the problem is:

The user that needs to finish the record cant see what the inital keyer has keyed until she goes all the way out of the database and back in.

Is there a refresh button somewhere that will allow all the users to see all that data that has been keyed in at that point? I hope thats clear enough.....

(Access 2003 )
 

Answer:Updating data within a form/table......

Figured it out . For the readers all I had to do was add "Me.Requery" to the code of the Find Record command and it updated the database before they actually would look for the records.
 

1 more replies
Relevance 78.31%

Access 2003

Standard split database with a back end and front end. The form that is being used has one field that is not transfering its data to the table. Its a calculated field. Its called Premium Difference. Its taking one field and subtracting it with another and the total is populating in the Prem. Diff field within my form but its not saving to the table.

Can someone help. Its really probably something simple ..
 

Answer:Problem transfering data from form to table

6 more replies
Relevance 77.08%

Hi folks, I have a multipage form as part of an Excel development that requires checking the completion of fields on page 1 when a user enters page 2. All very straightforward I thought having done it many times before in word macros

eg If Multipage1(0).<variablename> = "" then
msgbox(<error message>)
multipage1.value = 0
<variablename>.setfocus
Else
End if

but no. The tab of page one is activated but the form controls displayed remain stubbornly those of page 2 and vb sicks up because it can't see <variablename>. The only reference I've found to this is that it was driving people mad and 'appeared to be a bug in XL2003'(sic) I'm using Office 2010.

Has anyone else had this problem, and more importantly found a cause/solution?

cheers
 

More replies
Relevance 76.67%

I have my form set up so that once the user enters data in the last field of the main form, focus moves to the first field of the subform. When the user tabs from the last field of the subform, focus is set on the first field of the subform instead of moving back to the first record of the Main form. This is because data in the fields in the Main form will be the same for multiple records in the subform and this speeds data entry.

I solved several problems thanks to the the help of "OBP" in this forum, but discovered that I've got 1 more:

The data in my main form is only saving in the underlying table for the 1st record that is entered. Once the user tabs from the last field of the subform to the first field a new record in the subform those fields are saved in the table, but not the corresponding fields in the Main form. (Very confusing but attached visual of my form and table should help).

I have set the fields in the Main form to copy to the next record, but they are not saving to the table.

Thank you for your help
 

Answer:Solved: Ac2007 Main Form Data Not Saved in Table

8 more replies
Relevance 76.26%

There are expressions to display the time and date in a report. Is there a way to link a text box in a report so that it displays the name of a table? If not, is there any other way to display the name of a table automatically in a report.

Thanks...
 

Answer:Microsoft Access - Displaying the name of a table in a report.

Depends what you mean. If it's the underlying table or query on which the report is based, sure. Just set a text box's control source to "=[RecordSource]" (without the quotation marks, of course). Retrieving any other name, however, including names of tables that may be included in a record source, would be a little trickier.

Please let us know if this does what you're looking for.
 

2 more replies
Relevance 75.85%

Is it posible to perform calculations within a table or data entry form. I have a date of birth field and an age field. I have an Update Action Query that automatically works out the date, (See below). However if I design the form with a close form event that triggers the query I get the confirmation message. I can only supress all Action queries confirmation messages or none at all and I do not want to disable this. Therefore I would like the age to be calculated within the table, (or form if that was possible). However I do not know how to do it.

DateDiff("yyyy",[DateofBirth],Now())+Int(Format(Now(),"mmdd")<Format([DateofBirth],"mmdd"))
 

Answer:Access Table/Form calculations

neil. Tables do not allow calculation, but that is what queries and forms are for.
You can either do it in the query that you should be using to supply your form with it's data or in the Form itself using a calculated field or VBA.
It is not normal to store the "age" in a table field however as it can be calculated at any time.
If you want to store it for some reason then the best way is to use some simple VBA.
 

1 more replies
Relevance 75.85%

Hi all,

I have created a table in access and on one of the fields in Employer table, I have stored a picture in the column "IMAGE" as an OLE object. Now I have created a form in VB 6 and I have linked all other fields successfull and they do appear on the form. On the same form I want to also to display the picture relating to the same employee that is in the database in the VB 6 form. When I try linking the Image box to the recordsourde, say "adoEmployee" and then I choose the datafield as "Image", running the form displays a message "Unable to bind field or DataMember: IMAGE"....please help....

Beginner
 

More replies
Relevance 75.03%

I basically have a Main Menu form, on which I have a variety of tabs. On one of the tabs I want to have a series of combo boxes from which users can enter/select the value they require. In the After Update I need some sort of code as below, that will allow me to display the record/s matching the value provided/selected by the user.

docmd.OpenForm(frmImprovements,acNormal,,[[Forms]![fmnuMainMenu]![cboID].[AfterUpdate]=[tblImprovements]![lngID]],,acWindowNormal)
-this command is expecting an =

So a user has a combo box called ID, they may enter or select a value from a drop down list, a form is then displayed from tblImprovements in frmImprovements. What am I missing? Any ideas?
 

Answer:Filtering in Access and displaying in a form

I think you have the order of the filter wrong. Also you should not need the .AfterUpdate piece of the combo box. Try
docmd.OpenForm(frmImprovements,acNormal,,[lngID]=[[Forms]![fmnuMainMenu]![cboID]],,acWindowNormal)
 

1 more replies
Relevance 75.03%

I have a bound form that is not registering new records that I added in the corresponding table, any ideas on what the issue could be? I am still learning the in's and out's of access 2010 but have never had this issue.

Many Thanks!
 

Answer:Form in Access is not registering new records in table

tookiemouse, welcome to the forum.
I do not have Access 2007/2010 but I can ask some general questions.
Is the Form's Data Entry Property set to "Yes", as it is does not allow displaying of olod records, only the entry of new ones?
Can you enter Records in the Form?
Do they appear in the Table?
 

1 more replies
Relevance 75.03%

I have a question that deals with how best to approach an issue with segredating data inside a form. Let me explain...

I have a database that contains various information about our employees. One such database table contains education information. I have a form page dedicated to displaying this data for each employee. My delima is how to display only the data pertaining to just that particular employee when their form is opened. I took the Employees form in the Northwind database to use as a template. In the sample database form, each employee has sperate tabs that display only their data on the form. One can also add either data of a new Employee for this form. The goal is to click on JohnQ form, click on the tab for Education and have only JohnQ's education information extracted from the tblEducation table come up or add the data here. So that as the employees change so do the data. I have read about queries but will this feat be better served by using some sort of code? This is one of only two parts to a MAJOR task that I must complete to finish this project. The last task is to send a date triggered email to all the email addresses in this database via OutLook. I will leave that for yet another day.

R,
QSligh
 

Answer:Table properties on a form is Access 2003

16 more replies
Relevance 75.03%

Hello. I created a new database modeling a form in Access 2002 from another database program that I have, yet I have not added any tables yet. I have always created forms, using a wizard, from existing tables in Access, yet this time, because there was so much needed on the form, I have done it backwards and I would like to now create the tables for it.

The problem is, either it doesn't seem to be possible, or I just can't find the way to do it.

Can anyone help me???
 

Answer:Can you create a table from a form in Access 2002?

As far as I know there is no automatic way to create a Table from an Unbound Form.
It could probably be done using VBA, but the amount of Programming involved is likely to be more than just creating it manually in design view.
One way to help you create the Table manually would be to use the Access Documenter to print out the Design Specification of your Form.
I am not sure that the way that you have gone about it is the is the best way though.
By just creating a form to enter the data may lead you to put all the data in one Table when in fact it should be split up between related tables.
I can help you if you think that you need it.
 

2 more replies
Relevance 75.03%

hi
i need to get which option buttons are selected on my vb6 form to a table in my Access db . i already have an ADO connection to populate the vb form from an Acces table, what i need to do now is capture that form that is filled out in a different table. any ideas , suggestions or tips would be appreciated

Thnx
[email protected]
 

Answer:option buttons from vb form to Access table

13 more replies
Relevance 75.03%

I am relatively new to access 2007, so forgive me if this is simple.

I created a database to store Overtime hours for our employees(50 or so)

There are only two tables:
Employees(Employee #, Last Name, First Name, Active(yes/no))
Overtime Hours(Employee #, DateWorked, Hours Accepted, Hours Rejected)

The tables are linked with a one to many relationship on Employee#

I was able to import the data from the excel sheet we use, and generate the reports we need from that without a problem. However, I am stuck on the hour entry form.

What I would like is to have the form to have a date entry text box on the top, then display the list of active employees(gotten from query), and next to each employee have a place to enter the hours accepted and hours rejected. Everything I have tried so far has failed.

I tried making a multiple item form bound to the active employee query, with 3 text boxes(HoursAcc, HoursRej, DateWorked) that has sql that executes from a command button using the following code

INSERT INTO OvertimeHours (EmployeeNumber, DateWorked, HoursAccepted, HoursRejected) VALUES ('" & EmployeeNumber.Value & "', #" & Format(DateWorked.Value,"mm/dd/yyyy" ) & "# ," & HoursAcc.Value & ", " & HoursRej.Value & " )

This has three issues,
1. Filling the Hours Accepted, or Hours Rejected text box next to any employee causes the text boxes for all the employees to be filled.
2. It only ... Read more

Answer:Access New Records from Non-bound table in a Form

WolfRoolz, welcome to the Forum.
I think that you have 3 problems with your current setup.
1. You are you using Unbound Text boxes for some reason?
2. The form for Hours should be based on the Overtime Hours table.
You are using an Insert into SQL Query when you don't need to.
Ideally you would have an Employee mainform which also has the date selection/entry on it, the Employee selctable from a Combobox.
The Subform would have the Employee # as it's Master/Child link to the Mainform and the date would be transferred by some simple VBA (or you could have it entered on the subform)
 

1 more replies
Relevance 75.03%

Hi All,

I have one problem i want to show some fields in tabular in Form, is there any way to do like this, also i tabular item is from one table only. Its not from multiple table.
 

Answer:Can we make table in form in access 2007

On the Main Menu, Create section, Forms section, click Multiple Items.
 

2 more replies
Relevance 75.03%

I have a problem I made a database that has all my movie collection from different formats (DVD, Blu-Ray, HD DVD, etc...) but in my Movies Form under the text box format it shows the ID but not the actual format but on all others like TV Shows it shows the format? How can i fix that?
 

Answer:Form/Table Problem in MS Access 2010?

16 more replies
Relevance 75.03%

Hello all!

I am currently working on an Access file to help save the company I work for a lot of money and time.

I am looking to create a Form that the operators can only add a location to the table and edit a comment field. Right now I have the table with all of the fields neccessary for this set up. I need to make it so I can add more to it from the form. I have four forms currently, a main form that brings the user to the other three forms, a How To, Diagram, and Add form. The How To and Diagram form I am having no problems with, and they even link to the main form properly, as desired. As for the Add and Main form go, they are not linking to the Table I have created which is causing much difficulty with getting everything functional. Also I need to access program to be foul-proof so there are no issues with data being changed or edited without approval. Also I would like to include a Rework field that shows if the parts need to be looked at by a supervisor, the supervisor would be able to look at this by a query form.

Thank you to all who can assist me in this. I can send you what I have if you need to see it.
 

Answer:Access Problems with Linking Form to Table

7 more replies
Relevance 74.21%

Hi Guys
First timer - so please tread gently! I'm sure this is a similar situation, but wasn't sure whether it warranted another separate conversation.
I have a situation on an unbound list box in Access 2000 and haven't yet got to the bottom of it. I have a box which displays incorrect records for the user to correct. When they first go into the box, they see all 500 records (for example) and use a dble click event to edit the one they want, when they return to the box it will only display the first visible amount of records. They can use the scroll bar but the records do not scroll!

The Code behind ( and here's where I get lost - I don't do VB!) opens another form, allows you to edit the record, saves the changes, closes the form and returns you to the original form. We have tried keeping the original form open, as it was closed and reopened, but no change.

The DB has been inherited, so I have no idea why a List box has been used an not a combo.
Forms data is taken from a query.

I'll work out how to post a copy of the empty DB to you!
Jacally
 

Answer:Access 2000 - Not displaying all records on Form

6 more replies
Relevance 74.21%

Hi all,

I am a beginner to MS Access. I was trying to create a form in Access which has 3 combo boxes on it, with each of them having the data on accountname, report type and date respectively. This data comes from 3 tables which are two column tables with one filed being the primary key one and the other fiield having the say accountname as type Text and so on. The form has the functionality that as a user chooses a particular combination of accountname, report type and date from the 3 combo boxes and clicks on a button titled 'Display report' the respective pdf file opens up. These pdf files are stored in a particular folder and are named as a concatenation of 'accountname+reporttype+date'. The form also needs to have a button clicking on which the user can close the form.
Any directions members !

Also could anyone explain me what actually a query is and what relationship does it have with a form. I have some idea about this but i am still not fully clear about these concepts.

Chow
Goels
 

Answer:Creating a form in MS Access for displaying reports

i posted a similar question about 2-3 weeks ago... I hope someone has the answer...
 

2 more replies
Relevance 74.21%

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 74.21%

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 74.21%

I am certain that this is easy for someone - not me. My existing application is broken. The fellow who created it is long gone. I can supply data, written summary and screen shots of how it all used to work. There is only one table having 10 fields and 51999 records. I understand that the data is not normalized and that there is a better way to accomplish the result. I have what I inherited. I fully intend to create something better but I am in a pinch. I need to get something working now so that the data can be used. The purpose of TaskList is to create a list of tasks, set a daily schedule for the tasks, reschedule if necessary keeping the original task data, and print a daily report for any single selected day.

The user interface is a form that shows the TaskDate field. The form needs open with today's date - two buttons to the right - one increments the date by one day, the other by one week - same to the left of the date that will decrement. There is a calendar button to the right that allows date selection.

This main form has a subform (datasheet type) that shows the task data fields for the date selected - each task on one line. The main form includes separate buttons for -providing a new blank line at the end of the datasheet to add a new task -a button to delete - a button to defer the selected task to a later date - a button to print the task details for the date selected.
 

Answer:Access 2003 Project - Form and subform / same table

6 more replies
Relevance 74.21%

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?

Thanks!
 

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

10 more replies
Relevance 74.21%

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 74.21%

Hi

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?

Thanks,
Barbos

 

Answer:Solved: access 2003 form (table) update

13 more replies
Relevance 74.21%

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:

[Deposit1]+[Deposit2]+Deposit3]

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??

Mario
 

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]?

Deej
 

2 more replies
Relevance 74.21%

I am super new to Access (like, what I know I taught myself or learned from some short youtube tutorials a few days ago), but I'm putting together a database for my reporting at work. This is the first time I've really run into an issue.

I am using Access 2010. I have a table called "Holds Table", and a form called "Holds Form". I have a field on both called "Fraud Status Date?", and a field called "Case Age".

"Fraud Status Date?" is, obviously, a date field, and the control source for that text box on my form references the correct field on my table.

"Case Age" is another matter. It has to calculate how many days have passed since the case was opened. Currently, the control source I have for that is "=DateDiff("d",[Fraud Status Date?],Date())", and it's working great.

However, the "Case Age" field in the table is blank, because no text box is pointing to it. I need to have the number from the form show up in the table, so that I can query it.

Any suggestions on how to do this? I'm not super solid with VBA, so if you have suggestions with that, please spell out exactly where I would click and what I would enter where (step by step, because I'm such a n00b). If I'm going about this backwards, or the wrong way, please give me alternatives!

I really appreciate ANY help you can give!
 

More replies
Relevance 74.21%

Hi All,

I am new to the form creation in access database, but i am very much familiar with queries and table creation. Now i want to create a form for the user to provide some inputs to show the results from table. And if they want to change any record of the displayed result then they need to click on edit option in form to edit those details. then those details need to be updated in source table also.

I am beginner in form creation i have given try to create it but i don't know where to start and where to end. Can you please anyone help me to create this form . May be if you have give some suggestion and idea's i can able to try myself.
Thanks in advance.

Regards,

Vinwin
 

Answer:Solved: User Input form from table in MS access

16 more replies
Relevance 74.21%

Hi -(Sorry, the heading should say add a record not add a line)

I have a form in which displays the records, a subset chosen by the user. If they choose to update a date the following must happen:
1. A field in the chosen record that notes the foldername must be amended with the word POSTPONED added
2. The actual physical folder must be renamed
3. An actual physical folder must be created with the new date
4. A new record in the table with the updated date must be created.

Actions 1 - 3 are happening in the Form_BeforeUpdate, and I have coded around and the date they have chosen does not change in the current record. So I have a field that I keep with the current date and a field with the New date that they chose; that I want to use in creating a new record.
How do I force it to goto Form_BeforeInsert to achieve the creating of a new record, or how do I code it.
 

Answer:From within a Form, add a line to the CURRENT table - Access 2010

Okay, found a way - it is not pretty, but it works. If someone can improve on my solution, I would be happy. I have put this code in the Form_AfterUpdate event and the mym- prefixed fields are global ones.

Dim rst As DAO.Recordset
On Error GoTo myError
Set rst = CurrentDb.OpenRecordset("MeetingDates", dbOpenDynaset)

rst.AddNew
rst!ABR = myABR
rst!MeetingDate = mymnewdate
rst!foldername = mymfoldernamex
rst!Foldercreated = -1
rst!foldername = mymfoldernamex
rst!Timechange = mymTimechange
rst.Update

response = acDataErrAdded
leave:
If Not rst Is Nothing Then
rst.Close: Set rst = Nothing
End If

Exit Sub

myError:

MsgBox "Error " & Err.Number & ": " & Error$
Resume leave
 

2 more replies
Relevance 74.21%

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
http://forums.techguy.org/business-applications/931652-vba-ms-access-2007-a.html
at post #5.
 

3 more replies
Relevance 74.21%

simple databe for inventory i created and 2 of the fields are not writing info to the table after they are filled in on the form. One of them is a drop down box for several choices and the other is a field for the amount of inventory left which is automatically calculated depending on the user entering what they shipped verus what they received in? I am stumped on this since all the other fields are writing to the perspective table columns. Please someone help me !!! lol

Anthony
 

Answer:Access 2007 Table does not contain all info from form fields

6 more replies
Relevance 74.21%

I am really not sure where to go from here. I currently have a client table, a client activity table, insurance table, procedure table. They are mainly linked by the client ID. I have a main form for the client and each subform is associated with each clients data. Their activity, insurance, procedures. What I need to do is have the ability to select certain activity to invoice from for a client. I am not sure the best way to handle this. I can send in the database that I have created so far if someone can help lead me in the right direction. I am guessing I use update. I am usually better at this but my brain has been in a fog (not to get personal but I recently had a family tragedy) I just need a kick start on this. I would really love for someone to take a look and help me to move forward in the right direction.
 

Answer:Access - creating invoice table using a form with subforms

Do you still need help?
 

1 more replies
Relevance 74.21%

I actually stumbled across this post by User OBP...and am trying to do the very same thing. I would like to get the SQL string that OBP references.

FORMER OBP POST
All you have to do is set up a table the way that you want it to appear.
Import the data in to a Temporary table, I have a form that was created for another poster that allows you to select the field names on the left hand side (Imported table) and match them to the Left Hand side (Required layout Table) and an SQL routine puts the data in the correct place. The version that I have was for importing an Excel Worksheet.
 

Answer:Access: Rename Columns Using Temp Table & Form

6 more replies
Relevance 74.21%

Hi there,
I'm working on my A level coursework and trying to update a table by using a form which allows the "user" to input their information easier. I have tried using an update table query, however, I can't run that off of a form. Anyone got any ideas?
Thanks 

Answer:Microsoft access: Is it possible to update table fields from a form

Hi,
 
You need to create a Select query to do that.

2 more replies
Relevance 73.8%

I have a form (parent form) that has more than 1 pages and on 1 page I put a sub-form with text boxes and combo boxes linked to a table say Table 2.
In this Table 2 it contains historical data of a particular field, say previous employers and the current employer and the data on each employer of a person, so there's a field: a flag to mark that a record is the current one and the other records of the same person are not flagged.

On the parent form, there's a list box displaying content of say Table 1, say list of people.
Whenever a row is selected in this list box, the text boxes and combo boxes on the sub-form should display this person's current data (a record with the current flag checked).
So I linked the list box to the sub-form by the person ID in both tables, by populating the child and parent fields on the forms.

What I'm confused about is the first text boxes display the current dummy data, but then the combo boxes are empty.
It's dummy data cos for now in Table 2, there's only 1 record for a person.
I haven't put the criteria to display data that has the current flag checked, cos there's only 1 record per person now.

I linked each combo box to the relevant field of Table 2 on the "control source".
There's nothing in the "row source" of the combo box, even though later on there should be.

Any idea what went wrong?

Thanks in advance.
 

Answer:Problem displaying value of a table field in a combo box in a subform in Access 2003

9 more replies
Relevance 73.39%

I'm sure this is an easy fix but I can't seem to find it. I just have a form, that will be a subform of another, that needs to display the results of a query.
The query is simple enough, just displays all fields of records that fall between specified dates. The query works great, but when I attach it to the form as its record source it doesn't display the data. I can see the correct amount of record selectors so I know its understanding the query but its as if all fields are hidden!

I have also tried building a query to the forms record source that was simply Select query.* From query. Oddly I have had this working before but I had to specify every field. What I mean is:

Select title From query
Select type From query
Select date From query ...

And so on for all the fields but this seems foolish, can anyone think of what I may be doing wrong?
Thanks in advance!
Edit, forgot to mention I also tried the foolish solution that I mentioned above and it didn't work so its definitely some issue that I'm not seeing, some property that's probably not appropriately set
 

Answer:Solved: Access 2010 form not displaying query

8 more replies
Relevance 73.39%

I have created a reservation form for 2010, I want to put a check box on the form that will link (for lack of a better word)to my masterlist of customers and activate the 2010 checkbox there. Hopefully I can then tell at a glance which customers on the masterlist have visited in 2010. I've got the checkboxes set up in both places. How do I tell the masterlist that I have activated the box in my reservation form?

thanks!
 

Answer:Solved: Access 2003-a form button to update a table

7 more replies
Relevance 73.39%

I'm trying to update a table that is based on a form that includes a list box. I am a little familiar with VBA code but can't figure out how to accomplish this. I have a customer table and a price table. The prices are based on the price item codes selected. I have reviewed lots of vba code examples but have not been able to implement them.
 

More replies
Relevance 73.39%

Is it possible for an access form to filter specific fields in the record source table so the user can deal with them only a part form the rest of the table fields.
The table contains many fields and the user needs to enter data in all fields for the different records in the table. To make it easy, I need to filter the records which have similar or are common in a specific character to enter their data, then remove the filter and put another filter for another character.
Please advice,
Thanks in advance.
M Hegazy
 

Answer:Building a form in Access can filter specific records in a table

9 more replies
Relevance 73.39%

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!

Cheers

Ty
 

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 73.39%

Basically I have a situation where users are creating data tables in Microsoft Word using the creating table functionality, and are then resquesting that the data will be imported into a single Microsoft Database field.

Please could someone show me the best way of doing this!!!!!!

Cheers !!!
 

Answer:Importing Table Data Into Multiple Access Field Data?

Do you mean they're creating, say, a 3-row by 3-column table and then they want to import everything in that one table into a single field? a single record?

Out of curiosity, what happens if you copy and paste, perhaps with an intermediate step through Excel?

Also, is there a reason why your users cannot work directly in Access?

More details, please.
 

3 more replies
Relevance 72.57%

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 72.57%

Semi beginner in Access 2003. My problem is I think a little complicated so here goes. On a specific week, our department will get a list mail renewal packets with 5 different expiration months. So I have to find a way to create a form (which I have done) with a week ending date that will allow the user to enter multiple expiration month dates. I figured how to do one at a time and get the form to update but can't figure out how to update all of them at the same time. The problem I think has to do with the control source but not sure. so here's a sample of the info:

For week ending 9/20/2008,
August September October November December
Opening Balance 1000 1200 1300 1500 1600
Received in Unit 200 100 50
Deferred
Deferred Closed
Deferred Pilot
Deferrals Return
Processed
Closing Balance
Mail Room Count

So I set up a form similar to the look above but can't figure out how to update it to a single table that has all the left handed information on it. Please help!!!!!

Thanks a million... You know bosses they want it particular way.
 

Answer:Access 2003 - Updating multiple records from a form to table simutaneously

16 more replies
Relevance 71.75%

Hello, I've been struggling with getting a simple InfoPath 2007 form to work with a test MS Access 2007 database. I feel the best way to explain what I am doing is by giving you an example.

Background of scenario:

Lets say I am a distributor of gas cylinders. I have 100 different gas cylinders that I fill and send to customers. When they are done with the gas, they return the cylinders and I fill them back up. Each cylinder has specific data related to each: manufacturer, serial #, specific cylinder ID, and material of construction (carbon steel, stainless steel, etc). I have this data in my MS Access database, in an individual table called "CYLINDER_DATA". Every time I refill a cylinder, I need to record the date and measure the pressure and temperature (among other things) of the gas in the cylinder. This data (along with the specific "CYLINDER_DATA" at this date of measurement) must be added as a new record in a different table called "CYLINDER_MEASUREMENTS" in the same database.

What I would like to do:

So, I want to create an InfoPath form for operators to use whenever they need to perform measurements on a newly filled cylinder. So, I created an InfoPath form, that starts with a drop-down list box control that is populated with the 100 different cylinder IDs. A user would choose one of the "cylinder IDs" from the drop down list and then click "retrieve data". This would then populate the fields directly below:... Read more

More replies
Relevance 69.7%

I have a table in Access that contains 2 fields, ID and CODE. I have another spreadsheet that contains ID's for which the user needs to get their code again. Is there anyway that I can create a query that will take the ID's from the Spreadsheet, Look them up in Access, and then assign the correct code back to the spreadsheet?
 

Answer:How do Get data from One table in Access To another?

Spreadsheet or database table? Are both tables going to be "permanent"? If so, then "copying" this data is not appropriate normalization on the database and you'll just be "bloating" your data.

Your database is not going to be normalized properly if you plan on using two of the same exact fields in two separate tables unless you are using a lookup to obtain them. If you need more info, email me requesting the normalization.doc file.

Meanwhile, if you just need to get it out to a table or spreadsheet, just create a query on that table that grabs those two columns. When you're sure this query contains the exact data you want, open it again in design view and hit Query-Make table query (an "action" query). Now, when you run the query, it will create a new table. You can then go into the design of this NEW table to add other fields as necessary.

Hope this helps.

------------------
~dreamboat~
Brainbench MVP for Microsoft Word
Brainbench
 

1 more replies
Relevance 69.29%

MS Access:
I have several fields in a form that uses two tables. I would like to set up a command button so that when you are on a current record, it will copy the field information from the record you are on to other fields in the same record. I do not know the SQL commands to set this up.

For example, there are name fields, address fields, and phone to be copied. Reason: One is contact information; one is survey information. In most cases the information is the same but on rare instances, the information needs to be entered (when different).
This is not an update for all records in the database. This is based individually on each record.
Not sure if you need to do On current, on click, or after update.
Not sure if default value in form can be used.
Please help.
 

Answer:Copy data fields in same table to other fields in same table MS Access

16 more replies
Relevance 68.88%

I am studying for the ECDL. I have to add data to a TABLE using a FORM. When I enter data into the FORM, it does not show in the TABLE until I shut down Access and re-open the database.How can I get it to appear in the TABLE without closing the database?Thanks for any help with this query.

Answer:MS ACCESS - Entering data into a TABLE

How do you know that it does not show in the table. Since it shows when you close access tells me it is been written to the table.If you use the navigation keys at the bottom of the form to move off the current record, then back onto it, does it then show in the form?

3 more replies
Relevance 68.88%

Real dummy here using Access. I have two tables containing 80,000 names. These tables are going to change weekly. What I need is a query that will compare new user in table 2 to the ones in table 1. Then I need a query that will show the users that are not in table 2 that were in table 1.Hope this makes sense.
 

Answer:Comparing Access table data

14 more replies
Relevance 68.47%

Hi there,
I am not that cluey with access vba programming and I need some help please.
I have a Table called (Parts) with columns (PartName), (PartNumber), (PartCost), (Quantity)...
I have (Parts) table linked to (Repairs) table in one to many relationship.
I have a Form linked to the table (Repairs) with different fields to enter different repair details.
Among these fields is: combo box (PartsNameCombo) where you can select the Part Name from values listed in (PartName) column in table (Parts), and I have used the following codes to populate the selected part's cost accordingly:

Private Sub PartNameCombo_Change()
'Auto populate Part cost based on Part Name
Me.PartCost.Value = Me.PartNameCombo.Column(2)
End Sub

I have repeated the above combo box three times (in case different parts used in one repair).

My Question: I would like to add code to automatically subtract (1) from part's Quantity in (Parts) table whenever a particular (PartName) is selected in the combo box.
so for example, if in a repair, I selected: (front case) in the first combo box and (display screen) in second combo box, I want the cases quantity to drop by one and also screens quantity to drop by 1?
Any one can please help?
 

Answer:Updating table value when different table form field updated

16 more replies
Relevance 68.47%

Hi there,
I am not that cluey with access vba programming and I need some help please.
I have a Table called (Parts) with columns (PartName), (PartNumber), (PartCost), (Quantity)...
I have (Parts) table linked to (Repairs) table in one to many relationship.
I have a Form linked to the table (Repairs) with different fields to enter different repair details.
Among these fields is: combo box (PartsNameCombo) where you can select the Part Name from values listed in (PartName) column in table (Parts), and I have used the following codes to populate the selected part's cost accordingly:

Private Sub PartNameCombo_Change()
'Auto populate Part cost based on Part Name
Me.PartCost.Value = Me.PartNameCombo.Column(2)
End Sub

I have repeated the above combo box three times (in case different parts used in one repair).

My Question: I would like to add code to automatically subtract (1) from part's Quantity in (Parts) table whenever a particular (PartName) is selected in the combo box.
so for example, if in a repair, I selected: (front case) in the first combo box and (display screen) in second combo box, I want the cases quantity to drop by one and also screens quantity to drop by 1?
Any one can please help?
 

Answer:Updating table value when different table form field updated

At least two ways to do this but I wouldn't make it automatic. What if the wrong Part name was inadvertently selected? I would ad a button to the form "Take one from Inventory" and execute an update query that basically does Quantity= Quantity-1 . This query would need to link to PartNumber on the active form.

The other way would be to write the SQL to do the update in VBA and run the EXEC SQL command under the new button.
 

2 more replies
Relevance 68.06%

Help-I am trying to do a task for my boss, we have code that transfers information from one table to another once a date has been populated it looks like this:

-----------------------------------

Private Sub Fund_Date_AfterUpdate()
On Error GoTo err_Fund_Date_AfterUpdate
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblProjects", DB_OPEN_DYNASET)
rs.AddNew
rs![Reference Number] = Me![WO]
rs![Title] = Me![Title]
rs![Original Cost] = Me![FundAmt]
rs![Bldg] = Me![Bldg]
rs![PM] = Me![Owner]
rs![Project Type] = "New"

rs.Update
Me![WO].SetFocus
MsgBox "Update the NEW Project Record!"
exit_Fund_Date_AfterUpdate:
Exit Sub
err_Fund_Date_AfterUpdate:
MsgBox Error
Resume exit_Fund_Date_AfterUpdate
End Sub

-----------------------------------
There is another piece of data coming from the same location that needs to be populated in another table, the location is "tblAdditionalData" and the field is "Comments" I tried putting:
rs![Desc] = Me![Comments] but of course that did not work because it is a different table. What can I add to to make this happen?

Thanks, Joe
 

Answer:MS Access-transfer data from one table to annother

9 more replies
Relevance 68.06%

Hi All,

Can i write array data to access table without looping ?

dim db as database
dim rs1 as recordset
dim rs2 as recordset
dim arr1 as Variant
dim R1 as long
dim R2 as long

set db = currentdb()
set rs1 = db.openrecordset("Select * from Table1)
set rs2 = db.openrecordset("Select * from Table2)

for R1 = Lbound(arr1,2) to Ubound(arr1,2)
rs2.AddNew
for C1 = Lbound(arr1,1) to Ubound(arr1,1)
rs2.fields(C1).Value = arr1(C1,R1)
Next C1
rs2.Update
rs2.MoveNext
Next R1

Thanks
 

More replies
Relevance 68.06%

I am using access 2003 at my work that was created before I started here. The table I am using was created a long time ago and has 3200 records.
Since the table was created there has been a lot more information that would be useful to have in our customer database.
Ultimately what I would like to do is create a new table and move all the information from one table into the new table because one of the necessary new fields would be Customer ID # using auto number. Although I realize Access wont let me assign that field in the current table as it already has data.
So with out much knowledge of the program it seems to me that I need to make a new table with all the new fields and then move the data from the old table to the new table.
I have been searching for answers to this delemia and cant find any one who has asked this question.
Thank you,
Lindsey
 

Answer:Access 2003, Moving data from one table to another

Lindsey, welcome to the forum.
Add the field to your current table and make it an Autonumber.
It will do it.
What it won't do is take a current number field and change it to an Autonumber if there are any values in it.
 

1 more replies
Relevance 68.06%

Hi!

I have a database with 10 different tables for 10 different classes. I want to create one table that has the information for all 10 classes. The fields are the same (first, last name, etc.) Do I use query? Import data? Simple question, but not so simple answer for me..

I'm a newbie...please use small words and speak slowly..
 

Answer:access 2003 import data from one table to another

14 more replies
Relevance 68.06%

Hi,

Within Outlook, I am looking to add a Toolbar button that calls a vba procedure that does the following:
-Prompt user for a code
-Find the code in fldCode in tblLookup in an Access database
-Fill in the To, Subject, Body, etc in a new mail message based on contents from the tblLookup

Any ideas. I have been scratching my head in defeat for quite a long time - any help would be truly appreciated!!
 

Answer:Outlook VBA to fetch data from Access table

This should get you started. Make sure you add in the 'Microsoft ActiveX Data Objects 2.8 Library' (I added 2.8, other versions may work) by opening Outlook Visual Basic and going to 'Tools', 'References'.

Create a Macro in Outlook and code it to connect to your database something like this:

Sub GetMyData()
Dim myCn As New ADODB.Connection
Dim myCmd As New ADODB.Command
Dim myRs As New ADODB.Recordset

Set myItem = Outlook.CreateItem(olMailItem)

myCn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=databasename.mdb;" & _
"DefaultDir=databaselocation (c:\whatever\whatever);"

Set myRs = myCn.Execute("Select * from tablename")

'Replace field names in my example below to match your database fields
myItem.To = myRs.Fields("EmailAddress")
myItem.Subject = myRs.Fields("Subject")
myItem.Send

'Be sure to close the recordset and connection when you're done
Set myRs = Nothing
Set myCn = Nothing

End Sub

After you create that macro, you can add it as a button to a toolbar. In Outlook, select 'Tools', 'Customize', 'Commands' tab, 'Macros'. Drag your macro to the toolbar.

Hope this helps.
 

1 more replies
Relevance 68.06%

04/21 - Moved question to this forum from Development Forum​
Workstations: 13 XP Pro systems
Server: Win 2003

Currently using MSAccess 2k database for client listing and associated notes, etc (one-to-many / tbl_client to tbl_notes). We recently purchased a software package, and I believe I read in the literature that it is built on sybase platform?? In talking to the company about migrating data, one of their techs stated that they Export access tables to csv comma del format, bring the new systems table open in foxpro, append the data from the csv files. He said that foxpro accepts the tables better, and keeps the table in a correct format for the commercial program to read/use them.
So I contacted a friend who had Visual FoxPro 6 on his laptop, and he let me use the laptop for a short period of time.
I exported a few of the MSaccess tables to csv format, and then opened FoxPro.... BIG SURPRISE...
Never used or even seen screen shots of FoxPro, so when it opened up, I guess I was expecting something "similar" to access or whatever, but that was not the case.... Foxpro uses a command window, and, ... well Im lost.
I opened one of the commercial programs tables in foxpro and it took me a few minutes to look at the data currently in their table, figured out it was some "browse" command, but absolutely - totally different than what I was expecting.
QUESTION: How do I append data to existing tables, from csv files in FoxPro
QUESTION: Is there some down ... Read more

Answer:Access table data INTO foxpro tables

8 more replies
Relevance 68.06%

How can I take data from an Access table and paste it into a new workbook/worksheet of Excel?~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~I know little enough of Access and much less even than that of Excel!I have a particular completed table in Access from which all the data has long since been reported and published.A friend who seems familiar with Excel though not Access was trying to teach me some basic Excel, and I challenged him to extract some rather complex data using Excel.I don't seem to be able to transfer this table into Excel for him. If I copy the table from an open Access database and paste it into Excel it looks alright, but the fields which should be all numbers (0-3)are not seen as numerical fields.This was a part of an official parish questionnaire five years ago, I was a little over confident with Access and couldn't extract someof the more complex fields where I stumbled over two many parameters.A whizz-kid did it for me at the time using Excel, but he did it with such ill grace that I didn't dare ask him how he did it!So it is just an an excercise now and part of my learning curve, but there must be a simple way to transfer just the contents of a large but simple table. It has 254 records each with a possible 197 fields

Answer:Data from an Access table to a new Excel workbook?

Open the Access database and then the relevant table. Click on the File menu and select "Export..". Select "Microsoft Excel 97-2003" in the "Save as type" box and save the file. It should now open in Excel with all field names in the first row.

3 more replies
Relevance 67.65%

Iím creating a website that will handle tons of data, using MS SQL Server for the active content, basically dropping records into a variety of webpage templates for the end-user. But, my back-office first has to sort through tons of raw data and whip it into shape, drafting and editing text, cropping/resizing photos, assigning links, etc. Iím using Access 2003 for that process, to handle everything that is not yet ready to be imported into the SQL Server for publication to the live website.

I understand that I can display data from an Access db on a webpage, somehow (just a facsimile of the real webpages in which it will be displayed, later, one record at a time). It would be very helpful for the back-office staff to have an in-house, interactive way to see how the data theyíre editing will eventually appear on the website, giving them insights into how they need to adjust it further.

My head is reeling from all of the different stuff Iíve read about how I might do this. Can you all give some general advice on how I should get started, which direction I should take?

Thank you.
 

Answer:Displaying Access data records on a webpage

Access 2000-2003 uses Data access Pages to display data on the Web. They are an html version of a Form and can be created by saving a Form as an access Page.
See
http://support.microsoft.com/kb/264080
 

1 more replies
Relevance 67.65%

I have a combobox in a subform within a subform that will not display data when the main form is displayed/loaded. If I click on the combobox (giving focus to the field) the data will appear. All other data within the subform displays fine.
 

Answer:Access 2003 ComboBox not displaying data

13 more replies
Relevance 67.24%

Recently, my office upgraded our server to Windows Server 2000 Pro (& all the workstations to 2000 Pro as well.) We are all running Access 2000, with which we utilize tables and corresponding reports. With the old system, we could enter data in any table; save that table, preview the corresponding report, print that report; & the new data would be included in the report.

With the new system, it seems like Access has to be closed after saving data in order for the report to include the new data, because when we print a report directly after saving, we get the old data. When we close out the app; then reopen the app; & then preview & print the reports, the new data is then correctly included.

This is really inconvenient for the nature of the work we do. Is there any way to set up Access to work our old way...to have the saved data immediately available for inclusion in the corresponding reports?

Thanks in advance.

mark4man
 

Answer:Access 2000 - Saved Table Data For Reports

7 more replies
Relevance 67.24%

Hi
My DB has a table Info with fields :Index and Data. A form has a series of textboxes 1 ....x. and a Command button which I want to use to automatically transfer data from table index 1 data to form Text1, index 2 to text2..etc.
I have spent hours trying to do this using For/Next loops and many other methods, none of which worked.
My Latest attemt was
DoCmd.OpenTable "Info"
DoCmd.GoToRecord acTable, "Info", acFirst
Text1 = acFirst
This is returning the second record index. Text1 =2.
Copy of table attached. What I want is F1 data, not interested in IDX apart from providing Text box ID.. If I can get the right Data I am thinking of a For/Next loop to populate the series of textboxes. Text Box no = IDX
IDX 1 2 3 4 5 6 7 8 9 10 11 12
F1 449 8 20 34 15 31 516 795 161 8 1 1
Can anyone help or am I trying the impossible?
 

Answer:Retrieve data from table to textbox in access 2000

9 more replies
Relevance 67.24%

Hi

I have a pivot table that was created in office 97. I converted the database to office 2002.

The pivot table does not refresh based on the new data. Pleas help.

OLE Class - Microsoft Excel 2000

Row Source - MyAnalysisReportsQuery

OLE type - Embedded

OLE type allowed - Embedded

Class Excel.Sheet.8

Code

stDocName = "MyAnalysisReportsQuery"

If MyQB.CreateNewQuery("Analysis Reports Selection Form", stDocName, False, strSQL) Then

SysCmd acSysCmdSetStatus, "Data has been retrieved from your server"

Me.PivotTableTab.Visible = True

Me.Repaint

Me.PivotTable.Refresh

Me.PivotTableTab.SetFocus

Me!PivotTable.Verb = acOLEVerbOpen

Me!PivotTable.Action = acOLEActivate

Me!PivotTable.ActiveData

Me.Clear_All.SetFocus

Me.PivotTableTab.Visible = False

Else

msgbox "There are no records with the criteria you have specified."

End If

Thanks inadvance.
 

Answer:Access 2000 pivot table not refreshing data

One thing I've found when migrating between different versions of Access is the VB references to be unchecked. In your VB editor locate Tools, references. If theres something either Missing or not ticked that should be ticked then you've found your problem.
 

3 more replies
Relevance 67.24%

Hallo.

I'm writing an MS Access database (will end up being Access 2010, but is currently a .mdb), which is to be updated by Append and Update queries, and also exports various data. As far as possible, I'm trying to make everything one-click deals for the user.
The data source for the Append and Update queries is an Excel file that contains 20 columns of data. This file is sent back and forth from the user to an external company, who fill in data on existing rows, add new records and delete old ones.

If there are values in column A, and nothing in the other columns, this generally means this is a new record that needs appending to the database table. I've written the Append query, and that's operating fine.
If there are values in the other columns, this generally means this is an existing record with new info, so the database table needs to be updated.

I figured I'd add a macro to the OnClick event of a button on the switchboard, which would first fire the Append query, and then the Update query.

However, I don't know what I'm doing with the Update Query.
6 of the 20 columns in the Excel sheet are relevant, and I'm updating the Stays table in the database:
Excel > Access
Chassis > FullChassis
Final Distr Point > Dealer
Order Type > OrderType
Model Type > ModelType
Hold > Hold
Sold > VehicleSold

However...
The Dealer field in the Stays table is actually a lookup (autonumber) ID field. It looks up the DealerID f... Read more

Answer:UD query to update Access table from Excel data

9 more replies
Relevance 67.24%

I created an access 2003 db & populated a table using 'get external data' from excel 2003. Now I need to import more records from excel to that same table. I tried the same process, but it insisted on creating a new table. Repeat 2x, get 2 more new tables. Even though when using 'get external data' there is an option to import 'into an existing table,' the option is greyed out.

I need to get the 'new' excel data into the already-populated table.

Speaking in human terms (not Microsoftese), how to I either

merge the data from newly-created,newly-populated, and unwanted table 2 into the existing table 1
or

import from excel into the existing table

I realize that I could export from access back into excel, merge the data there, and import into a new table, but surely there's a better way. Or not, since we're talking about one of the Big Flopper's biggest flops (Access).

Of course, the best solution would be to import it into an SQL db, but that's not an option in this case (office politics).

[note to self: CHILL]
 

Answer:Solved: Import data into existing access table

7 more replies
Relevance 67.24%

Daily I receive an html email that contains multiple tables in the email
There are two table's whose data I want to import from that email. The only development tool I have is VBA

Importing the data is not the problem. The problem is that the resulting table is in a format that I can't use. I want every record to contain a name, but at the moment there will be a name and five empty fields beneath it for the same column. I want to fill those empty fields with the name above. I'd also like to fill in the same date for all records of a corresponding day for which I'm doing the import. I will be importing the data daily

Can I do that with VBA?
 

Answer:Access: importing data and manipulating resulting table

15 more replies
Relevance 67.24%

Does anyone know the best way to copy records from one field in a table to another field in the same table? There are approx. 40,000 records that need to be copied.
 

Answer:Solved: Access Table: Copy Data from One Field To Another

16 more replies
Relevance 67.24%

I have an old database with customer info in it. I am working with a new database however, that has some of the old customers in it. The old database has some customer details about these customers that I would like to have in my new database. I am having trouble adding the data from the table in the old database to the table in the new database.

So far I have imported the old table into my new database and have tried to Insert Into the New Customers table the CustDescriptions FROM the Old Customers table WHERE FirstName=FirstName And LastName=LastName And Address=Address. This did not work it just added new rows to my table instead of inserting the data into the current rows where the criteria matched.

Any help on how to make this work would be great! Thanks ahead of time.

swebster
 

Answer:Access 2000 - adding additional data to a table

Hi - firstly are you using different or the same versions of Access? Secondly I would just cut/copy and paste the records from table to table but you must ensure that the properties for the fields are identical.

stepheno
 

3 more replies
Relevance 67.24%

Workstations: 13 XP Pro systems
Server: Win 2003

Currently using MSAccess 2k database for client listing and associated notes, etc (one-to-many / tbl_client to tbl_notes). We recently purchased a software package, and I believe I read in the literature that it is built on sybase platform?? In talking to the company about migrating data, one of their techs stated that they Export access tables to csv comma del format, bring the new systems table open in foxpro, append the data from the csv files. He said that foxpro accepts the tables better, and keeps the table in a correct format for the commercial program to read/use them.
So I contacted a friend who had Visual FoxPro 6 on his laptop, and he let me use the laptop for a short period of time.
I exported a few of the MSaccess tables to csv format, and then opened FoxPro.... BIG SURPRISE...
Never used or even seen screen shots of FoxPro, so when it opened up, I guess I was expecting something "similar" to access or whatever, but that was not the case.... Foxpro uses a command window, and, ... well Im lost.
I opened one of the commercial programs tables in foxpro and it took me a few minutes to look at the data currently in their table, figured out it was some "browse" command, but absolutely - totally different than what I was expecting.
QUESTION: How do I append data to existing tables, from csv files in FoxPro
QUESTION: Is there some down and dirty tutorials on FoxPro 6 that I can read through. Im not to... Read more

Answer:Solved: Access table data INTO foxpro tables

bump....
is this in the right forum, or should it be moved?
 

2 more replies
Relevance 66.42%

I am working on an Access DB written by someone much cleverer than me who has use a lot of SQL which I find hard to interpret. It is a DB of client info and order info. I exported the Client Table to Excel so that I could more easily sort out the data (separate name fields into Title/First Name/Last Name, for example) and then I wanted to import the cleaned data back into the Access DB.

I renamed the existing table as Client Original and I successfully imported the new table giving it the samename as the original used to have so that there wouldn't be any issues involving queries etc which relied on it unbeknown to me.

I needed to add a field from the Client Table to an Order Form but - to my surprise - the only table available to me was the first one which I had renamed as Original; the new one which had the old name wasn't seen by the Add Fields box - it is as if it doesn't exist (but it does!!)

I then tried to import into the Client Original table instead, but get the message that it cannot overwrite this table when I get to the end of the wizard. There are no relationships at all involving the Client table (and when I show the Client Original table on the relationships screen it's name is given with _1 at the end of it which isn't in the name I gave it .) I am thinking that the guy who wrote the DB has something hidden in the SQL somewhere which is stopping me from being able to overwrite this table.

Has anyone got any ideas please?

Many tha... Read more

Answer:Access 2007 - will not overwrite existing table with Excel data

11 more replies
Relevance 66.42%

Hello I hope someone can help. I am using Excel and Access 2000. I am trying to create a Pivot Table in Excel with a union query I created in Access. The union query works fine in Access and is pulling data straight from some text files (no calculations or anything). When I try to pull the data in from the access query I get the error "Too few parameters. Expected 11". I only have 10 columns of information that I am trying to pull over. I have spent all day trying to figure this out to no avail. We have done this before with other files in Access and it has worked before, but for some reason this one will not.

Any help would be appreciated.

Kimberli
 

More replies
Relevance 66.42%

I have created a select query where I am attempting to update/revise data to the Table in the datasheet view. This process seems to work only when running the query is first run. When changing criteria of the query and re-running the query, the fields are locked and I can not make any changes. Why is this? Is there a better way to make Table updates rather than a select query?
 

Answer:Update data in Table via Select Query wit Access 2003

8 more replies
Relevance 66.01%

So, subsequent to my previous thread...

(Basically, I have an Access database which is to be used by many users to make changes).

...once the record has been amended, I need to allow the user to either "save changes" or "exit without saving". At the moment, the data is updated as soon as I leave the form - how do I get round this?

Thanks

Ed
 

Answer:Solved: Saving (or not) changes to data in a form in Access 97

If the user does not want to save the changes (or additions) they can press the Esc twice to "Undo" the Actions.
However if you want to have a second Button that says Close without saving then before the button's VBA code Docmd.close enter
Me.Undo
 

3 more replies
Relevance 66.01%

How am i able to modify data in a form that is loading data from a query? I only seem to be able to retrieve data, but not upload. More specificly I use a query to filter some data from my table. With a form, i then want to be able to update some of the values (in my table ŗnd query). I have to use the query since not all colums/rows from the table may be displayed.
Any help is welcome!
 

Answer:Access 2000: Data entry in a form

What application are you using?
 

2 more replies
Relevance 66.01%

Hello to everyone on my first posting ever,

I am a novice and a very green user of Microsoft Access but I have taken on the daunting task of trying to set up a database for our local high school lacrosse team. As part of that, I am designing a Data Entry Form to caapture and record the stats for a game and have set up a field with a combo box which requires the user to select the type of stat. If the stat type is a penalty, I would like the form to automatically enable three other fields that I have disabled in order to control entries to those fields. The first disabled field is a combo box with a penalty description drop down list; the second is the duration time of the penalty which is a one decimal number field and the last is a yes/no check box asking if the penalty is a releasable penalty. How do I "activiate" these fields for entry only if the type of stat selected is "PEN" for penalty?

I apolgize in advance for the length of the post, but it must be the preacher in me. Can someone help an old preacher who is too cheap to buy software already on the market which will do the trick and thinks he is smart enough to do it himself?
 

More replies
Relevance 66.01%

Hi. This is my first time ever using MS Access, and I'm a little stuck on something. I'm creating a database in MS Access 97 which keeps track of rail car incidents. The main menu of my database is a form consisting of combo boxes and a command button. The command button takes you to a data entry form bound to a table called tbl_incidents. This form allows the user to add or edit data in the Incidents table.

I also want the user to be able to search the incidents based on user-specified criteria. This is where the combo boxes come to use. I have 2 combo boxes, one each for a specific field in the Incidents table: Date and System. Instead of binding the combo boxes to their respective fields in tbl_incidents however, I created seperate tables called tbl_date and tbl_system for the combo boxes. These tables are indexed with no duplicates, this way each value is only displayed ONCE in the combo box. I cannot index the fields in tbl_incidents with no duplicates because tbl_incidents is used as a log for all problems relating to the rail cars and each incident needs to be recorded with its corresponding date and system. So for instance, I may have one entry:

[Date] 6/2/11; [System] Brake; [Incident] Brake fault on car 1555

Then I may have a following entry with duplicate dates and systems, but with a different incident:

[Date] 6/2/11; [System] Brake; [Incident] Brake pipe on car 1234 frozen

So, I need to allow duplicates in tbl_incidents. For this reason, I c... Read more

Answer:I need help with a data entry form in Microsoft Access 97

Your main issue is to bring Unique values of Date field (don't use the field name as Date because it is a builtin function, use something like incDate for incidents date) and System Fields of tbl_incidents into the Combobox Source. For that you don't have to maintain two different tables, one for date and the other for system.

You can create two Select Queries from the tbl_incidents itself and use them as Row Source for the combo boxes. This way you don't have to worry about adding the same record information on three different tables at the same time.

Copy the following SQL into a new Query's SQL Editing window and save it with the name incDateQ:


Code:

SELECT tbl_incidents.incDate
FROM tbl_incidents
GROUP BY tbl_incidents.incDate;

Create another Query with the following SQL for System combobox source, name the Query as SystemQ:


Code:

SELECT tbl_incidents.system
FROM tbl_incidents
GROUP BY tbl_incidents.system;

Use above Queries as Row source for Date as well as System Combo Boxes replacing the Date and System tables you are using now.

This way you don't have to bother about updating all the three tables at the same time. When you add a new record with different values they will automatically appear in both Combo Boxes.

NB: The current new record values, if different, may not immediately appear in the Combo Boxes, but it will update when you move out to another record.
 

5 more replies
Relevance 66.01%

Sorry for all the posts but as I work through the design I think of new ways to make it better/easier which is the main requirement of it. So here's my question, I have a data entry form to at items (Movies/Games) into the inventory. The form comprises of ItemID, TitleID, Title, Producer, Category, Type, HireFee and Copy. I am unsure on TitleID at the moment, but where the title is entered once into the tblItemDetails table, it can be entered numerous times in the tblInventory table so I am considering this TitleID to reduce data usage. Now, is there a way to repeat the entry numerous times without having to re-enter data? As in, there's five copies of a movie, input five entries to tblInventory in one go, rather than having to re-enter all the information again and just have a new ItemID and Copy number for each.

That, or can I just have it so that I enter a new title, e.g. 'Fallout 3' along with all the details, then create a new entry and having title as a combo box being typing and select 'Fallout 3' and have all the other fields auto filled? Because all that will be different is the ItemID which is an autonumber and the copy number...

Sorry if that doesn't make any sense.

Thanks in advance,
Mark.
 

Answer:Access 2010 - Data Entry Form

Mark, the TitleID (like all repeated data) should only be entered once.
ie the data goes in a table for the Game/Film and then the titleID is used in the inventory along with a Quantity field ie 5 copies) which is updated when the item is issued or returned.
The issuing and returning should be logged in a separate table.
 

1 more replies
Relevance 66.01%

I created a MS Access form for data entry. As the user enters in the address information, I want to check the table to see if the name and address is a duplicate record. Does anybody know a way or have some code to help me out?
 

Answer:MS Access 2000 Data Entry Form

6 more replies
Relevance 66.01%

Hi all,

I want to use the DoCmd.TransferSpreadsheet command to transfer multiple queries into separate tabs in one Excel workbook.
The issue I have is that many of the queries I want to export use Query by Form for the user to enter date selection. Is there anyway I can use one form for the user to enter the dates & these dates feed into the other forms that drive the separate queries?

As an example, there is a form called Form1 and Query1 has field [Forms]![Form1]![StartDate]
and there is a second form called Form2 and Query2 also has field [Forms]![Form2]![StartDate]

Is there anyway I can have ExportForm display and have the selected date [Forms]![ExportForm]![StartDate]automatically feed into both [Forms]![Form1]![StartDate] & [Forms]![Form2]![StartDate] in their seperate queries? Without opening the individual forms and queries?

How would I change this code to do this?

Code:
DoCmd.TransferSpreadsheet acExport, , "Query1", "c:\Test.xls", True
DoCmd.TransferSpreadsheet acExport, , "Query2", "c:\Test.xls", True
Thanks!
janie
 

Answer:MS Access DoCmd.TransferSpreadsheet with form data

If the Start date is the same for all the Forms, why not just have one form for all the Exports?
 

2 more replies
Relevance 66.01%

I have a database with multiple records which track training records of individuals. To update a record I open a form using a combo box linked to the table. One of the fields need to be checked against the current date and colour value adjusted as needed. I am using the following code to set the value:

CurrentZoneMonth = DateDiff("m", [ZoneStart], Now) + 1

When the value reaches 1, first month of training window, it flags until the training is completed. The problem is the value is not being set. It works fine when I place it in the FormLoad event, but using the combo box it is only initiated when the form loads. Because of the combo box search, it needs to be placed in the OnCurrent event instead and it keeps causing an error due to Update or Edit method not being invoked.
When the code is in the FormLoad event it will work when I go and manually re-input the ZoneStart value, it updates correctly and changes the field.
Any push in the right direction would be appreciated.
 

Answer:Form data loading problems (Access 07)

6 more replies
Relevance 66.01%

I am using Microsoft Access 97. I have set up a database with many fields. I have created a form for data entry.
I want to print a form with the fields and names of fields on paper so that it can be used by people to fill in the information and then someone else can enter the data.

I cant seem to get this done automatically like the form was set up with the wizard. I cant print the blank form from the wizard.
Please help.
Thanks
 

Answer:Access: How to print a data entry form

6 more replies
Relevance 66.01%

Now that I have the rate information pulling from my other table, the Rate Total won't calculate unless I overtype the rate field with a new entry. What I would like to do is just press enter to confirm the field and move past it and have the total automatically recalculate.

I think that this would be an event procedure on Enter but I don't know what VBA code to use to make it happen?

Can you please tell me if this is correct and what code will make this recalculate?
 

Answer:Solved: Access 2007, Recalculate field after data is pulled from table. OBP??

8 more replies