Updating a subform

[blueboy2001]

I have a subform displayed on a main form that shows the output from a query. The grid should show the pieces of equipment assigned to a job.

When I create a new job and add a piece of equipment, it doesn't appear in the grid unless I close the form and open it again. If I add another piece of equipment to an existing job it works fine.

If I run the query that holds the data for the grid, the query window shows the equipment that is assigned to that job as it should do, so the data is right but the grid remains blank.

The command button on the form to add the equipment runs an append query to put the data into the table (which it must be doing correctly, because opening the query from the same macro displays the correct data), then it requerys the grid, but still nothing is displayed.

Have I missed something obvious?

 

[Ally]

You need to requery your form via code. If you check out this thread posted by Mile-O there's a really helpful sheet on what to put where.

Click Here

Post back if you have any more questions.

 

[blueboy2001]

Requerying the subform doesn't seem to help where its the first piece of equipment thats being added. If I add a second piece of equipment and click the add button, it requerys instantly and displays the list of equipment.

If I requery the main form, the the list appears but that means it defaults to the first record in the job file.

 

[Rich]

Why are you using an append query?

 

[blueboy2001]

Why am I using an append query?

Because I need to be able to select multiple pieces of equipment for each job. The user selects the equipment from a combobox and clicks the command button which appends the jobid and equipmentid to the jobequipment table. As there needs to be multiple entries, I found a bound combobox was no use and after a search on here I discovered an append query would be a way to accomplish it.

As far as I can see the append query works ok, because if I open the query that drives the datagrid immediately after the append query has run using a macro, it shows the appropriate entries in the table.

Pat Hartman mentioned in another thread I could accomplish this with a using a subform. Would this be a better idea?

 

[Rich]

You have a design problem, since one job can have many items of equipment then equipment items belong in a separate table. You can then use a subform and add many pieces of equipment for each job. I also would use a combo box to get the equipment items from a lookup table

 

[blueboy2001]

I have got separate tables for jobs (tbljob), equipment used on jobs (tbljobequipment) and for equipment (tblequipment). tbljobequipment holds jobid and equipmentid.

Having been playing around with this all morning, a subform seems to be the way to go.

What do you mean when you refer to a lookup table?

 

[Rich]

Use tblEquipment as the Row source of your combo for your subform jobEquipment

 

[blueboy2001]

Thanks Rich, I've got the whole thing working now. I've had to use a query to populate the subform as it was producing a list of all the records in the jobequipment table.

My next problem is how can I stop the user selecting the same piece of equipment twice? If they do it chucks up an error the form has to be closed.