By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 994 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

button command to add a new list

P: 2
Hello,

I want to add a function to a button so if I clicked on that button a new list appears and that list has some items on it.

e.g. I have a list with names of medicine for a patient
I need to add a button with "new medicine" as a name.
and when i click on it a new list with the same names of medicine as the first list appears and i can choose from it.

Thanks in advance for help. ^^
Jan 22 '17 #1

✓ answered by zmbd

Hello KillerEGY
Sorry this took so long, weekends tend to be for family time :)

> SO one answer to your original question, as both Phil and I have alluded to, is a Parent/Child form arrangement with a combobox

> A second answer is to add a command button on your form
- The code for the command button will open a form that can have a table or combobox with a record source pointing back to your table (or a hard coded list which will be a nightmare down the road to maintain)
- To get the selected medication back to the calling form; either, the code calling the medication form can then either have passed the current patient id to the medication from via either open arguments or by direct code and then the medication form makes data entries or the medication form can pass the selected information back to the calling form - I've done things like this in the past; however, these solution can be come fairly complex in a very nasty and way very quickly!

By far, I suspect the first answer to be the easier to implement...

SO, attached is a very simple example of what both Phil and I are talking about in that first solution

This was built using Access2013 and may not open in versions earlier than Access2010

I've based this on a template that has people names and a word list, for the sake of simplicity, if we can, please pretend that the words are medications for this example (and with the names I've seen given to recent medications, this shouldn't be too much of a stretch of the imagination :-) )

1) On open the main navigation form will appear
- you may be prompted in Access2013 for security, as always, you should scan all downloaded content with your antivirus.
- there are no active VBA or Macro scripts
- Press [F11] once you've taken a look at the SIMPLE interface. I've used a two tier navigation button (THESE ARE NOT FORM TABS!) as an example of how one might group things for the end user

2) Three tables, there's a report that shows their relationships.
- Field names: PK_ = Primary Key, FK_ = Foreign key
- [tbl_Words2People]
-- this is a many to many type table
-- has an additional key
--- Open this table in design view
--- In the Design context ribbon, SHOW/HIDE, Indexes
--- You will see the primary key
--- You will see the key [PrescribedKey]
Note the single Index name with three field names in the group. This key is setup to prevent assigning the same word to the same person on the same day. I've added a few records for you to see this

The relationships are set to enforce related records; however, I do not use the cascade options... that's a topic for another thread and one I've covered before if you want to search here on my user name and "cascade" for keywords.

3) One query [qry_Words2People_HumanReadable]
- Run this first so that you can see the dropdown type controls
If you open the table [tbl_Words2People] all you will see are the numerical values from the related tables.
- Open in design view:3
-- Right-click the [fk_people] column and select properties
--- select the lookup tab... this is the only place I recommend using the lookup field, in queries, unless one is using SharePoint tables.
--- Notice how I've set up the control. In this case I've hardcoded a query against the people table. You could also use a table or stored query
---> case in point, lift-click [FK_Words] (the property window should stay open) if not selected click on the lookup tab, I've opted to show a very simple example of using just a table. This provides very little control over how the data is presented in the dropdown list; however, if one has simple data to present then this can be an acceptable method.

4) The forms, notice that there is no VBA nor Macro scripting here for any of the forms, I've opted for simple for this example. Normally I would have code checking for dirty records etc...

+ [NavigationForm] is the one that opened to frm_100_parent_people

+ [frm_100_parent_people]
I would normally add some more obvious record and search controls to [frm_100_parent_people] for the end user.
- this is bound to table [tbl_people]
- something to note: Open this form up in design view
- show the property sheet
- in the property sheet dropdown select
"z_ctrl_ChildFormContainer"
I want to draw attention to this, I've renamed the subform container control. The default is to name it the same as the child form's name. This can lead to some confusion when trying to manipulate controls on the child from the parent. However, that's for another thread and you will many threads covering this topic here at Bytes.com

+[frm_101_Child_Words2People]
- this is bound to [qry_Words2People_HumanReadable]. I could have bound this to the table too. In this case it doesn't matter; however, I wanted to show that the form can be bound to queries too and that is a potentially very valuable tool!
>> VERY IMPORTAINT HERE
I've left the field [People] shown in the child form - this is not my normal design.
The reason I left this field is so that you can see that when you enter a new record into the child form that this field is automagically assigned to the current record in the parent form. There is NO need to update this information "by hand" in the [tbl_Words2People] table. For now I've simply set the control [Enabled]= No in the properties. I've also use a combo box here, because I wanted to keep the query simple for this example, so that you could see the name. Normally, this control wouldn't be on the child form.

+ The remaining forms should be fairly self explanatory.


(hopefully this hasn't cross posted... I've been building the database at the same time I've been typing the above... :) )

Share this Question
Share on Google+
5 Replies


zmbd
Expert Mod 5K+
P: 5,287
KillerEGY,
There is almost nothing to go on here to help you with...

If you can provide a much more detailed description of your database design, which version of Access/Office you are using, the nature of your application, and the context you're working with your thread might be salvageable.

FAQ: How to ask a question


BTW: Using a combobox with the rowsource property bound to the proper query/table can often provide the kind of list you seem to be asking for without a lot of programing; however, you lack enough details to help us help you.

One more thing, we're not a programing service, we do need information about what you've already tried and why it's not working as expected.
Jan 22 '17 #2

P: 2
Hello,

Thanks for replying.

I'm using office 2016 .. and I'm working on a programme for a dental clinic managment.

In the patient profile, I have a list with medicine that I can choose from the medicine I want for that patient.

when I save the patients profile, and he visit me again, I may add a new medicine for him.

so I want to add a button next to the first list with the name "new medicine" . so in the 2nd visit when he visit me I click on it to give me a new list that I can choose from. Then I will have 2 lists. and so on in every new visit.

I don't have knowledge about the programming codes and how to use it.

so I'm asking for the code that I can write it as a function of that button.

Can anyone help please?

Thanks in advance
Jan 26 '17 #3

zmbd
Expert Mod 5K+
P: 5,287
The first place to start is database normalization.
Properly normalizing your database from the start will help eliminate many major issues down the road.
home > topics > microsoft access / vba > insights > database normalization and table structures

So while you read through that, I'll see if I can put together something simple to show one way to accomplish what you are working on, it'll be a little bit later.

Also take a look at your Bytes.com>Inbox I'll forward a copy of my boilerplate with some resources that I hope will by useful for you in a few minutes
Jan 27 '17 #4

PhilOfWalton
Expert 100+
P: 1,430
Seems an odd way to do it.

I would have a main form for the patient, and a continuous subform for the medicines prescribed and the date prescribed.
For convenience, I would show those dates in reverse order (i.e. latest date and hence the most recent prescription on the top)

Phil
Jan 27 '17 #5

zmbd
Expert Mod 5K+
P: 5,287
Hello KillerEGY
Sorry this took so long, weekends tend to be for family time :)

> SO one answer to your original question, as both Phil and I have alluded to, is a Parent/Child form arrangement with a combobox

> A second answer is to add a command button on your form
- The code for the command button will open a form that can have a table or combobox with a record source pointing back to your table (or a hard coded list which will be a nightmare down the road to maintain)
- To get the selected medication back to the calling form; either, the code calling the medication form can then either have passed the current patient id to the medication from via either open arguments or by direct code and then the medication form makes data entries or the medication form can pass the selected information back to the calling form - I've done things like this in the past; however, these solution can be come fairly complex in a very nasty and way very quickly!

By far, I suspect the first answer to be the easier to implement...

SO, attached is a very simple example of what both Phil and I are talking about in that first solution

This was built using Access2013 and may not open in versions earlier than Access2010

I've based this on a template that has people names and a word list, for the sake of simplicity, if we can, please pretend that the words are medications for this example (and with the names I've seen given to recent medications, this shouldn't be too much of a stretch of the imagination :-) )

1) On open the main navigation form will appear
- you may be prompted in Access2013 for security, as always, you should scan all downloaded content with your antivirus.
- there are no active VBA or Macro scripts
- Press [F11] once you've taken a look at the SIMPLE interface. I've used a two tier navigation button (THESE ARE NOT FORM TABS!) as an example of how one might group things for the end user

2) Three tables, there's a report that shows their relationships.
- Field names: PK_ = Primary Key, FK_ = Foreign key
- [tbl_Words2People]
-- this is a many to many type table
-- has an additional key
--- Open this table in design view
--- In the Design context ribbon, SHOW/HIDE, Indexes
--- You will see the primary key
--- You will see the key [PrescribedKey]
Note the single Index name with three field names in the group. This key is setup to prevent assigning the same word to the same person on the same day. I've added a few records for you to see this

The relationships are set to enforce related records; however, I do not use the cascade options... that's a topic for another thread and one I've covered before if you want to search here on my user name and "cascade" for keywords.

3) One query [qry_Words2People_HumanReadable]
- Run this first so that you can see the dropdown type controls
If you open the table [tbl_Words2People] all you will see are the numerical values from the related tables.
- Open in design view:3
-- Right-click the [fk_people] column and select properties
--- select the lookup tab... this is the only place I recommend using the lookup field, in queries, unless one is using SharePoint tables.
--- Notice how I've set up the control. In this case I've hardcoded a query against the people table. You could also use a table or stored query
---> case in point, lift-click [FK_Words] (the property window should stay open) if not selected click on the lookup tab, I've opted to show a very simple example of using just a table. This provides very little control over how the data is presented in the dropdown list; however, if one has simple data to present then this can be an acceptable method.

4) The forms, notice that there is no VBA nor Macro scripting here for any of the forms, I've opted for simple for this example. Normally I would have code checking for dirty records etc...

+ [NavigationForm] is the one that opened to frm_100_parent_people

+ [frm_100_parent_people]
I would normally add some more obvious record and search controls to [frm_100_parent_people] for the end user.
- this is bound to table [tbl_people]
- something to note: Open this form up in design view
- show the property sheet
- in the property sheet dropdown select
"z_ctrl_ChildFormContainer"
I want to draw attention to this, I've renamed the subform container control. The default is to name it the same as the child form's name. This can lead to some confusion when trying to manipulate controls on the child from the parent. However, that's for another thread and you will many threads covering this topic here at Bytes.com

+[frm_101_Child_Words2People]
- this is bound to [qry_Words2People_HumanReadable]. I could have bound this to the table too. In this case it doesn't matter; however, I wanted to show that the form can be bound to queries too and that is a potentially very valuable tool!
>> VERY IMPORTAINT HERE
I've left the field [People] shown in the child form - this is not my normal design.
The reason I left this field is so that you can see that when you enter a new record into the child form that this field is automagically assigned to the current record in the parent form. There is NO need to update this information "by hand" in the [tbl_Words2People] table. For now I've simply set the control [Enabled]= No in the properties. I've also use a combo box here, because I wanted to keep the query simple for this example, so that you could see the name. Normally, this control wouldn't be on the child form.

+ The remaining forms should be fairly self explanatory.


(hopefully this hasn't cross posted... I've been building the database at the same time I've been typing the above... :) )
Attached Files
File Type: zip Bytesthread_968091.zip (137.9 KB, 33 views)
Jan 29 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.