Entering multiple jobs done for the day

[Carl Foster]

I can't seem to find a way to solve this problem

I'm working on a database to run my window cleaning business. I have a customers table which contains all the details for each customer ie address, name etc.

There is also a Jobs table which will contain a record every time a customer's windows are cleaned.

There is a one to many relationship between them ie, 1 customer to many jobs.

I would like to create a form which would list all the customers which are due to be done and then be able to create a job record for each customer in the Jobs table, showing that it's done, with the click of a button.

For one, I can't get both tables to display on a continous form because of the relationship and then I don't know any other way of creating multiple new records in one go.

Would i have to use VB code for this or is there a simpler way? I just can't get my head around this one

 

[Banana]

No you don't have to use VBA. To be able to show several jobs records to one customer record, you need a subform for this.

Make your Customer form the master form, add some space for the Jobs form, then click on subform button. If you have wizard on, it'll help you through the steps.

HTH.

 

[Carl Foster]

Thanks for the reply i have tried that but it doesn't really do what i would like. Datasheet view is perhaps the closest, but any editing would require a lot of clicking by the user which I'm trying to avoid.

I was hoping for a simple continuous form with a list of check boxes for each address to say done, then simply click ok and new entries are made into the jobs table for each customer.

I hope you can see what I'm getting at

 

[Banana]

You can change the subform from a datasheet to continous form. In subform's data properties, set it to Continous Form.

HTH.

 

[pbaldy]

How about a multiselect listbox displaying customers? Select the ones that were done, and then add records to the jobs table for each selected.

 

[Carl Foster]

I'm not sure you understand what I'm looking for. I need to be able to see all the customers at once and update them. I've included a file to show you what I mean. Just click on the "update record" button to open the form.

As you will see, only one customer will show.

thanks for your help
Carl

 

[Banana]

That's exactly what we are proposing-

Remove the Job details section and paste it into a new blank form. Drag that form (in database windows) to the form you just cut. It will now appear as a subform. You can then set it as continous if you want.

 

[Carl Foster]

Thanks Banana, I think I know what you mean. I'll give it a try

 

[pbaldy]

Somebody here is on the wrong page, and the odds are fairly good that it's me. Having said that, the form/subform is the appropriate way to view existing data, but I don't think it will let you "simply click ok and new entries are made into the jobs table for each customer". For that, you're either going to need a new field in your table (to bind a checkbox to), or use my suggestion in post 5. Either one will require VBA, though the code would not be particularly tricky. I think the listbox is easiest, which is why I suggested it.

 

[Carl Foster]

Yes Paul, you're right. I've tried form/subform, but that doesn't work for new data.

What I'm gonna have to do is create a form with the customers table, then place unbound check boxes for each customer with code to create a new entry in the jobs table for each customer that is marked as done.

There is a corresponding field in the jobs table for thee done check box, but I may not need it.

I'll probably have to do some reading to work out the code to use ADO to create the records, shouldn't be too hard though. Thanks for all your help guys.

 

[pbaldy]

What I'm gonna have to do is create a form with the customers table, then place unbound check boxes for each customer with code to create a new entry in the jobs table for each customer that is marked as done.

That's a "job security" type of solution. Any time you add/delete a customer you'll have to modify the form? I'll mention one last time that the simplest solution is a multiselect listbox based on the customer table (it could even be filtered to only show customers with a due date of today/this week/whatever).

 

[Carl Foster]

I'll try the listbox solution Paul. I haven't used list boxes very much so I can't really "see" the solution, so i'll just have to play around with it and hopefully I can get it to do what I'm looking for.

Thanks again.