Solved A Parent and Child Form for data entry

[silversun]

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]

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]

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]

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!

 

[Gasman]

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]

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.

 

[strive4peace]

Unbound forms require less work to correct data than unbound forms.

Sorry but I must disagree ... this is opposite of the truth. It is much easier to bind data

 

[isladogs]

Unbound forms require less work to correct data than unbound forms.

I think you meant

bound forms require less work to correct data than unbound forms.

 

[Gasman]

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]

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]

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]

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]

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!

 

[strive4peace]

you're welcome ~ happy to help

 

[silversun]

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.

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]

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]

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]

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]

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]

@ 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.