Assign a record to a table

[aiikahn]

I was wondering if anyone could help me with a problem I'm having with a form...

I have 2 tables, one called 'dept' and the other called 'employee.' These tables have a one-to-many relationship; basically, one department to many employees.

Instead of using a combo box, how do I assign an employee (employee_table) to a department (dept_table) by simply selecting a checkbox on a tabular (employee) form and the record is automatically added to the dept table?

I would greatly appreciate any help. Thank you!!!

 

[Jonathan Kok]

Is the checkbox meant as a, "this department does not exist, so add it" sort of thing?
You could do it via code and recordsets.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db, recordset1
Set db = CurrentDb()
Set recordset1 = db.OpenRecordset("depttable")
With recordset1
    .AddNew
    !deptname = deptnamefromcurrentform
    .Update
End With
End Sub

 

[aiikahn]

I think I figured it out but thank you so much for the response. The way it's supposed to work is that when I open the dept_form and click on a command button to open a tabular form with a list of employees not assign to any department, when checkbox is checked it would assign the particular employee to the record of the dept previously opened...

I'm not sure if I'm explaining myself clearly but I figured out another way by using the DLOOKUP function. Thank you soooo much for the response!!! I'm sure I'd be able to utilize that code somewhere else. I'm still kinda new to this...Thank you again!!!!

However, I do have a new problem...
I've managed to when I double-click to current record on a form, I'm able to open a new form to display the details of the employee. Unfortunately, I'm unable to update it....how do I go about update certain fields on a FILTERED form?

 

[Jonathan Kok]

Filtering shouldn't have any effect on being able to update a record. Either you've accidentally disabled edits on the form, or the source query is configured in such a way that it can't allow for updates.

 

[aiikahn]

Hmm?...I'm not sure...it's giving me an error saying that the table is currently being using by another program or user even though the form is already closed and the only form that's open is the filter form...I'll have to check that out again...

 

[Jonathan Kok]

Does the same form access the same information multiple times? IE, is there a subform on the main form that uses info from the same table as the main form?