I have a form based on a table with about 15 fields. Based on user input, I need to keep adding records. It could be say 5, 10 or 20 records in a grid like display on the form.
Out of the 15 fields, some 8 need to be in the grid like interface as they may be repeating values. The rest 7 fields will be just one time entry.
Is this possible in Ms-Access 2010 that I'm using now ?
Your question is not clear. To clarify, my understanding of your question is that you want to enter 10 records into a table of 15 fields. However you have a lot of duplicate data in that you have possibly 8 fields in the 10 you are filling which will have the same data. You would like to input this data once on the form, then have it distributed to each of the 10 records.
If that's a correct interpretation of your question, then it is possible. However what you describe would suggest that there is something very wrong with the structure of your database. You must provide more information about what you are doing, because there will be likely be a far better way, which will make things easier in the long run...
As has been stated, your explanation is somewhat unclear, but if you're meaning that some of the Fields in your Records will be repeated, for a number of Records, and you don't want your users to have to keep re-entering this same data, Record after Record, you can use the AfterUpdate event of the Control holding your data to set the DefaultValue for the Field. From that time forward, until you either manually change the data or close your form, the data will be entered automatically in each New Record.
Code:
Private Sub YourControlName_AfterUpdate()
Me.YourControlName.DefaultValue = """" & Me.YourControlName.Value & """"
End Sub
This syntax is valid for Text, Number, DateTime and Boolean Datatypes.
You’ll need to do this for eachControl that you want to ‘carry forward.’
If you have a slew of Fields, you can use the Tag Property to mark the ones to be defaulted and then loop through them, setting the DefaultValue.
Go into Form Design View
Select all Controls you want to 'carry forward,' by pressing and holding down the <Shift> Key and clicking on each one.
Go to Properties - Other
In the Tag Property enter CarryForward (without quotes)
Now use this code:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "CarryForward" Then
ctl.DefaultValue = """" & ctl.Value & """"
End If
Next ctl
End Sub
Once again, each of the "tagged" Controls' values will be carried forward into a New Record until you either Edit the data in a given Control or Close the Form or Access itself.
Having identical data, for certain Fields, in Record after Record, can be a sign of a non-normalized design, as has been previously expressed, but at times it can be appropriate. An example would be if user were entering customer contacts made during a given day: the date would be repeated, for X number of Records, as would the agent making the contacts.
I have a similar question to ask to with adding multiple records into an access database using the form method.
I have x360 records to add to an asset management database.
The static control fields for the database are
Description
Make
Model
PO Number
Date of Purchase
Location
The primary key and unique identifier for the x360 records is the serial number of the device.
Please could you advise on how to create the x360 records and method for entering the serial number criteria.
MS Access 2016
I have a similar question to ask to with adding multiple records into an access database using the form method.
I have x360 records to add to an asset management database.
The static control fields for the database are
Description
Make
Model
PO Number
Date of Purchase
Location
The primary key and unique identifier for the x360 records is the serial number of the device.
Please could you advise on how to create the x360 records and method for entering the serial number criteria.
MS Access 2016
