Saving Query Data Into Table

Mar 18, 2008

Ive created a query that asks the user to enter data (month and year) before it can be processed. This is causing a problem for me as i need to produce a chart from this data and access isnt letting me (access doesnt like me asking the user to enter data). So i thought if the data could be stored in a table then the chart could be drawn from the table.is this possible.Below is a screengrab of the design view fro the query.

http://i30.photobucket.com/albums/c329/oohmygod831/screengrab-1.jpg

and this is the sql for the query

SELECT Shift.Shift_ID, Shift.Shift_Type, Sum(Main.Shift) AS SumOfShift, Format([Date],"mmmm") AS Expr3, Year([Date]) AS Expr4
FROM Shift INNER JOIN Main ON Shift.Shift_ID = Main.Shift
WHERE (((Month([date]))=[Month]) AND ((Year([date]))=[Year]))
GROUP BY Shift.Shift_ID, Shift.Shift_Type, Format([Date],"mmmm"), Year([Date]);

View Replies


ADVERTISEMENT

Table Not Saving Data

May 22, 2007

I have a form that stores its data in a table called Participants. Within that table is a field called Payment Due Date. On the form, this field is populated via the following:

Private Sub PaymentDueDate_Exit(Cancel As Integer)
If Me![PaymentSchedule] = "Bi-Weekly" And Me![SentenceLength] < 30 Then
Me![PaymentDueDate] = Me![StartDate]

ElseIf Me![PaymentSchedule] = "Bi-Weekly" And Me![SentenceLength] > 30 Then
Me![PaymentDueDate] = (Me![StartDate] + 14)

ElseIf Me![PaymentSchedule] = "Monthly" And Me![SentenceLength] < 30 Then
Me![PaymentDueDate] = Me![StartDate]

ElseIf Me![PaymentSchedule] = "Monthly" And Me![SentenceLength] > 30 Then
Me![PaymentDueDate] = (Me![StartDate] + 30)

ElseIf Me![PaymentSchedule] = "Paid" Then
Me![PaymentDueDate] = Me![StartDate]

End If
End Sub

My problem is after the field is populated in the form, the data does not store in the table. Have I written something wrong? Any help would be appreciated. Thanks

View 6 Replies View Related

Forms :: Saving Data From Form To Table

Jul 1, 2015

I created a table name HEAD with column names (ID, Begin, End, Month, Year and Quarter)

Begin and End are dates. I haven't entered any data in Month, Year and Quarter fields. In the table properties I kept a validation rule of ([Begin]<=[End]). It worked.

I Created a form named FORM. I named the control source for Begin and End to Begin and End from HEAD table. For the Month, Year and Quarter control sources I wrote

=MonthName(Month([End]))
=Year([End])
=Format([End];"q"". Quartal")

The form consists of all the data ID, Begin, End, Month, Year and Quarter values too..

My problem is the data of Month, Year and Quarters are not saving in the HEAD table from the FORM.

The FORM data of Begin and end dates are saving into the HEAD table but not the month, year and quarters.

View 5 Replies View Related

Forms :: Subform Not Saving Data Into Table

Nov 5, 2014

I have a subform which is for payment, it has a calculated field which is called TotalPayment, with expression:

=Nz([Forms]![Reservations2]![Days]*[Forms]![Reservations2]![Rate],0);

in the control source. When a value is displayed in this field an ID number should be assigned to the PaymentID field, but this does not happen, the field remains null.

View 1 Replies View Related

Forms :: Continuous Subform - Validate Data Before Saving To Table

May 18, 2013

I have a continuous subform that displays lots of questions. This specific form has 10 questions on it. Each question has one answer and the users are required to select one answer for each question. I am trying to do some data validation before the data gets saved to my table to ensure that users have answered all the questions. I found some very useful code online and tweaked it to suit my needs. The validation works perfectly on subforms with only one question, as I have some other tabs that use it.

On the specific form that has the issue, the validation works as expected when no question has been answered, but when a user answers only one question and leaves the remaining nine questions blank, the validation does not work. I guess it is as a result of the continuous form repeating the different questions and ms access thinking that the data entered for one question alone is complete when it actually isn't.

Code:
Private Sub Ctl4_frm_Staff_Exit(Cancel As Integer)
Dim ctrl As Control, EmptyStr$
For Each ctrl In Form_subFrm_staffQuestion.Controls
If InStr(1, ctrl.Tag, "Required") > 0 Then
If IsNull(ctrl.Value) Or ctrl.Value = vbNullString Or Len(ctrl.Value) = 0 Then

[Code] ......

View 1 Replies View Related

Saving Value To Variable? (update/make Table Query)

Aug 28, 2007

I am trying to create a table to show the parent/child relationships between assemblies, sub-assemblies and detail parts. The table I start with has 2 fields a "Part Number" and an "Indenture".

Please forgive all the periods, couldnt think of how else to seperate the values to depict the tables.

Example:
IND..PN
00....A
01....B
01....C
02....D
03....E
03....F
01....G
00....H
01....I
02....J

I can get the first step to completeing the new table of parent/child relationship like this:

PN....00....01....02....03
.A.....A
.B....."".....B
C......"".....C
D......"".....""......D
E......"".....""......"".....E
F......"".....""......"".....F
G......"".....""......""....G
H......H
I......""......I
J......"".....""......J

Where "" are null values

My problem is making it so that every part number has its own array, aka [family tree], so that for part "F" the table would hold "A,C,D,F"

I though an update query where for example:
*"Zero" is a single field string.

IIF([00]<>null(), Save [00] to Zero, Write Zero to [00])

Would work but have been unable to find any information about passing a field value to a variable for later use or comparison. I would have already done this in excel if the table had less that 65000 records.


Example of final table:

PN....00....01....02....03
.A.....A
.B.....A.....B
C......A.....C
D......A.....C......D
E......A.....C......D.....E
F......A.....C......D.....F
G......A.....C......D....G
H......H
I.......H......I
J.......H......I......J

Any help is greatly appreciated.
BsK

View 1 Replies View Related

Forms :: Saving Specific Data From Data Entry Form Depending On User Selection

Dec 18, 2014

I am trying to save specific data from a data entry form to a specific depending on what the user selects from a dropdown/combo box.

So if the user selects "test company from the combo box, that data (from that form) will save to the Test company data table.

View 5 Replies View Related

Refreshing A Data Entry Form After Saving Data

Jul 6, 2005

This has most definitely been asked before but I really cant find anything on the forum! Ive created a data entry form which has a save button which the user must click each time to save the record. However how do I refresh the form i.e. empty all data from the previous record without exiting the form. Ive tried to combine the undo macro but that simply deletes the previous record. Any help please!!!

View 3 Replies View Related

Saving Data

Oct 12, 2006

I have a form "release_details" having fields date, version, cksum ,comments,labels, and is link to a table,
it has a button "mai"l ,on clicking this button a new form is open which has a button "send mail" on clicking this a mail is sent and pops a message "mail sent".I need when "send mail" is click it should also save the fields of form
"release_details" to the table.

Thanxx

View 6 Replies View Related

Saving Data

Oct 5, 2007

Hello, I'm new to this whole microsoft access program, so I'm learning from scratch. My question is I can't figure out how to save the database, so that I can go back into it later. This is a tutorial that I'm working off of in my online class, so once I exit out and go back in, all the info that I inputed is gone.

View 2 Replies View Related

Saving Data On A Form

Oct 7, 2005

Is there a simple way to make sure all fields on a form are filled before the data is saved to the table? I looked into using thew IF is Null Then but for 30 fields that is alot of code. Does anyone know a easier way?

Jim

View 3 Replies View Related

Copy Of Data Not Saving

Jun 14, 2005

This is going to be a hard one...

We recently switched over to A2003. As far as my db everything went ok except for one thing.

I have a continuous form that we enter data into. When you open a new record the form only displays one empty row. Because we enter tons of rows that come from an AS400 system I made a button on the top of the form that switches from continuous form view to datasheet view.


Private Sub Label59_Click()
Me.[Part #].SetFocus
DoCmd.RunCommand acCmdSubformDatasheet
End Sub


Within datasheet view I am able to paste many rows at once. However, after the change to A2003 it still "appears" to work. Many rows are pasted in but if you exit the db and come back to the record all of the data is lost and you are back to one blank row in continuous form view.

When a record is added to the continuous form each record is assigned an autonumber primary key. I think that this is where it is failing. Within Access help is the following under "Cutting And Copying Data", "Copy or move records or data from multiple fields to a datasheet": To paste the data as new records at the end of the datasheet, click Paste Append on the Edit menu.

I think this is what I need to do but I don't allow my users access to any menus so I create command buttons or auto running code in place of menu items that they need.

Can anyone think of some code to run a Paste Append of these records or of any other method in which I might restore the multiple paste funtionality I had with A2K?

View 12 Replies View Related

Saving Data With A Form

Jun 7, 2006

What's Up,

Got a quick question, is there a way to have a field in a subform update a field on the primary form's table? In other words, enter the data once and it stores it twice, on the subform table and the primary form table.

Thanks for any help.

Trey

View 1 Replies View Related

Prevent Saving Bad Data

Oct 29, 2004

My boss wants to add his own job numbers, so I made the JobID field a number field that he can enter (instead of autonumber, which is obviously preferable). So, in order to prevent him from entering a duplicate primary key (JobID), I have the following code in the BeforeUpdate event:

Code: Dim dbRoofing As DAO.DatabaseDim rcdJobs As DAO.RecordsetDim intJobID As IntegerSet dbRoofing = CurrentDbSet rcdJobs = dbRoofing.OpenRecordset("tblJobs")intJobID = Me!JobIDrcdJobs.FindFirst "JobID=" & intJobIDIf rcdJobs.NoMatch = False ThenMsgBox "This job number already exists.", vbOKOnly, "Invalid Job Number"Me.JobID = ""Exit SubEnd If
with the 'Me.JobID = ""' in there to set JobID back to nothing so he can't save a duplicate. However, Access won't let me do this, and when a duplicate is added, all sorts of errors pop up. How do I prevent him from saving a duplicate job number, or more generally, how do I prevent certain data from being saved based on other data?

Thanks

-Jason

View 7 Replies View Related

Trouble Saving Sub Data Sheet Name

Jan 21, 2008

Hi

I always feel as though I'm asking a stupid question but here goes. I am making a database with sub tables and sub forms, and working from "Access 2003 inside out". When I open table properties and enter a subdata sheet name and fill in child and master links everything seems fine until I try to save it. I open the table properties window again and the sub data sheet option has returned to "auto". Am I doing something stupid or is there a bug in my system?

View 2 Replies View Related

Saving Form Data Before Going To Subform

Jun 29, 2006

And another question...

In my database I have a button on a form which leads to a subform popping up. On the subform popping up, I display data from the first form. However, unless I first save the original form and then go to the subform, the data isn't passed on. How can I make the button both open up the subform and save the form so that the data is passed on?

Again, any help is much appreciated.

View 1 Replies View Related

Help With Saving Form Data To Tables

Jan 24, 2007

Hello,

I have a form with only 2 fields. One field is a drop down (Yes, No, N/A). The other field reads the first field and then assigns a value using an IIF statement (If No, then 0, otherwise 1).

I want to run a query on this data, but when I do, the 2nd field's result doesn't appear in the query (nor the main table).

How do I save/record the result from the 2nd field in the form to the table?

View 2 Replies View Related

Forms :: Saving Data From A Form

Apr 11, 2013

I have created a form with data pulled from tables and queries, I need a way to create a submit button and have it saved to a table I have created. and also clear the data from the form, also to give a warning if not all fields have been filled in. the Form is frmTM and the table that I need it sent to is Team Member Data.

View 1 Replies View Related

Modules & VBA :: Loops Saving Data

Nov 19, 2014

Having problem with loops. The inner loop updates a table. The outer loop pulls the record number from the "tblChangeOrderTable_Edit_Count" and is assigned to strRecordID . The inner loop uses strRecordID to find the right record. I keep getting errors like (Object variable or With Block variable not set.)

Code:
Private Sub btnClose_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim db As Database, rs As Recordset, rs1 As Recordset2
Dim Criteria As String
Dim strAns1 As String
Dim strAns2 As String
Dim strCount1 As String
Dim strAns3 As String

[code]....

View 12 Replies View Related

Check Boxes Saving Log In Data?

Mar 20, 2014

What I would like to happen is when either of the check boxes are checked it will either save the username and password or just the username, something like if you would log in to facebook and it asks do you want it to remember you log in details that's basically what I would like to have.

View 1 Replies View Related

Saving Data In Access That Has Been Filtered

Apr 14, 2014

I have a large database with over a million entries. I am trying to filter certain years (04-12) and certain states (western US). After the two filters are on my entries are down to 328,000. Now, I want to save the 328,000 to a new database.

View 1 Replies View Related

Saving A Form As A Data Access Page

Apr 16, 2007

Hi

I want to save an open form as a data access page. Is there any code to do this and bring me directly to the file location dialog or do I have to use the DoCmd.RunCommand acCmdSaveAs command and then choose data access page from the dialog box?

Also, is there a way of saving a form as a data access page, without opening the form?

Many thanks

AL

View 1 Replies View Related

Date Format Changing When Saving Data

Jan 19, 2004

Hi,
I'm using a web based form to store dates (and other records) in an access database. I use the dd/mm/yyyy date format (europe. go figure)
Anyway, the problem is that when submitting an ambiguous date, as in 11/01/2004, access sees it as 01-NOV-2004 rather then 11-JAN-2004, as it should.

Why does this happen, and how can I fix it. I have no problem doing the fixing in the access file itself or in the SQL command that populates it- whatever works best.

Thanks,
-Ethan

View 5 Replies View Related

General :: How To Disable Textbox After Saving Data

May 6, 2014

I have some field in my details section,It works like a grid view,if I search data by query then It shows all of the related data from database,then I edit or save data one by one,but the problem is I want to disable text box after edit/save data.But if I write code like--

If Me.Text103 > "" Then
Me.Text103.Enabled= False
Else
Me.Text103.Enabled= True
End If

It disable the all of the rows,but I want to disable only the specific field where I enter the data.

View 4 Replies View Related

Problem Saving Data Entered In Form (db Attached)

Aug 3, 2006

Can someone go through my forms and help me figure out why I cannot submit the entered data. Whenever I try to test my system and enter in inspection information I get an error that says....

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data and try again."


Steps:

1.) Type the given sample account number in the blue box
2.) Click "Search Acct #" button
3.) Click "Test" button
4.) Enter random info on the test form
5.) Read error message
6.) Help Ken_C :D

Thanks in advance :cool:

View 4 Replies View Related

Duplicate Data Error While Saving An Edited Record

Jan 8, 2005

Having a problem when saving a record that has been edited and contains a duplicate field. Here is what I'm doing.

I have an existing record that is being viewed by the user. I have an edit button on the form that is displaying the record. When the user clicks the edit button I do the following

'User clicked on edit customer record

Private Sub CustEditRec_Click()

'Store current customer key in string so we can cross

'check if user has changed the key during edit

EditCustKey = Me.txtCustomerKey

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'Set customer record test string so we can determine

'what the user is doing

CustomerRecStat = "edit"

' Go unlock the customer data fields for editing

UnlockCustomerFields

TxtCompanyName.SetFocus

'Go Unlock the customer editing buttons

UnLockCustomerAddButtons

'Lock the add, delete, edit buttons

CustAddRec.Enabled = False

CustEditRec.Enabled = False

CustDelRec.Enabled = False

'Go Disable the customer navagation buttons

DisableCustomerNavigation

txtCustomerKey.Visible = True

txtCustomerKey.Locked = False

txtCustomerKey.Enabled = True

'Set focus on the customer key

txtCustomerKey.SetFocus

'disable & hide the customer key combobox

cmboCustomerKey.Locked = True

cmboCustomerKey.Enabled = False

cmboCustomerKey.Visible = False

Exit_CustEditRec_Click:

Exit Sub

Err_CustEditRec_Click:

MsgBox Err.Description

Resume Exit_CustEditRec_Click

End Sub

The field which duplicate entries are not allowed in the table is txtCustomerKey. Now remember we are just editing a record NOT ADDING A NEW ONE.

When the user finished making the changes to the record we use the same procedure to save the changes as we when the user is adding a new record...here it is.

'User clicked save customer record

Private Sub CustSaveRec_Click()

On Error GoTo Err_CustSaveRec_Click

SaveCustomerRecord:

'Update the table data fields with the data contained on the form

CustomerKey = Me.txtCustomerKey

CustomerCompany = Me.TxtCompanyName

CustomerFirst = Me.txtCustomerFirst

CustomerLast = Me.txtCustomerLast

CustomerAddress = Me.txtCustomerAddress

CustomerCity = Me.txtCustomerCity

CustomerProvince = Me.txtCustomerProvince

CustomerPostal = Me.txtCustomerPostal

CustomerCountry = Me.txtCustomerCountry

CustomerPhone = Me.txtCustomerPhone

CustomerFax = Me.txtCustomerFax

CustomerEmail = Me.txtCustomerEmail

CustomerWeb = Me.txtCustomerWeb

CustomerNotes = Me.txtCustomerNotes

'Save the record

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'*** IF WE GOT THIS FAR WITH OUT ERRORS WE SAVED THE RECORD

'*** GO AHEAD & DISABLE THE FORMS VARIOUS FIELDS

'*** & BUTTONS ONCE AGAIN AS WE ARE JUST BACK TO VIEWING

'*** THE CUSTOMERS DATABASE

'Enable and unlock the customer key field

txtCustomerKey.Visible = True

txtCustomerKey.Locked = False

txtCustomerKey.Enabled = True

'Hide & disable the customer keycombo box

cmboCustomerKey.Locked = True

cmboCustomerKey.Enabled = False

cmboCustomerKey.Visible = False

'Set focus on the customer key field

txtCustomerKey.SetFocus

'Lock the customer fields

LockCustomerFields

'Enable the navigation buttons

EnableCustomerNavigation

'Lock the customer adding buttons

LockCustomerAddButtons

'Clear the record testing status

CustomerRecStat = ""

txtCustomerKey_AfterUpdate

Exit_CustSaveRec_Click:

Exit Sub

Err_CustSaveRec_Click:

'If the error generated was by a duplicate value.

'This can only be caused by the customer key as this

'is the only field which does not allow duplicate values.

'so warn the user of this duplicate value error and set

'the focus on the customer key field

If Err.Number = 3022 Then

'if user is editing a record

If CustomerRecStat = "edit" Then

'And the entered customer key has not changed

If Me.txtCustomerKey = EditCustKey Then

'Return to saving the record as the key is

'not really a duplicate

GoTo SaveCustomerRecord

End If

End If



Select Case MsgBox("This Customer ID was already located in the database. Click OK to enter a new Customer ID or Cancel to stop adding this record?", vbExclamation + vbOKCancel + vbDefaultButton1, "Duplicate Customer ID")

Case vbOK

Me.txtCustomerKey.SetFocus

Resume Exit_CustSaveRec_Click

Case vbCancel

'Go simulate undo record click

CustUndoRec_Click

Resume Exit_CustSaveRec_Click

End Select

End If

MsgBox Err.Description

Resume Exit_CustSaveRec_Click

End Sub



The problem is when the user is editing a record. The database assumes the txtCustomerKey is a duplicate in the table. However we are not adding a new record so the duplicate error is false. Its just that the txtCustomerKey is the same as the record being edited. Its not DUPLICATED its the SAME.....



Any help anyone? Sorry for the long post but I'm a strong believer in the more information the better when trying to solve a problem....

Thx

Kao

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved