Adding records by using checkbox

[wnicole]

Need help.


The DB I have uses checkbox 2, 4, 6 in terms of how many days each job would take. These are the only 3 blocks of days that can be used so check boxes were made to ensure they only choose this block of days

However, when for example 2 is check off (for 2days job will go on) I want duplicate records to generate for each day. Make sense?

So 9/27/2013 is the start day of Job. I check off box 4
Then 9/28/2013
9/29/2013
and
9/30/2013
is automatically generated with duplicated information.

We don't want to keep hitting the duplicate button and having to change the date for 6 jobs.

Is this possible?

[ItsMe]

The only way I can see to accomplish this is through the VBA editor. You will need to write a sub routine that will analyze what the user select and then update the appropriate records. Your sub routine can fire via a click event or maybe an after update. Click event will probably be safest.

You can update records using an SQL UPDATE or a loop command.

[wnicole]

I so hoped I was this talented. Can u assist me with writing the subroutine or direct me where I can go?
Appreciate it :-)

[ItsMe]

This is a complicated procedure you are trying to accomplish. In order for the DB to perform the request there will need to be proper table design. Relations are key here so you have a WAY to write the code.

Reading your question in post #1 raises concern that your table structure may not be what it could be. You may be working harder than need be. In fact, the solution you seek and the route you are considering as a solution may be the product of poor design.

I would start by rethinking why you need to duplicate records. You may not need to. However, even if you do not need to duplicate records you will still need to understand how to query the results you desire from the tables. So, catch 22. No way around not knowing how to code.

You can start with researching a CASE statement. You present a scenario where a user says something. In the CASE they say, "4", you need to pass that value to the program. Once the program has the user's input stored in a variable, you can begin doing something with it.

In your case I may start by declaring a variable that the program can store the user's input in.

Dim intUserInput as Integer

Here I imagine an intuitive name to name my variable and assign the data type Integer to it. This example is not addressing related issues to data types and validation and truncated values.

Now I can pass the user input to the declared variable.

intUserInput = Me.txtUserInput.Value

The example assumes the user is typing data into a text box named txtUserInput within a form.

Now you can apply a CASE statement to determine what to do with the user input.

CASE Select intUserInput

Case "2"
[Insert statement that does something twice]

Case "4"
[Insert statement that does something four times]

Case Else
MsgBox "The data base does not like your Input"

End Select

I suggest you play around with collecting user input and doing things with said input. You can build your own practice data bases to explore, IF THEN ELSE and CASE SELECT statements along with a MsgBox response. If this is something you feel comfortable with then you might want to do more exploring.

[wnicole]

I was thinking of changing it as well to maybe a combo box not a check box. With the option 2, 4, 6.
Depending on which is chosen is there a way to create duplicate records for that many future dates?

[ItsMe]

After you play around with user input for a while you will discover ways to control what the user can "Say" or "Tell" the DB to do. A combo box is a great way to control the options. Another approach would be an option group you create with the wizard. I generally don't recommend things like this because option groups hide what is really going on in a macro.

Whatever you decide, I would stay away from assigning lookup fields to your tables. I was recently reminded this when I looked at an example that contained lookup fields.