Setting on click event of a control in VBA

[ziad]

dears
hope you are fine, i am trying to create appointment using on click event in vba, i decided to create a form , writing all codes in design view, then, a record set to read input from a table, then, for each input , i create a button to be placed in a graph, based on the time it was scheduled and a certain date, it took me time but finally i managed to create a dynamic form, the start of the form is the first appointment and the end is the last one, long story short, i am stuck now, on clicking on the specific button , to be able to open the appointment again , change it or confirm it.

here are the steps of the code.

Set BSS = db.OpenRecordset("select * from ConsultationScheduletbquery)

BSS.MoveFirst



Do Until BSS.EOF

 Set ctl = CreateControl("form3", acCommandButton, acDetail, , , f, 1440 + n, BSS!txttimediff * 24, 700)

f and n to control the position of the buttons

 ctl.Caption = BSS!FullName

IDfrromapprovedsession =BSS!IDfrromapprovedsession

 IDfrommain=BSS!IDfrommain

studentID=BSS!studentID

i want the click to a specific button to open form as per the below with the below where condition.

ctl.OnClick = "docmd.OpenForm(ApprovedSessionsTbQueryform where IDfrromapprovedsession = 25 and IDfrommain=443 and studentID=121)"  these numbers are obtained from the BSS move next,

each button has on click different than the other one.
please check the attached pictures.

any proposal please?
the highlighted one on the table is the same as the one on the from and the same as the one on the graph.

in another word, how to program when we create a button from Set ctl = CreateControl"form3", acCommandButton , and dedicate for each button a special docmd.openform "MyForm" where studentID=anynumber and idfromMain=anynumber and IDfromapprovedsession=anynumber?
your support is much appreciated

 

[Edgar_]

@ziad
Creating controls on the fly will eventually render your form useless, I suggest you create a new thread for that because it's a very different topic. Moved by UG

 

[MajP]

Do not create controls. Start with lots of hidden controls. Name them consistently example cmd1, cm2..
Each should have a onclick property being a single function. Like =openform(). Then you show controls as needed. Move into place. Then add tag like 25;443;121
Trap the event and split the tag. Open as needed.

 

[MajP]

This is more complicated than yours but general idea.
Post in thread 'User made command button that can be moved with mouse?' https://www.access-programmers.co.u...t-can-be-moved-with-mouse.329403/post-1897600

 

[Pat Hartman]

i managed to create a dynamic form, the start of the form is the first appointment and the end is the last one, long story short, i am stuck now, on clicking on the specific button , to be able to open the appointment again , change it or confirm it.

Since your requirement is for a maximum fixed number of appointments, perhaps this example would help:

Bound Denormalized Form

I've left the old versions here but ALWAYS go to the last post to get the current version. This database contains two examples (using the same technique) of how to take data that is properly normalized and display it so that it looks like a spreadsheet (rows displayed as columns) but is...

www.access-programmers.co.uk

There are things Access can do using VBA which are ultimately dangerous and will cause errors as Maj has pointed out. Just because you can code these things doesn't mean that you should. Essentially, if Access can't do it "naturally", you need to think very hard about whether Access is the correct platform for your app or find a "natural" method that will work. I'm sure Maj's solution will work for you and I also know that the one I posted will too. Both are fixed width. That is their limitation. My solution was developed to work for a forecasting app which was limited to 12 months per year for each record. You have x slots per day. Same concept. Slightly different code. My queries used 1-12 as the hard coded value. Yours will use 1-x as the hardcoded value. My solution was developed because the user wanted to see all twelve months "across" as he was entering the forecast just as he did when he was using Excel. However, this is a relational database and to make everything else work correctly, the data needed to be properly normalized as one month per row. If you follow the logic, you will end up with one slot per row which is properly normalized but the user will be happy because the input form looks like a grid.

 

[GPGeorge]

Since your requirement is for a maximum fixed number of appointments, perhaps this example would help:

Bound Denormalized Form

I've left the old versions here but ALWAYS go to the last post to get the current version. This database contains two examples (using the same technique) of how to take data that is properly normalized and display it so that it looks like a spreadsheet (rows displayed as columns) but is...

www.access-programmers.co.uk

There are things Access can do using VBA which are ultimately dangerous and will cause errors as Maj has pointed out. Just because you can code these things doesn't mean that you should. Essentially, if Access can't do it "naturally", you need to think very hard about whether Access is the correct platform for your app or find a "natural" method that will work. I'm sure Maj's solution will work for you and I also know that the one I posted will too. Both are fixed width. That is their limitation. My solution was developed to work for a forecasting app which was limited to 12 months per year for each record. You have x slots per day. Same concept. Slightly different code. My queries used 1-12 as the hard coded value. Yours will use 1-x as the hardcoded value. My solution was developed because the user wanted to see all twelve months "across" as he was entering the forecast just as he did when he was using Excel. However, this is a relational database and to make everything else work correctly, the data needed to be properly normalized as one month per row. If you follow the logic, you will end up with one slot per row which is properly normalized but the user will be happy because the input form looks like a grid.

You will find a video of Pat's presentation on her method to our Access User Group.

 

[Pat Hartman]

Thanks George. I forgot about that video. I added a link to the AWF article.

 

[GPGeorge]

Since your requirement is for a maximum fixed number of appointments, perhaps this example would help:

Bound Denormalized Form

I've left the old versions here but ALWAYS go to the last post to get the current version. This database contains two examples (using the same technique) of how to take data that is properly normalized and display it so that it looks like a spreadsheet (rows displayed as columns) but is...

www.access-programmers.co.uk

There are things Access can do using VBA which are ultimately dangerous and will cause errors as Maj has pointed out. Just because you can code these things doesn't mean that you should. Essentially, if Access can't do it "naturally", you need to think very hard about whether Access is the correct platform for your app or find a "natural" method that will work. I'm sure Maj's solution will work for you and I also know that the one I posted will too. Both are fixed width. That is their limitation. My solution was developed to work for a forecasting app which was limited to 12 months per year for each record. You have x slots per day. Same concept. Slightly different code. My queries used 1-12 as the hard coded value. Yours will use 1-x as the hardcoded value. My solution was developed because the user wanted to see all twelve months "across" as he was entering the forecast just as he did when he was using Excel. However, this is a relational database and to make everything else work correctly, the data needed to be properly normalized as one month per row. If you follow the logic, you will end up with one slot per row which is properly normalized but the user will be happy because the input form looks like a grid.

You will find a video of Pat's presentation on her method to our Access User Group.

Thanks George. I forgot about that video. I added a link to the AWF article.

Thank you for the presentation.

 

[Pat Hartman]

You're welcome. Always glad to share