Populating a Table With a Form (1 Viewer)

[themurph2000]

OK, for once I have a complicated one.

I have a form containing work order descriptions that are used to generate production orders on a daily basis. Right now, a person would have to click on a button on a continuous form next to the proper work order and fill in information like the date, the shift, and the person in charge in a separate form. Since it has to be done individually for each work order each day, each shift, it takes hours to do them all. (this was created before I ever worked here) What I'm hoping to do is alter the form so that a person could click on a check box next to each of the work orders, click a single button at the bottom of the form, and Access would generate production orders for every single work order checked at the same time. (The date and production shift they're working would be in a text box on the form and passed through to the orders.)

I have the code for generating each record, but the design relies on doing them one-by-one. Can code be written to see if a check box is activated and then run the code for that work order if it is? And if possible, run enough times to create multiple production orders if the user indicates how many?

I wish I could post the database, but our company prohibits it.

Thanks!

 

[jzwp22]

I would probably use a new, unbound form. On that form would be a text box for the date, and combo boxes for the shift and person in charge. I would then have a multi-select list box that has the available work orders, and finally, a command button.

The user would enter the date, select the shift and person in charge and then select the applicable work orders and then click the button. Code behind the button would loop through the selected list box items and each time through the loop append a new record to the production order table.

 

[themurph2000]

I would probably use a new, unbound form. On that form would be a text box for the date, and combo boxes for the shift and person in charge. I would then have a multi-select list box that has the available work orders, and finally, a command button.

The user would enter the date, select the shift and person in charge and then select the applicable work orders and then click the button. Code behind the button would loop through the selected list box items and each time through the loop append a new record to the production order table.

I thought about that, but the problem there is that some work orders need to have multiple records created, so there has to be a control number to iterate the loop for creating those records.

Although a list box would very much speed things up.

 

[jzwp22]

So a work order can have many related records created, that can be done also as long as there is a way to identify which of the work orders require multiple related records. What do you mean by control number?

It sounds like you'll have to provide some more info on your current tables and their relationships in order to help us better understand your application. Can you post a copy of your database with all data removed and possibly some dummy data added? If that is not possible either, then you will have to provide a list of your tables with their important fields and the relationships between the tables involved.

 

[themurph2000]

So a work order can have many related records created, that can be done also as long as there is a way to identify which of the work orders require multiple related records. What do you mean by control number?

It sounds like you'll have to provide some more info on your current tables and their relationships in order to help us better understand your application. Can you post a copy of your database with all data removed and possibly some dummy data added? If that is not possible either, then you will have to provide a list of your tables with their important fields and the relationships between the tables involved.

Here's a sample database that illustrates what I'm trying to do. But I just realized attempting to use a check box won't work because of the way the form is designed since I am uncertain if I can add a field to the table that would be the flag for the checkbox. (I have higher-ups I'd have to clear that with.)

But here it is if you have any ideas. I already have the code ready for doing the writing; it's setting up the multiple-rows vs. single rows I'm having the issue with.

 

[jzwp22]

On the form in the database you provided there is an unbound control: # Lines
What does this represent? I assume this has something to do with the multiple records you mentioned.

 

[themurph2000]

On the form in the database you provided there is an unbound control: # Lines
What does this represent? I assume this has something to do with the multiple records you mentioned.

That's exactly it. I plan to pass the value of that field into the VBA code to have the FOR loop to add records.

 

[jzwp22]

The continuous form will not work because if you check the box for 1 work order all will be checked. The list box option I discussed earlier also will not work if you each work order can have different values for the # Lines control.

What determines the value used in the #Lines control?

 

[themurph2000]

The continuous form will not work because if you check the box for 1 work order all will be checked. The list box option I discussed earlier also will not work if you each work order can have different values for the # Lines control.

What determines the value used in the #Lines control?

I drew the same conclusion myself. If I want to use the #Lines control, which the user would input (default of 1), I think what I'll have to do is just create a command button that will execute the AddRecord code. That's an improvement over what we have now, but I think TPTB in my office were hoping for something a little more grandiose in design. Click one button and everything's magically done. That sort of thing.

 

[jzwp22]

The only other thing I can thick of is to use the list box approach and then have the code prompt the user for the # lines interactively for each order selected and create that number of records.

 

[themurph2000]

The only other thing I can thick of is to use the list box approach and then have the code prompt the user for the # lines interactively for each order selected and create that number of records.

I was looking through the VBA library to come up with the right type of MsgBox to do that, but I couldn't find anything. Does anybody have samples of looping code where the program pauses to take an input from the user?

 

[jzwp22]

VBA has the Inputbox()

I will work on an example for you, please give me a little while.

 

[themurph2000]

VBA has the Inputbox()

I will work on an example for you, please give me a little while.

Thank you for all of your efforts on this. I'm trying to look up some examples myself, since the InputBox will also have to contain the name of the Work Order, as the user would have to know the number of records from that Work Order they want.

 

[jzwp22]

Sorry, it took a little longer than I thought. Here is the revised DB (I did change your table structure and used numeric key fields and eliminated redundant info in the production line table). The form frmAddProductionLineRecords creates the records.

 

[themurph2000]

Sorry, it took a little longer than I thought. Here is the revised DB (I did change your table structure and used numeric key fields and eliminated redundant info in the production line table). The form frmAddProductionLineRecords creates the records.

Oooh, a database dipped in awesome sauce. (Did I say that correctly? I'm trying to keep up with these kids...fershizzle. )

Thank you so much for all your help.

 

[jzwp22]

You're welcome! I gave up trying to keep up with my kids.