Solved A Parent and Child Form for data entry (1 Viewer)

silversun

Registered User.
Local time
Yesterday, 23:18
Joined
Dec 28, 2012
Messages
122
Hi everyone.
I am about to start a simple project to save all data from my grocery shopping and/or Gas, Millage and car services in a 2016 MS Access Database.
I know some VBA or none-VBA data manipulating in Access. What I am not sure how to do is here:
A data entry form saves data in related table automatically [I don't like it :( !!! ] as you type them in and if there was error and user wanted to correct it then it is a complicated task (we'll also lose some Primary Key #'s).
Let's say I have a form that shows a supermarket info on top and a list of items on bottom. Number of the items in this list is variable from each shopping invoice.
How do I create a form that holds any number of data (rows)?
Once user verified all data then using a "Save" button user can save them in related tables, for example, tbl_store to save the store's data and tbl_merchandises to save details about each item.

We all know there should be some relationships between the tables and let's assume that part is done properly for now. If I had difficulties in creating Relationships then I will ask.
I hope I've presented my thread clearly.
Thank you for your time.
 

theDBguy

I’m here to help
Local time
Yesterday, 23:18
Joined
Oct 29, 2018
Messages
10,676
Hi. One advantage of using Access for database development is it provides certain "automatic" functions to make the development faster. When you create a form bound to a table, you don't have to create a "Save" button to click it just to save the record you just entered into the table. To most, this is a benefit. To others, this is an inconvenience. If you're okay with this automatic feature, all you need to do is build a main form and subform setup. The main form is a single view form showing information about each supermarket, and the subform is a continuous view or datasheet view displaying all the items purchased. Both information will be automatically saved into their respective tables.

On the other hand, if you prefer not to have the "save" operation done for you automatically, then you can try using unbound forms. However, unbound forms cannot display multiple rows of records. If you want that, then you can use a temporary table. At this point, using your own Save button will give you all the control you need to only save the data you want to keep (but it will be more work for you).

Cheers!
 

strive4peace

AWF VIP
Local time
Today, 01:18
Joined
Apr 3, 2020
Messages
518
hi silversun,

adding on ...

> " user verified all data then using a "Save" button "

when using Access, bound forms is the way to go! If you're connected to a big back end and concerned about performance, you can set criteria for the Record source to just get what you want to pull.

Most important is data structure ... how tables, fields, and relationships are designed. If they're done well, than you can build forms with subforms to enter data into related tables.

When using a bound form, to synchronize the information, you can use LinkMasterFields and LinksChildFields on the subform control. This will make it so that only related records show up in a subform, and if you make a new record there, the link fields will automatically get values from the parent record.

I'm with theDBguy on using bound over unbound forms. so no need for a Save button -- but perhaps you might want an Undo button ...

> " How do I create a form that holds any number of data (rows)? "

you can use a continuous form, or a datasheet form
 

strive4peace

AWF VIP
Local time
Today, 01:18
Joined
Apr 3, 2020
Messages
518
oh, ps, silversun ... on Save ...
reason there is no need for a Save button is because, when using bound forms (RecordSource is specified), Access automatically saves a record when you leave it ... or manually save, or save using a macro or VBA.

The hard part is Undo!
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 07:18
Joined
Sep 21, 2011
Messages
5,949
I am a little confused as to your syntax?

One moment it is I want to', then it is a 'user wanted'?

Having a DB for myself, I will accept limitations and not add unneeded code as I know better on how to use it, but if I am to give a DB to users then I will do much more work to attempt to make it bullet proof?

What is your true situation?
 

plog

Banishment Pending
Local time
Today, 01:18
Joined
May 11, 2011
Messages
9,884
if there was error and user wanted to correct it then it is a complicated task
How so? With a bound form (automatically saving) the user identifies the bad data and corrects it. With an unbound form (manual saving) the user identifies the bad data and corrects it and clicks the save button. Unbound forms require less work to correct data than unbound forms.

(we'll also lose some Primary Key #'s).
Who cares? Did you buy the version of Access that charges per autonumber? My version is the unlimited plan. Further, an autonumber primary key's sole purpose is to be a unique identifier. If you're using it for more than that then you are doing it wrong.
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:18
Joined
Sep 21, 2011
Messages
5,949
Unbound forms require less work to correct data than unbound forms.
Come on people, surely a copy and paste error?,:) the sentence does not even make sense, plus it's Plog?
Correct the post, as I tend to do, if you think it is wrong, but @isladogs has the idea?
 

plog

Banishment Pending
Local time
Today, 01:18
Joined
May 11, 2011
Messages
9,884
Sorry. Strive is right on the issue and right for clarifying it. I did mistype it and meant to say bound forms are easier.

Now that we've had further posts referencing it I'm not going to edit it.
 

strive4peace

AWF VIP
Local time
Today, 01:18
Joined
Apr 3, 2020
Messages
518
Strive is right on the issue and right for clarifying it. I did mistype it and meant to say bound forms are easier.
thanks! I thought it was odd you'd say that ... typos get us all!
 

silversun

Registered User.
Local time
Yesterday, 23:18
Joined
Dec 28, 2012
Messages
122
Thanks all you guys for discussions. I am going to do this project with bound forms and if the issue came up and I did't know how to solve it I will ask you. I am working on tables and relationships now.
When I said user(s) I meant person who is entering data either myself or my family members. A good application should be user friendly no matter who the user is.
Thank you all
 

theDBguy

I’m here to help
Local time
Yesterday, 23:18
Joined
Oct 29, 2018
Messages
10,676
Thanks all you guys for discussions. I am going to do this project with bound forms and if the issue came up and I did't know how to solve it I will ask you. I am working on tables and relationships now.
When I said user(s) I meant person who is entering data either myself or my family members. A good application should be user friendly no matter who the user is.
Thank you all
Hi. Good luck! We'll be here if you need us. Cheers!
 

silversun

Registered User.
Local time
Yesterday, 23:18
Joined
Dec 28, 2012
Messages
122
Hi everybody.
As you all recommended I started my project with bounded forms. I created an invoice form that shows details of the store on top along with the transaction's date. I also created a sub-form that is supposed to list all the items in that transaction. Having said that I am working on a data entry form rather than viewing data. I have a combobox for selecting any item from the list to make it easier and faster to enter data.
The problem is if for example I select coffee in first row of sub-form, in the next row whatever I select then the first row shows the same item too (looks like it takes its value from previous entry) and even in third row if I select eggs in my combobox all previous items change to eggs.
1592093210950.png
Even when I switched from Datasheet View to Continuous in my sub-form I had the same issue. I also had my form formatted as Data Entry form.

Please see the attached image and tell me how I can solve this issue.
Thank you
 

theDBguy

I’m here to help
Local time
Yesterday, 23:18
Joined
Oct 29, 2018
Messages
10,676
Hi everybody.
As you all recommended I started my project with bounded forms. I created an invoice form that shows details of the store on top along with the transaction's date. I also created a sub-form that is supposed to list all the items in that transaction. Having said that I am working on a data entry form rather than viewing data. I have a combobox for selecting any item from the list to make it easier and faster to enter data.
The problem is if for example I select coffee in first row of sub-form, in the next row whatever I select then the first row shows the same item too (looks like it takes its value from previous entry) and even in third row if I select eggs in my combobox all previous items change to eggs.
View attachment 82776
Even when I switched from Datasheet View to Continuous in my sub-form I had the same issue. I also had my form formatted as Data Entry form.

Please see the attached image and tell me how I can solve this issue.
Thank you
Sounds like your combobox is unbound. What is the Control Source property of the Combobox?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:18
Joined
Feb 19, 2002
Messages
28,992
A data entry form saves data in related table automatically [I don't like it :( !!! ] as you type them in
I'm glad the others have convinced you to to with bound forms. Access is a RAD (Rapid Application Development) tool and therefor it does a lot of stuff for you. That's the good and the bad of it. If you are not willing to allow Access to do the things it is designed to do, you really should not be using Access at all. You would get no benefit and cause yourself an excessive amount of work. Go with a different platform that lets you do things the way YOU want to do them. The downside of that is, it will take you a minimum of 5 times the amount of time it would take to create the same app in Access when you go with the flow.

Once you understand how form events work, you will discover that you have absolute control over when and If rows get saved. When you are using an autonumber as your PK, you WILL lose autonumbers if you don't complete a record or if you later delete one. That is NO PROBLEM since autonumbers are for internal use only and if you are assigning some other meaning than a simple unique ID, you will be disappointed. There are other methods if you want a sequential number that does not have gaps, ever. For starters, you can't ever delete records since that would leave a gap. Otherwise, you can generate your own sequence number.

Regarding the "duplication" of the Item value, that control is not bound. Once you bind the control, you will not see duplicates unless the user has entered the same item more than once.
 

silversun

Registered User.
Local time
Yesterday, 23:18
Joined
Dec 28, 2012
Messages
122
Sounds like your combobox is unbound. What is the Control Source property of the Combobox?
@ theDBguy: The Control Source of that Combobox is empty. I am not sure what should be there as Control Source. As far as I know the Row Source is where the list is taking values from and Bound Column is the column number that its value returns. Please explain.
Thank you
 

silversun

Registered User.
Local time
Yesterday, 23:18
Joined
Dec 28, 2012
Messages
122
I'm glad the others have convinced you to to with bound forms. Access is a RAD (Rapid Application Development) tool and therefor it does a lot of stuff for you. That's the good and the bad of it. If you are not willing to allow Access to do the things it is designed to do, you really should not be using Access at all. You would get no benefit and cause yourself an excessive amount of work. Go with a different platform that lets you do things the way YOU want to do them. The downside of that is, it will take you a minimum of 5 times the amount of time it would take to create the same app in Access when you go with the flow.

Once you understand how form events work, you will discover that you have absolute control over when and If rows get saved. When you are using an autonumber as your PK, you WILL lose autonumbers if you don't complete a record or if you later delete one. That is NO PROBLEM since autonumbers are for internal use only and if you are assigning some other meaning than a simple unique ID, you will be disappointed. There are other methods if you want a sequential number that does not have gaps, ever. For starters, you can't ever delete records since that would leave a gap. Otherwise, you can generate your own sequence number.

Regarding the "duplication" of the Item value, that control is not bound. Once you bind the control, you will not see duplicates unless the user has entered the same item more than once.
@Pat Hartman:
I think once I feel comfortable enough with access and all features involve in a DB then I will switch to a different platform. For now I continue working with Access and some VBA. I will ask more questions about automatic features when I have my project being tested.
Thank you for clarifications anyway.
 

theDBguy

I’m here to help
Local time
Yesterday, 23:18
Joined
Oct 29, 2018
Messages
10,676
@ theDBguy: The Control Source of that Combobox is empty. I am not sure what should be there as Control Source. As far as I know the Row Source is where the list is taking values from and Bound Column is the column number that its value returns. Please explain.
Thank you
Hi. I thought so. What do you want to do with the value selected from the dropdown? If you want to store it in your table, then you would select the field name for the control source property.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom