Saving Data from Form to Table

[Mahendra]

Hello All,

Am a Novice to MS Access. I just created a demo sample (Let me explain in detail)

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 quareters

Let me know how can I sort it out?

 

[wim1981]

Hi Mahendra,

are you sure that the types of data in the textboxes and these in your table are the same?
You can use also plain variables to query an insert or update to your table.
The variables you fill as follow:

Dim variable As Type
TextBox.SetFocus
variable = TextBox.Text
Afther that you do your query

wim

 

[Mahendra]

Hello Wim,

Thanks for the response from your end.

Do we need to write a query for that?

How can we link the Month, Year and Quarter fields from FORM to HEAD table?

As in the control sources of those fields I have writtem some conditions for that

 

[wim1981]

If you want all these fields to store in the same table, then you can insert them with the ac.. options in the DoCmd statement. https://msdn.microsoft.com/en-us/library/office/ff192694.aspx

You have to first bound your fields to the table

Wim

 

[wim1981]

To bound the form to a table you need to set the RecordSource to a query that you first make with the query design.
When your recordsource is set, you can select for each Textbox the source wich will be the names of the columns in your table.

Wim

 

[CJ_London]

End and Year are reserved words and using them can have unpredictable results. In addition, month is a function. Putting them in square brackets will often solve the problem, but not always. I strongly recommend you change End to endDate or similar.

here is a list of reserved words

https://support.office.com/en-nz/ar...-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe

since you are calculating Month, Year and Quarter on your form, why do you need them in your table? If you do, change the field name for Month and Year to something like endMonth and endYear (and endQuarter for consistency)

If you want to update the table then you need to use an update query, you would not use a form in this way. But if you did, then you would set the control sources to the relevant field name and put the following code in your enddate control afterupdate event or the form beforeupdate event

endYear=Year(endDate)
endMonth=Month(endDate)
endQuarter=((Month([endDate])-1)\3)+1

but this will only work if you enter your endDates on the form

You need to understand the difference between fields, controls and properties. fields relate to tables whilst controls relate to forms and reports.

Both of these have properties. e.g a field property might be its name, a validation rule, it's datatype etc - as you see in the table builder, and controls will have many more properties as you will see in the form designer property sheet.

Controls can be bound to a field - the controlsource property will have the name of a field in your form recordsource (like your current endDate control) or left unbound - in which case the controlsource will either be blank or contain a formula such as you have used. They can have the same name (if you let access create your forms, they will be the same).