Update a table from a form?

[lavaghman]

I have data from 1 table displayed on a form. When on various different records in the form I need the ability to add the unique ID of each of these records to another table……HOW?

Do I use the runSQL macro as a button on my form and if so how do I pass the ID of the current record so that it’s only that record that gets appended to the other table ???

(what I have is a table with Employee records with their data displayed in a form and I want to be able to assign different employees to different upcoming jobs/tasks……….is there any sample apps out there that do something like this??)


Any help or advice much appreciated.
Thanx, Lavaghman

 

[jason246day]

I would say that you should create a subform to show the different jobs assigned to an employee. This way, you will also be able to show more than one job assigned to an employee. Your subform will then connect to your main form through a foreign key. That way, anytime you change to a new employee's record, it will show only the jobs associated to that particular employee. Does this make sense?

 

[lavaghman]

yes, I've tried what you said and I think it might work for me but is it possible to not display the subform on top of the main form but instead to use a button to display the subform. I've inserted a button on the main form to display the subform but it shows all records in the table and not just the ones associated with the current record on the main form??
Thanx

 

[jason246day]

How is the subform displayed? Does a new form pop up?

 

[lavaghman]

I used the subform wizard to create a new subform which is displayed on the main form and this works fine but I don't want to see the subform on my main form, i want to link to it with a button.....so I inserted a button on the main form using the wizard and selected 'form operations', 'open form' and selected the subform i had just created. When i click this button it opens the subform but does not display the record that corresponds to the record open in the main form?


Thanks again

 

[jason246day]

You have to specify the criteria to link the forms on. In the form properties of your subform, you need to check out the Record Source option. Click the ... that appears next to the box, and it should take you to the query builder. Now you should find the value that links your tables together. In the citeria of this field you should have some code that will link this field to a field on your main form. Then it will only display the entries on the subform form where the values are equal. The criteria should look something like this:

=Forms![main_form]![linking_attribute]

 

[lavaghman]

Thanks for your time & advise. It makes sense and i'm going to give it a try now.
Cheers, Lavaghman

 

[jason246day]

Excellent, let me know if you can't get it working.